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: Downloading / integrating the JDBC Driver.

Add JDBC driver

To add the JDBC driver in Exasol, create a configuration file called settings.cfg with the following settings:

Copy 
DRIVERNAME=Snowflake
PREFIX=jdbc:snowflake:
FETCHSIZE=100000
INSERTSIZE=-1
NOSECURITY=YES

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

To learn how to upload the driver and the configuration file to an Exasol as-application deployment, see Add JDBC driver.

To learn how to upload the driver and the configuration file to Exasol SaaS, see Add JDBC driver (SaaS).

Create connection

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 a connection to Snowflake
CREATE OR REPLACE CONNECTION SNOWFLAKE_CONNECTION 
    TO 'jdbc:snowflake://<myorganization>-<myaccount>.snowflakecomputing.com/?warehouse=<my_compute_wh>&role=<my_role>&CLIENT_SESSION_KEEP_ALIVE=true&JDBC_QUERY_RESULT_FORMAT=JSON'
    USER '<sfuser>' IDENTIFIED BY '<sfpwd>';

The parameter JDBC_QUERY_RESULT_FORMAT=JSON is required to specify JSON format for the returned query results. Snowflake will otherwise default to Arrow format, which will result in an error.

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.