ALTER STAGE¶

Syntax¶

ALTER STAGE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER STAGE [ IF EXISTS ] <name> SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]

ALTER STAGE <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

-- Internal stage
ALTER STAGE [ IF EXISTS ] <name> SET
  { [ COMMENT = '<string_literal>' ] }

-- External stage
ALTER STAGE [ IF EXISTS ] <name> SET {
    [ externalStageParams ]
    [ COMMENT = '<string_literal>' ]
    }

Where:

externalStageParams (for Amazon S3) ::=
  [ URL = 's3://<bucket>[/<path>/]' ]
  [ { STORAGE_INTEGRATION = <integration_name> } | { CREDENTIALS = ( {  { AWS_KEY_ID = '<string>' AWS_SECRET_KEY = '<string>' [ AWS_TOKEN = '<string>' ] } | AWS_ROLE = '<string>'  } ) } ]
  [ ENCRYPTION = ( [ TYPE = 'AWS_CSE' ] [ MASTER_KEY = '<string>' ] |
                   [ TYPE = 'AWS_SSE_S3' ] |
                   [ TYPE = 'AWS_SSE_KMS' [ KMS_KEY_ID = '<string>' ] |
                   [ TYPE = 'NONE' ] ) ]

Copy

externalStageParams (for Google Cloud Storage) ::=
  [ URL = 'gcs://<bucket>[/<path>/]' ]
  [ STORAGE_INTEGRATION = <integration_name> } ]
  [ ENCRYPTION = ( [ TYPE = 'GCS_SSE_KMS' ] [ KMS_KEY_ID = '<string>' ] | [ TYPE = 'NONE' ] ) ]

Copy

externalStageParams (for Microsoft Azure) ::=
  [ URL = 'azure://<account>.blob.core.windows.net/<container>[/<path>/]' ]
  [ { STORAGE_INTEGRATION = <integration_name> } | { CREDENTIALS = ( [ AZURE_SAS_TOKEN = '<string>' ] ) } ]
  [ ENCRYPTION = ( [ TYPE = { 'AZURE_CSE' | 'NONE' } ] [ MASTER_KEY = '<string>' ] ) ]

Copy

Directory table syntax¶

ALTER STAGE [ IF EXISTS ] <name> SET DIRECTORY = ( { ENABLE = TRUE | FALSE } )

ALTER STAGE [ IF EXISTS ] <name> REFRESH [ SUBPATH = '<relative-path>' ]

Parameters¶

name

Specifies the identifier for the stage to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

RENAME TO new_name

Specifies the new identifier for the stage; must be unique for the schema.

For more details, see Identifier requirements.

You can move the object to a different database and/or schema while optionally renaming the object. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.object_name or schema_name.object_name, respectively.

Note

• The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.

• Moving an object to a managed access schema is prohibited unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.

SET ...

Specifies the options/properties to set for the stage:

URL = ' ... ' , . STORAGE_INTEGRATION = ... , . CREDENTIALS = ( ... ) , . ENCRYPTION = ( ... )

Modifies the cloud-specific URL, storage integration or credentials, and/or encryption for the external stage. For more details, see External Stage Parameters (in this topic).

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

COMMENT = 'string_literal'

Adds a comment or overwrites an existing comment for the stage.

External stage parameters (externalStageParams)¶

URL = 'cloud_specific_url'

If a stage does not have a URL, it is an internal stage

Warning

Modifying the URL parameter of a stage can break the following functionality for objects that rely on the stage:

• Pipe objects that leverage cloud messaging to trigger data loads (i.e. where AUTO_INGEST = TRUE).

• External tables that leverage cloud messaging to trigger metadata refreshes (i.e. where AUTO_REFRESH = TRUE).

Amazon S3

URL = 's3://bucket[/path/]'

Modifies the URL for the external location (existing S3 bucket) used to store data files for loading/unloading, where:

• bucket is the name of the S3 bucket.

  Note that S3 buckets in non-public AWS regions, such as government regions, are not supported.

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

Google Cloud Storage

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

Modifies the URL for the external location (existing GCS bucket) used to store data files for loading/unloading, where:

• bucket is the name of the GCS bucket.

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

Microsoft Azure

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

Modifies the URL for the external location (existing Azure container) used to store data files for loading, where:

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

• container is the name of the Azure container (e.g. mycontainer).

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

STORAGE_INTEGRATION = integration_name or . CREDENTIALS = ( cloud_specific_credentials )

Required only if the Amazon S3, Google Cloud Storage, or Microsoft Azure is private; not required for public buckets/containers

Amazon S3

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Note

We highly recommend the use of storage integrations. This option avoids the need to supply cloud storage credentials using the CREDENTIALS parameter when creating stages or loading data.

CREDENTIALS = ( AWS_KEY_ID = 'string' AWS_SECRET_KEY = 'string' [ AWS_TOKEN = 'string' ] ) or . CREDENTIALS = ( AWS_ROLE = 'string' )

Modifies the security credentials for connecting to AWS and accessing the private S3 bucket where the files to load/unload are staged. For more information, see Configuring secure access to Amazon S3.

The credentials you specify depend on whether you associated the Snowflake access permissions for the bucket with an AWS IAM (Identity & Access Management) user or role:

• IAM user: IAM credentials are required. Temporary (aka “scoped”) credentials are generated by AWS Security Token Service (STS) and consist of three components:

  • AWS_KEY_ID

  • AWS_SECRET_KEY

  • AWS_TOKEN

  All three are required to access a private bucket. After a designated period of time, temporary credentials expire and can no longer be used. You must then generate a new set of valid temporary credentials.

  Important

  The COPY command also allows permanent (aka “long-term”) credentials to be used; however, for security reasons, Snowflake does not recommend using them. If you must use permanent credentials, Snowflake recommends periodically generating new permanent credentials for external stages.

• IAM role: Omit the security credentials and access keys and, instead, identify the role using AWS_ROLE and specify the AWS role ARN (Amazon Resource Name).

  Important

  The ability to use an AWS IAM role to access a private S3 bucket to load or unload data is now deprecated (i.e. support will be removed in a future release, TBD). We highly recommend modifying any existing S3 stages that use this feature to instead reference storage integration objects. For instructions, see Option 1: Configuring a Snowflake storage integration to access Amazon S3.

Google Cloud Storage

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Microsoft Azure

STORAGE_INTEGRATION = integration_name

Specifies the name of the storage integration used to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity. For more details, see CREATE STORAGE INTEGRATION.

Note

We highly recommend the use of storage integrations. This option avoids the need to supply cloud storage credentials using the CREDENTIALS parameter when creating stages or loading data.

CREDENTIALS = ( AZURE_SAS_TOKEN = 'string' )

Modifies the SAS (shared access signature) token for connecting to Azure and accessing the private container where the files containing loaded data are staged. Credentials are generated by Azure.

ENCRYPTION = ( cloud_specific_encryption )

Required only for loading from/unloading into encrypted files; not required if storage location and files are unencrypted

Data loading:

Modifies the encryption settings used to decrypt encrypted files in the storage location and extract data.

Data unloading:

Modifies the encryption settings used to encrypt files unloaded to the storage location.

Amazon S3

ENCRYPTION = ( [ TYPE = 'AWS_CSE' ] [ MASTER_KEY = '<string>' ] | [ TYPE = 'AWS_SSE_S3' ] | [ TYPE = 'AWS_SSE_KMS' ] [ KMS_KEY_ID = '<string>' ] | [ TYPE = 'NONE' ] )

TYPE = ...

Specifies the encryption type used. Possible values are:

• AWS_CSE: Client-side encryption (requires a MASTER_KEY value). Currently, the client-side master key you provide can only be a symmetric key. Note that, when a MASTER_KEY value is provided, Snowflake assumes TYPE = AWS_CSE (i.e. when a MASTER_KEY value is provided, TYPE is not required).

• AWS_SSE_S3: Server-side encryption that requires no additional encryption settings.

• AWS_SSE_KMS: Server-side encryption that accepts an optional KMS_KEY_ID value.

For more information about the encryption types, see the AWS documentation for client-side encryption or server-side encryption.

• NONE: No encryption.

MASTER_KEY = 'string' (applies to AWS_CSE encryption only)

Specifies the client-side master key used to encrypt the files in the bucket. The master key must be a 128-bit or 256-bit key in Base64-encoded form.

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 unloaded into the bucket. If no value is provided, your default KMS key ID is used to encrypt files on unload.

Note that this value is ignored for data loading.

Google Cloud Storage

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

TYPE = ...

Specifies the encryption type used. Possible values are:

• GCS_SSE_KMS: Server-side encryption that accepts an optional KMS_KEY_ID value.

  For more information, see the Google Cloud Platform documentation:

  • https://cloud.google.com/storage/docs/encryption/customer-managed-keys

  • https://cloud.google.com/storage/docs/encryption/using-customer-managed-keys

• NONE: No encryption.

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

Optionally specifies the ID for the Cloud KMS-managed key that is used to encrypt files unloaded into the bucket. If no value is provided, your default KMS key ID set on the bucket is used to encrypt files on unload.

Note that this value is ignored for data loading. The load operation should succeed if the service account has sufficient permissions to decrypt data in the bucket.

Microsoft Azure

ENCRYPTION = ( [ TYPE = 'AZURE_CSE' | 'NONE' ] [ MASTER_KEY = 'string' ] )

TYPE = ...

Specifies the encryption type used. Possible values are:

• AZURE_CSE: Client-side encryption (requires a MASTER_KEY value). For information, see the Client-side encryption information in the Microsoft Azure documentation.

• NONE: No encryption.

MASTER_KEY = 'string' (applies to AZURE_CSE encryption only)

Specifies the client-side master key used to encrypt or decrypt files. The master key must be a 128-bit or 256-bit key in Base64-encoded form.

Directory table parameters¶

ENABLE = TRUE | FALSE

Specifies whether to add a directory table to the stage. When the value is TRUE, a directory table is added to the stage.

Note

Setting this parameter to TRUE is not supported for S3-compatible external stages. The metadata for S3-compatible external stages cannot be refreshed automatically.

Default: FALSE

REFRESH

Accesses the staged data files referenced in the directory table definition and updates the table metadata:

• New files in the path are added to the table metadata.

• Changes to files in the path are updated in the table metadata.

• Files no longer in the path are removed from the table metadata.

You can execute this command each time files are added to the stage, updated, or dropped. This step synchronizes the metadata with the latest set of associated files in the stage definition for the directory table.

SUBPATH = '<relative-path>'

Optionally specify a relative path to refresh the metadata for a specific subset of the data files.

Access control requirements¶

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

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

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¶

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

Examples¶

Rename my_int_stage to new_int_stage:

ALTER STAGE my_int_stage RENAME TO new_int_stage;

Copy

Alter my_ext_stage (created in the CREATE STAGE examples) to change the URL to reference a sub-folder named new in the files folder. If a COPY INTO <table> command that references this stage encounters a data error on any of the records, it skips the file. All other copy options are set to the default values:

ALTER STAGE my_ext_stage
SET URL='s3://loading/files/new/'
COPY_OPTIONS = (ON_ERROR='skip_file');

Copy

Alter my_ext_stage to replace the supplied credentials with a reference to a storage integration named myint :

ALTER STAGE my_ext_stage SET STORAGE_INTEGRATION = myint;

Copy

Alter my_ext_stage to specify a new access key ID and secret access key for the stage:

ALTER STAGE my_ext_stage SET CREDENTIALS=(AWS_KEY_ID='d4c3b2a1' AWS_SECRET_KEY='z9y8x7w6');

Copy

(the credentials values used in the above example are for illustration purposes only)

Alter my_ext_stage3 to change the encryption type to AWS_SSE_S3 server-side encryption for the stage:

ALTER STAGE my_ext_stage3 SET ENCRYPTION=(TYPE='AWS_SSE_S3');

Copy

Directory table examples¶

Add a directory table to an existing stage named mystage:

ALTER STAGE mystage SET DIRECTORY = ( ENABLE = TRUE );

Manually refresh the directory table metadata in a stage named mystage:

ALTER STAGE mystage REFRESH;

+-------------------------+----------------+-------------------------------+
| file                    | status         | description                   |
|-------------------------+----------------+-------------------------------|
| data/json/myfile.json   | REGISTERED_NEW | File registered successfully. |
+-------------------------+----------------+-------------------------------+

Manually refresh the directory table metadata for the files in the data path in a stage named mystage:

ALTER STAGE mystage REFRESH SUBPATH = 'data';