Unloading into External Locations

In this Topic:

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). For more information, see Configuring an S3 Bucket to Use as an External Location.

  • 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.

Unloading Data Directly into an External Location

  1. Use COPY INTO location to unload the data from a table into a specified S3 bucket. You must specify the URI for the S3 bucket and the security credentials for accessing the bucket in the 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');
    

    The generated files are prefixed with the system default data_.

    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 external location, Snowflake recommends using temporary credentials to protect access to the location.

  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 into an External Stage

  1. Use COPY INTO location to unload the data from a table into an existing external stage.

    The following example uses the existing my_ext_stage stage to unload all the rows in the mytable table into one or more files with the folder path prefix unload/ and filename prefix d1 in the stage location:

    COPY INTO @my_ext_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.