Manage an Iceberg table

This topic covers the following tasks to help you manage an Iceberg table:

You can also convert an Iceberg table that uses a catalog integration into a table that uses Snowflake as the Iceberg catalog. To learn more, see Convert an Iceberg table.

Manually refresh the table metadata

When you use a catalog integration, you can refresh the table metadata from a specified Iceberg metadata file using the ALTER ICEBERG TABLE command. Refreshing the table metadata synchronizes the metadata with the most recent table changes.

Refresh the metadata for an Iceberg table that uses AWS Glue as the catalog

The following example manually refreshes the metadata for a table that uses AWS Glue for the Iceberg catalog. Refreshing the table keeps the table in sync with any changes that have occurred in AWS Glue.

With this type of Iceberg table, you don’t specify a metadata file path in the command.

ALTER ICEBERG TABLE my_iceberg_table REFRESH;
Copy

Refresh the metadata for an Iceberg table created from files in object storage

The following example manually refreshes a table created from Iceberg metadata files in an external cloud storage location, specifying the relative path to a metadata file. The metadata file defines the data in the table after refreshing.

ALTER ICEBERG TABLE my_iceberg_table REFRESH 'metadata/v1.metadata.json';
Copy

Generate snapshots of DML changes

For tables that use Snowflake as the catalog, Snowflake automatically generates the Iceberg metadata. Snowflake writes the metadata to a folder named metadata on your external volume in the location specified by the BASE_LOCATION parameter when the table was created.

Alternatively, you can call the SYSTEM$GET_ICEBERG_TABLE_INFORMATION function to generate Iceberg metadata for any new changes.

For example:

SELECT SYSTEM$GET_ICEBERG_TABLE_INFORMATION('db1.schema1.it1');
Copy

Output:

+-----------------------------------------------------------------------------------------------------------+
| SYSTEM$GET_ICEBERG_TABLE_INFORMATION('DB1.SCHEMA1.IT1')                                                   |
|-----------------------------------------------------------------------------------------------------------|
| {"metadataLocation":"s3://mybucket/metadata/v1.metadata.json","status":"success"}                         |
+-----------------------------------------------------------------------------------------------------------+

Query an Iceberg table

To query an Iceberg table, a user must be granted or inherit the following privileges:

  • The USAGE privilege on the database and schema that contain the table.

  • The SELECT privilege on the table.

You can query an Iceberg table using a SELECT statement. For example:

SELECT col1, col2 FROM my_iceberg_table;
Copy

Update an Iceberg table

You can use INSERT and UPDATE statements to modify an Iceberg table that uses Snowflake as the catalog.

The following example inserts a new value into an Iceberg table named store_sales, then updates the cola column to 1 if the value is currently -99.

INSERT INTO store_sales values (-99);

UPDATE store_sales
  SET cola = 1
  WHERE cola = -99;
Copy

Retrieve storage metrics for Iceberg tables

Snowflake does not bill your account for Iceberg table storage costs. However, you can track how much storage an Iceberg table occupies by querying the TABLE_STORAGE_METRICS and TABLES views in the Snowflake Information Schema or Account Usage schema.

The following example query joins the ACCOUNT_USAGE.TABLE_STORAGE_METRICS view with the ACCOUNT_USAGE.TABLES view, filtering on the TABLES.IS_ICEBERG column.

SELECT metrics.* FROM
  snowflake.account_usage.table_storage_metrics metrics
  INNER JOIN snowflake.account_usage.tables tables
  ON (
    metrics.id = tables.table_id
    AND metrics.table_schema_id = tables.table_schema_id
    AND metrics.table_catalog_id = tables.table_catalog_id
  )
  WHERE tables.is_iceberg='YES';
Copy