Snowflake create AWS S3 stage

1. Amazon S3 dashboard
   

2. create S3 bucket
   

3. Securtity Credentials
   

Access Key :
– Access Key ID
– Secret Access Key

4. CREATE STAGE command Usage

• URL, STORAGE_INTEGRATION, CREDENTIALS, and ENCRYPTION only apply to external stages. If you are creating an internal stage, you can ignore these parameters.

• CREATE STAGE does not check whether the specified URL or credentials are valid. If the credentials are not valid, when you attempt to use the stage, the system returns an error.

• If referencing a file format in the current namespace, you can omit the single quotes around the format identifier.

5. CREATE STAGE command Examples

Internal Stages
Create an internal stage named my_int_stage with the default file format type (CSV):

All the corresponding default CSV file format options are used.

All the default copy options are used, except for ON_ERROR. If a command that references this stage encounters a data error on any of the records, it skips the file.

CREATE OR REPLACE STAGE my_int_stage
  COPY_OPTIONS = (ON_ERROR='skip_file');

Create a temporary internal stage named my_temp_int_stage with all the same properties as the previous example, except the copy option to skip files on error:

CREATE OR REPLACE TEMPORARY STAGE my_temp_int_stage;

Create a temporary internal stage named my_int_stage that references a file format named my_csv_format (created using ):

CREATE OR REPLACE TEMPORARY STAGE my_int_stage
  FILE_FORMAT = my_csv_format;

When you reference the stage in a statement, the file format options are automatically set

External Stages – Amazon S3

Create an external stage named my_ext_stage using a private/protected S3 bucket named load with a folder path named files. Secure access to the S3 bucket is provided via the myint storage integration:

CREATE OR REPLACE STAGE my_ext_stage
  URL='s3://load/files/'
  STORAGE_INTEGRATION = myint;

Create an external stage named my_ext_stage1 using a private/protected S3 bucket named load with a folder path named files. The Snowflake access permissions for the S3 bucket are associated with an IAM user; therefore, IAM credentials are required:

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

Create an external stage named my_ext_stage2 using an S3 bucket named load with a folder path named encrypted_files and client-side encryption (default encryption type) with the master key to decrypt/encrypt files stored in the bucket:

CREATE OR REPLACE STAGE my_ext_stage2
  URL='s3://load/encrypted_files/'
  CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
  ENCRYPTION=(MASTER_KEY = 'eSxX0jzYfIamtnBKOEOwq80Au6NbSgPH5r4BDDwOaO8=');

Create an external stage named my_ext_stage3 using an S3 bucket named load with a folder path named encrypted_files and AWS_SSE_KMS server-side encryption with the ID for the master key to decrypt/encrypt files stored in the bucket:

CREATE OR REPLACE STAGE my_ext_stage3
  URL='s3://load/encrypted_files/'
  CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
  ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');

Same example as the immediately preceding example, except that the Snowflake access permissions for the S3 bucket as associated with an IAM role instead of an IAM user. Note that credentials are handled separately from other stage parameters such as ENCRYPTION. Support for these other parameters is the same regardless of the credentials used to access your external S3 bucket:

CREATE OR REPLACE STAGE my_ext_stage3
  URL='s3://load/encrypted_files/'
  CREDENTIALS=(AWS_ROLE='arn:aws:iam::001234567890:role/mysnowflakerole')
  ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');

**External Stages – Google Cloud Storage

Create an external stage named my_ext_stage using a private/protected GCS bucket named load with a folder path named files. Secure access to the GCS bucket is provided via the myint storage integration:

CREATE OR REPLACE STAGE my_ext_stage
  URL='gcs://load/files/'
  STORAGE_INTEGRATION = myint;

**External Stages – Microsoft Azure

Create an external stage named my_ext_stage using a private/protected Azure container named load with a folder path named files. Secure access to the container is provided via the myint storage integration:

CREATE OR REPLACE STAGE my_ext_stage
  URL='azure://myaccount.blob.core.windows.net/load/files/'
  STORAGE_INTEGRATION = myint;

Create an external stage named mystage using an Azure storage account named myaccount and a container named mycontainer with a folder path named files and client-side encryption enabled. The stage references a file format named my_csv_format:

CREATE OR REPLACE STAGE mystage
  URL='azure://myaccount.blob.core.windows.net/mycontainer/files/'
  CREDENTIALS=(AZURE_SAS_TOKEN='?sv=2016-05-31&ss=b&srt=sco&sp=rwdl&se=2018-06-27T10:05:50Z&st=2017-06-27T02:05:50Z&spr=https,http&sig=bgqQwoXwxzuD2GJfagRg7VOS8hzNr3QLT7rhS8OFRLQ%3D')
  ENCRYPTION=(TYPE='AZURE_CSE' MASTER_KEY = 'kPxX0jzYfIamtnJEUTHwq80Au6NbSgPH5r4BDDwOaO8=')
  FILE_FORMAT = my_csv_format;
(The AZURE_SAS_TOKEN and MASTER_KEY values used in this example are not actual values; they are provided for illustration purposes only.)