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.
1. Configure Snowflake Role
The snowlake 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;
2. Configure your Identity Provider
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
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 EXTENTIONS. 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
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 tha 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
- Token Endpoint - see Step 2. A.
- Client ID - see step 2. B. Client Settings
- Client Secret - see step 2. B. Client Settings
- Requested Scopes - This is a combination of the see Resoruce server identifier and the Scope Name from Step 2. C.. The format should be <Resource server identifier>/<Scope name>
- Account - Your Account Identifier in your IdP
- 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.