Databases - Connecting Spotfire Data Science to Amazon RedShift

Databases - Connecting Spotfire Data Science to Amazon RedShift

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