Skip to content

Latest commit

 

History

History
83 lines (59 loc) · 3.43 KB

redshift.md

File metadata and controls

83 lines (59 loc) · 3.43 KB
Raw

Redshift SQL Dialect

The Redshift SQL Dialect supports Amazon's AWS Redshift managed data warehouse. Redshift is at its core a relational database based on PostgreSQL.

In addition to reading from the regular relational database, this SQL dialect adapter also supports reading from Redshift Spectrum. This allows reading file based data from S3.

Registering the JDBC Driver in EXAOperation

First download the Redshift JDBC driver.

Now register the driver in EXAOperation:

  1. Click "Software"
  2. Switch to tab "JDBC Drivers"
  3. Click "Browse..."
  4. Select JDBC driver file
  5. Click "Upload"
  6. Click "Add"
  7. In dialog "Add EXACluster JDBC driver" configure the JDBC driver (see below)

You need to specify the following settings when adding the JDBC driver via EXAOperation.

Parameter Value
Name REDSHIFT
Main com.amazon.redshift.jdbc42.Driver
Prefix jdbc:redshift:
Files RedshiftJDBC42-<JDBC driver version>.jar

Please refer to the documentation on configuring JDBC connections to Redshift for details.

Upload JDBC Driver to EXAOperation

  1. Create a bucket in BucketFS (recommended: jdbc)
  2. Upload the driver to BucketFS

This step is necessary since the UDF container the adapter runs in has no access to the JDBC drivers installed via EXAOperation but it can access BucketFS.

Installing the Adapter Script

Upload the latest available release of Virtual Schema JDBC Adapter to Bucket FS.

Then create a schema to hold the adapter script.

CREATE SCHEMA ADAPTER;

The SQL statement below 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 OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
    %scriptclass com.exasol.adapter.RequestDispatcher;
    %jar /buckets/<BFS service>/<bucket>/virtualschema-jdbc-adapter-dist-2.2.0.jar;
    %jar /buckets/<BFS service>/<bucket>/RedshiftJDBC42-<JDBC driver version>.jar;
/
;

Defining a Named Connection

Define the connection to Redshift as shown below. We recommend using TLS to secure the connection.

CREATE OR REPLACE CONNECTION REDSHIFT_CONNECTION
TO 'jdbc:redshift://<cluster>.<region>.redshift.amazonaws.com:<port>/<database>'
USER '<user>'
IDENTIFIED BY '<password>';

Creating a Virtual Schema

Below you see how a Redshift Virtual Schema is created. Please note that you have to provide the name of the database in the property CATALOG_NAME since Redshift simulates catalogs.

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING ADAPTER.JDBC_ADAPTER
    WITH
    SQL_DIALECT = 'REDSHIFT'
    CONNECTION_NAME = 'REDSHIFT_CONNECTION'
    CATALOG_NAME = '<database name>'
    SCHEMA_NAME = 'public';