Docs
Snowflake with AWS S3

Use Snowflake and Amazon Web Services S3

Prerequisites

  • Access to Snowflake
  • Amazon Web Services S3 account

Configure Snowflake

Walkthrough

Configuring Snowflake.

Instructions

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_PASSOWRD variables. Run the entire query.

configure_snowflake.sql

_20
-- Set up variables
_20
SET MY_NAME = UPPER('Y42_DEMO');
_20
SET MY_COMMENT = 'Y42 DataOps Cloud demo';
_20
SET MY_PASSWORD = 'my_secret_password';
_20
_20
-- Set up user / role
_20
CREATE ROLE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT;
_20
CREATE USER IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT PASSWORD = $MY_PASSWORD MUST_CHANGE_PASSWORD = false DEFAULT_ROLE = $MY_NAME;
_20
GRANT ROLE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER('ACCOUNTADMIN');
_20
GRANT ROLE IDENTIFIER($MY_NAME) TO USER IDENTIFIER($MY_NAME);
_20
_20
-- Set up database
_20
CREATE DATABASE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT;
_20
GRANT CREATE SCHEMA ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);
_20
GRANT ALL ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);
_20
GRANT OWNERSHIP ON DATABASE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME) REVOKE CURRENT GRANTS;
_20
_20
-- Set up warehouse
_20
CREATE WAREHOUSE IDENTIFIER($MY_NAME) COMMENT = $MY_COMMENT WAREHOUSE_SIZE = 'X-Small' AUTO_RESUME = true AUTO_SUSPEND = 120 WAREHOUSE_TYPE = 'STANDARD';
_20
GRANT USAGE ON WAREHOUSE IDENTIFIER($MY_NAME) TO ROLE IDENTIFIER($MY_NAME);

The example query will generate the following credentials:

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

[1] Region-specific account locator (opens in a new tab)

Create a new AWS S3 user

Walkthrough

Creating access keys.

Instructions

Create a new user in AWS with programmatic access enabled.

Next, in the configuration detail page, add a new inline policy. Copy the JSON permissions below and paste it into the editor field in the JSON tab.

aws3_permissions.json

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

Create an AWS S3 access key

Select the "Security Credentials" tab and click "Create access key". When prompted, select "Command Line Interface (CLI) and confirm this choice.

Store the access key and secret access keys in a secure location. Lastly, enter the secret access keys in Y42.