Infrastructure at your Service

Daniel Westermann

Getting started with Exasol – Setting up an environment

Exasol is an in memory database with parallel processing, or shorter MPP(Massively Parallel Processing) database. I got in touch with some people from Exasol at the DOAG 2019 in Nuremberg but never had the time to take a closer look since then. Over the past few weeks however I finally had the chance to get in touch with the system and thought it might be a good idea to share what I learned by writing a couple of blog posts. This is the first one in this series and I’ll start with the basics: Getting a test system up and running, and once it is there how to connect to it, with either a graphical user interface or by using the command line. In the coming post we’ll look at how Exasol handles transactions, how you can connect Exasol to PostgreSQL and how you can import and export data, and few other topics that differentiate Exasol from other database systems.

For getting a test system up and running you basically have three choices:

  • Deployment in a private cloud
  • Deployment in a public cloud (AWS, Azure, Google)
  • Use the community edition (limited to 200GB of data but with all the features), either as an OVA file or as an ISO for installation

I went with the community edition and you can request a download here.

Once you either have installed from the ISO or imported the OVA you should see a screen like this which gives you the basic information you need to connect the system:

One graphical tool you can use to connect to Exasol is dbeaver. Setting up a connection to Exasol in dbeaver is just a matter of a few clicks:

Another option you have is the command line. The client is called EXAPlus and can be downloaded from the Exasol’s download page. The client is written in Java and should run on any operating system.

When start that up and connect the Exasol system you’ll notice that it pretty much feels like SQL*Plus from Oracle:

[email protected]:~/EXAplus-7.0.0$ ./exaplus -c 192.168.22.117:8563 -u sys -p exasol
EXAplus 7.0.0 (c) EXASOL AG

Monday, September 28, 2020 at 4:30:32 PM Central European Summer Time
Connected to database EXAone as user sys.
EXASolution 7.0.2 (c) EXASOL AG

SQL_EXA> select * from exa_parameters;
EXA: select * from exa_parameters;

PARAMETER_NAME                
------------------------------
SESSION_VALUE                                                                                                                                                                                           
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM_VALUE                                                                                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT               
YYYY-MM-DD                                                                                                                                                                                              
YYYY-MM-DD                                                                                                                                                                                              
NLS_FIRST_DAY_OF_WEEK         
7                                                                                                                                                                                                       
7                                                                                                                                                                                                       
NLS_TIMESTAMP_FORMAT          
YYYY-MM-DD HH24:MI:SS.FF6                                                                                                                                                                               
YYYY-MM-DD HH24:MI:SS.FF6                                                                                                                                                                               
NLS_NUMERIC_CHARACTERS        
.,                                                                                                                                                                                                      
.,                                                                                                                                                                                                      
NLS_DATE_LANGUAGE             
ENG                                                                                                                                                                                                     
ENG                                                                                                                                                                                                     
QUERY_TIMEOUT                 
0                                                                                                                                                                                                       
0                                                                                                                                                                                                       
CONSTRAINT_STATE_DEFAULT      
ENABLE                                                                                                                                                                                                  
ENABLE                                                                                                                                                                                                  
PROFILE                       
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
TIME_ZONE                     
UTC                                                                                                                                                                                                     
UTC                                                                                                                                                                                                     
TIME_ZONE_BEHAVIOR            
INVALID SHIFT AMBIGUOUS ST                                                                                                                                                                              
INVALID SHIFT AMBIGUOUS ST                                                                                                                                                                              
DEFAULT_CONSUMER_GROUP        
MEDIUM                                                                                                                                                                                                  
MEDIUM                                                                                                                                                                                                  
NICE                          
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
SQL_PREPROCESSOR_SCRIPT       
                                                                                                                                                                                                        
                                                                                                                                                                                                        
QUERY_CACHE                   
ON                                                                                                                                                                                                      
ON                                                                                                                                                                                                      
DEFAULT_LIKE_ESCAPE_CHARACTER 
\                                                                                                                                                                                                       
\                                                                                                                                                                                                       
TIMESTAMP_ARITHMETIC_BEHAVIOR 
INTERVAL                                                                                                                                                                                                
INTERVAL                                                                                                                                                                                                
SCRIPT_LANGUAGES              
PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3                                                                                                                             
PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3                                                                                                                             
SCRIPT_OUTPUT_ADDRESS         
                                                                                                                                                                                                        
                                                                                                                                                                                                        
PASSWORD_EXPIRY_POLICY        
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
PASSWORD_SECURITY_POLICY      
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
HASHTYPE_FORMAT               
HEX                                                                                                                                                                                                     
HEX                                                                                                                                                                                                     
TEMP_DB_RAM_LIMIT             
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
USER_TEMP_DB_RAM_LIMIT        
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
SESSION_TEMP_DB_RAM_LIMIT     
OFF                                                                                                                                                                                                     
OFF                                                                                                                                                                                                     
ST_MAX_DECIMAL_DIGITS         
16                                                                                                                                                                                                      
16                                                                                                                                                                                                      

25 rows in resultset.

SQL_EXA> col session_value for a80;
COLUMN   session_value ON
FORMAT   a80
SQL_EXA> col system_value for a80;
COLUMN   system_value ON
FORMAT   a80
SQL_EXA> select * from exa_parameters;
EXA: select * from exa_parameters;

PARAMETER_NAME                 SESSION_VALUE                                                                    SYSTEM_VALUE                                                                    
------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
NLS_DATE_FORMAT                YYYY-MM-DD                                                                       YYYY-MM-DD                                                                      
NLS_FIRST_DAY_OF_WEEK          7                                                                                7                                                                               
NLS_TIMESTAMP_FORMAT           YYYY-MM-DD HH24:MI:SS.FF6                                                        YYYY-MM-DD HH24:MI:SS.FF6                                                       
NLS_NUMERIC_CHARACTERS         .,                                                                               .,                                                                              
NLS_DATE_LANGUAGE              ENG                                                                              ENG                                                                             
QUERY_TIMEOUT                  0                                                                                0                                                                               
CONSTRAINT_STATE_DEFAULT       ENABLE                                                                           ENABLE                                                                          
PROFILE                        OFF                                                                              OFF                                                                             
TIME_ZONE                      UTC                                                                              UTC                                                                             
TIME_ZONE_BEHAVIOR             INVALID SHIFT AMBIGUOUS ST                                                       INVALID SHIFT AMBIGUOUS ST                                                      
DEFAULT_CONSUMER_GROUP         MEDIUM                                                                           MEDIUM                                                                          
NICE                           OFF                                                                              OFF                                                                             
SQL_PREPROCESSOR_SCRIPT                                                                                                                                                                         
QUERY_CACHE                    ON                                                                               ON                                                                              
DEFAULT_LIKE_ESCAPE_CHARACTER  \                                                                                \                                                                               
TIMESTAMP_ARITHMETIC_BEHAVIOR  INTERVAL                                                                         INTERVAL                                                                        
SCRIPT_LANGUAGES               PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3      PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3     
SCRIPT_OUTPUT_ADDRESS                                                                                                                                                                           
PASSWORD_EXPIRY_POLICY         OFF                                                                              OFF                                                                             
PASSWORD_SECURITY_POLICY       OFF                                                                              OFF                                                                             
HASHTYPE_FORMAT                HEX                                                                              HEX                                                                             
TEMP_DB_RAM_LIMIT              OFF                                                                              OFF                                                                             
USER_TEMP_DB_RAM_LIMIT         OFF                                                                              OFF                                                                             
SESSION_TEMP_DB_RAM_LIMIT      OFF                                                                              OFF                                                                             
ST_MAX_DECIMAL_DIGITS          16                                                                               16                                                                              

25 rows in resultset.

SQL_EXA> 

Even some of the parameters like the various “NLS_*” settings are the same than in Oracle. If I understood correctly, Exasol, in the beginning, was an accelerator on top of Oracle and that would explain why some aspects of the system are very close to it.

An Exasol database always contains two schemas, SYS and EXA_STATISTICS but you won’t see them in the corresponding system catalog view:

SQL_EXA> select * from EXA_DBA_SCHEMAS;
EXA: select * from EXA_DBA_SCHEMAS;

SCHEMA_NAME     SCHEMA_OWNER    SCHEMA_OBJECT_ID      SCHEM SCHEMA_COMMENT                
--------------- --------------- --------------------- ----- ------------------------------

0 rows in resultset.

Again, it pretty much feels like Oracle in the beginning. All the catalog views start with EXA_DBA_/EXA_USER_ or EXA_ALL_ (and yes, tab-completion works in EXAPlus):

SQL_EXA> select * from EXA_
Display all 143 possibilities? (y or n) 
EXA_ALL_COLUMNS                           EXA_ALL_VIRTUAL_TABLES                    EXA_DBA_RESTRICTED_OBJ_PRIVS              EXA_MONITOR_DAILY                         EXA_STATISTICS                            EXA_USER_OBJ_PRIVS_MADE
EXA_ALL_COLUMN_SIZES                      EXA_CACHE_CONTENT                         EXA_DBA_ROLES                             EXA_MONITOR_HOURLY                        EXA_STATISTICS_OBJECT_SIZES               EXA_USER_OBJ_PRIVS_RECD
EXA_ALL_CONNECTIONS                       EXA_CLUSTERS                              EXA_DBA_ROLE_PRIVS                        EXA_MONITOR_LAST_DAY                      EXA_SYSCAT                                EXA_USER_PROFILE_LAST_DAY
EXA_ALL_CONSTRAINTS                       EXA_CONSUMER_GROUPS                       EXA_DBA_SCHEMAS                           EXA_MONITOR_MONTHLY                       EXA_SYSTEM_EVENTS                         EXA_USER_PROFILE_RUNNING
EXA_ALL_CONSTRAINT_COLUMNS                EXA_DBA_AUDIT_IMPERSONATION               EXA_DBA_SCHEMA_OBJECTS                    EXA_OBJECTSTORAGE_USAGE                   EXA_SYS_COLUMNS                           EXA_USER_RESTRICTED_OBJ_PRIVS
EXA_ALL_DEPENDENCIES                      EXA_DBA_AUDIT_SESSIONS                    EXA_DBA_SCRIPTS                           EXA_PARAMETERS                            EXA_SYS_SCRIPTS                           EXA_USER_ROLE_PRIVS
EXA_ALL_FUNCTIONS                         EXA_DBA_AUDIT_SQL                         EXA_DBA_SESSIONS                          EXA_ROLE_CONNECTION_PRIVS                 EXA_TIME_ZONES                            EXA_USER_SCHEMAS
EXA_ALL_INDICES                           EXA_DBA_COLUMNS                           EXA_DBA_SESSIONS_LAST_DAY                 EXA_ROLE_OBJ_PRIVS                        EXA_USAGE_DAILY                           EXA_USER_SCHEMA_OBJECTS
EXA_ALL_OBJECTS                           EXA_DBA_COLUMN_SIZES                      EXA_DBA_SESSION_RESOURCES                 EXA_ROLE_RESTRICTED_OBJ_PRIVS             EXA_USAGE_HOURLY                          EXA_USER_SCRIPTS
EXA_ALL_OBJECT_SIZES                      EXA_DBA_CONNECTIONS                       EXA_DBA_SYS_PRIVS                         EXA_ROLE_ROLE_PRIVS                       EXA_USAGE_LAST_DAY                        EXA_USER_SESSIONS
EXA_ALL_OBJ_PRIVS                         EXA_DBA_CONNECTION_PRIVS                  EXA_DBA_TABLES                            EXA_ROLE_SYS_PRIVS                        EXA_USAGE_MONTHLY                         EXA_USER_SESSIONS_LAST_DAY
EXA_ALL_OBJ_PRIVS_MADE                    EXA_DBA_CONSTRAINTS                       EXA_DBA_TRANSACTION_CONFLICTS             EXA_SCHEMAS                               EXA_USER_COLUMNS                          EXA_USER_SESSION_RESOURCES
EXA_ALL_OBJ_PRIVS_RECD                    EXA_DBA_CONSTRAINT_COLUMNS                EXA_DBA_USERS                             EXA_SCHEMA_OBJECTS                        EXA_USER_COLUMN_SIZES                     EXA_USER_SYS_PRIVS
EXA_ALL_ROLES                             EXA_DBA_DEPENDENCIES                      EXA_DBA_VIEWS                             EXA_SESSION_CONNECTIONS                   EXA_USER_CONNECTION_PRIVS                 EXA_USER_TABLES
EXA_ALL_SCHEMAS                           EXA_DBA_DEPENDENCIES_RECURSIVE            EXA_DBA_VIRTUAL_COLUMNS                   EXA_SESSION_PRIVS                         EXA_USER_CONSTRAINTS                      EXA_USER_TRANSACTION_CONFLICTS_LAST_DAY
EXA_ALL_SCHEMA_OBJECTS                    EXA_DBA_FUNCTIONS                         EXA_DBA_VIRTUAL_SCHEMAS                   EXA_SESSION_ROLES                         EXA_USER_CONSTRAINT_COLUMNS               EXA_USER_USERS
EXA_ALL_SCRIPTS                           EXA_DBA_IMPERSONATION_LAST_DAY            EXA_DBA_VIRTUAL_SCHEMA_PROPERTIES         EXA_SNAPSHOT_BACKUP                       EXA_USER_DEPENDENCIES                     EXA_USER_VIEWS
EXA_ALL_SESSIONS                          EXA_DBA_IMPERSONATION_PRIVS               EXA_DBA_VIRTUAL_TABLES                    EXA_SPATIAL_REF_SYS                       EXA_USER_FUNCTIONS                        EXA_USER_VIRTUAL_COLUMNS
EXA_ALL_TABLES                            EXA_DBA_INDICES                           EXA_DB_SIZE_DAILY                         EXA_SQL_DAILY                             EXA_USER_IMPERSONATION_LAST_DAY           EXA_USER_VIRTUAL_SCHEMAS
EXA_ALL_USERS                             EXA_DBA_OBJECTS                           EXA_DB_SIZE_HOURLY                        EXA_SQL_HOURLY                            EXA_USER_IMPERSONATION_PRIVS              EXA_USER_VIRTUAL_SCHEMA_PROPERTIES
EXA_ALL_VIEWS                             EXA_DBA_OBJECT_SIZES                      EXA_DB_SIZE_LAST_DAY                      EXA_SQL_KEYWORDS                          EXA_USER_INDICES                          EXA_USER_VIRTUAL_TABLES
EXA_ALL_VIRTUAL_COLUMNS                   EXA_DBA_OBJ_PRIVS                         EXA_DB_SIZE_MONTHLY                       EXA_SQL_LAST_DAY                          EXA_USER_OBJECTS                          EXA_VIRTUAL_SCHEMAS
EXA_ALL_VIRTUAL_SCHEMAS                   EXA_DBA_PROFILE_LAST_DAY                  EXA_LOADAVG                               EXA_SQL_MONTHLY                           EXA_USER_OBJECT_SIZES                     EXA_VOLUME_USAGE
EXA_ALL_VIRTUAL_SCHEMA_PROPERTIES         EXA_DBA_PROFILE_RUNNING                   EXA_METADATA                              EXA_SQL_TYPES                             EXA_USER_OBJ_PRIVS

The “SYS” schema contains all meta data definitions while the the EXA_STATISTICS schema contains monitoring and use statistics of the system, like EXA_MONITOR_HOURLY:

SQL_EXA> col cluster_name for a15;
COLUMN   cluster_name ON
FORMAT   a15
SQL_EXA> select INTERVAL_START,CLUSTER_NAME,LOAD_AVG,LOAD_MAX,CPU_AVG,CPU_MAX from EXA_MONITOR_HOURLY;
EXA: select INTERVAL_START,CLUSTER_NAME,LOAD_AVG,LOAD_MAX,CPU_AVG,CPU_MAX f...

INTERVAL_START             CLUSTER_NAME    LOAD_A LOAD_M CPU_AV CPU_MA
-------------------------- --------------- ------ ------ ------ ------
2020-09-28 08:00:00.000000 MASTER             0.4    2.3    2.1    4.7
2020-09-28 09:00:00.000000 MASTER             0.1    0.4    2.8   12.0
2020-09-28 10:00:00.000000 MASTER             0.0    0.3    1.9    6.8
2020-09-28 11:00:00.000000 MASTER             0.1    0.6    2.6    6.8
2020-09-28 12:00:00.000000 MASTER             0.0    0.3    2.5    7.2
2020-09-28 13:00:00.000000 MASTER             0.1    0.6    2.7   12.9
2020-09-28 14:00:00.000000 MASTER             0.0    0.2    2.1    6.3
2020-09-28 15:00:00.000000 MASTER             0.0    0.2    1.8    6.1
2020-09-28 16:00:00.000000 MASTER             0.0    0.2    2.1    6.3

9 rows in resultset.

When it comes to users and schemas Exasol compares more to PostgreSQL or SQL Server. A user can create multiple schemas and schemas can be owned by a user, e.g.:

SQL_EXA> create schema s1;
EXA: create schema s1;

Rows affected: 0

SQL_EXA> create schema s2;
EXA: create schema s2;

Rows affected: 0

SQL_EXA> create user u1 identified by "u1";
EXA: create user u1 identified by "u1";

Rows affected: 0

SQL_EXA> alter schema s1 change owner u1;
EXA: alter schema s1 change owner u1;

Rows affected: 0

SQL_EXA> 

Now that we know how to setup a simple test system, how to connect and how the catalog is organized we can go into the details. But that will be the topic of the next post.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure