Friday, July 22, 2016

EXASOL - Query Tool Options

Tools to see inside EXASOL -  query and data

From what we have discovered, EXASOL has a split management approach.  General administrative functions are managed using a web portal, while data related functions are handled by a SQL query tool.  For this post our focus covers traditional DDL/DML tasks using a query styled tool.  In a future post, we'll look deeper into the web portal  'EXAoperation'  that supports general administration and operations.

If you are a SQL Server shop, and you use SQL Server Management Studio you already know that it is focused on SQL Server.  For EXASOL, SSMS is not an option.  So, while EXASOL provides the EXAplus query tool, we wanted to find out if other tools could be used to query EXASOL - from our experience, developers like to stay with the tools they have.  Plus, before moving into a new data framework, we like to know our options.  This is not by any means an exhaustive list. Just a short list of tools we have worked with in the past that we have found useful. Plus, the other tools are free.  


    Connecting to EXASOL:
Windows 10
EXAplus, v 5.0.15
DBeaver, v3.7.1
Success (1)
Success (1)
SQuirreL SQL, v3.7.1
Success (2)
Success (2)
Oracle SQL Developer, v
Not Successful (3)

n/a:  at this time, we don't have these tools installed on the listed OS.

(1)  DBeaver had the EXASOL jdbc driver installed and/or recognized the existing driver.
(2)  We had to install the EXASOL jdbc driver manually
        see - 
SQuirreL SQL - Install JDBC Drivers to make the connection to EXASOL
(3)   SQL Developer is Java based, and supports third party JDBC drivers, so we are not sure
        why it could not make a connection.   (see the SQL Developer menu item:  
          Tools | Preferences | Database | Third Party JDBC Drivers)


For general query building, data review and general DDL/DML tasks, both EXAplus or DBeaver are good choices. And EXASOL's EXAplus tool has some nice data import and export tools, and it's good to spend some time with it since it is the provided tool. But, if you want to get a deeper look inside EXASOL, also consider SQuirreL SQL. It has a lot going on, so it might be confusing at first, but its object explorer gives you insight into the EXASOL system that EXAplus just does not have. The downside of SQuirreL SQL is that it's not user friendly when building complex SQL. Realistically, expect to use more than one tool.

EXASOL does have a metadata viewer, called the "Data Provider Metadata Viewer." According to the manual, it is installed when you install the ADO.NET driver.  This tool, rather than SQuirrel SQL might be the tool for you to explore the internals.

Other Tools

The three additional tools we looked at are Java based and use JDBC. EXASOL supports ADO.NET, ODBC and JDBC to connect to the database.  Based on this limited review, we suspect that if your tool is configured to support multiple databases using one of these protocols, there is a good chance it will work with EXASOL.

No comments: