Databases - Connect Spotfire Data Science to Teradata (JDBC)

Databases - Connect Spotfire Data Science to Teradata (JDBC)

Connect Spotfire Data Science to Teradata (JDBC)

Follow these steps to configure the Teradata data source

1. Download Teradata jar files from their web site (terajdbc4.jar and tdgssconfig.jar) and copy them to these locations: $CHORUS_HOME/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/   and  $CHORUS_HOME/shared/libraries/. 

2. Make sure that chorus user owns those copied files.

3. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/teradata/driver.properties file and make sure that it has the following content:
 

# 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.teradata.jdbc.TeraDriver

4. Restart Spotfire Data Science and go to the data source configuration page. Configure a JDBC data connection with a similar JDBC URL like the one below:

jdbc:teradata://10.0.0.159/dbc

Troubleshooting

• If your Teradata is linked to LDAP, you will need to add the LOGMECH=LDAP parameter as described here - https://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#URL_LOGMECH

jdbc:teradata://10.0.0.159/LOGMECH=LDAP

• If you have restricted permissions to metadata tables of your Teradata, please make sure to open SELECT access to DBC.UDTInfo table for the Teradata user, whose credentials are used to connect from Spotfire Data Science.
• If you are using MULTISET tables or ANSI mode, you must change an option in the JDBC URL. Navigate to the data source configuration and replace the JDBC URL with

jdbc:teradata://1.2.3.4/TMODE=ANSI

Alternatively, you can change the SQL template from CREATE TABLE to CREATE MULTISET TABLE. Find this in ALPINE_DATA_REPOSITORY/jdbc/teradata/createTableFromSelect.sql

See https://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#TMODESEC for more information.

CREATE MULTISET TABLE ${targetTable} AS (SELECT ${columns} FROM ${sourceTable} ${additionalSql}) WITH DATA;