Add JDBC Drivers

This section 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.

  • 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 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: $EXASOL_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.

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.

Disabling the security manager may reduce the security of your system. 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 Manage Buckets and Files in BucketFS.

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

For example:

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 uses a self-signed certificate or a certificate that is not valid. 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'' '
  );

See Also