Databases - Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC) – Revvity Signals

Date Posted: 2018-01-16
Product: TIBCO Spotfire®

Problem:

Databases - Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)

Solution:

Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)

Follow these steps to configure the MySQL data source

1. Download the MySQL JDBC jar file from here and copy the mysql-connector-java-5.0.8-bin.jar file to the following locations: /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/ AND /usr/local/chorus/shared/libraries/. For MariaDB, download this jar file and copy it to the same mentioned locations.

2. Make sure that chorus user owns those copies (for MySQL and MariaDB):

chown chorus:chorus /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/mysql-connector-java-5.0.8-bin.jar
chown chorus:chorus /usr/local/chorus/shared/libraries/mysql-connector-java-5.0.8-bin.jar
  
chown chorus:chorus /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/mariadb-java-client-1.1.7.jar
chown chorus:chorus /usr/local/chorus/shared/libraries/mariadb-java-client-1.1.7.jar

3. Make sure that the appropriate permissions are granted to the user in MySQL that you are using in the data source connection:

[root@green ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql>

4. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mariadb/driver.properties file to have the following content (for MariaDB):

# 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=org.mariadb.jdbc.Driver
useCatalog=true

If /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mysql directory doesn't exist, copy the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mariadb directory and rename it to mysql. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mysql/driver.properties file to have the following content (for MySQL):

# 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.mysql.jdbc.Driver
useCatalog=true

5. Restart Spotfire® Data Science and go to the data source configuration page. Configure it similar to the attached screenshot.

For MariaDB, use this type of JDBC Url:

jdbc:mariadb://<ip/fqdn>:3306/?sessionVariables=sql_mode=ANSI_QUOTES

For MySQL, use this type of JDBC Url:

jdbc:mysql://<ip/fqdn>:3306/?sessionVariables=sql_mode=ANSI_QUOTES

For more information regarding connection to other data sources, look into this page - Connecting Spotfire Data Science to Data Sources