Adapters and Properties

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 task:

  • 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 creates 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, 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 JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jar /buckets/<BFS service>/<bucket>/virtualschema-jdbc-adapter-dist-3.1.1.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 these properties 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 if you have to specify this or not.

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 if you have to specify this or not.

Usually you have to specify it if the data source JDBC driver supports the concepts of catalogs.

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.