Add JDBC driver

Learn how to add a JDBC driver in Exasol.

Exasol supports loading data from most databases by using JDBC. 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.

Drivers must be uploaded to BucketFS on the cluster. The default path for JDBC drivers in BucketFS is:

/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.

  • You must set a write password for the default bucket. For more information, see Change bucket password.

Procedure

Create a configuration file

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

Copy 
DRIVERNAME=$MY_DRIVERNAME
PREFIX=$PREFIX
FETCHSIZE=100000
INSERTSIZE=-1
  • Replace $MY_DRIVERNAME with a unique name. This string 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.

The file must end with an empty line (line break followed by zero characters).

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.

Example:
Copy 
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.

Example:
Copy 
DRIVERMAIN=com.amazon.redshift.jdbc42.Driver

With some drivers you may receive an error message indicating file permission issues, or the query will hang without an error message. To resolve this issue, try disabling 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:

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

Upload the configuration file

Upload the settings.cfg file and the driver .jar file to BucketFS in the Exasol cluster.

In Exasol 2025.1 and later you can upload files using Exasol Admin. You can also use any compatible file transfer tool, including curl on the command line. See also Manage files in BucketFS.

In Exasol SaaS you must upload the driver through the web console.

If the driver was downloaded as a .tar.gz or .zip archive, make sure that you extract and upload only the .jar file along with the settings.cfg file.

Example (using curl in Linux):
Copy 
export WRITE_PW=<your_bucketfs_write_password>
export DATABASE_NODE_IP=<ip_address_of_cluster_node>
export PORT=<bucketfs_port> # default: 2581
export DRIVER=<driver_filename> # for example: exajdbc.jar

curl -v --insecure -X PUT -T settings.cfg https://w:$WRITE_PW@$DATABASE_NODE_IP:$PORT/default/drivers/jdbc/exasol/settings.cfg
curl -v --insecure -X PUT -T $DRIVER https://w:$WRITE_PW@$DATABASE_NODE_IP:$PORT/default/drivers/jdbc/exasol/$DRIVER

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.

In AWS native deployments you must use the IP address of a database node, not the access node.

Verification

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

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