Load data from Snowflake

This article describes 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 know how to upload the JDBC driver and the configuration file to BucketFS, see Add JDBC Driver.

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'
  USER '<sfuser>' IDENTIFIED BY '<sfpwd>';

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.