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: