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:
The following procedure describes how to add JDBC drivers for use in the database.
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
this port is open in your network settings.
Change the write password of the default bucket. For more information, see Change Bucket Password.
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:
$MY_DRIVERNAMEwith a unique name of your choice. This name is used in the
driverclause in an IMPORT or EXPORT statement.
$PREFIXwith the required URL prefix for the JDBC driver. The prefix can be found on the driver manufacturer's website.
- Do not modify the
INSERTSIZEparameters unless instructed to by Exasol support.
Every line in the settings.cfg file - including the final line - must end with a newline character (LF).
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.
The following example is a settings.cfg file for the Exasol JDBC driver:
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.
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
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
IDENTIFIED BY 'exasol';
SELECT * FROM
(IMPORT FROM JDBC AT EXASOL_CONNECTION_JDBC
STATEMENT 'select ''Connection works'' '