ALTER ICEBERG TABLE¶

Syntax¶

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> REFRESH [ '<metadata_file_relative_path>' ]

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> CONVERT TO MANAGED [ BASE_LOCATION = '<file_path>' ]

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> clusteringAction

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

ALTER ICEBERG TABLE [ IF EXISTS ] <name> UNSET TAG <tag_name> [ , <tag_name> ... ]

Where:

clusteringAction ::=
  {
     CLUSTER BY ( <expr> [ , <expr> , ... ] )
     /* { SUSPEND | RESUME } RECLUSTER is valid action */
   | { SUSPEND | RESUME } RECLUSTER
   | DROP CLUSTERING KEY
  }

Copy

Parameters¶

name

Identifier for the Iceberg table 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.

REFRESH [ 'metadata_file_relative_path' ]

Accesses a metadata file at the specified path (relative to the table’s external volume storage location) for an Iceberg table that Snowflake doesn’t manage, and updates the table metadata.

Only use this option if your Iceberg table uses Iceberg files in object storage as a source. Omit this option if you use AWS Glue as your Iceberg catalog.

For example, if s3://mybucket_us_east_1/metadata/v1.metadata.json is the full path to your metadata file, and the storage location for the external volume that is associated with the table is s3://mybucket_us_east_1, specify metadata/v1.metadata.json (without a leading forward slash) as the metadata_file_relative_path.

Note

• Don’t include a leading forward slash (/) in the metadata file path.

• Before Snowflake version 7.34, a parameter named BASE_LOCATION (also called FILE_PATH in previous versions) was required to create a table from Iceberg files in object storage. The parameter specified a relative path from the EXTERNAL_VOLUME location.

  To refresh a table that you created using the old syntax, specify a path relative to the BASE_LOCATION. For example, if the full path to your metadata file is s3://mybucket_us_east_1/my_base_location/metadata/v1.metadata.json, specify metadata/v1.metadata.json as the metadata-file-relative-path.

CONVERT TO MANAGED

Converts a read-only Iceberg table that uses an external catalog into a table that uses Snowflake as the Iceberg catalog. The converted table supports both read and write operations, and Snowflake handles all life-cycle maintenance, such as compaction, for the table. For more information, see Convert an Iceberg table.

[ BASE_LOCATION = 'file_path' ]

Specifies a relative path to a directory from the table’s EXTERNAL_VOLUME location where Snowflake can write table data and metadata. You must specify a value for this property during conversion if the original CREATE ICEBERG TABLE statement did not allow or include a BASE_LOCATION.

The value of this parameter cannot be changed after you convert a table.

SET ...

Specifies one or more properties/parameters to set for the external table (separated by blank spaces, commas, or new lines):

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.

UNSET

Currently, you can only unset the following with this command:

• TAG tag_name [ , tag_name ... ]

Clustering actions (clusteringAction)¶

CLUSTER BY ( expr [ , expr , ... ] )

Specifies (or modifies) one or more table columns or column expressions as the clustering key for the table. These are the columns/expressions for which clustering is maintained by Automatic Clustering.

To learn more about clustering, see Clustering Keys & Clustered Tables.

SUSPEND | RESUME RECLUSTER

Enables or disables Automatic Clustering for the table.

DROP CLUSTERING KEY

Drops the clustering key for the table.

For more information about clustering keys and reclustering, see Understanding Snowflake Table Structures.

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¶

• Only the table owner (that is, the role with the OWNERSHIP privilege on the table) or higher can execute this command.

• Clustering is only supported for tables that use Snowflake as the Iceberg catalog. To add clustering to an Iceberg table, you must also have the USAGE or OWNERSHIP privileges on the schema and database that contain the table.

• Using the ALTER ICEBERG TABLE … REFRESH command in transactions (implicit or explicit) is not supported.

• Before Snowflake version 7.34, a parameter named BASE_LOCATION (also called FILE_PATH in previous versions) was required to create a table from Iceberg files in object storage. The parameter specified a relative path from the EXTERNAL_VOLUME location.

  To refresh a table that you created using the old syntax, specify a path relative to the BASE_LOCATION. For example, if the full path to your metadata file is s3://mybucket_us_east_1/my_base_location/metadata/v1.metadata.json, specify metadata/v1.metadata.json as the metadata-file-relative-path.

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

ALTER ICEBERG TABLE my_iceberg_table REFRESH 'path/to/metadata/v2.metadata.json';

ALTER ICEBERG TABLE myIcebergTable REFRESH;

ALTER ICEBERG TABLE myTable CONVERT TO MANAGED
  BASE_LOCATION = myBaseLocation;