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

  • Mandatory properties

  • Common properties (optional)

  • Advanced properties (optional)

You can change these properties 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 to it.

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

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

Common Properties (Optional)

Property Value
CATALOG_NAME

The name of the remote JDBC 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 JDBC 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 documentation of the 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).