Snowflake and Amazon Web Services S3

Snowflake and Amazon Web Services S3

If you want to use Snowflake and Amazon Web Services S3 (AWS S3) for your data warehouse, you need the following prerequisites:

  • Access to Snowflake
  • An Amazon Web Services S3 account

Configure Snowflake

How to configure Snowflake.

In Snowflake, create a new SQL worksheet and paste in the query shown below. The query will create a Snowflake database, a warehouse, and a user with the required permissions.

After changing the MY_NAME, MY_COMMENT, and MY_PASSWORD variables, run the entire query.

configure_snowflake.sql

_27
-- Set up variables
_27
SET MY_NAME = UPPER('Y42_DEMO');
_27
SET MY_COMMENT = 'Y42 platform demo';
_27
SET MY_PASSWORD = 'my_secret_password';
_27
_27
-- Your current role will get access to the role and database we create here
_27
SET EXECUTING_ROLE = CURRENT_ROLE();
_27
_27
-- Set up user and role
_27
CREATE ROLE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT;
_27
CREATE USER IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT PASSWORD = $MY_PASSWORD MUST_CHANGE_PASSWORD = false DEFAULT_ROLE = $MY_NAME;
_27
GRANT ROLE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER('ACCOUNTADMIN');
_27
GRANT ROLE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($EXECUTING_ROLE);
_27
GRANT ROLE IDENTIFIER($MY_NAME) TO USER IDENTIFIER($MY_NAME);
_27
_27
-- Set up database
_27
CREATE DATABASE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT;
_27
GRANT CREATE SCHEMA ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);
_27
GRANT ALL ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);
_27
GRANT OWNERSHIP ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME) REVOKE CURRENT GRANTS;
_27
_27
-- Set up warehouse
_27
CREATE WAREHOUSE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT WAREHOUSE_SIZE = 'X-Small' AUTO_RESUME = true AUTO_SUSPEND = 60 WAREHOUSE_TYPE = 'STANDARD';
_27
GRANT USAGE ON WAREHOUSE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);
_27
_27
-- Create network policy if required
_27
-- CREATE NETWORK POLICY y42_allow_list ALLOWED_IP_LIST=('<ip_addresses>');

The example query will generate the following credentials:

FieldExample value
UserY42_DEMO
Passwordmy_secret_password
Account [1]https://<account_locator>.<cloud_region_id>.snowflakecomputing.com
WarehouseY42_DEMO
DatabaseY42_DEMO

[1] Region-specific account locator (opens in a new tab). You can retrieve this by hovering over your account in the account list and clicking on the 🔗 link icon that pops up.

Store these credentials in a secure location.

Create a new AWS S3 user

Walkthrough

Creating access keys.

Instructions

You'll need to create a new user in AWS with programmatic access enabled. From the search bar, navigate to IAM and then select Users in the left pane. Click Add users and provide a user name (e.g., y42_demo_user). Now leave the other settings on their default values and create the new user.

Next, click on the newly created user and under Permission policies find the Add permissions button. Select Create inline policy and navigate to the JSON tab. Copy the JSON permissions below and paste it into the editor.

aws3_permissions.json

_31
{
_31
"Version": "2012-10-17",
_31
"Statement": [
_31
{
_31
"Sid": "VisualEditor0",
_31
"Effect": "Allow",
_31
"Action": [
_31
"s3:CreateBucket",
_31
"s3:ListBucket",
_31
"s3:ListAllMyBuckets",
_31
"s3:PutBucketCORS",
_31
"s3:GetBucketLocation",
_31
"s3:PutLifecycleConfiguration",
_31
"s3:PutBucketPublicAccessBlock"
_31
],
_31
"Resource": "arn:aws:s3:::y42*"
_31
},
_31
{
_31
"Sid": "VisualEditor1",
_31
"Effect": "Allow",
_31
"Action": [
_31
"s3:PutObject",
_31
"s3:GetObject",
_31
"s3:DeleteObjectVersion",
_31
"s3:DeleteObject",
_31
"s3:GetObjectVersion"
_31
],
_31
"Resource": "arn:aws:s3:::y42*/*"
_31
}
_31
]
_31
}

Click Next, provide a policy name (e.g., y42_demo_access) and then click Create policy.

Create an AWS S3 access key

On the user page, select the Security Credentials tab and click Create access key. Under Use case, select Command Line Interface (CLI), and confirm this choice when prompted.

Store the access key and secret access keys in a secure location.

Connect Y42

Back on Y42, select Snowflake as the provider. Add all the Snowflake credentials generated by the query above: User, Password, Account URL, Warehouse, and Database.

Under Storage, select Amazon S3 Storage. Enter the Access Key ID and Secret Access Key credentials from the previous step.

Choose a hosting location

Choose a location or region where Y42 will create the AWS S3 bucket.

Up next

You can now continue with configuring the Git repository for your Y42 space.