Categories:

User & Security DDL (Third-Party Service Integrations)

CREATE STORAGE INTEGRATION

Creates a new storage integration in the account or replaces an existing integration.

A storage integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations (i.e. AWS S3 or Google Cloud Storage buckets or Microsoft Azure containers). Cloud provider administrators in your organization grant permissions on the storage locations to the generated entity. This option allows users to avoid supplying credentials when creating stages or loading data.

Note

This command is currently only supported for access to AWS S3 or Google Cloud Storage. Support for Microsoft Azure is planned.

See also:

ALTER STORAGE INTEGRATION , DROP INTEGRATION , SHOW INTEGRATIONS

In this Topic:

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 AWS S3 bucket)

cloudProviderParams ::=
  STORAGE_PROVIDER = S3
  STORAGE_AWS_ROLE_ARN = '<iam_role>'

cloudProviderParams (for Google Cloud Storage bucket)

cloudProviderParams ::=
  STORAGE_PROVIDER = GCS

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 entire identifier 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 integration function normally.

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

STORAGE_ALLOWED_LOCATIONS = ('cloud_specific_url')

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

AWS S3

STORAGE_ALLOWED_LOCATIONS = ('s3://bucket/path/', 's3://bucket/path/')

  • 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 data files.

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 path (or directory) in the bucket that further limits access to data files.

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 or GCS buckets). Supports a comma-separated list of URLs for existing storage locations and, optionally, paths used to store data files for loading/unloading. Commonly used when STORAGE_ALLOWED_LOCATIONS is set to the * wildcard, allowing access to all buckets in your account except for blocked storage locations and, optionally, paths.

AWS S3

STORAGE_BLOCKED_LOCATIONS = ('s3://bucket/path/', 's3://bucket/path/')

  • 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 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 data files.

COMMENT = 'string_literal'

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

Default: No value

Cloud Provider Parameters (cloudProviderParams)

AWS S3

STORAGE_PROVIDER = S3

Specifies the cloud storage provider that stores your data files.

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 bucket containing your data files. For more information, see Configuring Secure Access to AWS S3.

Google Cloud Storage

STORAGE_PROVIDER = GCS

Specifies the cloud storage provider that stores your data files.

Usage Notes

  • Only account administrators (users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

  • Warning: Recreating a storage integration (using CREATE OR REPLACE STORAGE INTEGRATION) breaks the association between the storage integration and any stage that references it. This is because a stage links to a storage integration using a hidden ID rather than the name of the storage integration. Behind the scenes, the CREATE OR REPLACE syntax drops the object and recreates it with a different hidden ID.

    If you must recreate a storage integration after it has been linked to one or more stages, you must reestablish the association between each stage and the storage integration by executing ALTER STAGE stage_name SET STORAGE_INTEGRATION = storage_integration_name, where:

    • stage_name is the name of the stage.

    • storage_integration_name is the name of the storage integration.

Examples

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

AWS 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/');

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

AWS 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/');