Connecting to Data Warehouse

While creating your awesome ML models, and webapps you might need to connect to tenant’s data warehouse - this can be either Redshift or Snowflake. peak-sdk provides with a function to get the credentials for a tenant data warehouse. These credentials can then be used to connect to the data warehouse using your favorite package or tool.

Getting the Credentials

Getting the credentials for the tenant’s data warehouse is as easy as calling a function - you just need to run the get_credentials function in the tenant module of the peak-sdk

from typing import Any
from peak.resources import tenants

client: tenants.Tenant = tenants.get_client()
credentials: dict[str, Any] = client.get_credentials()

The Response

Redshift

{
    "connectionString": "postgresql://good-user:<password>@a-good-redshift-host.com:5439/good-db",
    "iamRole": "arn:aws:iam::<account>:role/<role>,arn:aws:iam::<account>:role/<role>",
    "port": "5439",
    "schema": "publish",
    "user": "good-user",
    "authType": "basic",
    "database": "good-db",
    "host": "a-good-redshift-host.com",
    "password": "<password>",
    "dataWarehouseType": "amazon_redshift"
}

Snowflake (Basic Auth)

{
    "application": "good_app",
    "connectionString": "snowflake://user:<password>@host/database",
    "integration": "integration",
    "port": 443,
    "role": "role_name",
    "schema": "schema",
    "user": "user",
    "warehouse": "warehouse",
    "authType": "basic",
    "database": "dabase",
    "host": "host",
    "password": "<password>",
    "dataWarehouseType": "snowflake"
}

Snowflake (OAuth)

{
    "application": "application",
    "connectionString": "snowflake://host/database?authenticator=OAUTH&token=<generated-access-token>",
    "integration": "integration_name",
    "port": 443,
    "role": "role_name",
    "schema": "schema",
    "warehouse": "warehouse",
    "accessToken": "<generated-access-token>",
    "authType": "oauth",
    "database": "database",
    "host": "host",
    "dataWarehouseType": "snowflake"
}

Connecting to the Warehouse

The get_credentials function returns all the details about the warehouse including the credentials you can use to connect to the warehouse. Not only that but it also returns a connection string you can easily use to connect.
For Redshift and Snowflake with Basic Auth, it returns the username and password whereas for Snwoflake with OAuth setup, it returns the Access Token (more details about this later).

Here’s a sample code of how you can connect to Redshift using the connection string that get_credentials returns:

"""
This expects the following packages to be installed on top of peak-sdk
- For Redshift:
    - sqlalchemy-redshift
- For Snowflake:
    - snowflake-sqlalchemy

To work properly, these packages might need psycopg2 as well. So, remember
to install that as well.
"""

from sqlalchemy import create_engine, text
from peak.resources import tenants

client = tenants.get_client()
credentials = client.get_credentials()

engine = create_engine(credentials["connectionString"])

try:
    connection = engine.connect()
    results = connection.execute(text("select 1")).fetchone()
    print(results[0])
finally:
    connection.close()
    engine.dispose()

One thing to note is that the connection string returned from the API does not contain optional keys like - schema, role, warehouse and application.
If you are using the snowflake connector and don’t wish to use the connection string, you can use the available values as mentioned in the response format above.

Snowflake OAuth Access Token

When OAuth has been enabled for a Snowflake cluster, the connection should be made using an access token.
For your convenience, the API returns the access token and even a connection string formed with the access token.
One thing to note is that this token has a validity of 10 minutes, so a connection should be made using the token within 10 minutes of you calling the function.