Add JDBC Driver

This article explains how to add a JDBC driver to BucketFS.

Exasol supports loading data from most databases using a JDBC driver. In the IMPORT and EXPORT statement, you can specify a JDBC connection string to a foreign database and load data from that source. Similarly, the CREATE CONNECTION command saves this connection string in a connection object and can be re-used in other queries.

By default, the database is configured to use the following path for all JDBC drivers:

/buckets/bfsdefault/default/drivers/jdbc/

Prerequisites

  • The JDBC driver must be downloaded to your local machine and must consist of one or more jar files.

  • Traffic must be allowed on the port used for the default bucket in BucketFS. The default port for this bucket is 2581.

  • Change the write password of the default bucket. For more information, see Change Bucket Password.

Procedure

The following examples use curl on a Linux terminal. You can also use other interfaces and languages to execute the curl commands.

Placeholder values in the examples are styled as Bash variables, for example: $NODE_IP. Replace these placeholders with your own values.

Create a configuration file

Create a configuration file settings.cfg that specifies the configuration parameters for your driver using the following format:

DRIVERNAME=$MY_DRIVERNAME
PREFIX=$PREFIX
FETCHSIZE=100000
INSERTSIZE=-1
  • Replace $MY_DRIVERNAME with a unique name of your choice. This name is used in the driver clause in an IMPORT or EXPORT statement.
  • Replace $PREFIX with the required URL prefix for the JDBC driver. The prefix can be found on the driver manufacturer's website.
  • Do not modify the FETCHSIZE or INSERTSIZE parameters unless instructed to by Exasol support.

Optional parameters

You can force IMPORT/EXPORT to use only a subset of the .jar files in the BucketFS folder by adding a line for each JAR file to the settings.cfg file, using the syntax JAR=my-file-n.jar. For example: 

JAR=mssql-jdbc-11.2.0.jre8.jar

Exasol will normally determine the main JDBC driver class to load. In case this does not work, you can specify the main driver explicitly using the DRIVERMAIN parameter in settings.cfg. For example: 

DRIVERMAIN=com.amazon.redshift.jdbc42.Driver

Every line in the settings.cfg file, including the final line, must end with a newline character (LF).

With some driver versions you may receive an error message indicating file permission issues. In this case you may have to disable the security manager by adding the line NOSECURITY=YES in the configuration.

Example:

The following example is a settings.cfg file for the Exasol JDBC driver:

DRIVERNAME=EXASOL_JDBC
PREFIX=jdbc:exa:
FETCHSIZE=100000
INSERTSIZE=-1

Upload the configuration file

Upload the settings.cfg file and the driver jar files to the specified bucket path. For more information, see Manage Buckets and Files in BucketFS.

Examples:
curl -v --insecure -X PUT -T settings.cfg https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/settings.cfg
curl -v --insecure -X PUT -T exajdbc.jar https://w:$WRITE_PW@$DATABASE_NODE_IP:2581/default/drivers/jdbc/exasol/exajdbc.jar

The option --insecure or -k tells curl to bypass the TLS certificate check. This option allows you to connect to a HTTPS server that does not have a valid certificate. Only use this option if certificate verification is not possible and you trust the server.

Verification

To verify that the JDBC driver is installed, perform an IMPORT statement using the JDBC driver. For example:

CREATE OR REPLACE CONNECTION EXASOL_CONNECTION_JDBC
TO 'jdbc:exa:192.168.0.72:8563'
USER 'SYS'
IDENTIFIED BY 'exasol';
 SELECT * FROM 
 (IMPORT FROM JDBC AT EXASOL_CONNECTION_JDBC
   STATEMENT 'select ''Connection works'' '
  );