Storage Integrations
3 minute read
Introduction
Snowflake storage integrations enable access to external cloud storage like Amazon S3, Google Cloud Storage, and Azure Blob Storage. They manage authentication through generated IAM roles, enhancing security and simplifying data operations without exposing sensitive credentials. This approach centralizes and controls access, streamlining workflows across major cloud platforms.
The Snowflake emulator supports storage integrations, allowing you to test interactions with external storage using the same commands and syntax as the Snowflake service. The following operations are supported:
Getting started
This guide is designed for users new to Storage Integration and assumes basic knowledge of SQL and Snowflake. Start your Snowflake emulator and connect to it using an SQL client in order to execute the queries further below.
In this guide, you will create a Snowflake Storage Integration with Amazon S3 and creating an external stage to load data.
Create an S3 bucket
You can create a local S3 bucket using the mb command with the awslocal CLI.
$ awslocal s3 mb s3://testbucket
Upload some sample CSV file into the S3 bucket using the following command:
awslocal s3 cp file.csv s3://testbucket
Create a Storage Integration
You can now create a Storage Integration named s_example which will connect Snowflake to your S3 bucket using the following statement:
CREATE STORAGE INTEGRATION s_example
TYPE = EXTERNAL_STAGE
ENABLED = TRUE
STORAGE_PROVIDER = 'S3'
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::000000000000:role/testrole'
STORAGE_ALLOWED_LOCATIONS = ('s3://testbucket');
The expected output is:
+---------------------------------------------+
| ?COLUMN? |
|---------------------------------------------|
| Integration S_EXAMPLE successfully created. |
+---------------------------------------------+
Describe the Storage Integration
After creating the storage integration, you can retrieve important configuration by running the following statement:
DESCRIBE STORAGE INTEGRATION s_example;
The expected output is:
+---------------------------+---------------+-----------------------------------------+------------------+
| property | property_type | property_value | property_default |
|---------------------------+---------------+-----------------------------------------+------------------|
| ENABLED | Boolean | true | false |
| STORAGE_PROVIDER | String | S3 | |
| STORAGE_ALLOWED_LOCATIONS | List | s3://testbucket | [] |
| STORAGE_BLOCKED_LOCATIONS | List | | [] |
| STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::000000000000:user/test | |
| STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::000000000000:role/testrole | |
| STORAGE_AWS_EXTERNAL_ID | String | TEST_SFCRole=test | |
| COMMENT | String | | |
+---------------------------+---------------+-----------------------------------------+------------------+
8 Row(s) produced. Time Elapsed: 0.050s
Create a stage
You can now create an external stage using the following statement:
CREATE STAGE stage_example
STORAGE_INTEGRATION = s_example
URL = 's3://testbucket'
FILE_FORMAT = (TYPE = CSV);
The expected output is:
+------------------------------------------------+
| ?COLUMN? |
|------------------------------------------------|
| Stage area STAGE_EXAMPLE successfully created. |
+------------------------------------------------+
0 Row(s) produced. Time Elapsed: 0.083s
List the files
To list the files in the stage, you can run the following statement:
LIST @stage_example;
The output will show the files.csv file that we uploaded earlier to the S3 bucket.