Virtual Schema Adapters
If you create a virtual schema, you need to specify the corresponding adapter (an adapter script) which implements the logic how to access data from the external system. This is different for different technologies, however, it includes two main tasks:
- Read metadata: Receive information about the objects included in the schema (tables, columns, and types) and define the logic how to map the data types of the source system to the Exasol data types.
- Push down query: Push down parts of the Exasol SQL query into an appropriate query to the external system. The adapter defines what kind of logic Exasol can push down (filters or certain functions). The Exasol optimizer will then try to push down as much as possible and execute the rest of the query locally on the Exasol cluster.
Adapters are similar to UDF Scripts. They can be implemented in one of the supported programming languages, for example Java or Python. They can access the same metadata which is available within UDF scripts. To install an adapter, you can download and execute the SQL scripts that create the adapter script. Refer to the Virtual Schema GitHub repository to view the available adapters.
If you want to create your own adapter, see Information for Developers.
The below example SQL statement creates the adapter script which defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.
CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.RequestDispatcher;
%jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-9.0.3-generic-2.0.1.jar;
%jar /buckets/<BFS service>/<bucket>/<source database jdbc driver>.jar;
/Virtual Schema Properties
The Virtual Schema properties control the behavior of the JDBC adapter. There are three types of properties that you can configure using CREATE VIRTUAL SCHEMA and you can change these properties using ALTER VIRTUAL SCHEMA SET.
These properties are always strings, For example, TABLE_FILTER='T1,T2'.
The virtual schema properties of each supported data source are available in their respective documentation. For more information, see Supported Data Sources.
Mandatory Properties
| Property | Value |
|---|---|
| CONNECTION_NAME |
Name of the connection created with CREATE CONNECTION which contains the JDBC connection string, username, and password. |
Common Properties (Optional)
| Property | Value |
|---|---|
| CATALOG_NAME |
The name of the remote JDBC catalog. This is usually case-sensitive. Depending on the dialect, you may have to specify this. Usually you have to specify it if the data source JDBC driver supports the concepts of catalogs. |
| SCHEMA_NAME |
The name of the remote JDBC schema. This is usually case-sensitive. Depending on the dialect, you may have to specify this. Usually you have to specify it if the data source JDBC driver supports the concepts of schemas. |
| TABLE_FILTER |
A comma-separated list of table names (case-sensitive). Only these tables are available as virtual tables, other tables are ignored. Use this if you don't want to have all remote tables in your virtual schema. |
Advanced Properties (Optional)
| Property | Value |
|---|---|
| EXCEPTION_HANDLING |
The property activates or deactivates different exception handling modes. Valid values for the property are IGNORE_INVALID_VIEWS and NONE (default). Currently this property only affects the Teradata dialect. |
| EXCLUDED_CAPABILITIES |
A comma-separated list of capabilities that you want to deactivate (although the adapter might support them). |
| IGNORE_ERRORS |
The property is used to ignore errors thrown by the adapter. Check the documentation of the dialects that support this property for additional information. |
