How to connect to Snowflake using info from snowflake_connector_python

GX OSS Support
,
1

I use following code to connect to Snowflake from python (using snowflake_connector_python version 3.0.1).

import snowflake.connector

snowflake.connector.connect(
            authenticator='externalbrowser',
            user=user,
            role=role,
            warehouse=warehouse,
            account=account,
            region=region)

Please note that my authenticator is ‘externalbrowser’. I’m using browser based SSO.

My question is how should I connect from GX? What should be my connection string and how do I provide ‘authenticator’ parameter?

I appreciate help, thanks a lot :slight_smile:

2

Hi @dnikolic, thanks for reaching out!

First, here’s a link to our docs page on connecting to Snowflake data if you haven’t yet seen it: Connect to SQL database Data Assets

For browser-based SSO authentication, the connection string format that you want to use is below; note that you’ll have the additional query param authenticator=externalbrowser and you need to set your password to an empty string.

snowflake://{USER_NAME}:{PASSWORD}@{ACCOUNT_NAME}/{DATABASE_NAME}/{SCHEMA_NAME}?warehouse={WAREHOUSE_NAME}&role={ROLE_NAME}&authenticator=externalbrowser

Here’s an example of creating a Snowflake Data Source with that connection string:

import os
import great_expectations as gx

USER_NAME = os.getenv("SNOWFLAKE_USERNAME")
ACCOUNT_NAME = os.getenv("SNOWFLAKE_ACCOUNT")
PASSWORD = ""

ROLE_NAME = "<your-role>"
DATABASE_NAME = "<your-database>"
SCHEMA_NAME = "<your-schema>"
WAREHOUSE_NAME = "<your-warehouse>"

snowflake_connection_str = (
    f"snowflake://{USER_NAME}:{PASSWORD}@{ACCOUNT_NAME}/{DATABASE_NAME}/{SCHEMA_NAME}?"
    f"warehouse={WAREHOUSE_NAME}&role={ROLE_NAME}&authenticator=externalbrowser"
)

context = gx.get_context()

datasource = context.sources.add_snowflake(
    name="snowflake_data_source",
    connection_string=snowflake_connection_str
)
3

Hi @rachel.house , thanks a lot for prompt response.

It is now clear how to provide ‘authenticator’ parameter, thanks.

Unfortunaltely I’m still not able to connect. Getting following error:

raise TestConnectionError(
great_expectations.datasource.fluent.interfaces.TestConnectionError: Attempt to connect to datasource failed with the following error message: 
(snowflake.connector.errors.OperationalError) 250003: Failed to get the response. Hanging? method: post, 
url: https://myaccount.snowflakecomputing.com:443/session/authenticator-request?request_guid=2df584de-b8d1-4e8b-9087-d1a5ed6ea5c1

I noticed that in my python connection string (which works fine) I have also ‘region’ parameter. I tried using following connection string:

f"snowflake://{USER_NAME}:{PASSWORD}@{ACCOUNT_NAME}/{REGION}/{DATABASE_NAME}/{SCHEMA_NAME}?"
    f"warehouse={WAREHOUSE_NAME}&role={ROLE_NAME}&authenticator=externalbrowser"

but that did not work. Error was ‘Invalid name space is specified: /<database_name>/<schema_name>’.

Any suggestion?

Thanks a lot :slight_smile:

4

Hi @dnikolic, the authenticator query param is the &authenticator=externalbrowser text at the end of the connection string - it’s provided just by including it in the connection string (as shown in my last message).

You don’t need to include region separately. I see now that calling it ACCOUNT_NAME in my example might have been confusing, since Snowflake has two formats of account identifiers, the account name and the account locator. You can use either the account name or account locator in the connection string.

  • Note that if you copy your account name using the Snowflake instructions, it’ll be in format <orgname>.<account_name>. For the connection string, replace the period (.) with a hyphen (-). An example copied account name WGECESQ.WBU94726 would be specified as WGECESQ-WBU94726 in your connection string.

As an example, below are two connection strings, with fake values, that use the correct connection string format:

snowflake://{USER_NAME}:{PASSWORD}@{ACCOUNT_IDENTIFIER}/{DATABASE_NAME}/{SCHEMA_NAME}?warehouse={WAREHOUSE_NAME}&role={ROLE_NAME}&authenticator=externalbrowser

With account name:

snowflake://snowflakeuser1:@WGECESQ-WBU94726/SNOWFLAKEDB/MYTABLE?warehouse=DEFAULT_WAREHOUSE&role=PUBLIC&authenticator=externalbrowser

With account locator:

snowflake://snowflakeuser1:@wbu94726.us-east-1/SNOWFLAKEDB/MYTABLE?warehouse=DEFAULT_WAREHOUSE&role=PUBLIC&authenticator=externalbrowser