ALTER ICEBERG TABLE

Modifies the properties, columns, or constraints for an existing Iceberg table. When an Iceberg table uses a catalog integration, use ALTER ICEBERG TABLE to refresh the table with new data.

You can also use ALTER ICEBERG TABLE to convert a table that uses a catalog integration into a table that uses Snowflake as the Iceberg catalog. For more information, see Convert an Iceberg table.

This topic refers to Iceberg tables as simply “tables” except where specifying Iceberg tables avoids confusion.

See also:

CREATE ICEBERG TABLE , DROP ICEBERG TABLE , SHOW ICEBERG TABLES , DESCRIBE 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> ... ]
Copy

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.

Use this option only 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 as the metadata_file_relative_path.

Note

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 a catalog integration 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)

Note

Clustering is only supported for tables that use Snowflake as the Iceberg catalog.

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:

Privilege

Object

Notes

OWNERSHIP

Iceberg table

OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

USAGE

External volume

Required to manually refresh the table metadata.

USAGE

File format

Required to manually refresh the table metadata.

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.

  • The following command can be used in explicit transactions (using BEGINCOMMIT): ALTER ICEBERG TABLE ... REFRESH ['metadata_file_relative_pathname']

    Explicit transactions could be used to ensure a consistent state when manually replacing updated files in table metadata.

  • 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

Manually refresh table metadata for tables that use a catalog integration

Iceberg files in object storage

This example manually refreshes the table metadata based on changes in a new metadata file. In this example, the full path to the metadata file is <external-volume-storage-base-url>/path/to/metadata/v2.metadata.json.

Note

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.

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

AWS Glue

This example manually refreshes the metadata for a table that uses AWS Glue for the Iceberg catalog. When you use AWS Glue as the Iceberg catalog, you don’t specify a metadata file path in the refresh command.

ALTER ICEBERG TABLE myIcebergTable REFRESH;
Copy

Convert an Iceberg table

The following example uses the ALTER ICEBERG TABLE … CONVERT TO MANAGED statement to convert a table that Snowflake doesn’t manage into a table that uses Snowflake as the Iceberg catalog.

ALTER ICEBERG TABLE myTable CONVERT TO MANAGED
  BASE_LOCATION = myBaseLocation;
Copy