Connecting Snowflake

Preparing your Warehouse for Camber

Before you connect Camber to your data warehouse, you must create a dedicated Role and User for Camber. This guide will walk you through creating a user for password authentication or key-pair authentication.

1. Create a Role and Service User for Camber

Log into Snowflake with a user that has ACCOUNTADMIN privileges.

First, create a dedicated role for Camber to use.

USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS CAMBER_ROLE;

Next, create the CAMBER_USER based on the authentication method you plan to use.

Option 1: For Username / Password or Access Token Auth

Create a user with a password. This single user can be used for both direct password authentication and for generating access tokens.

CREATE USER IF NOT EXISTS CAMBER_USER
  PASSWORD = '<password>';

GRANT ROLE CAMBER_ROLE TO USER CAMBER_USER;
ALTER USER CAMBER_USER
  SET DEFAULT_ROLE = CAMBER_ROLE;

Option 2: For Private Key Authentication

This method uses an encrypted RSA key pair for a more secure connection.

A. Generate the Key Pair Run the following commands in your terminal to generate a 4096-bit encrypted RSA key pair.

# 1. Generate an encrypted private key (you will be prompted for a passphrase)
openssl genrsa -aes256 -out rsa_key.p8 4096

# 2. Generate the public key from your private key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

B. Create the User in Snowflake Copy the content of the public key file (rsa_key.pub). Paste the key string (excluding the ----BEGIN... headers/footers) into the RSA_PUBLIC_KEY field of the SQL command.

CREATE USER IF NOT EXISTS CAMBER_USER
  -- Paste your public key string here
  RSA_PUBLIC_KEY = '<your_public_key_string>';

GRANT ROLE CAMBER_ROLE TO USER CAMBER_USER;
ALTER USER CAMBER_USER
  SET DEFAULT_ROLE = CAMBER_ROLE;

Option 3: For Programmatic Access Tokens

This method creates a user and generates a dedicated programmatic access token for authentication.

-- Create a user for programmatic access
CREATE USER IF NOT EXISTS CAMBER_USER;

-- Generate the programmatic access token.
-- IMPORTANT: Securely copy the token output by this command.
ALTER USER CAMBER_USER ADD PROGRAMMATIC ACCESS TOKEN;

-- Grant the role and set it as default
GRANT ROLE CAMBER_ROLE TO USER CAMBER_USER;
ALTER USER CAMBER_USER
  SET DEFAULT_ROLE = CAMBER_ROLE;

2. Grant Permissions to the Camber Role

Now, grant the CAMBER_ROLE the necessary privileges. This includes read access to your data and write access to a schema for Camber to store results.

A. Grant Read Access to Your Data Grant SELECT permissions on any tables you want Camber to access.

GRANT USAGE ON DATABASE <database> TO ROLE CAMBER_ROLE;
GRANT USAGE ON SCHEMA <schema> TO ROLE CAMBER_ROLE;
GRANT SELECT ON TABLE <schema>.<table1> TO ROLE CAMBER_ROLE;
GRANT SELECT ON TABLE <schema>.<table2> TO ROLE CAMBER_ROLE;
...
GRANT SELECT ON TABLE <schema>.<tableN> TO ROLE CAMBER_ROLE;

B. Create a Write Schema for Camber Create a dedicated schema for Camber to write intermediate results and temporary tables.

CREATE SCHEMA IF NOT EXISTS CAMBER_OUTPUT;
GRANT ALL ON SCHEMA CAMBER_OUTPUT TO ROLE CAMBER_ROLE;

(Optional) Create a Dedicated Warehouse

You can create a dedicated warehouse for Camber to ensure workloads do not interfere with other operations.

CREATE WAREHOUSE IF NOT EXISTS CAMBER_WH
  WAREHOUSE_SIZE = <wh_size>
  AUTO_SUSPEND = 300
  INITIALLY_SUSPENDED = true;

GRANT ALL PRIVILEGES ON WAREHOUSE CAMBER_WH TO ROLE CAMBER_ROLE;

3. (Optional) Add Camber’s static IP addresses to your Network Policy if you have one

Connecting your Warehouse to Camber

Now that you have a proper Service User created for Camber, you can use it to connect Camber to your warehouse.

Initial Configuration of Credentials

1. Log in to your Camber account at app.cambercloud.com
2. Navigate to the Connector section.
3. Click the Snowflake tab. You will be prompted to enter the necessary information.

Common Fields

First, fill in the fields that are common to all authentication methods:

• Account: Everything before the .snowflakecomputing.com in your snowflake URL. For example, if your Snowflake account URL is my-company.us-east-1.snowflakecomputing.com then the value to enter is my-company.us-east-1.
• Warehouse: The warehouse for Camber to use (e.g., CAMBER_WH).
• Database: Your database name.
• Schema: CAMBER_OUTPUT (or the schema you created in step 2).
• Role: CAMBER_ROLE (or the role you created in step 1).

Authentication Method

Next, select your preferred authentication method and provide the required credentials.

1. Username / Password

This is the most straightforward method.

• User*: The user you created (e.g., CAMBER_USER).
• Password*: The <password> you set for the CAMBER_USER in step 1.

2. Access Token

Use a Snowflake Access Token for authentication.

• User*: The user you created (e.g., CAMBER_USER).
• Access Token*: Your generated Snowflake access token.

3. Private Key

Use key-pair authentication for a more secure connection. This requires an RSA key to be assigned to the user (as shown in the optional part of step 1).

• User*: The user you created (e.g., CAMBER_USER).
• Private Key*: Paste the full contents of your private key file, including the header and footer.

  -----BEGIN PRIVATE KEY-----
  MIIEv...
  ...
  -----END PRIVATE KEY-----
• Private Key Password (Optional): If your private key is encrypted, enter the passphrase here.

Snowflake Notebook Example

You can find this application in the demos folder of your Jupyter notebook environment.

Snowflake Connection Demo

This notebook demonstrates how to connect to Snowflake using the camber.snowflake.connect function.

This function is designed to securely read credentials from your Camber environment variables, making it easy to connect to your data warehouse without hard-coding sensitive information.

Prerequisites:

Before you begin, ensure you have set your Snowflake credentials in https://app.cambercloud.com/data-connectors

# Import necessary libraries
import pandas as pd
import camber.snowflake as sf
from camber.exceptions import CamberEnvironmentException

Now, we’ll call the sf.connect() function. It will automatically read all the environment variables we just checked and attempt to establish a connection.

conn = None
try:
    conn = sf.connect()
    print("Connection to Snowflake successful!")
    
except CamberEnvironmentException as e:
    print(f"Connection Failed: {e}")
    print("Please ensure your Snowflake environment variables are set correctly.")
except Exception as e:
    print(f"An unexpected error occurred: {e}")

Connection to Snowflake successful!

If the connection was successful, we can run a simple query to verify that everything is working.

if conn:
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT CURRENT_VERSION()")
        
        # Fetch the result
        version = cursor.fetchone()[0]
        print(f"Snowflake Version: {version}")
        
    except Exception as e:
        print(f"Query failed: {e}")
    finally:
        # Always close the cursor
        if 'cursor' in locals():
            cursor.close()
else:
    print("Skipping query: Connection object 'conn' was not created.")

Snowflake Version: 9.34.0

The connection object is compatible with Pandas’ read_sql function. Let’s try to list the databases in your account.

if conn:
    try:
        df = pd.read_sql("SHOW DATABASES LIMIT 10;", conn)
        print("Successfully fetched databases:")
        display(df)
    except Exception as e:
        print(f"Pandas read_sql failed: {e}")
else:
    print("Skipping query: Connection object 'conn' was not created.")

/tmp/ipykernel_480/1565414139.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql("SHOW DATABASES LIMIT 10;", conn)

Successfully fetched databases:

Finally, it’s important to close the connection when you’re done to free up resources.

if conn:
    try:
        conn.close()
        print("Snowflake connection closed.")
    except Exception as e:
        print(f"Error closing connection: {e}")
else:
    print("No connection to close.")

Snowflake connection closed.