Geek Logbook

Tech sea log book

Load data from Snowflake to S3

If you want to load data from Snowflake to S3 should try to use the COPY INTO command so, you run something like this command in the snowflake Web App:

copy into @my_ext_unload_stage/d1 from mytable;

And receive the following error:

Failure using stage area. Cause: [Access Denied (Status Code: 403; Error Code: AccessDenied)]

To solve this error and copy data you need to supply the credentials as you can see below:

copy into 's3://mybucket/unload/'
  from mytable
  credentials = (aws_key_id='xxxx' aws_secret_key='xxxxx' aws_token='xxxxxx')

And in order to get the proper key_id, secret_key, and token you can run the following command in your cli (After installing the AWS CLI)

aws sts get-session-token

And you will receive the information required to run the query. For example if you receive:

{
    "Credentials": {
        "AccessKeyId": "AKIAIOSFODNN7EXAMPLE",
        "SecretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYzEXAMPLEKEY",
        "SessionToken": "AQoEXAMPLEH4aoAH0gNCAPyJxz4BlCFFxWNE1OPTgk5TthT+FvwqnKwRcOIfrRh3c/LTo6UDdyJwOOvEVPvLXCrrrUtdnniCEXAMPLE/IvU1dYUg2RVAJBanLiHb4IgRmpRV3zrkuWJOgQs8IZZaIv2BXIa2R4OlgkBN9bkUDNCJiBeb/AXlzBBko7b15fjrBs2+cTQtpZ3CYWFXG8C5zqx37wnOE49mRl/+OtkIKGO7fAE",
        "Expiration": "2020-05-19T18:06:10+00:00"
    }
}

You should format your query as:

copy into 's3://mybucket/unload/'
  from mytable
  credentials = (aws_key_id='AKIAIOSFODNN7EXAMPLE' 
aws_secret_key='wJalrXUtnFEMI/K7MDENG/bPxRfiCYzEXAMPLEKEY' aws_token='AQoEXAMPLEH4aoAH0gNCAPyJxz4BlCFFxWNE1OPTgk5TthT+FvwqnKwRcOIfrRh3c/LTo6UDdyJwOOvEVPvLXCrrrUtdnniCEXAMPLE/IvU1dYUg2RVAJBanLiHb4IgRmpRV3zrkuWJOgQs8IZZaIv2BXIa2R4OlgkBN9bkUDNCJiBeb/AXlzBBko7b15fjrBs2+cTQtpZ3CYWFXG8C5zqx37wnOE49mRl/+OtkIKGO7fAE')

Remember the session has an expiration date

Documentation:

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*
You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>