Virtual Schema User Guide

The JDBC adapter for virtual schemas allows you to connect to JDBC data sources like Hive, Oracle, Exasol, or any other data source supporting JDBC. It uses the IMPORT FROM JDBC statement in the background to obtain the requested data when running a query on a virtual table. A JDBC adapter also serves as the reference adapter for the Exasol virtual schema framework. Exasol provides many Supported Data Sources that you can use to create virtual schemas. You can use the generic dialect to work with any JDBC driver that is not listed in Supported Data Sources.

The user guides for all dialects are provided in their respective repositories. You can check them from Supported Data Sources.

Creating a virtual schema requires the following steps:

• Deploy JDBC Driver Files
• Install Adapter Script
• Define a JDBC Connection
• Create a Virtual Schema

Deploy JDBC Driver Files

Install Adapter Script

Run the following statement and create a schema to hold the adapter script.

CREATE SCHEMA SCHEMA_FOR_VS_SCRIPT;

Create an adapter script by running the following script and replace all the placeholders with the paths to your virtual schema libraries (JAR files) and JDBC driver. The exact syntax for your dialect can be found in the respective dialect user guides in Github.

By default, the path to the JDBC drivers is /buckets/bfsdefault/default/drivers/jdbc/<Database_name>/<JDBC driver>.jar

--/
CREATE JAVA ADAPTER SCRIPT SCHEMA_FOR_VS_SCRIPT.JDBC_ADAPTER_SCRIPT AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/your-bucket-fs/your-bucket/virtual-schema-dist-<version>-<dialect>-<version>.jar;
  %jar /buckets/your-bucket-fs/your-bucket/<JDBC driver>.jar;
/

If you notice any issue with your SQL client not identifying where a script ends or truncating scripts, see the SQL Client Troubleshooting section for solutions.

Define a JDBC Connection

Run the following statement and create a JDBC connection.

CREATE CONNECTION JDBC_CONNECTION TO '<jdbc connection string>' USER '<username>' IDENTIFIED BY '<password>';

Create a Virtual Schema

Run the following statement to create the Virtual Schema. The adapter retrieves the metadata through JDBC and maps them to virtual tables. The metadata (virtual tables, columns, and data types) are then cached in Exasol.

CREATE VIRTUAL SCHEMA VS_TEST
USING SCHEMA_FOR_VS_SCRIPT.JDBC_ADAPTER_SCRIPT
WITH
  CONNECTION_NAME = 'JDBC_CONNECTION'
  SCHEMA_NAME     = '<SCHEMA_NAME>';

For a list of all properties supported by the JDBC adapter, see Adapters and Properties.

Using the Adapter

The following table lists the commands you can use to manage the adapter: 

CREATE VIRTUAL SCHEMA VS_TEST USING SCHEMA_FOR_VS_SCRIPT.JDBC_ADAPTER_SCRIPT WITH
  CONNECTION_NAME = 'JDBC_CONNECTION'
  SCHEMA_NAME     = '<SCHEMA_NAME>';

DROP VIRTUAL SCHEMA VS_TEST;

ALTER VIRTUAL SCHEMA VS_TEST REFRESH;
ALTER VIRTUAL SCHEMA VS_TEST REFRESH TABLES T1 T2; 
-- refresh only these tables
ALTER VIRTUAL SCHEMA VS_TEST SET TABLE_FILTER='MY_TABLE, CLICKS';
ALTER VIRTUAL SCHEMA VS_TEST SET TABLE_FILTER=null;

EXPLAIN VIRTUAL SELECT * FROM MY_TABLE;

OPEN SCHEMA VS_TEST;
SELECT * FROM MY_TABLE;
SELECT count(*) FROM MY_TABLE;
SELECT DISTINCT USER_ID FROM MY_TABLE;

Virtual Schema Limitations

When developing custom virtual schema dialects, keep the following limits in mind:

Request Limitations

The limitations of a virtual schema request are as follows:

• SQL statements are limited to 64 MiB. This affects the size of CREATE VIRTUAL SCHEMA including all its properties.

• A single property is limited to two million characters (Exasol's VARCHAR limit).

Response Limitations

The limitations of a virtual schema response are as follows:

• Virtual Schema responses are limited to 2 GiB by protocol buffer.

• Adapter Notes are limited to two million characters.

• Value lists are limited by the SQL compiler run time. In general, value lists should not exceed 100.000 entries.

Exasol recommends using the IMPORT statement for virtual schema results instead of using value lists, as this allows parallel import.

ORDER BY Clause Limitations

The limitations for use of the ORDER BY clause in virtual schemas are as follows:

• ORDER BY can be used without limitations in virtual schemas that do not use IMPORT:
  • Exasol virtual schemas using a local connection. For more information, see VSEXA dialect guide.
  • Virtual schemas using row level security (RLS). RLS implementations in Java and Lua are currently available.

• For virtual schemas using IMPORT, only unordered transfer is supported. The outermost order of the imported result rows is therefore not guaranteed.

• Document-based virtual schemas do not support ORDER BY COLUMN at all.

If you need ordering, wrap your query inside an extra SELECT * FROM (<virtual-schema-query> ORDER BY FALSE) ORDER BY <criteria> [, ...] statement. The query will then be executed on the target Exasol database, not on the source of the virtual schema.

SQL Client Troubleshooting

DBeaver

Problem: If a script contains multiple semicolons or newlines, DBeaver has problem with identifying where a script ends.

Solution: The simplest way to get around that problem is to highlight the whole script and execute it as a single step.

DbVisualizer

Problem: DbVisualizer has problem with delimiting Scripts.

Solution: To tell DbVisualizer that a part of a script should be handled as a single statement, you can insert an SQL block begin-identifier just before the block and an end-identifier after the block.
The delimiter must be the only text on the line. The default begin-identifier consists of two dashes followed by a forward slash (--/) and for the End Identifier it is a single slash (/).