Snowflake

Connect & Ingest data from / to a Snowflake database

Setup

The following credentials keys are accepted:

• account (required) -> The hostname or account the instance (eg. pua90768.us-east-11)

• user (required) -> The username to access the instance

• database (required) -> The database name of the instance

• password (optional) -> The password to access the instance

• schema (optional) -> The default schema to use

• role (optional) -> The role to access the instance

• warehouse (optional) -> The warehouse to use

• passcode (optional) -> Specifies the passcode provided by Duo when using multi-factor authentication (MFA) for login.

• authenticator (optional) -> Specifies the authenticator to use to login (e.g. snowflake, snowflake_jwt, externalbrowser, oauth, programmatic_access_token, username_password_mfa).

• token (optional since v1.5.1) -> Specifies the token for OAuth or PAT authentication. Required when using authenticator=programmatic_access_token.

• private_key (optional) -> Specifies the private key to use for snowflake_jwt authentication. Accepts a PEM-encoded key body, a Base64-encoded DER key (convenient for CI/CD environment variables, no PEM wrapping needed), or a file path to a key file.

• private_key_passphrase (optional) -> Specifies the private key file passphrase.

• max_chunk_download_workers (optional) -> Specifies the Maximum Number of Result Set Chunk Downloader (integer).

• custom_json_decoder_enabled (optional) -> Specifies to use the Custom JSON Decoder for Parsing Result Set (true or false).

• internal_stage (optional) -> Specifies a custom internal stage to use for bulk operations. If not provided, Sling will attempt to create a stage in the default schema named SLING_SCHEMA.SLING_STAGING.

• copy_method (optional) -> Specifies to use the platform to use for loading/unloading (DEFAULT, AWS, AZURE). For AWS or AZURE, you'll need to provide the necessary credentials, such as aws_bucket, aws_access_key_id and aws_secret_access_key, for AWS, or azure_account, azure_container and azure_sas_svc_url for AZURE.

• use_adbc (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See ADBC for setup and details. (v1.5.2+)

• adbc_uri (optional) -> Override the automatically constructed ADBC connection URI when using use_adbc=true.

Using sling conns

Here are examples of setting a connection named SNOWFLAKE. We must provide the type=snowflake property:

$ sling conns set SNOWFLAKE type=snowflake account=<account> user=<user> database=<database> password=<password> role=<role>

# Or use url
$ sling conns set SNOWFLAKE url="snowflake://myuser:[e-mail]/mydatabase?schema=<schema>&role=<role>"

Environment Variable

See here to learn more about the .env.sling file.

export SNOWFLAKE='snowflake://myuser:[e-mail]/mydatabase?schema=<schema>&role=<role>'

# use JSON format
export SNOWFLAKE_CONN='{ "type": "snowflake", "account": ..., "private_key": "<private-key-file-path>", "private_key_passphrase": "<passphrase>" }'

# use YAML format (with new lines)
export SNOWFLAKE='
type: snowflake
account: <account>
user: <user>
password: <password>
database: <database>
schema: <schema>
role: <role>
warehouse: <warehouse>
private_key: |
    -----BEGIN PRIVATE KEY-----
    MIIEvgIBADANBgkqhkiG7w0BAQEFAASCBKgwggSkAgEAAoIBAQDFWDdPxN7sKH/i
    ......
    SxUARJ4Rd2euQIEMqSY2UVPlNSaZK4wEq12jhXEM98cINVyKomJcThOHblz5IbV6
    +5I2kK6DCYSY2zm0xzYqeGFN
    -----END PRIVATE KEY-----
'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  # Standard password authentication
  SNOWFLAKE:
    type: snowflake
    account: <account>
    user: <user>
    password: <password>
    database: <database>
    schema: <schema>
    role: <role>
    warehouse: <warehouse>

  # Key-pair authentication
  SNOWFLAKE_JWT:
    type: snowflake
    account: <account>
    user: <user>
    database: <database>
    schema: <schema>
    role: <role>
    warehouse: <warehouse>
    authenticator: snowflake_jwt
    private_key: |
        -----BEGIN PRIVATE KEY-----
        MIIEvgIBADANBgkqhkiG7w0BAQEFAASCBKgwggSkAgEAAoIBAQDFWDdPxN7sKH/i
        ......
        SxUARJ4Rd2euQIEMqSY2UVPlNSaZK4wEq12jhXEM98cINVyKomJcThOHblz5IbV6
        +5I2kK6DCYSY2zm0xzYqeGFN
        -----END PRIVATE KEY-----

  # Programmatic Access Token (PAT) authentication
  SNOWFLAKE_PAT:
    type: snowflake
    account: <account>
    user: <user>
    database: <database>
    schema: <schema>
    role: <role>
    warehouse: <warehouse>
    authenticator: programmatic_access_token
    token: <token>  # Your PAT token

  # MFA authentication (with token caching)
  SNOWFLAKE_MFA:
    type: snowflake
    account: <account>
    user: <user>
    password: <password>
    database: <database>
    schema: <schema>
    role: <role>
    warehouse: <warehouse>
    authenticator: username_password_mfa

  # URL format
  SNOWFLAKE_URL:
    url: "snowflake://myuser:[e-mail]/mydatabase?schema=<schema>&role=<role>"

Troubleshooting

Key-Pair Authentication

Error: only PKCS #5 v2.0 supported

This error, or only PBES2 supported, has two causes:

1. You set private_key_passphrase, but the key is not an encrypted PKCS#8 key. Remove private_key_passphrase if the key starts with -----BEGIN PRIVATE KEY-----.

2. The key uses old PKCS#5 v1.5 encryption. This occurs when you encrypt a key with openssl pkcs8 -topk8 -v1 .... Create the key again with the -v2 option, as shown below.

Create a new key pair

Do these steps to create a key in the correct format.

Step 1. Create the private key. Use one of the two commands.

For an unencrypted key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

For an encrypted key, the -v2 option is necessary. It selects PBES2 encryption:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out rsa_key.p8

OpenSSL asks for a passphrase. Keep this passphrase for the private_key_passphrase property.

Step 2. Make sure the file starts with the correct line:

head -1 rsa_key.p8

The output must be -----BEGIN PRIVATE KEY----- or -----BEGIN ENCRYPTED PRIVATE KEY-----.

Step 3. Create the public key:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

If the private key is encrypted, OpenSSL asks for the passphrase.

Step 4. Assign the public key to the Snowflake user. Remove the header, the footer, and all new lines from the value:

ALTER USER my_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...';

Step 5. Set the Sling connection:

sling conns set SNOWFLAKE type=snowflake account=<account> user=<user> database=<database> \
  authenticator=snowflake_jwt private_key=/path/to/rsa_key.p8

# add private_key_passphrase if the key is encrypted
sling conns set SNOWFLAKE ... private_key_passphrase=<passphrase>

Step 6. Test the connection:

sling conns test SNOWFLAKE

Use a key in an environment variable

To put a key in an environment variable, encode the DER body with base64. This removes the new lines:

export SNOWFLAKE_PRIVATE_KEY=$(openssl pkcs8 -topk8 -in rsa_key.p8 -outform DER -nocrypt | base64 | tr -d '\n')

Then use the variable in the connection:

connections:
  SNOWFLAKE:
    type: snowflake
    account: <account>
    user: <user>
    database: <database>
    authenticator: snowflake_jwt
    private_key: ${SNOWFLAKE_PRIVATE_KEY}

If you are facing issues connecting, please reach out to us at [e-mail], on discord or open a Github Issue here.