Creating a Stage in Snowflake with and without a Storage Integration

,

Is there someone you trust, but really don’t trust? Meaning, you would like to give him access to e.g. an S3 Bucket, but you don’t want to share authentication data with him?

Storage Integrations are a great way of giving untrustworthy individuals access in such a manner!

Kidding aside, Storage Integrations are a great way of handling authentication when you want to access files in an AWS S3 Bucket from Snowflake.

SQL
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -- -- CREATING A STAGE WITHOUT STORAGE INTEGRATION -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

-- Creating a stage WITHOUT a Storage Integration (not a best practise, but just to show you it is possible). First you will have to:
    -- Create an IAM policy for a bucket in AWS
    -- Create a user and attach the IAM policy to that user
    -- Create an access key for that user and record the key-value pair

-- Creating the stage using the new user created in AWS and the key-value pair to authenticate
CREATE OR REPLACE STAGE MY_S3_STAGE
    URL = 's3://mybucket'
    CREDENTIALS = (AWS_KEY_ID = 'KEYID' AWS_SECRET_KEY = 'UNCRACKABLEKEY');

-- Showing the files present in the stage
LIST @MY_S3_STAGE;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
-- -- -- --  CREATING A STAGE WITH A STORAGE INTEGRATION -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

-- Creating a stage WITH a Storage Integration. First you will have to:
    -- Create an IAM policy for a bucket in AWS
    -- Create an IAM role in AWS

-- Creating the Storage Integration; an IAM user will be created automatically by Snowflake
CREATE OR REPLACE STORAGE INTEGRATION MY_S3_INTEGRATION
    TYPE = EXTERNAL_STAGE
    ENABLED = TRUE
    STORAGE_PROVIDER = 'S3'
    STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::1337:role/snowflake_rol'
    STORAGE_ALLOWED_LOCATIONS = ('s3://mybucket');

-- After creating the Storage Integration, retrieve values STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID with DESCRIBE INTEGRATION
    -- These properties are metadata about the IAM user automatically created by Snowflake when creating the Storage Integration
-- Insert the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID in the Trust policy of the role in AWS
DESCRIBE INTEGRATION MY_S3_INTEGRATION;

-- Validate Storage Integration using the system information function SYSTEM$VALIDATE_STORAGE_INTEGRATION
SELECT SYSTEM$VALIDATE_STORAGE_INTEGRATION('MY_S3_INTEGRATION', 's3://mybucket', 'COPYINTOAppendtest1.csv', 'all');

-- Creating the stage using the Storage Integration to authenticate
CREATE OR REPLACE STAGE MY_S3_STAGE_STORAGE_INTEGRATION
    URL = 's3://mybucket'
    STORAGE_INTEGRATION = MY_S3_INTEGRATION;

-- Checking out what is in the stage
LIST @MY_S3_STAGE_STORAGE_INTEGRATION;
SQL

Leave Comment