Snowflake SQL

Jump to Section

The Snowflake SQL connection connects to Snowflake using JDBC driver. The connection supports reading data from Snowflake using SQL queries.

Connection Settings

Account Identifier

The identifier for the Snowflake account, which can be found when logging into the Snowflake portal. See the Snowflake documentation for more details. An example format is ZHX29753.

https://docs.snowflake.com/en/user-guide/admin-account-identifier

User

The user account in Snowflake to authenticate with.

Role

The role of this user account. The role must have the ability to create tables if create is used in the Output settings.

Private Key

The private key used to authenticate the user. See the Snowflake documentation for generating private key for an account. The private key must be imported into the Intelligence Hub certificate store (See Certificates) and referenced here.

https://docs.snowflake.com/en/user-guide/key-pair-auth

Database

The Snowflake database to write to.

Schema

The default schema to use. Defaults to Public.

Warehouse

The warehouse to use for any processing of data.

Input Settings

See JDBC Driver Settings for input settings.

Errors with JVM Version

Depending on your JVM, you may see an error similar to the following when reading a Snowflake SQL input.

json 
JDBC driver internal error: exception creating result java.lang.NoClassDefFoundError: Could not initialize class net.snowflake.client.jdbc.internal.apache.arrow.memory.RootAllocator...

This is caused by an issue with Arrow, an open source Apache project used by the Snowflake JDBC driver to provide a more efficient binary format for exchanging data with Snowflake. This issue exists on JVM versions 16 and 17. To get around this issue there are a few options.

  1. Upgrade your JVM to the latest v21
  2. Add a JVM_OPTION via the command line or environments variable to find/load the dependency
    1. Command line: --add-opens=java.base/java.nio=ALL-UNNAMED
    2. ENV variable: _JAVA_OPTIONS="--add-opens=java.base/java.nio=ALL-UNNAMED"
  3. Once the connection is made in HighByte, run the following SQL query on the input. Note this needs to be run any time the connection reconnects. This command skips the Arrow binary format and exchanges data in JSON.
    1. ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT='JSON'