Skip to content

Connect Snowflake to S3 Storage via Key/Secret

In this tutorial we will show how to generate a key and secret for an S3 bucket and then use that in Snowflake to create a stage.

Requirements

  • Snowflake account, you can use a free trial. We also assume no complex security needs.
  • AWS account, you can setup a free account to get started.

Video

Video in development.

Download

  • Sample data (Link)

AWS

Sign into your aws account.

If you don't have a bucket yet follow here

Search S3 in the navigation bar. UPDATE

Click Create bucket. UPDATE

Select general purpose and give your bucket a name. UPDATE

Keep all the default settings and click "Create bucket". UPDATE""

Lets start by selecting the bucket we want Snowflake to access. UPDATE

We'll first copy our ARN by going to properties. UPDATE

If we have a new bucket, we can take the time here to upload the sample data. UPDATE

Access Policy

Lets setup a read policy that we later apply to a user that will then be used by Snowflake to access the bucket. Search and click on "IAM". UPDATE

Click Policies. UPDATE

Click create policy. UPDATE

Next we'll want to click json and add the JSON below and updating our ARN.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
            "s3:GetObject",
            "s3:GetObjectVersion"
            ],
            "Resource": "<COPY ARN HERE>/*" /* (1)! */
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "<COPY ARN HERE>", /* (2)! */
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "*"
                    ]
                }
            }
        }
    ]
}

  1. Copy ARN name

  2. Copy ARN name

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
            "s3:GetObject",
            "s3:GetObjectVersion"
            ],
            "Resource": "arn:aws:s3:::danielwilczak/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::danielwilczak",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "*"
                    ]
                }
            }
        }
    ]
}

This is what it will look like. UPDATE

Give the policy a name, make sure to remember this name we will need it later. Click "Create Policy". UPDATE

Create User

Lets create the user and apply the policy to it. Start by going to users. UPDATE

Click create user. UPDATE

Give the user a name. UPDATE

Now will select "Attach policies directly", search and select our policy, and click next. UPDATE

Click "Create user". UPDATE

Key/Secret Generation

Now that we have our user lets generate our credientals. Select the user. UPDATE

Go to "Security credentials" and then click "create access key". UPDATE

Select other and then click next. UPDATE

Click "Create access key". UPDATE

Copy your access key and secret we will use this in Snowflake in the next step. UPDATE

Snowflake

Lets now head into Snowflake and create a sql sheet in workspaces.

If you don't have a database, schema or warehouse yet. 
```sql linenums="1"

-- Create a database to store our schemas.
create database if not exists raw;

-- Create the schema. The schema stores all objects.
create schema if not exists raw.aws;

/*
    Warehouses are synonymous with the idea of compute
    resources in other systems. We will use this
    warehouse to query our integration and to load data.
*/
create warehouse if not exists development 
    warehouse_size = xsmall
    auto_suspend = 30
    initially_suspended = true;

use database raw;
use schema aws;
use warehouse development;
```

UPDATE

Create a new sheet. UPDATE

From here we'll add our stage code and paste in our bucket name, key and secret. Click run.

1
2
3
4
5
6
7
create or alter stage s3
    url='s3://<BUCKET NAME>/'
    credentials=(
        aws_key_id='<KEY>'
        aws_secret_key='<Secret>'
    )
    directory=(enable=true);

1
2
3
4
5
6
7
create or alter stage s3
    url='s3://danielwilczak/'
    credentials=(
        aws_key_id='AKIARH....BWUZ7Q'
        aws_secret_key='Vh597QKZqMX....sdoAAK4GD90M'
    )
    directory=(enable=true);

1
Stage area S3 successfully created.

This is what it will look like once ran. UPDATE

Now that we have created our stage we can go view the files in it. If you don't see the file right away, hit refresh. From here we can start loading data. But the process is the same as our S3 tutorial and not need to repeat here. UPDATE