Creating an S3 Stage

.

Bulk Loading from Amazon S3

An external (i.e. S3) stage specifies where data files are stored so that the data in the files can be loaded into a table.

Data can be loaded directly from files in a specified S3 bucket, with or without a folder path (or prefix, in S3 terminology). If the path ends with /, all of the objects in the corresponding S3 folder are loaded.

Note

In the previous step, if you followed the instructions to configure an AWS IAM role with the required policies and permissions to access your external S3 bucket, you have already created an S3 stage. You can skip this step and continue to Copying Data from an S3 Stage.

In this Topic:

External Stages

In addition to loading directly from files in S3 buckets, Snowflake supports creating named external stages, which encapsulate all of the required information for staging files, including:

  • The S3 bucket where the files are staged.

  • The named storage integration object or S3 credentials for the bucket (if it is protected).

  • An encryption key (if the files in the bucket have been encrypted).

Named external stages are optional, but recommended when you plan to load data regularly from the same location.

Creating an External Stage

You can create a named stage using either the web interface or SQL:

Web Interface

Click on Databases Databases tab » <db_name> » Stages

SQL

CREATE STAGE

Example

The following example uses SQL to create an external stage named my_s3_stage that references a private/protected S3 bucket named mybucket with a folder path named encrypted_files. The CREATE statement includes S3 credentials (associated with an AWS IAM user) and a master encryption key. The stage references a named file format object called my_csv_format created in Preparing to Load Data:

CREATE OR REPLACE STAGE my_s3_stage URL='s3://mybucket/encrypted_files/'
  CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
  ENCRYPTION=(MASTER_KEY = 'eSxX0jzYfIamtnBKOEOwq80Au6NbSgPH5r4BDDwOaO8=')
  FILE_FORMAT = my_csv_format;

Note

By specifying a named file format object (or individual file format options) for the stage, it isn’t necessary to later specify the same file format options in the COPY command used to load data from the stage.

Next: Copying Data from an S3 Stage