Automating Snowpipe for Amazon S3¶

Step 1: Configure access permissions for the S3 bucket¶

Step 2: Create the IAM Role in AWS¶

To configure access permissions for Snowflake in the AWS Management Console, do the following:

1. From the left-hand navigation pane in the Identity and Access Management (IAM) Dashboard, select Roles.

2. Select Create role.

3. Select AWS account as the trusted entity type.

4. In the Account ID field, enter your own AWS account ID temporarily. Later, you modify the trust relationship and grant access to Snowflake.

5. Select the Require external ID option. An external ID is used to grant access to your AWS resources (such as S3 buckets) to a third party like Snowflake.

   Enter a placeholder ID such as 0000. In a later step, you will modify the trust relationship for your IAM role and specify the external ID for your storage integration.

6. Select Next.

7. Select the policy you created in Step 1: Configure Access Permissions for the S3 Bucket (in this topic).

8. Select Next.

   

9. Enter a name and description for the role, then select Create role.

   You have now created an IAM policy for a bucket, created an IAM role, and attached the policy to the role.

10. On the role summary page, locate and record the Role ARN value. In the next step, you will create a Snowflake integration that references this role.

Step 3: Create a Cloud Storage Integration in Snowflake¶

Create a storage integration using the CREATE STORAGE INTEGRATION command. A storage integration is a Snowflake object that stores a generated identity and access management (IAM) user for your S3 cloud storage, along with an optional set of allowed or blocked storage locations (i.e. buckets). Cloud provider administrators in your organization grant permissions on the storage locations to the generated user. This option allows users to avoid supplying credentials when creating stages or loading data.

A single storage integration can support multiple external (i.e. S3) stages. The URL in the stage definition must align with the S3 buckets (and optional paths) specified for the STORAGE_ALLOWED_LOCATIONS parameter.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<iam_role>'
  STORAGE_ALLOWED_LOCATIONS = ('<protocol>://<bucket>/<path>/', '<protocol>://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('<protocol>://<bucket>/<path>/', '<protocol>://<bucket>/<path>/') ]

Where:

• integration_name is the name of the new integration.

• iam_role is the Amazon Resource Name (ARN) of the role you created in Step 2: Create the IAM Role in AWS (in this topic).

• protocol is one of the following:

  • s3 refers to S3 storage in public AWS regions outside of China.

  • s3china refers to S3 storage in public AWS regions in China.

  • s3gov refers to S3 storage in government regions.

• bucket is the name of a S3 bucket that stores your data files (e.g. mybucket). The required STORAGE_ALLOWED_LOCATIONS parameter and optional STORAGE_BLOCKED_LOCATIONS parameter restrict or block access to these buckets, respectively, when stages that reference this integration are created or modified.

• path is an optional path that can be used to provide granular control over objects in the bucket.

The following example creates an integration that allows access to all buckets in the account but blocks access to the defined sensitivedata folders.

Additional external stages that also use this integration can reference the allowed buckets and paths:

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole'
  STORAGE_ALLOWED_LOCATIONS = ('*')
  STORAGE_BLOCKED_LOCATIONS = ('s3://mybucket1/mypath1/sensitivedata/', 's3://mybucket2/mypath2/sensitivedata/');

Step 4: Retrieve the AWS IAM User for your Snowflake Account¶

1. To retrieve the ARN for the IAM user that was created automatically for your Snowflake account, use the DESCRIBE INTEGRATION.

   DESC INTEGRATION <integration_name>;
   

   Copy

   Where:

   • integration_name is the name of the integration you created in Step 3: Create a Cloud Storage Integration in Snowflake (in this topic).

   For example:

   DESC INTEGRATION s3_int;
   

   Copy

   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   | property                  | property_type | property_value                                                                 | property_default |
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------|
   | ENABLED                   | Boolean       | true                                                                           | false            |
   | STORAGE_ALLOWED_LOCATIONS | List          | s3://mybucket1/mypath1/,s3://mybucket2/mypath2/                                | []               |
   | STORAGE_BLOCKED_LOCATIONS | List          | s3://mybucket1/mypath1/sensitivedata/,s3://mybucket2/mypath2/sensitivedata/    | []               |
   | STORAGE_AWS_IAM_USER_ARN  | String        | arn:aws:iam::123456789001:user/abc1-b-self1234                                 |                  |
   | STORAGE_AWS_ROLE_ARN      | String        | arn:aws:iam::001234567890:role/myrole                                          |                  |
   | STORAGE_AWS_EXTERNAL_ID   | String        | MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=                               |                  |
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   

2. Record the values for the following properties:

   Property	Description
   STORAGE_AWS_IAM_USER_ARN	The AWS IAM user created for your Snowflake account; for example, arn:aws:iam::123456789001:user/abc1-b-self1234. Snowflake provisions a single IAM user for your entire Snowflake account. All S3 storage integrations in your account use that IAM user.
   STORAGE_AWS_EXTERNAL_ID	The external ID that Snowflake uses to establish a trust relationship with AWS. If you didn’t specify an external ID (STORAGE_AWS_EXTERNAL_ID) when you created the storage integration, Snowflake generates an ID for you to use.

   You provide these values in the next section.

Step 5: Grant the IAM User Permissions to Access Bucket Objects¶

The following step-by-step instructions describe how to configure IAM access permissions for Snowflake in your AWS Management Console so that you can use a S3 bucket to load and unload data:

1. Log in to the AWS Management Console.

2. Select IAM.

3. From the left-hand navigation pane, select Roles.

4. Select the role you created in Step 2: Create the IAM Role in AWS (in this topic).

5. Select the Trust relationships tab.

6. Select Edit trust policy.

7. Modify the policy document with the DESC STORAGE INTEGRATION output values you recorded in Step 4: Retrieve the AWS IAM User for your Snowflake Account (in this topic):

   Policy document for IAM role

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "",
         "Effect": "Allow",
         "Principal": {
           "AWS": "<snowflake_user_arn>"
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "<snowflake_external_id>"
           }
         }
       }
     ]
   }
   

   Copy

   Where:

   • snowflake_user_arn is the STORAGE_AWS_IAM_USER_ARN value you recorded.

   • snowflake_external_id is the STORAGE_AWS_EXTERNAL_ID value you recorded.

     In this example, the snowflake_external_id value is MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=.

     Note

     For security reasons, if you create a new storage integration (or recreate an existing storage integration using the CREATE OR REPLACE STORAGE INTEGRATION syntax) without specifying an external ID, the new integration has a different external ID and can’t resolve the trust relationship unless you update the trust policy.

8. Select Update policy to save your changes.

Step 1: Create a stage (if needed)¶

Create an external stage that references your S3 bucket using the CREATE STAGE command. Snowpipe fetches your data files from the stage and temporarily queues them before loading them into your target table. Alternatively, you can use an existing external stage.

The following example creates a stage named mystage in the active schema for the user session. The cloud storage URL includes the path files. The stage references a storage integration named my_storage_int:

USE SCHEMA snowpipe_db.public;

CREATE STAGE mystage
  URL = 's3://mybucket/load/files'
  STORAGE_INTEGRATION = my_storage_int;

Copy

Step 2: Create a pipe with auto-ingest enabled¶

Create a pipe using the CREATE PIPE command. The pipe defines the COPY INTO <table> statement used by Snowpipe to load data from the ingestion queue into the target table.

The following example creates a pipe named mypipe in the active schema for the user session. The pipe loads the data from files staged in the mystage stage into the mytable table:

CREATE PIPE snowpipe_db.public.mypipe
  AUTO_INGEST = TRUE
  AS
    COPY INTO snowpipe_db.public.mytable
      FROM @snowpipe_db.public.mystage
      FILE_FORMAT = (type = 'JSON');

Copy

The AUTO_INGEST = TRUE parameter specifies to read event notifications sent from an S3 bucket to an SQS queue when new data is ready to load.

Step 3: Configure security¶

For each user who will execute continuous data loads using Snowpipe, grant sufficient access control privileges on the objects for the data load (i.e. the target database, schema, and table; the stage object, and the pipe).

Using Snowpipe requires a role with the following privileges:

Use the GRANT <privileges> command to grant privileges to the role.

For example, create a role named snowpipe_role that can access a set of snowpipe_db.public database objects as well as a pipe named mypipe; then, grant the role to a user:

-- Create a role to contain the Snowpipe privileges
USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROLE snowpipe_role;

-- Grant the required privileges on the database objects
GRANT USAGE ON DATABASE snowpipe_db TO ROLE snowpipe_role;

GRANT USAGE ON SCHEMA snowpipe_db.public TO ROLE snowpipe_role;

GRANT INSERT, SELECT ON snowpipe_db.public.mytable TO ROLE snowpipe_role;

GRANT USAGE ON STAGE snowpipe_db.public.mystage TO ROLE snowpipe_role;

-- Pause the pipe for OWNERSHIP transfer
ALTER PIPE mypipe SET PIPE_EXECUTION_PAUSED = TRUE;

-- Grant the OWNERSHIP privilege on the pipe object
GRANT OWNERSHIP ON PIPE snowpipe_db.public.mypipe TO ROLE snowpipe_role;

-- Grant the role to a user
GRANT ROLE snowpipe_role TO USER jsmith;

-- Set the role as the default role for the user
ALTER USER jsmith SET DEFAULT_ROLE = snowpipe_role;

-- Resume the pipe
ALTER PIPE mypipe SET PIPE_EXECUTION_PAUSED = FALSE;

Copy

Step 4: Configure event notifications¶

Configure event notifications for your S3 bucket to notify Snowpipe when new data is available to load. The auto-ingest feature relies on SQS queues to deliver event notifications from S3 to Snowpipe.

For ease of use, Snowpipe SQS queues are created and managed by Snowflake. The SHOW PIPES command output displays the Amazon Resource Name (ARN) of your SQS queue.

1. Execute the SHOW PIPES command:

   SHOW PIPES;
   

   Copy

   Note the ARN of the SQS queue for the stage in the notification_channel column. Copy the ARN to a convenient location.

   Note

   Following AWS guidelines, Snowflake designates no more than one SQS queue per S3 bucket. A SQS queue may be shared among multiple buckets in the same region from the same AWS account. The SQS queue coordinates notifications for all pipes connecting the external stages for the S3 buckets to the target tables. When a data file is uploaded into the bucket, all pipes that match the stage directory path perform a one-time load of the file into their corresponding target tables.

2. Log into the Amazon S3 console.

3. Configure an event notification for your S3 bucket using the instructions provided in the Amazon S3 documentation. Complete the fields as follows:

   • Name: Name of the event notification (e.g. Auto-ingest Snowflake).

   • Events: Select the ObjectCreate (All) option.

   • Send to: Select SQS Queue from the dropdown list.

   • SQS: Select Add SQS queue ARN from the dropdown list.

   • SQS queue ARN: Paste the SQS queue name from the SHOW PIPES output.

Snowpipe with auto-ingest is now configured!

When new data files are added to the S3 bucket, the event notification informs Snowpipe to load them into the target table defined in the pipe.

Step 5: Load historical files¶

To load any backlog of data files that existed in the external stage before SQS notifications were configured, see Loading historic data.

Step 6: Delete staged files¶

Delete the staged files after you successfully load the data and no longer require the files. For instructions, see Deleting staged files after Snowpipe loads the data.

Prerequisite: Create an Amazon SNS Topic and Subscription¶

1. Create an SNS topic in your AWS account to handle all messages for the Snowflake stage location on your S3 bucket.

2. Subscribe your target destinations for the S3 event notifications (e.g. other SQS queues or AWS Lambda workloads) to this topic. SNS publishes event notifications for your bucket to all subscribers to the topic.

For instructions, see the SNS documentation.

Step 1: Subscribe the Snowflake SQS Queue to the SNS Topic¶

1. Log into the AWS Management Console.

2. From the home dashboard, choose Simple Notification Service (SNS).

3. Choose Topics from the left-hand navigation pane.

4. Locate the topic for your S3 bucket. Note the topic ARN.

5. Using a Snowflake client, query the SYSTEM$GET_AWS_SNS_IAM_POLICY system function with your SNS topic ARN:

   select system$get_aws_sns_iam_policy('<sns_topic_arn>');
   

   Copy

   The function returns an IAM policy that grants a Snowflake SQS queue permission to subscribe to the SNS topic.

   For example:

   select system$get_aws_sns_iam_policy('arn:aws:sns:us-west-2:001234567890:s3_mybucket');
   
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | SYSTEM$GET_AWS_SNS_IAM_POLICY('ARN:AWS:SNS:US-WEST-2:001234567890:S3_MYBUCKET')                                                                                                                                                                   |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | {"Version":"2012-10-17","Statement":[{"Sid":"1","Effect":"Allow","Principal":{"AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"},"Action":["sns:Subscribe"],"Resource":["arn:aws:sns:us-west-2:001234567890:s3_mybucket"]}]}                 |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   

   Copy

6. Return to the AWS Management Console. Choose Topics from the left-hand navigation pane.

7. Select the topic for your S3 bucket, and click the Edit button. The Edit page opens.

8. Click Access policy - Optional to expand this area of the page.

9. Merge the IAM policy addition from the SYSTEM$GET_AWS_SNS_IAM_POLICY function results into the JSON document.

   For example:

   Original IAM policy (abbreviated):

   {
     "Version":"2008-10-17",
     "Id":"__default_policy_ID",
     "Statement":[
        {
           "Sid":"__default_statement_ID",
           "Effect":"Allow",
           "Principal":{
              "AWS":"*"
           }
           ..
        }
      ]
    }
   

   Copy

   Merged IAM policy:

   {
     "Version":"2008-10-17",
     "Id":"__default_policy_ID",
     "Statement":[
        {
           "Sid":"__default_statement_ID",
           "Effect":"Allow",
           "Principal":{
              "AWS":"*"
           }
           ..
        },
        {
           "Sid":"1",
           "Effect":"Allow",
           "Principal":{
             "AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"
            },
            "Action":[
              "sns:Subscribe"
            ],
            "Resource":[
              "arn:aws:sns:us-west-2:001234567890:s3_mybucket"
            ]
        }
      ]
    }
   

   Copy

10. Add an additional policy grant to allow S3 to publish event notifications for the bucket to the SNS topic.

    For example (using the SNS topic ARN and S3 bucket used throughout these instructions):

    {
        "Sid":"s3-event-notifier",
        "Effect":"Allow",
        "Principal":{
           "Service":"s3.amazonaws.com"
        },
        "Action":"SNS:Publish",
        "Resource":"arn:aws:sns:us-west-2:001234567890:s3_mybucket",
        "Condition":{
           "ArnLike":{
              "aws:SourceArn":"arn:aws:s3:*:*:s3_mybucket"
           }
        }
     }
    

    Copy

    Merged IAM policy:

    {
      "Version":"2008-10-17",
      "Id":"__default_policy_ID",
      "Statement":[
         {
            "Sid":"__default_statement_ID",
            "Effect":"Allow",
            "Principal":{
               "AWS":"*"
            }
            ..
         },
         {
            "Sid":"1",
            "Effect":"Allow",
            "Principal":{
              "AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"
             },
             "Action":[
               "sns:Subscribe"
             ],
             "Resource":[
               "arn:aws:sns:us-west-2:001234567890:s3_mybucket"
             ]
         },
         {
            "Sid":"s3-event-notifier",
            "Effect":"Allow",
            "Principal":{
               "Service":"s3.amazonaws.com"
            },
            "Action":"SNS:Publish",
            "Resource":"arn:aws:sns:us-west-2:001234567890:s3_mybucket",
            "Condition":{
               "ArnLike":{
                  "aws:SourceArn":"arn:aws:s3:*:*:s3_mybucket"
               }
            }
          }
       ]
     }
    

    Copy

11. Click the Save changes button.

Step 2: Create a stage (if needed)¶

Create an external stage that references your S3 bucket using the CREATE STAGE command. Snowpipe fetches your data files from the stage and temporarily queues them before loading them into your target table.

Alternatively, you can use an existing external stage.

The following example creates a stage named mystage in the active schema for the user session. The cloud storage URL includes the path files. The stage references a storage integration named my_storage_int:

CREATE STAGE mystage
  URL = 's3://mybucket/load/files'
  STORAGE_INTEGRATION = my_storage_int;

Copy

Step 3: Create a pipe with auto-ingest enabled¶

Create a pipe using the CREATE PIPE command. The pipe defines the COPY INTO <table> statement used by Snowpipe to load data from the ingestion queue into the target table. In the COPY statement, identify the SNS topic ARN from Prerequisite: Create an Amazon SNS Topic and Subscription.

The following example creates a pipe named mypipe in the active schema for the user session. The pipe loads the data from files staged in the mystage stage into the mytable table:

CREATE PIPE snowpipe_db.public.mypipe
  AUTO_INGEST = TRUE
  AWS_SNS_TOPIC='<sns_topic_arn>'
  AS
    COPY INTO snowpipe_db.public.mytable
      FROM @snowpipe_db.public.mystage
      FILE_FORMAT = (type = 'JSON');

Copy

Where:

AUTO_INGEST = TRUE

Specifies to read event notifications sent from an S3 bucket to an SQS queue when new data is ready to load.

AWS_SNS_TOPIC = '<sns_topic_arn>'

Specifies the ARN for the SNS topic for your S3 bucket, e.g. arn:aws:sns:us-west-2:001234567890:s3_mybucket in the current example. The CREATE PIPE statement subscribes the Snowflake SQS queue to the specified SNS topic. Note that the pipe will only copy files to the ingest queue triggered by event notifications via the SNS topic.

To remove either parameter from a pipe, it is currently necessary to recreate the pipe using the CREATE OR REPLACE PIPE syntax.

Step 4: Configure security¶

For each user who will execute continuous data loads using Snowpipe, grant sufficient access control privileges on the objects for the data load (i.e. the target database, schema, and table; the stage object, and the pipe).

Using Snowpipe requires a role with the following privileges:

Use the GRANT <privileges> command to grant privileges to the role.

For example, create a role named snowpipe_role that can access a set of snowpipe_db.public database objects as well as a pipe named mypipe; then, grant the role to a user:

-- Create a role to contain the Snowpipe privileges
USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROLE snowpipe_role;

-- Grant the required privileges on the database objects
GRANT USAGE ON DATABASE snowpipe_db TO ROLE snowpipe_role;

GRANT USAGE ON SCHEMA snowpipe_db.public TO ROLE snowpipe_role;

GRANT INSERT, SELECT ON snowpipe_db.public.mytable TO ROLE snowpipe_role;

GRANT USAGE, READ ON STAGE snowpipe_db.public.mystage TO ROLE snowpipe_role;

-- Pause the pipe for OWNERSHIP transfer
ALTER PIPE mypipe SET PIPE_EXECUTION_PAUSED = TRUE;

-- Grant the OWNERSHIP privilege on the pipe object
GRANT OWNERSHIP ON PIPE snowpipe_db.public.mypipe TO ROLE snowpipe_role;

-- Grant the role to a user
GRANT ROLE snowpipe_role TO USER jsmith;

-- Set the role as the default role for the user
ALTER USER jsmith SET DEFAULT_ROLE = snowpipe_role;

-- Resume the pipe
ALTER PIPE mypipe SET PIPE_EXECUTION_PAUSED = FALSE;

Copy

Snowpipe with auto-ingest is now configured!

When new data files are added to the S3 bucket, the event notification informs Snowpipe to load them into the target table defined in the pipe.

Step 5: Load historical files¶

To load any backlog of data files that existed in the external stage before SQS notifications were configured, see Loading historic data.

Step 6: Delete staged files¶

Delete the staged files after you successfully load the data and no longer require the files. For instructions, see Deleting staged files after Snowpipe loads the data.

Step 1: Create an Amazon SNS topic¶

Follow Prerequisite: Create an Amazon SNS Topic and Subscription (in this topic).

Step 2: Create an EventBridge rule to subscribe S3 buckets and send notifications to SNS topic¶

• Enable Amazon EventBridge for S3 buckets.

• Create EventBridge rules to send notifications to the SNS topic created in step 1.

Step 3: Configuring Amazon SNS to automate Snowpipe using SQS notifications¶

Follow Option 2: Configuring Amazon SNS to automate Snowpipe using SQS notifications (in this topic).