Databases - Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)
Article ID: KB0082707
Updated On:
| Products | Versions |
|---|---|
| Spotfire Data Science | 6.x |
Description
Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)
Issue/Introduction
Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)
Resolution
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):
3. Make sure that the appropriate permissions are granted to the user in MySQL that you are using in the data source connection:
4. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mariadb/driver.properties file to have the following content (for MariaDB):
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):
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:
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
Additional Information
For more information regarding connection to other data sources, look into this page - Connecting Spotfire Data Science to Data Sources
Attachments
Feedback
Yes
No
Powered by
