Adapters and properties

Learn how to use virtual schema adapters.

Virtual schema adapters

When you create a virtual schema you must specify a corresponding adapter. The adapter is a script that implements the logic for accessing data from the external system. Adapters are different for different technologies but always include 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 User defined functions (UDFs). They can be implemented in one of the supported programming languages Java, Python, or Lua, and can access the same metadata that is available within UDF scripts. To install an adapter, you can download and execute the SQL scripts that create the adapter script.

R is not supported as a programming language for virtual schema adapters.

For information about Exasol version compatibility and other dependencies for a specific virtual schema adapter, refer to the release notes for the respective adapter in Supported Dialects on GitHub.

Adapter script example

The following SQL example creates an adapter script that defines a Java class that serves as an entry point, and tells the UDF framework where to find the libraries (JAR files) for the virtual schema and database driver.

Copy 
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 adapter.

You can create schema properties using CREATE SCHEMA and change them using ALTER SCHEMA. Property changes are incremental. For example, if you set a single parameter, all previously set parameters remain unchanged.

The properties are always strings. For example: TABLE_FILTER='T1,T2'. To unset a property, assign NULL.

The following properties are available in adapters developed by Exasol. For details about the virtual schema properties of each supported data source, see the documentation for the respective source on the Supported Dialects page on GitHub.

The Mandatory/Common/Advanced classification scheme is specific to adapters developed by Exasol. Other adapters may use a completely different scheme for classifying schema properties.

Mandatory properties

Property Value
CONNECTION_NAME

The name of the connection created with CREATE CONNECTION that contains the connection string, username, and password.

Common properties (optional)

Property Value
CATALOG_NAME

The name of the remote catalog.

This property may be mandatory or optional depending on the dialect. If the data source JDBC driver supports the concepts of catalogs, you normally have to specify the catalog name.

Catalog names are usually case-sensitive.

SCHEMA_NAME

The name of the remote schema.

This property may be mandatory or optional depending on the dialect. If the data source JDBC driver supports the concepts of schemas, you normally have to specify the schema name.

Schema names are usually case-sensitive.

TABLE_FILTER

A comma-separated list of table names.

Only the specified tables will be available as virtual tables, other tables are ignored. Use this property if you do not want to have all remote tables in your virtual schema.

Table names are always case-sensitive.

TELEMETRY

The virtual schema adapters use telemetry to send anonymous usage metrics to Exasol. Telemetry is enabled by default. To disable it, set this property to false.

For more information, see the Telemetry User Guide on GitHub.

Advanced properties (optional)

Property Value
EXCLUDED_CAPABILITIES

A comma-separated list of capabilities that you want to deactivate (although they are supported in the adapter).
IGNORE_ERRORS

To ignore errors thrown by the adapter, set this property to true.

For more information, see Supported Dialects on GitHub.
DEBUG_ADDRESS

The IP address and port to use for virtual schema logging.

For more information, see Virtual schema logging.

LOG_LEVEL

The log level to use when logging.

For more information, see Virtual schema logging.