CREATE STORAGE INTEGRATION | Snowflake Documentation (2023)

Note

• Storage integrations can be configured to support Amazon S3, Google Cloud Storage, or Microsoft Azure cloud storage regardless of thecloud provider that hosts your Snowflake account. If your cloud storage is located on a different cloud platform from your Snowflakeaccount, the storage location must be in the public cloud and not a virtual private environment.

  Cloud providers charge for data transferred out of their own network. To recover these expenses, Snowflake charges a per-byte fee whenyou unload data from Snowflake (hosted on Amazon Web Services (AWS), Google Cloud Platform, or Microsoft Azure) into an external stagein a different region or different cloud provider. Snowflake does not charge for data ingress(i.e. when loading data into Snowflake). For details, see the pricing page (on the Snowflakewebsite).

• Currently, accessing cloud storage in a government region using a storage integration is limited toSnowflake accounts hosted in the same government region. Accessing your cloud storage from an account hosted outside of the governmentregion using direct credentials is supported.

Syntax¶

CREATE [ OR REPLACE ] STORAGE INTEGRATION [IF NOT EXISTS] <name> TYPE = EXTERNAL_STAGE cloudProviderParams ENABLED = { TRUE | FALSE } STORAGE_ALLOWED_LOCATIONS = ('<cloud>://<bucket>/<path>/' [ , '<cloud>://<bucket>/<path>/' ... ] ) [ STORAGE_BLOCKED_LOCATIONS = ('<cloud>://<bucket>/<path>/' [ , '<cloud>://<bucket>/<path>/' ... ] ) ] [ COMMENT = '<string_literal>' ]

Where:

cloudProviderParams (for Amazon S3) ::= STORAGE_PROVIDER = 'S3' STORAGE_AWS_ROLE_ARN = '<iam_role>' [ STORAGE_AWS_OBJECT_ACL = 'bucket-owner-full-control' ]

cloudProviderParams (for Google Cloud Storage) ::= STORAGE_PROVIDER = 'GCS'

cloudProviderParams (for Microsoft Azure) ::= STORAGE_PROVIDER = 'AZURE' AZURE_TENANT_ID = '<tenant_id>'

Required Parameters¶

name

String that specifies the identifier (i.e. name) for the integration; must be unique in your account.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entireidentifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier Requirements.

TYPE = EXTERNAL_STAGE

Specify the type of integration:

• EXTERNAL_STAGE: Creates an interface between Snowflake and an external cloud storage location.

ENABLED = TRUE | FALSE

Specifies whether this storage integration is available for usage in stages.

• TRUE allows users to create new stages that reference this integration. Existing stages that reference this integrationfunction normally.

• FALSE prevents users from creating new stages that reference this integration. Existing stages that reference this integrationcannot access the storage location in the stage definition.

(Video) Using Snowpipe | How to ingest data from AWS S3 | Snowflake Demo

STORAGE_ALLOWED_LOCATIONS = ('cloud_specific_url')

Explicitly limits external stages that use the integration to reference one or more storage locations (i.e. S3 bucket, GCS bucket, orAzure container). Supports a comma-separated list of URLs for existing buckets and, optionally, paths used to store data files forloading/unloading. Alternatively supports the * wildcard, meaning “allow access to all buckets and/or paths”.

Note that if the STORAGE_ALLOWED_LOCATIONS value includes a specific storage location, all subpaths in the same location mustbe allowed. No subpath can be included in a STORAGE_BLOCKED_LOCATIONS value. For example, if STORAGE_ALLOWED_LOCATIONSincludes storage_location/, then STORAGE_BLOCKED_LOCATIONS cannot include storage_location/path1/.Likewise, if STORAGE_ALLOWED_LOCATIONS includes storage_location/path1/, then STORAGE_BLOCKED_LOCATIONScannot include storage_location/path1/path2/, and so on.

Amazon S3

STORAGE_ALLOWED_LOCATIONS = ( 's3://bucket/path/' [ , 's3://bucket/path/' ... ] ) | STORAGE_ALLOWED_LOCATIONS = ( 's3gov://bucket/path/' [ , 's3gov://bucket/path/' ... ] )

See Also

Configuring an Azure Container for Loading Data

• The s3 prefix refers to S3 storage in public AWS regions.

  The s3gov prefix refers to S3 storage in government regions.

• bucket is the name of an S3 bucket that stores your data files (e.g. mybucket).

• path is an optional case-sensitive path for files in the cloud storage location (i.e. files have names that begin witha common string) that limits access to a set of files. Paths are alternatively called prefixes or folders by different cloudstorage services.

Google Cloud Storage

STORAGE_ALLOWED_LOCATIONS = ( 'gcs://bucket/path/' [ , 'gcs://bucket/path/' ... ] )

• bucket is the name of a GCS bucket that stores your data files (e.g. mybucket).

• path is an optional case-sensitive path for files in the cloud storage location (i.e. files have names that begin witha common string) that limits access to a set of files. Paths are alternatively called prefixes or folders by different cloudstorage services.

Microsoft Azure

STORAGE_ALLOWED_LOCATIONS = ( 'azure://account.blob.core.windows.net/container/path/' [ , 'azure://account.blob.core.windows.net/container/path/' ... ] )

• account is the name of the Azure storage account (e.g. myaccount). Use the blob.core.windows.net endpointfor all supported types of Azure blob storage accounts, including Data Lake Storage Gen2.

• container is the name of a Azure blob storage container that stores your data files (e.g. mycontainer).

• path is an optional case-sensitive path for files in the cloud storage location (i.e. files have names that begin witha common string) that limits access to a set of files. Paths are alternatively called prefixes or folders by different cloudstorage services.

Optional Parameters¶

STORAGE_BLOCKED_LOCATIONS = ('cloud_specific_url')

Explicitly prohibits external stages that use the integration from referencing one or more storage locations (i.e. S3 buckets orGCS buckets). Supports a comma-separated list of URLs for existing storage locations and, optionally, paths used to store data filesfor loading/unloading. Commonly used when STORAGE_ALLOWED_LOCATIONS is set to the * wildcard, allowing access to all bucketsin your account except for blocked storage locations and, optionally, paths.

Note

Make sure to enclose only individual cloud storage location URLs in quotes. If you enclose the entireSTORAGE_BLOCKED_LOCATIONS value in quotes, the value is invalid. As a result, the STORAGE_BLOCKED_LOCATIONSparameter setting is ignored when users create stages that reference the storage integration.

(Video) File Storage Integration Set-Up

Amazon S3

STORAGE_BLOCKED_LOCATIONS = ( 's3://bucket/path/' [ , 's3://bucket/path/' ... ] ) | STORAGE_ALLOWED_LOCATIONS = ( 's3gov://bucket/path/' [ , 's3gov://bucket/path/' ... ] )

• The s3 prefix refers to S3 storage in public AWS regions.

  The s3gov prefix refers to S3 storage in government regions.

• bucket is the name of an S3 bucket that stores your data files (e.g. mybucket).

• path is an optional path (or directory) in the bucket that further limits access to the data files.

Google Cloud Storage

STORAGE_BLOCKED_LOCATIONS = ( 'gcs://bucket/path/' [ , 'gcs://bucket/path/' ... ] )

• bucket is the name of a GCS bucket that stores your data files (e.g. mybucket).

• path is an optional path (or directory) in the bucket that further limits access to the data files.

Microsoft Azure

STORAGE_BLOCKED_LOCATIONS = ( 'azure://account.blob.core.windows.net/container/path/' [ , 'azure://account.blob.core.windows.net/container/path/' ... ] )

• account is the name of the Azure storage account (e.g. myaccount).

• container is the name of a Azure blob storage container that stores your data files (e.g. mycontainer).

• path is an optional path (or directory) in the bucket that further limits access to the data files.

COMMENT = 'string_literal'

String (literal) that specifies a comment for the integration.

Default: No value

Cloud Provider Parameters (cloudProviderParams)¶

Amazon S3

STORAGE_PROVIDER = { S3 | S3GOV }

Specifies the cloud storage provider that stores your data files:

• S3: S3 storage in public AWS regions.

• S3GOV: S3 storage in AWS government regions.

STORAGE_AWS_ROLE_ARN = iam_role

Specifies the Amazon Resource Name (ARN) of the AWS identity and access management (IAM) role that grants privileges on the S3 bucketcontaining your data files. For more information, see Configuring Secure Access to Amazon S3.

STORAGE_AWS_OBJECT_ACL = 'bucket-owner-full-control'

Enables support for AWS access control lists (ACLs) to grant the bucket owner full control. Files created in Amazon S3 buckets fromunloaded table data are owned by an AWS Identity and Access Management (IAM) role. ACLs support the use case where IAM roles in oneAWS account are configured to access S3 buckets in one or more other AWS accounts. Without ACL support, users in the bucket-owneraccounts could not access the data files unloaded to an external (S3) stage using a storage integration.

When users unload Snowflake table data to data files in an S3 stage using COPY INTO <location>, the unloadoperation applies an ACL to the unloaded data files. The data files apply the "s3:x-amz-acl":"bucket-owner-full-control"privilege to the files, granting the S3 bucket owner full control over them.

(Video) Configuring a Snowflake Storage Integration to Access Amazon S3

Google Cloud Storage

STORAGE_PROVIDER = 'GCS'

Specifies the cloud storage provider that stores your data files.

Microsoft Azure

STORAGE_PROVIDER = 'AZURE'

Specifies the cloud storage provider that stores your data files.

AZURE_TENANT_ID = 'tenant_id'

Specifies the ID for your Office 365 tenant that the allowed and blocked storage accounts belong to. A storage integration canauthenticate to only one tenant, and so the allowed and blocked storage locations must refer to storage accounts that all belongthis tenant.

To find your tenant ID, log into the Azure portal and click Azure Active Directory » Properties. The tenant IDis displayed in the Tenant ID field.

Access Control Requirements¶

A role used to execute this SQL command must have the followingprivileges at a minimum:

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions onsecurable objects, see Access Control in Snowflake.

Usage Notes¶

• Regarding metadata:

  Attention

  (Video) Snowflake - AWS Integration

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata Fields in Snowflake.

• CREATE OR REPLACE <object> statements are atomic. That is, when the object is replaced, the old object deletion and the new object creation are processed in a single transaction.

Examples¶

The following example creates an integration that explicitly limits external stages that use the integration to reference either oftwo buckets and paths:

Amazon S3

CREATE STORAGE INTEGRATION s3_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('s3://mybucket1/path1/', 's3://mybucket2/path2/');

Google Cloud Storage

CREATE STORAGE INTEGRATION gcs_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'GCS' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('gcs://mybucket1/path1/', 'gcs://mybucket2/path2/');

Microsoft Azure

CREATE STORAGE INTEGRATION azure_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'AZURE' ENABLED = TRUE AZURE_TENANT_ID = '<tenant_id>' STORAGE_ALLOWED_LOCATIONS = ('azure://myaccount.blob.core.windows.net/mycontainer/path1/', 'azure://myaccount.blob.core.windows.net/mycontainer/path2/');

The following example creates an integration that allows external stages that use the integration to reference any bucket andpath in your account except for those that are explicitly blocked:

Amazon S3

CREATE STORAGE INTEGRATION s3_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('*') STORAGE_BLOCKED_LOCATIONS = ('s3://mybucket3/path3/', 's3://mybucket4/path4/');

Google Cloud Storage

CREATE STORAGE INTEGRATION gcs_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'GCS' ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('*') STORAGE_BLOCKED_LOCATIONS = ('gcs://mybucket3/path3/', 'gcs://mybucket4/path4/');

Microsoft Azure

CREATE STORAGE INTEGRATION azure_int TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'AZURE' ENABLED = TRUE AZURE_TENANT_ID = 'a123b4c5-1234-123a-a12b-1a23b45678c9' STORAGE_ALLOWED_LOCATIONS = ('*') STORAGE_BLOCKED_LOCATIONS = ('azure://myaccount.blob.core.windows.net/mycontainer/path3/', 'azure://myaccount.blob.core.windows.net/mycontainer/path4/');

(Video) Want to know how your organisation can process big data more efficiently?