Adapters and Properties

This article explains how to create and use adapter scripts for virtual schemas.

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. Adapter scripts 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 UDF Scripts. 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 adapters.

For information about available adapters, see the Virtual Schema GitHub repository. If you want to create your own adapter, see Information for Developers.

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.

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.To unset a property, assign NULL.

The properties are always strings. For example: TABLE_FILTER='T1,T2'.

The virtual schema properties of each supported data source are described in their respective documentation. For more information, see Supported Data Sources. The following properties are available in adapters developed by Exasol.

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. Catalog names are usually case-sensitive.

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.

SCHEMA_NAME

The name of the remote schema. Schema names are usually case-sensitive.

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.
TABLE_FILTER

A comma-separated list of table names. Table names are always case-sensitive.

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.

Advanced properties (optional)

Property Value
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.

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 (see above).