Exasol Analytic Data Warehouse Features for SQL Developer and Database Administrator

In this Exasol Analytic Data Warehouse guide, I want to list some features of Exasol database for SQL developers, database administrators and data professionals including architects, data scientists, self-service BI consumers, etc. Exasol is the fastest analytic database in the world according to benchmarks for TPC-H data. It provides smart in-memory processing algorithms to auto tune itself for best performance possible. As some database management and SQL query tuning tasks are done in background automatically, it is an easy to manage data warehouse platform and provides high performance.

For data professionals who want to try Exasol Analytic Data Warehouse and experience the features that we will discuss here briefly, you can use the public demo by registering to request access to publicly available demo database of Exasol, and download the Exasol Virtual Machine and test it on your local computer.

In addition to trial options, SQL developers and database administrators can enroll to the Exacademy courses to learn more about the Exasol Analytic Database in more detail

Exasol provides a free to use single node database aka Exasol Community Edition.
It is also free for Exasol license, use of Exasol database up to 200 GB of raw data. Please visit Community Edition page to download the Exasol Community Edition free for your personal use, for your projects, own developments and as a trial environment.

Basic architecture of Exasol Analytic Database is MPP or Massive Parallel Processing architecture.

Exasol Data Warehouse is a distributed database which runs normally as a multinode cluster which provides a fault tolerant and high performance production environment.

As all MPP data warehouse platforms, Exasol supports use of Distribution Keys which provides a means of how to decide distribution of a database table's data among cluster nodes.
A correctly chosen distribution key decreases the traffic between data nodes during the execution of database queries and enables local joins instead of global joins to increase performance.
Distribution key should be chosen between the fields used most frequently to join big data tables in SQL queries.
Exasol has iproc() and value2proc() functions which let's database developer and administrators to check the existing data distribution of a table between data nodes, or possible distribution figures if an other table field is selected as the distribution key.
Developers and admins can easily identify the data screw cases which are a result of non-uniform data distribution between data nodes.

In addition to distribution keys, Exasol also supports Partition Keys which identifies the data block of a specific table row. Partition keys is a way of sorting data within a table in each data node.
Distribution keys should be selected among the fields which are most frequently used in WHERE clauses of SQL queries.
A wisely use of distribution keys brings data pruning during the execution of SQL queries which improves the performance of database.

Exasol is a self-tuning database. It creates and maintains indexes on database tables automatically.
For each table join, Exasol creates an index before joining these two tables. If an index exists before it uses the index.
If an index is not used for more than 5 weeks, it is dropped automatically.

Exasol has a colum-oriented storage model as it is the most suitable data storage model for analytical workloads which require aggregation functions on selected few columns in a database table.
Column-oriented storage boosts performance with read operations on analytic databases.

Use of column-store tables enables high ratios of compression within data blocks keeping same type of data and mostly repeating data values.
Compressed data storage enables to store raw data up to its one third of original sizes in Exasol.
With data stored in less sized, it is faster to access and process data in Exasol database.

Another important architectural feature of Exasol is its in-memory processing feature.
With intelligent in-memory algoriths Exasol keeps the frequently used data blocks or hot data in memory instead of reading from disk everytime. As an in-memory analytic database, reading most used data in memory, disk I/O is minimized for performance.
It is important to prevent misunderstanding here, not all data is stored in memory like SAP HANA database. But it is not required to keep all of the data in memory according to Exasol's architectural approach.

Using reserved nodes or stand-by nodes the multi-node cluster architecture with data redundancy enables automatic fail-over or fail-safety for data warehouse administrators.
Data is not lost in a disaster scenario in this case. Recovery is most of the times transient for the end-users.

Exasol database run on EXAClusterOS operating system designed by Exasol for the performance, high availability and management features of the data platform.

Exasol database can run on commodity hardware.

Exasol can be deployed on premise environments and on cloud platforms including AWS, Azure and Google Cloud easily.

It is easy to integrate Exasol DWH to any data warehouse architecture either on premise or on cloud.
With provided native drivers, data virtualization capabilities and integration with standard tools, Exasol database can be easily used as a data warehouse solution on any architecture.

Exasol supports most of Standard SQL features introduced 2008. Database developers can easily use their SQL codes on their previous data platforms without any code change or with minor modifications by removing non-standard functions provided by previous platform.

Major SQL clients can easily be used for developing on Exasol. DbVisualizer, DBeaver, DataGrid and many others can be used with Exasol database.

Following screen is showing the reports on the monitoring dashboard for Exasol database of DbVisualizer Pro SQL Client tool.

For increasing query performance and tune deeper, SQL developers can use profiling features of Exasol. Either during execution of a SQL query or after the queries are executed, it is possible to display SQL profile data and processed tasks using system profie tables.

Exasol provides native drivers for other platforms to connect to Exasol database and developers to access Exasol within their codes. ODBC, JDBC and ADO.NET drivers exists beside Python libraries enabling access to Exasol data warehouse.

Many BI tools can easily connect Exasol database and can be used as a data souce for their reports, dashboard applications, etc.

Common ETL tools used in IT environments can connect Exasol with ease.

Exasol provided Import and Export commands for data ingestion and data export between other data platforms and object stores.

Exasol released an XML/RPC Interface which enables management of Exasol cluster using command line entries or programmatically.

Using logging, adminsitrators can display useful information about what is going on within the Exasol cluster.

Automatic and manual backups can be taken and scheduled easily.

For online help, Exasol has a portal for online documentation. It is an other valuable resource just like Exacademy to learn and troubleshoot on issues about database and SQL development. Please visit Exasol Documentation for the latest version.

As many SQL objects, schemas are supported on Exasol database.

Virtual schemas are just like any other schema but provides access to remote data platforms and enables virtual access or a virtualization layer for accessing remote data sources.

Beside supporting standard SQL, Exasol has many built-in functions introduced with the database.

Analytical functions are supported on Exasol.

SQL Merge command which enables executing Insert and Update statements at the same time, is supported on Exasol. This is an important SQL command especially for custom ETL solutions which inserts new data as well as updates existing data with newer values.

Exasol supports isolation level serializable for ACID complaint transactions.

On Exasol, database administrators and SQL developers can query system tables under SYS and EXA_STATISTICS schemas to manage and monitor tasks running on Exasol, etc.

Auditing component enables data warehouse administrators audit their data platform and control accesses to the Exasol databases.

Using session management, it is possible to display and monitor active sessions and tasks they are executing on the Exasol database

Just like transactional databases aka OLTP databases, Exasol supports Primary Keys for data integrity. But for performance during bulk write operations, it is better to deactivate the primary keys and then identify the data rows violating the primary key (and foreign key) constraints.

For user management on Exasol database, platform supports LDAP and Kerberos integrations

For workload management, Exasol provides use of priority groups which can be customized easily. Priority groups decide how much cluster resources a query or SQL task consume.

In Exasol, SQL developers can build solution with LUA script which is easy to learn. Using LUA script a SQL developer can build UDF scripts and procedures on Exasol database.

Exasol enables correction or translation of user SQL commands during execution time by preprocessor scripts.
Preprocessor scripts can be used for non-compliant legacy systems' SQL codes by translating them into correct forms on Exasol.

UDF scripts enable developers to store their codes into reusable parts, codes or functions which can be executed with parameters

Extensible script languages is a way of introducing new programming languages to Exasol Data Warehouse platform for specific certain development tasks.
Right now, by default, Exasol supports Java, Lua, Python and R. But users can extend these languages by adding new ones.

Using Python and R libraries, and using these languages as script development languages, it is possible to built machine learning algorithms and use them in Exasol. With consume of machine learning libraries, Exasol provides an in-database analytics environment for data scientists too.

Exasol provides Skyline queries which use Preferring clause, intoducing performance as an alternative algoritm to windowing functions consuming more resources on analytical databases when compared with OLTP databases.

Exasol supports geospatial data and provides a set geospatial data types and functions.

Union operations in use with Group By operations are very costly. In Exasol database, it is possible to use Grouping Sets clause as well as Rollup and Cube operators.

In Exasol database, SQL developers can use Connect By clause for querying hierarchical data and build recursive queries.

Query Cache feature enables Exasol cluster to cache query results in memory and use this data for similar requests to increase performance.

Exasol has a number of connectors published on Github repository which enables access to Hadoop, Spark, Kafka environments.

According to TPC-H analytical benchmark results, Exasol is the fastest analytical database in the world. Please review TPC-H - Top Ten Performance Results for Exasol Analytic Data Warehouse performance.

One last important note, although I have been experiencing on Exasol, got certified and using on my work environment, I took the video published at 50 Awesome features of the Exasol database as a baseline for this Exasol tutorial by adding my comments and knowhow coming from experience.