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
The JDBC Driver is used by the EXALoader to run the IMPORT
statements generated by the virtual schema adapter and to read metadata from the data source. To upload the JDBC driver, see Add JDBC Driver.
Some JDBC drivers may contain multiple files and you need to upload all of them. For more information, see Supported Data Sources.
Install Adapter Script
Run the following statement and create a schema to hold the adapter 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:
Command | Description | Example |
---|---|---|
CREATE SCHEMA | Creates a virtual schema. | |
DROP SCHEMA | Deletes a virtual schema and all of its virtual tables. | |
ALTER SCHEMA | Modifies the properties of a virtual schema or refreshes the metadata using REFRESH option. |
|
EXPLAIN VIRTUAL | Analyzes the resulting queries for external system that are created by the Exasol compiler. | |
OPEN SCHEMA and SELECT | Explore the tables in the virtual schema, just like a regular schema. |
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 useIMPORT
:- 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 (/
).