Configuring a Data Destination to Snowflake.
At One Model, we think it's critical that your data goes to where you need it most. If Snowflake is your platform of choice, you can use our Snowflake Data Destination to send data from One Model to Snowflake.
Choose an authentication method
One Model can connect to Snowflake using either of the methods below. Whichever you choose, start with Step 1. Configure Snowflake Role and finish with Step 4. One Model Configuration.
- External OAuth (client credentials) – uses your Identity Provider to issue short-lived tokens. Complete Step 2 and Step 3.
- Programmatic access token – a Snowflake-generated token used in place of a password. Simpler to set up, but the token expires and must be rotated. Skip Steps 2 and 3 and follow Using a programmatic access token instead.
Note: Snowflake is phasing out single-factor password sign-in for users, so we don't recommend a standard Snowflake password. Key-pair authentication is not currently supported.
1. Configure Snowflake Role
The Snowflake role can be configured using the following commands
-- The required permissions for snowflake role to be able to use data destination
CREATE ROLE IF NOT EXISTS snowflake_om; -- this can be named what you want
GRANT USAGE
ON WAREHOUSE COMPUTE_WH -- your warehouse
TO ROLE snowflake_om;
GRANT USAGE
ON DATABASE SNOWFLAKE_DB -- your db
TO ROLE snowflake_om;
GRANT CREATE INTEGRATION -- will need account with higher privilege
ON ACCOUNT TO ROLE snowflake_om;
USE DATABASE SNOWFLAKE_DB;
SHOW GRANTS TO ROLE snowflake_om;
-- Access to create a schema can be given by:
GRANT CREATE SCHEMA
on DATABASE SNOWFLAKE_DB
TO ROLE snowflake_om;
-- Access to use a schema can be given by:
GRANT USAGE, CREATE STAGE, CREATE FILE FORMAT, CREATE TABLE
on SCHEMA TEST -- your schema
TO ROLE snowflake_om;
Note: we also recommend setting QUOTED_IDENTIFIERS_IGNORE_CASE to True at the account level if you don't want One Model to create tables as lower-case. This can be done using the following command:
ALTER ACCOUNT SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;
2. Configure your Identity Provider (External OAuth only)
In this example, we're using Amazon Cognito.
A. Configure the User Pool
Firstly navigate to User Pools, and select "Create user Pool"
Specify "Machine-to-machine application", and then name your application. This does not need to match the Role configured in snowflake, but in this case we will keep them similar.
Then select "Create user directory"
Finally, the pool is created.
Note: We will use the Token signing by URL later.
B. Configure the Client Settings
In the User Pool created above, navigate to App clients, and select the application created above
You will then see Client ID and Client Secret that we will use later
C. Resource Server
The next step is to create a Resource server. Navigate to Userpool > Domain > Create resource server
This will return the following page.
Note: We will use the Resource server identifier and the Scope Name later
D. Lambda
You will need to create following nodejs lambda. This can use the following code:
export const handler = function(event, context) {
var claims = {}
claims["aud"]= event.callerContext.clientId;
claims["scp"]="session:role:snowflake_m2m_role";
event.response = {
"claimsAndScopeOverrideDetails": {
"idTokenGeneration": {
"claimsToAddOrOverride": claims
},
"accessTokenGeneration": {
"claimsToAddOrOverride": claims
}
}
};
context.done(null, event);
};
Back in Amazon Cognito, add the Lambda as an Extension:
and set the following:
- Type: Authentication
- Authentication: Pre token generation trigger
- Trigger event version: Basic Features + access token customization for user and machine identities - Recommended
- Assign Lambda Function: Reference the function created in the previous step
3. Configure the Integration in Snowflake (External OAuth only)
A. Create the Integration
Add new integration using the code below:
-- Integration for oauth credential flow
CREATE OR REPLACE SECURITY INTEGRATION external_oauth_cognito_m2m -- this can be named whatever you want
TYPE = EXTERNAL_OAUTH
ENABLED = TRUE
EXTERNAL_OAUTH_TYPE = CUSTOM
EXTERNAL_OAUTH_ISSUER = 'https://cognito-idp.ap-southeast-2.amazonaws.com/ap-southeast-2_hN8H7rsZo' -- User pool => App client Client settings
EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://cognito-idp.ap-southeast-2.amazonaws.com/ap-southeast-2_hN8H7rsZo/.well-known/jwks.json' -- User pool => Overview User pool
EXTERNAL_OAUTH_AUDIENCE_LIST = ('188u34ndj6i7mfhqbbcm22am3b') -- need to use lambda with pre token generations in cognito under EXTENSIONS. It is also
EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE'
EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME'
EXTERNAL_OAUTH_SCOPE_MAPPING_ATTRIBUTE = 'scp' -- the field in your response token from idp that decides the role being used format session:role:snowflake_m2m_role
EXTERNAL_OAUTH_ALLOWED_ROLES_LIST = ('snowflake_m2m_role'); -- must be in aws pre token generation lambda needs to add scp (default scope field in token look at line 14) to claim
B. Create the Snowflake User
Use the following code to create the user
CREATE USER 188u34ndj6i7mfhqbbcm22am3b -- client id of cognito app client and in token response from idp field sub
PASSWORD = 'MyPassword123!@#' -- set this to the desired value
DEFAULT_ROLE = snowflake_om; -- role we created earlier
Using a programmatic access token (alternative to Steps 2 and 3)
If you're not using External OAuth, you can authenticate with a Snowflake programmatic access token (PAT). Snowflake supports using a PAT in place of a password, so One Model connects as if using a username and password.
A. Create the Snowflake user
Create a dedicated service user for One Model and grant it the role from Step 1:
CREATE USER IF NOT EXISTS onemodel_svc -- this can be named what you want TYPE = SERVICE DEFAULT_ROLE = snowflake_om; -- role we created in Step 1 GRANT ROLE snowflake_om TO USER onemodel_svc;
Note: Snowflake requires a user to be subject to a network policy before a programmatic access token can be used. If your account doesn't already apply one to this user, add a network policy that allows One Model's IP addresses (contact One Model Support if you need these).
Authentication policies: if an authentication policy already applies to this user or your account, make sure its AUTHENTICATION_METHODS list includes 'PROGRAMMATIC_ACCESS_TOKEN', otherwise the token will be rejected.
B. Generate the token
- In Snowflake, go to Governance & Security > Users & roles and select the One Model service user.
- Under Programmatic access tokens, select Generate token.
- Restrict the token to the role configured in Step 1, and set an expiry that suits your security policy. (Note: Snowflake defaults to 15 days, so we recommend setting a longer expiry to avoid frequent rotation)
- Copy the token. Snowflake only shows it once.
Important: when the token expires, the Data Destination will fail to connect. Generate a new token before the expiry date and update the Password field in One Model.
4. One Model Configuration
The following settings will need to be configured for a Snowflake Data Destination:
- Host - The host URI of your Snowflake account
- Note: This should not have https:// or http:// as starting characters.
- Port - The port used to connect to Snowflake. 443 is the default
- Database - The Database in Snowflake that data is destined for. This field is Case Sensitive
- Warehouse - The Warehouse in Snowflake that the data is destined for. This field is Case Sensitive.
- Schema - The Schema configured in Snowflake that the data is destined for. This field is Case Sensitive
- Username - The username of the Snowflake user that One Model will connect through. We recommend setting up a user in Snowflake specifically for One Model so that it can have the required Snowflake permissions applied
- Authentication Method - External OAuth, or Username/Password if using a programmatic access token
- Password (programmatic access token only) - paste the token generated in Using a programmatic access token
- Token Endpoint (External OAuth only) - see Step 2. A.
- Client ID (External OAuth only) - see step 2. B. Client Settings
- Client Secret (External OAuth only) - see step 2. B. Client Settings
- Requested Scopes (External OAuth only) - This is a combination of the Resource server identifier and the Scope Name from Step 2. C.. The format should be <Resource server identifier>/<Scope name>
- Account - For External OAuth, your Account Identifier in your IdP. For a programmatic access token, your Snowflake account identifier
- Role - The role that has been configured with the required permissions to upload data, see Step 1. This field is Case Sensitive.
Comments
0 comments
Please sign in to leave a comment.