ALTER ICEBERG TABLE … REFRESH

Refreshes the metadata for an Iceberg table that uses an external Iceberg catalog. Refreshing an Iceberg table synchronizes the table metadata with the most recent table changes.

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>' ]
Copy

Parameters

table_name

Identifier for the table to refresh.

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.

For more information, see Identifier requirements.

'metadata_file_relative_path'

Specifies a metadata file path for a table created from Iceberg files in object storage. The path must be relative to the active storage location of the external volume associated with the table.

The following table shows what value to specify based on an example storage location:

Active storage location for the table’s external volume

s3://mybucket_us_east_1

Full path to the metadata file

s3://mybucket_us_east_1/metadata/v1.metadata.json

Value to specify as the 'metadata_file_relative_path'

metadata/v1.metadata.json (without a leading forward slash)

Note

  • If the table uses AWS Glue as the catalog, don’t specify a metadata file path.

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

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

Catalog integration

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.

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

  • 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

Refresh a table that uses AWS Glue as the catalog

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

Refresh a table created from 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.

When specifying a metadata file, you don’t include a leading forward slash (/) in the metadata file path.

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

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.

Language: English