exasol-virtual-schemas

Exasol Virtual Schemas

Virtual schemas expose external data sources (other databases, files, cloud services) as read-only Exasol schemas. Queries against virtual tables are pushed down to the source where possible; the rest is processed in Exasol.

How It Works

1. Exasol identifies virtual tables in a query and locates the adapter script.
2. It asks the adapter which operations it supports (pushdown capabilities).
3. A pushdown request is sent; the adapter returns an IMPORT statement or a SELECT with a UDF.
4. The result is merged into the rest of the query execution.

Metadata (table names, column types) is retrieved via JDBC and cached in Exasol until explicitly refreshed.

Setup

Step 1 — Upload the JDBC Driver to BucketFS

# Default driver path inside BucketFS
/buckets/bfsdefault/default/drivers/jdbc/<database>/<driver>.jar

Upload the JAR using the download_file BucketFS tool (downloads from a URL to BucketFS) or the BucketFS web interface. The download_file tool requires the JAR to be hosted at an accessible URL (e.g., a GitHub release or S3).

Step 2 — Install the Adapter Script

CREATE SCHEMA adapter_schema;

CREATE OR REPLACE JAVA ADAPTER SCRIPT adapter_schema.jdbc_adapter AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/bfsdefault/default/virtual-schema-dist-12.0.0.jar;
  %jar /buckets/bfsdefault/default/postgresql-42.7.1.jar;
/

Replace the JAR names and paths with the actual versions in your BucketFS.

Step 3 — Create a Connection Object

CREATE OR REPLACE CONNECTION pg_conn
TO 'jdbc:postgresql://host:5432/mydb'
USER 'user' IDENTIFIED BY 'password';

Step 4 — Create the Virtual Schema

CREATE VIRTUAL SCHEMA pg_schema
USING adapter_schema.jdbc_adapter
WITH CONNECTION_NAME = 'PG_CONN'
     SCHEMA_NAME     = 'public';

SCHEMA_NAME is the remote schema to expose. Adapter-specific properties (e.g. CATALOG_NAME, TABLE_FILTER) go in the same WITH clause.