Snowflake

To write transformed data into a Snowflake table using Upsolver, you need to create a connection with the appropriate credentials. Upsolver supports two authentication methods for Snowflake:

1. Username and Password

2. Key Pair Authentication: Requires a username, private key file, and an optional private key passphrase.

Syntax

CREATE SNOWFLAKE CONNECTION <connection_identifier> 
    CONNECTION_STRING = '<connection_string>' 
    USER_NAME = '<user_name>'
    { PASSWORD = '<password>' 
        | PRIVATE_KEY_FILE='<file_path>' [PRIVATE_KEY_FILE_PWD=<file_passphrase>] }
    [ MAX_CONCURRENT_CONNECTIONS = <integer> ]
    [ COMMENT = '<comment>' ]

Jump to

• CONNECTION_STRING

• USER_NAME

• PASSWORD

• PRIVATE_KEY_FILE

• PRIVATE_KEY_FILE_PWD

• MAX_CONCURRENT_CONNECTIONS

• COMMENT

Connection options

CONNECTION_STRING — editable

Type: text

The connection string to use when connecting to the database.

Format:

jdbc:snowflake://
    <ACCOUNT_WITH_REGION>.snowflakecomputing.com?
    db=<DB_NAME>&warehouse=<WAREHOUSE_NAME>&role=<ROLE_NAME>

Where:

• ACCOUNT_WITH_REGION.snowflakecomputing.com

  • The connection URL in Snowflake.

  • Example: snowflakedemo.us-east-2.aws.snowflakecomputing.com

• DB_NAME

  • The name of the database to connect to.

• WAREHOUSE_NAME

  • (Optional) The warehouse name. If not provided, the default warehouse is used. If no default warehouse exists, the CREATE CONNECTION command fails.

• ROLE_NAME

  • (Optional) The name of the role to use when connecting. If not provided, the default role is used. If no default role exists, the CREATE CONNECTION command fails. To ensure proper functionality and access for our user when connecting to Snowflake, the following permissions need to be granted in snowflake:

    • Grant usage on the specified database:

      GRANT USAGE ON DATABASE <database_name> TO ROLE <Role_name>;
    • Grant usage on the specified schema within the database:

      GRANT USAGE ON SCHEMA <schema_name> TO ROLE <Role_name>;
    • Grant permissions to create tables and stages within the specified schema:

       CREATE TABLE, CREATE STAGE ON SCHEMA <schema_name> TO ROLE <Role_name>;
    • Grant permission to create schemas within the specified database:

      CREATE SCHEMA ON DATABASE <database_name> TO ROLE <Role_name>;

Read more about connection string arguments in Snowflake.

USER_NAME

Type: text

The user to authenticate to the database with.

PASSWORD — editable with user_name

Type: text

The password for the user.

PRIVATE_KEY_FILE - editable with user_name

Type: text

Local path to the private key on Upsolver's server.

Key-Pair Authentication Setup

Step 1 - Generate keys

Configure your Snowflake account for key-pair authentication following the Snowflake Documentation.

Step 2 - Upload the Key

Upload the private key to Upsolver's server. Use the resulting file path on the server as the PRIVATE_KEY_FILE parameter for the connection setup. For detailed instructions on uploading the key, refer to this guide.

PRIVATE_KEY_FILE_PWD- editable with PRIVATE_KEY_FILE

(Optional) Specifies the passphrase used to decrypt the private key file if it is encrypted. This parameter must be used alongside PRIVATE_KEY_FILE.

MAX_CONCURRENT_CONNECTIONS — editable

Type: integer

(Optional) The maximum number of concurrent connections to the database.

Limiting this may reduce the load on the database but could result in longer data latency.

COMMENT — editable

Type: text

(Optional) A description or comment regarding this connection.

Minimum example

CREATE SNOWFLAKE CONNECTION my_snowflake_connection
    CONNECTION_STRING = 'jdbc:snowflake://
        snowflakedemo.us-east-1.snowflakecomputing.com?
        db=DEMO_DB&warehouse=DEMO_WH&role=ADMIN'
    USER_NAME = 'your_user'
    PASSWORD = 'your_pass';

Full example

User-Password Authentication Example

CREATE SNOWFLAKE CONNECTION my_snowflake_connection
    CONNECTION_STRING = 'jdbc:snowflake://
        snowflakedemo.us-east-1.snowflakecomputing.com?
        db=DEMO_DB&warehouse=DEMO_WH&role=ADMIN'
    USER_NAME = 'your_user'
    PASSWORD = 'your_pass'
    MAX_CONCURRENT_CONNECTIONS = 23
    COMMENT = 'Snowflake connection example';

Key-Pair Authentication Example

CREATE SNOWFLAKE CONNECTION my_snowflake_connection
    CONNECTION_STRING = 'jdbc:snowflake://
        snowflakedemo.us-east-1.snowflakecomputing.com?
        db=DEMO_DB&warehouse=DEMO_WH&role=ADMIN'
    USER_NAME = 'your_user'
    PRIVATE_KEY_FILE = 'path/to/rsa_key_encrypted.p8'
    PRIVATE_KEY_FILE_PWD = 'private_key_passphrase'
    MAX_CONCURRENT_CONNECTIONS = 23
    COMMENT = 'Snowflake connection example';