0

I am trying to unload the results of a particular query in Snowflake to an S3 location directly. 

copy into 's3://bucket-name/folder/text.csv' 
from <Some SQL Query>
file_format = (type = CSV file_extension = '.csv' field_optionally_enclosed_by = NONE empty_field_as_null = false) 
max_file_size = 5000000000 
storage_integration = aws 
single = true;

The problem with this is after the write is successful, the bucket owner cannot read the new file from S3 because of the ACL. So, how do you add the canned ACL of "Bucket-Owner-Full-Control" while writing to the S3 from Snowflake? And I am not much into Google Cloud Storage, what will be the scenario in GCS buckets??

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Manoj Acharya
  • 1,331
  • 2
  • 15
  • 27
  • Bit of a guess but : When you run the `copy into ` command you have to specify a user / storage integration to use. The files will be unloaded as that user and this is probably what is causing issues with permissions. – Simon D Jan 23 '20 at 13:13
  • @SimonD yes, that is the problem. Snowflake uses an AWS user (In their AWS account) solely created for one particular Snowflake account, which we add as trusted entity to the Role that we created for Snowflake user to assume. So there should be some configuration to specify the ACL while writing to the location. We can use Canned ACL for that purpose but not sure how to do that with snowflake.https://docs.snowflake.net/manuals/user-guide/data-load-s3-config.html#option-1-configuring-a-snowflake-storage-integration – Manoj Acharya Jan 23 '20 at 13:20
  • I thought that Snowflake used the user that you specify in the credentials / storage integration object that you used, not their own one? – Simon D Jan 23 '20 at 13:28
  • @SimonD yes but still the bucket owner doesn't have sufficient permissions to the object without the canned ACL no matter which credentials are used. Bucket owner might be a different entity than the one writing to the bucket. – Manoj Acharya Jan 23 '20 at 13:33
  • Have you tried creating the s3 location as a stage with the authentication information in the stage settings? – Mike Walton Jan 23 '20 at 14:49
  • @MikeWalton I might want to write to any multiple buckets. So instead of creating a different external stage for each bucket, I think it is better to create a Storage Integration. But to my knowledge, as long as we don't put ACL while writing and the AWS entity that is writing is different from the bucket owner, the problem still persists. – Manoj Acharya Jan 23 '20 at 16:00

1 Answers1

0

You might not be able to add a canned ACL to your COPY INTO statement, however what you can do is to add the required parameter to the Storage Integration.

When you create your Storage Integration or if you have to update it, please add this to the statement. STORAGE_AWS_OBJECT_ACL = 'bucket-owner-full-control'

This should ensure whatever data you unload to a bucket from Snowflake will let the the bucket owner have full control over the object.

https://docs.snowflake.com/en/user-guide/data-unload-s3.html#configuring-support-for-amazon-s3-access-control-lists-optional

Apuroop
  • 116
  • 1
  • 3