Snowflake
This example demonstrates how to use the Snowflake connector in ydata-sdk.
Accounts with MFA enforced
Snowflake is phasing out password-only logins, and a user with MFA enforced
cannot connect with just username/password. Leave password out of the
connection string and pass one of these to SnowflakeConnector instead:
| Option | Authenticator | Use |
|---|---|---|
private_key_file (+ private_key_file_pwd for encrypted keys) |
SNOWFLAKE_JWT (default when a key is given) |
Key-pair authentication, the recommended option for service users |
token |
PROGRAMMATIC_ACCESS_TOKEN (default when a token is given) |
A programmatic access token, a long-lived secret that MFA does not block |
Interactive logins (browser-based SSO, one-time MFA passcodes) are not supported: the connector runs unattended. OAuth access tokens are not supported either, because they are short-lived and the connector cannot refresh them. An invalid combination (for example a password-less connection without a key or token) is rejected when the connector is created, with a message naming the alternatives.
Setting this up on the Snowflake side has a few non-obvious steps; see Setting up token or key-pair authentication below.
Example Code
"""Example of a snowflake connector interface."""
from ydata.connectors import SnowflakeConnector
if __name__ == "__main__":
USERNAME = "username"
PASSWORD = "password"
ACCOUNT_IDENTIFIER = "account"
PORT = 443
DATABASE_NAME = "DATABASE"
SCHEMA = "SCHEMA"
WAREHOUSE = "WAREHOUSE"
conn_str = {
"hostname": ACCOUNT_IDENTIFIER,
"username": USERNAME,
"password": PASSWORD,
"port": PORT,
"database": DATABASE_NAME,
"warehouse": WAREHOUSE
}
conn = SnowflakeConnector(conn_string=conn_str)
# Accounts with MFA enforced cannot log in with a password alone. Drop
# "password" from conn_str and authenticate with one of the unattended
# methods below (interactive logins such as browser SSO are not supported).
# "username" must then be the user's LOGIN_NAME (often the e-mail), not
# the user NAME, or Snowflake reports the token as invalid.
#
# - an RSA key pair (recommended for service users)
# conn = SnowflakeConnector(conn_string=conn_str,
# private_key_file="/path/to/rsa_key.p8",
# private_key_file_pwd="key-passphrase") # only for encrypted keys
#
# - a programmatic access token
# conn = SnowflakeConnector(conn_string=conn_str, token="<programmatic access token>")
schemas_available = conn.list_schemas()
# use a schema
conn.set_schema('schema')
# Get a the details of a selected database schema (tables, columns, etc)
conn.get_database_schema("schema")
# Query
conn.query(quer="SELECT * FROM TABLE")
# Query sample
sample = conn.query_sample(query="SELECT * FROM TABLE",
sample_size=1000)
# Read a table
table = conn.get_table(table="table",
schema_name='schema',
index_col='id')
# Read table sample
tables = conn.get_tables(tables=['table', 'TABLE'],
schema_name='schema')
# write data to a new table
conn.write_table(data=sample,
name='new_table',
schema_name='schema',
if_exists='replace')
Setting up token or key-pair authentication
Everything below is run in Snowsight (or any SQL client) by an administrator, usually
with the ACCOUNTADMIN or SECURITYADMIN role. Replace <user> with the Snowflake user
the connector will use.
1. Use the login name, not the user name
A Snowflake user has two names: NAME, the identifier used in SQL, and LOGIN_NAME, the
value entered at login. They are often identical, but a user created from an e-mail
signup typically has the e-mail as LOGIN_NAME and a short NAME.
The connector's username must be the LOGIN_NAME when authenticating with a token
or key pair. A password login accepts either value, which is why a connection string that
worked with a password can stop working when the password is swapped for a token: Snowflake
then reports "Programmatic access token is invalid" even though the token is fine.
2a. Programmatic access token (PAT)
Generate the token, either in Snowsight (user menu → Settings → Authentication → Programmatic access tokens) or with SQL. The secret is shown once; store it in a secrets manager, never in code.
ALTER USER "<user>" ADD PROGRAMMATIC ACCESS TOKEN ydata_connector DAYS_TO_EXPIRY = 90;
SHOW USER PROGRAMMATIC ACCESS TOKENS FOR USER "<user>"; -- status, expiry, role restriction
Tokens expire (15 days by default, up to 365 with DAYS_TO_EXPIRY or an authentication
policy), so plan for rotation: ALTER USER "<user>" ROTATE PROGRAMMATIC ACCESS TOKEN
ydata_connector; returns a new secret and keeps the old one valid for 24 more hours
(EXPIRE_ROTATED_TOKEN_AFTER_HOURS changes the grace period; 0 revokes it at once). The
rotation cannot be run from a session that itself logged in with that user's token.
A human user needs a network policy to log in with a token. Snowflake lets a PERSON
user generate a token without one, but refuses to authenticate with it until the user is
covered by a network policy, again with the generic "Programmatic access token is invalid"
error. (Service users, TYPE = SERVICE, need the policy even to generate the token.) There
are two ways to satisfy the requirement:
Restrict token logins to the addresses the connector runs from. Replace the CIDR with the egress IP(s) of your deployment.
Make the network policy optional for token logins of this user. Authentication
policies are schema-level objects, so a database and schema are needed to hold it.
AUTHENTICATION_METHODS = ('ALL') keeps the user's other logins working.
CREATE DATABASE IF NOT EXISTS ADMIN;
CREATE AUTHENTICATION POLICY IF NOT EXISTS ADMIN.PUBLIC.ydata_pat_policy
AUTHENTICATION_METHODS = ('ALL')
PAT_POLICY = (NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED);
ALTER USER "<user>" SET AUTHENTICATION POLICY ADMIN.PUBLIC.ydata_pat_policy;
ENFORCED_NOT_REQUIRED still honours a network policy if one exists; it only stops
the absence of one from blocking token logins. There is no user-level
"bypass network policy for tokens" property.
Then connect:
conn = SnowflakeConnector(
conn_string={"hostname": "<account>", "username": "<login name>",
"database": "<db>", "schema": "<schema>", "warehouse": "<wh>"},
token="<programmatic access token>",
)
2b. Key pair
Key-pair authentication does not expire and needs no network policy, which makes it the
best fit for a dedicated service user. Generate the key pair on the machine that will run
the connector (-nocrypt produces an unencrypted key; omit it to be prompted for a
passphrase, which then goes in private_key_file_pwd):
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
chmod 600 rsa_key.p8
Register the public key on the user (paste the contents of rsa_key.pub without the
BEGIN/END lines) and verify the fingerprint is recorded:
ALTER USER "<user>" SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
DESC USER "<user>"; -- RSA_PUBLIC_KEY_FP must now be set
Then connect:
conn = SnowflakeConnector(
conn_string={"hostname": "<account>", "username": "<login name>",
"database": "<db>", "schema": "<schema>", "warehouse": "<wh>"},
private_key_file="/secrets/rsa_key.p8",
# private_key_file_pwd="...", # only for an encrypted key
)
3. Checking the setup from Snowflake's side
These statements contain no secrets and answer most first-time problems:
-- Who is this user, and which name does the driver need?
SHOW USERS LIKE '%<user>%'; -- NAME vs LOGIN_NAME, TYPE, DISABLED
DESC USER "<user>"; -- same, plus RSA_PUBLIC_KEY_FP, DEFAULT_WAREHOUSE
-- Tokens and the policies that apply to the user
SHOW USER PROGRAMMATIC ACCESS TOKENS FOR USER "<user>";
SHOW PARAMETERS LIKE 'NETWORK_POLICY' FOR USER "<user>"; -- empty value = no network policy
SHOW PARAMETERS LIKE '%POLICY%' FOR USER "<user>";
-- Resources the connector will use
SHOW WAREHOUSES; -- the warehouse in the connection string must exist
SHOW GRANTS TO USER "<user>"; -- roles, and through them database/warehouse access
-- How recent logins actually authenticated (available immediately, unlike ACCOUNT_USAGE)
SELECT EVENT_TIMESTAMP, IS_SUCCESS, FIRST_AUTHENTICATION_FACTOR, SECOND_AUTHENTICATION_FACTOR,
ERROR_CODE, ERROR_MESSAGE, CLIENT_IP, REPORTED_CLIENT_TYPE
FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY_BY_USER(USER_NAME => '<user>', RESULT_LIMIT => 20))
ORDER BY EVENT_TIMESTAMP DESC;
A successful connector login shows FIRST_AUTHENTICATION_FACTOR = 'PROGRAMMATIC_ACCESS_TOKEN'
(or 'RSA_KEYPAIR') and no second factor. Failed attempts list Snowflake's internal
ERROR_MESSAGE, which is more specific than what the driver receives.
4. Common errors
| Error | Cause | Fix |
|---|---|---|
Programmatic access token is invalid |
The usual message for any token rejection: username is the NAME instead of the LOGIN_NAME; the user has no network policy (human users); the token was generated for another user; it expired or was revoked; its role restriction no longer exists. |
Steps 1 and 2a above; check SHOW USER PROGRAMMATIC ACCESS TOKENS and the login history. |
Invalid Snowflake credentials: password authentication requires a password ... |
Raised by the connector when neither password, private_key_file nor token is given. |
Pass one of them. |
Invalid Snowflake credentials: authenticator '...' is not supported |
An interactive (EXTERNALBROWSER, USERNAME_PASSWORD_MFA) or OAuth authenticator was requested. |
Use a token or key pair. |
No active warehouse selected in the current session |
The warehouse in the connection string does not exist or is not granted to the user's role. Snowflake accepts the login and silently leaves the session without a warehouse. |
SHOW WAREHOUSES; fix the name or grant USAGE. |
JWT token is invalid |
Key-pair login with a key that does not match RSA_PUBLIC_KEY, or username is not the LOGIN_NAME. |
Compare DESC USER → RSA_PUBLIC_KEY_FP with openssl rsa -pubin -in rsa_key.pub -outform DER \| openssl dgst -sha256 -binary \| openssl enc -base64. |
TypeError: reduce() of empty iterable with no initial value during get_database_schema |
snowflake-sqlalchemy older than 1.9 crashes when the session has no current schema. |
Install the pinned driver: pip install "ydata-sdk[snowflake]". |