Unloading into Amazon S3

This topic describes how to use the COPY INTO location command to unload data from a table into an external stage that points to an Amazon S3 bucket. You can then download the unloaded data files to your local file system.

Unloading data to S3

As illustrated in the diagram above, unloading data to a local file system is performed in two, separate steps:

Step 1:

Use the COPY INTO location command to copy the data from the Snowflake database table into one or more files in an S3 bucket.

Specify the URL and security credentials for the S3 bucket where the files are written or use a stage object that references the S3 bucket.

This step requires an active virtual warehouse. The warehouse provides the compute resources to write rows from the table.

Step 2:

Download the files from the S3 bucket.

Use the interfaces/tools provided by Amazon S3 to get the data files.

Note

The instructions in this set of topics presume you have read Preparing to Unload Data and have created a named file format, if desired.

Tip

Before you begin, you may also want to read Data Unloading Considerations for best practices, tips, and other guidance.

In this Topic:

Configuring an S3 Bucket for Unloading Data

Snowflake can load data from files stored in S3 buckets, as well as unload data into files stored in S3 buckets. If you already have an AWS account and use S3 buckets for storing and managing your files, this feature allows you to make use of your existing buckets and folder path prefixes for loading/unloading data for Snowflake.

AWS Access Control Requirements

Snowflake requires the following permissions on an S3 bucket and folder to create new files in the folder (and any sub-folders).

  • s3:DeleteObject
  • s3:PutObject

As a best practice, Snowflake recommends creating an Identity & Access Management (IAM) policy and user for Snowflake access to the S3 bucket. You can then attach the policy to the user and use the security credentials generated by AWS for the user to access files in the bucket.

For configuration instructions, see Configuring an S3 Bucket for Loading Data.

Unloading Data into an External Stage

External stages are named database objects that provide the greatest degree of flexibility for data unloading. Because they are database objects, privileges for named stages can be granted to any role.

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

Web Interface:Click on Databases > db_name > Stages
SQL Command:CREATE STAGE

Creating a Named Stage

The following example creates an external stage named my_ext_unload_stage using an S3 bucket named load with a folder path named files. The stage references the named file format object called my_csv_unload_format that was created in Preparing to Unload Data:

CREATE OR REPLACE STAGE my_ext_unload_stage URL='s3://load/files/'
    CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
    FILE_FORMAT = my_csv_unload_format;

Note that the AWS_KEY_ID and AWS_SECRET_KEY values used in this example are for illustration purposes only.

Unloading Data to the Named Stage

  1. Use the COPY INTO location command to unload data from a table into an S3 bucket using the external stage.

    The following example uses the my_ext_unload_stage stage to unload all the rows in the mytable table into one or more files in the S3 bucket with a unload/ folder prefix and d1 filename prefix:

    COPY INTO @my_ext_unload_stage/unload/d1 from mytable;
    
  2. Use the S3 console (or equivalent client application) to retrieve the objects (i.e. files generated by the command) from the bucket.

Unloading Data Directly into an S3 Bucket

  1. Use the COPY INTO location command to unload data from a table directly into a specified S3 bucket. This option works well for ad hoc unloading, when you aren’t planning regular data unloading with the same table and bucket parameters.

    You must specify the URI for the S3 bucket and the security credentials for accessing the bucket in the COPY command.

    The following example unloads all the rows in the mytable table into one or more files with the folder path prefix unload/ in the mybucket S3 bucket:

    COPY INTO s3://mybucket/files/unload/ from mytable credentials = (AWS_KEY_ID='xxxx' AWS_SECRET_KEY='xxxxx' AWS_TOKEN='xxxxxx');
    

    Note

    This example uses temporary S3 credentials, which are generated by AWS STS and expire after a specific period of time. When unloading directly into an S3 bucket, Snowflake recommends using temporary credentials to protect access to the bucket.

  2. Use the S3 console (or equivalent client application) to retrieve the objects (i.e. files generated by the command) from the bucket.