Load data from Snowflake

Learn how to connect to Snowflake with Exasol and then load data.

Prerequisites

Snowflake must be reachable from the Exasol system.
The user credentials in the connection must be valid.

Download driver

Download the latest JDBC driver from the Snowflake website.

Add JDBC driver

Create a configuration file settings.cfg with the following settings:
Copy
```
DRIVERNAME=Snowflake
PREFIX=jdbc:snowflake:
FETCHSIZE=100000
INSERTSIZE=-1
NOSECURITY=YES
```
The file must end with an empty line (line break followed by zero characters).
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.

For more details about how to add JDBC drivers and configuration files, see Add JDBC driver.
To learn more about how to upload files to BucketFS, see Manage files in BucketFS.

Create connection

When creating a connection, the Snowflake JDBC driver uses Apache Arrow to transport query results. However, Arrow's off-heap memory allocator can fail to initialize because Exasol's JDBC import runs on a Java 17 runtime with module encapsulation. You can avoid this issue using one of the following workarounds:

Use JSON result format (Disables Arrow)

Add JDBC_QUERY_RESULT_FORMAT=JSON to the JDBC URL. This parameter instructs the Snowflake JDBC driver to return query results in JSON format instead of Arrow, avoiding the compatibility issue with the Java 17 runtime.

To create a connection, run the following statement. Replace the placeholders in the connection string and credentials with the corresponding values for your Snowflake account.

Copy

CREATE OR REPLACE CONNECTION SNOWFLAKE_CONNECTION
TO 'jdbc:snowflake://<org>-<account>.snowflakecomputing.com/?warehouse=<wh>&role=<role>&CLIENT_SESSION_KEEP_ALIVE=true&JDBC_QUERY_RESULT_FORMAT=JSON'
USER '<user>'
IDENTIFIED BY '<password_or_token>';

This option requires no database configuration and is recommended for most workloads.

JSON is a row-oriented result format and is less efficient for high-volume, recurring data loads. To optimize data transfer, unload the data from Snowflake to object storage before loading it into Exasol.

Use Arrow result format

To retain Arrow, pass the required Java Virtual Machine (JVM) flag to the JDBC import process using the database parameter etlJdbcJavaEnv. Complete the following steps:

Use ConfD to stop the database, set the parameter etlJdbcJavaEnv to the single --add-opens directive required by the Arrow memory allocator, and restart the database.
Copy
```
confd_client db_stop      db_name: <my_database>
confd_client db_configure db_name: <my_database> params_add: '[etlJdbcJavaEnv=-add-opens=java.base/java.nio=ALL-UNNAMED]'
confd_client db_start     db_name: <my_database>
```
This setting is stored in the database configuration and persists across restarts and upgrades.

Copy
After configuring etlJdbcJavaEnv, create the connection without the JDBC_QUERY_RESULT_FORMAT=JSON parameter. Replace the placeholders in the connection string and credentials with the corresponding values for your Snowflake account.
Copy
```
CREATE OR REPLACE CONNECTION SNOWFLAKE_CONNECTION
TO 'jdbc:snowflake://<org>-<account>.snowflakecomputing.com/?warehouse=<wh>&role=<role>&CLIENT_SESSION_KEEP_ALIVE=true'
USER '<user>'
IDENTIFIED BY '<password_or_token>';
```

To test the connection, run the following statement.

Copy

SELECT * FROM 
(
IMPORT FROM jdbc AT SNOWFLAKE_CONNECTION
STATEMENT 'select ''Connection works!'' as connection_status'
);

Load data

Use IMPORT to load data from a table or SQL statement using the connection that you created.