This topic explains how to configure an Amazon Redshift database as an external data source.

The Redshift Driver

LabKey Server requires the Redshift driver to connect to Amazon Redshift databases.

Obtain the latest JDBC 4.2 driver from this page, and place it in the <tomcat-home>/lib directory.

Note: It is important to have only one Redshift driver in the Tomcat lib directory. Be sure to delete the existing one when you replace it with the latest one.

Tomcat Configuration

Add a <Resource> element, to your installation's labkey.xml configuration file. Use the configuration template below as a starting point. Replace USERNAME and PASSWORD with the correct credentials.

Note: This template applies to Tomcat 8.5 (or later).

<Resource name="jdbc/$DATASOURCE_NAME" auth="Container"
type="javax.sql.DataSource"
username="$USERNAME"
password="$PASSWORD"
driverClassName="com.amazon.redshift.jdbc.Driver"
url="jdbc:redshift://$URL:$PORT/$DATABASE_NAME"
maxTotal="20"
maxIdle="10"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"/>

Define a New Schema

Now define a new schema from the Redshift data source. For details see Set Up an External Schema.

Supported Functionality

Most queries that would work against a PostgreSQL data source will also work against a Redshift data source.

Of the known differences most are due to limitations of Redshift, not the LabKey SQL dialect, including:

  • GROUP_CONCAT is not supported (Redshift does not support arrays).
  • Recursive CTEs are not supported; but non-recursive CTEs are supported.
  • Most PostgreSQL pass-through methods that LabKey SQL supports will work, but any involving binary types, such as string encode(), will not work.

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all