Add JDBC Drivers

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/

The following procedure describes how to add JDBC drivers for use in the database.

Prerequisites

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

  • The default bucket is available on port 2581 by default. Ensure that the security group contains an inbound rule with this port. For more information see Authorize inbound traffic to your linux instances.

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

Procedure

The examples in this procedure are written using Bash on a Linux terminal. You can also use other interfaces and languages to execute the curl commands.

Placeholder values are styled as Bash variables, starting with the dollar sign ($) and using UPPERCASE characters. Replace the placeholders with your own values before executing the curl command.

Create a Configuration File

Create a configuration file called 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.

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

Disabling Security

Some drivers require additional permissions to run correctly. In these cases, you may receive an error message pointing to permission problems in the IMPORT statement. To disable the security manager, add the line NOSECURITY=YES to the settings.cfg file. The security manager is enabled by default.

Only add NOSECURITY=YES if the driver cannot run with the security manager enabled.

Examples

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 Access Files in BucketFS.

You must use the IP address of one of the database nodes, not the database access node.

For example:

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

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'' '
  );

See Also