Connecting Spotfire Data Science to Amazon RedShift

Follow the steps below to connect Spotfire Data Science to RedShift.

1. Copy the RedShift driver to the $CHORUS_HOME/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public and $CHORUS_HOME/shared/libraries directories and change the ownership of these copies to the user who runs Spotfire Data Science (usually user 'chorus').

2. Create a new redshift directory: $CHORUS_HOME/shared/ALPINE_DATA_REPOSITORY/jdbc/redshift and copy the driver.properties file from $CHORUS_HOME/shared/ALPINE_DATA_REPOSITORY/jdbc/default directory to the newly created redshift directory.

3. Edit the content of $CHORUS_HOME/shared/ALPINE_DATA_REPOSITORY/jdbc/redshift/driver.properties file:

# Specify the JDBC class driver for the desired database type.
# Examples:
# Oracle = oracle.jdbc.driver.OracleDriver
# Greenplum = org.postgresql.Driver
# DB2 = com.ibm.db2.jcc.DB2Driver
# Netezza = org.netezza.Driver
# PostgreSQL = org.postgresql.Driver
# SQLServer = com.microsoft.sqlserver.jdbc.SQLServerDriver
# MySQL = com.mysql.jdbc.Driver
# Teradata = com.teradata.jdbc.TeraDriver
# Vertica = com.vertica.jdbc.Driver
# Sybase = com.sybase.jdbc2.jdbc.SybDriver
# Informix = com.informix.jdbc.IfxDriver
# SAPDB = com.sap.dbtech.jdbc.DriverSapDB
# InterBase = interbase.interclient.Driver
# HSqlDB = org.hsqldb.jdbcDriver
# MariaDB = org.mariadb.jdbc.Driver
# MySQL = com.mysql.jdbc.Driver
driverClass=com.amazon.redshift.jdbc41.Driver

4. Edit the content of additional_jdbc_drivers.rb file (with a path similar to this one: /data/chorus/install/releases/5.9.1.0.3973-5d95f7c97/components/core/app/mixins/sequel/extensions/additional_jdbc_drivers.rb) and add a line for redshift class so that the content looks similar to this:

module Sequel
  module AdditionalJdbcDrivers
    MAP =  {
        mariadb: ->(db) { org.mariadb.jdbc.Driver },
        teradata: ->(db) { com.teradata.jdbc.TeraDriver },
        vertica: ->(db) { com.vertica.jdbc.Driver },
        hive2: ->(db) { org.apache.hive.jdbc.HiveDriver },
        hive: ->(db) { org.apache.hadoop.hive.jdbc.HiveDriver },
        redshift: ->(db) { com.amazon.redshift.jdbc41.Driver }
        }
 
    MAP.each do |key, driver|
      ::Sequel::JDBC::DATABASE_SETUP[key] = driver
    end
  end
end

Note: The change in additional_jdbc_drivers.rb file needs to be applied again after upgrading Spotfire Data Science.

5. Restart Spotfire Data Science and set the Data Connection using a similar URL (you can copy your RedShift URL from your AWS RedShift page):

dbc:redshift://armen-jjredshift.csyb6t8bifc8.us-west-1.redshift.amazonaws.com:5439/armenjjdb