This topic explains how to configure an Amazon Redshift database as an
external data source.
The Redshift Driver
The Redshift JDBC driver is bundled with the LabKey Redshift module.
Driver Note:
If you are upgrading from any version prior to 21.3 (March 2021), you must delete any Redshift drivers and dependencies previously installed in the CATALINA_HOME/lib directory to avoid conflicts with the bundled driver. LabKey Server will fail to start if it detects an old Redshift driver in CATALINA_HOME/lib.
Tomcat Configuration
Add a <Resource> element, to your installation's
labkey.xml configuration file. Use the configuration template below as a starting point.
If you will be connecting to more than one data source, be sure that all have unique
name parameters, i.e. redshiftDataSource1, redshiftDataSource2, or similar, where you see DATASOURCE_NAME in the below. This value will be used whenever you connect an
external schema to this datasource so it must be unambiguous. Replace USERNAME and PASSWORD with the correct credentials, and complete the url parameter with your own URL, PORT, DATABASE_NAME.
<Resource name="jdbc/DATASOURCE_NAME" auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="com.amazon.redshift.jdbc42.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.
Related Topics
