CREATE EXTERNAL VOLUME¶

Creates a new external volume for Apache Icebergâ„¢ tables in the account or replaces an existing external volume.

See also:

ALTER EXTERNAL VOLUME , DROP EXTERNAL VOLUME , SHOW EXTERNAL VOLUMES, DESCRIBE EXTERNAL VOLUME

Syntax¶

CREATE [ OR REPLACE ] EXTERNAL VOLUME [IF NOT EXISTS]
  <name>
  STORAGE_LOCATIONS =
    (
      (
        NAME = '<storage_location_name>'
        cloudProviderParams
      )
      [, (...), ...]
    )
  [ ALLOW_WRITES = { TRUE | FALSE }]
  [ COMMENT = '<string_literal>' ]
Copy

Where:

cloudProviderParams (for Amazon S3) ::=
  STORAGE_PROVIDER = '{ S3 | S3GOV }'
  STORAGE_AWS_ROLE_ARN = '<iam_role>'
  STORAGE_BASE_URL = '<protocol>://<bucket>[/<path>/]'
  [ STORAGE_AWS_EXTERNAL_ID = '<external_id>' ]
  [ ENCRYPTION = ( [ TYPE = 'AWS_SSE_S3' ] |
              [ TYPE = 'AWS_SSE_KMS' [ KMS_KEY_ID = '<string>' ] ] |
              [ TYPE = 'NONE' ] ) ]
Copy
cloudProviderParams (for Google Cloud Storage) ::=
  STORAGE_PROVIDER = 'GCS'
  STORAGE_BASE_URL = 'gcs://<bucket>[/<path>/]'
  [ ENCRYPTION = ( [ TYPE = 'GCS_SSE_KMS' ] [ KMS_KEY_ID = '<string>' ] |
              [ TYPE = 'NONE' ] ) ]
Copy
cloudProviderParams (for Microsoft Azure) ::=
  STORAGE_PROVIDER = 'AZURE'
  AZURE_TENANT_ID = '<tenant_id>'
  STORAGE_BASE_URL = 'azure://<account>.blob.core.windows.net/<container>[/<path>/]'
Copy

Required parameters¶

name

String that specifies the identifier (the name) for the external volume; must be unique in your account.

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 (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more details, see Identifier requirements.

STORAGE_LOCATIONS = ( ( NAME = 'storage_location_name' cloudProviderParams ) [, (...), ...] )

Set of named cloud storage locations in different regions and, optionally, cloud platforms.

Note

  • Each external volume that you create supports a single active storage location.

  • Cross-cloud and cross-region tables are not currently supported when you use Snowflake as the Iceberg catalog.

Optional parameters¶

ALLOW_WRITES = '{ TRUE | FALSE }'

Specifies whether write operations are allowed for the external volume; must be set to TRUE for Iceberg tables that use Snowflake as the catalog.

The value of this parameter must also match the permissions that you set on the cloud storage account for each specified storage location.

Note

If you plan to use the external volume for externally managed Iceberg tables, you can set this parameter to FALSE. Snowflake doesn’t write data or Iceberg metadata files to your cloud storage when you use an external Iceberg catalog.

Default: TRUE

COMMENT = 'string_literal'

String (literal) that specifies a comment for the external volume.

Default: No value

Cloud provider parameters (cloudProviderParams)¶

Note

The KMS keys are managed by the storage owner in Amazon S3 or Google Cloud Storage instances. The service principals (IAM role and GCS service account) must be granted privileges to use KMS keys. For more information, see Encrypting table files.

Amazon S3

STORAGE_PROVIDER = '{ S3 | S3GOV }'

Specifies the cloud storage provider that stores your data files.

  • 'S3': S3 storage in public AWS regions outside of China.

  • 'S3GOV': S3 storage in AWS government regions.

STORAGE_AWS_ROLE_ARN = 'iam_role'

Specifies the case-sensitive 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 Configure an external volume for Amazon S3.

STORAGE_BASE_URL = 'protocol://bucket[/path/]'

Specifies the base URL for your cloud storage location, where:

  • protocol is one of the following:

    • s3 refers to S3 storage in public AWS regions outside of China.

    • s3gov refers to S3 storage in government regions.

  • bucket is the name of an S3 bucket that stores your data files.

  • path is an optional path that can be used to provide granular control over objects in the bucket.

Important

To create an Iceberg table that uses an external catalog, your Parquet data files and Iceberg metadata files must be within the STORAGE_BASE_URL location.

STORAGE_AWS_EXTERNAL_ID = 'external_id'

Optionally specifies an external ID that Snowflake uses to establish a trust relationship with AWS. You must specify the same external ID in the trust policy of the IAM role that you configured for this external volume. For more information, see How to use an external ID when granting access to your AWS resources to a third party.

If you don’t specify a value for this parameter, Snowflake automatically generates an external ID when you create the external volume.

ENCRYPTION = ( [ TYPE = 'AWS_SSE_S3' ] | [ TYPE = 'AWS_SSE_KMS' [ KMS_KEY_ID = 'string' ] ] | [ TYPE = 'NONE' ] )

Specifies the properties needed to encrypt data on the external volume.

TYPE = ...

Specifies the encryption type used. Possible values are:

KMS_KEY_ID = 'string' (applies to AWS_SSE_KMS encryption only)

Optionally specifies the ID for the AWS KMS-managed key used to encrypt files written to the bucket. If no value is provided, your default KMS key is used to encrypt files for writing data.

Note that this value is ignored when reading data.

Google Cloud Storage

STORAGE_PROVIDER = 'GCS'

Specifies the cloud storage provider that stores your data files.

STORAGE_BASE_URL = 'gcs://bucket[/path/]'

Specifies the base URL for your cloud storage location, where:

  • bucket is the name of a Cloud Storage bucket that stores your data files.

  • path is an optional path that can be used to provide granular control over objects in the bucket.

Important

To create an Iceberg table that uses an external catalog, your Parquet data files and Iceberg metadata files must be within the STORAGE_BASE_URL location.

ENCRYPTION = ( [ TYPE = 'GCS_SSE_KMS' ] [ KMS_KEY_ID = 'string' ] | [ TYPE = 'NONE' ] )

Specifies the properties needed to encrypt data on the external volume.

TYPE = ...

Specifies the encryption type used. Possible values are:

KMS_KEY_ID = 'string' (applies to GCS_SSE_KMS encryption only)

Specifies the ID for the Cloud KMS-managed key used to encrypt files written to the bucket.

Note that this value is ignored when reading data. The read operation should succeed if the service account has sufficient permissions to the data and any specified KMS keys.

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 storage location belongs to. An external volume can authenticate to only one tenant, so the storage location must refer to a storage account that belongs to this tenant.

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

STORAGE_BASE_URL = 'azure://account.blob.core.windows.net/container[/path/]'

Specifies the base URL for your cloud storage location, where:

  • account is the name of your Azure account; for example, myaccount.

  • container is the name of an Azure container that stores your data files.

  • path is an optional path that can be used to provide granular control over logical directories in the container.

Note

Use the azure:// prefix and not https://.

Important

To create an Iceberg table that uses an external catalog, your Parquet data files and Iceberg metadata files must be within the STORAGE_BASE_URL location.

Access control requirements¶

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

Privilege

Object

Notes

CREATE EXTERNAL VOLUME

Account

Only the ACCOUNTADMIN role has this privilege by default. The privilege can be granted to additional roles as needed.

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 on securable objects, see Overview of Access Control.

Usage notes¶

Important

External volumes in Amazon S3 storage only: If you recreate an external volume (using the CREATE OR REPLACE EXTERNAL VOLUME syntax) without specifying an external ID, you must repeat the steps to grant the AWS identity and access management (IAM) user for your Snowflake account the access permissions required on the S3 storage location. For more information, see the instructions for retrieving the AWS IAM user for your Snowflake account in Configure an external volume for Amazon S3.

  • You can’t drop or replace an external volume if one or more Iceberg tables are associated with the external volume.

    To view the tables that depend on an external volume, you can use the SHOW ICEBERG TABLES command and a query using RESULT_SCAN that filters on the external_volume_name column.

    Note

    The column identifier (external_volume_name) is case-sensitive. Specify the column identifier exactly as it appears in the SHOW ICEBERG TABLES output.

    For example:

    SHOW ICEBERG TABLES;
    
    SELECT * FROM TABLE(
      RESULT_SCAN(
          LAST_QUERY_ID()
        )
      )
      WHERE "external_volume_name" = 'my_external_volume_1';
    
    Copy
  • Regarding metadata:

    Attention

    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 an object is replaced, the old object is deleted and the new object is created in a single transaction.

Examples¶

The following examples create external volumes that define storage locations with different cloud providers:

Amazon S3

The following example creates an external volume that defines an Amazon S3 storage location with encryption:

CREATE OR REPLACE EXTERNAL VOLUME exvol
  STORAGE_LOCATIONS =
      (
        (
            NAME = 'my-s3-us-west-2'
            STORAGE_PROVIDER = 'S3'
            STORAGE_BASE_URL = 's3://MY_EXAMPLE_BUCKET/'
            STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/myrole'
            ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID='1234abcd-12ab-34cd-56ef-1234567890ab')
        )
      );
Copy

Google Cloud Storage

The following example creates an external volume that defines a GCS storage location with encryption:

CREATE EXTERNAL VOLUME exvol
  STORAGE_LOCATIONS =
    (
      (
        NAME = 'my-us-east-1'
        STORAGE_PROVIDER = 'GCS'
        STORAGE_BASE_URL = 'gcs://mybucket1/path1/'
        ENCRYPTION=(TYPE='GCS_SSE_KMS' KMS_KEY_ID = '1234abcd-12ab-34cd-56ef-1234567890ab')
      )
    );
Copy

Microsoft Azure

The following example creates an external volume that defines an Azure storage location with encryption:

CREATE EXTERNAL VOLUME exvol
  STORAGE_LOCATIONS =
    (
      (
        NAME = 'my-azure-northeurope'
        STORAGE_PROVIDER = 'AZURE'
        STORAGE_BASE_URL = 'azure://exampleacct.blob.core.windows.net/my_container_northeurope/'
        AZURE_TENANT_ID = 'a123b4c5-1234-123a-a12b-1a23b45678c9'
      )
    );
Copy