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 an external catalog into a table that uses Snowflake as the Iceberg catalog. To learn more, see Convert an Iceberg table.

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

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 tables that aren’t managed by Snowflake, the function returns information about the latest refreshed snapshot.

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"}                         |
+-----------------------------------------------------------------------------------------------------------+

Maintain tables that use an external Iceberg catalog

You can perform maintenance operations on Iceberg tables that use an external catalog.

Maintenance operations include the following:

  • Expiring snapshots

  • Removing old metadata files

  • Compacting data files

Important

To keep your Iceberg table in sync with external changes, it’s important to align your Snowflake refresh schedule with table maintenance. Refresh the table each time you perform a maintenance operation.

To learn about maintenance for Iceberg tables that aren’t managed by Snowflake, see Maintenance in the Apache Iceberg documentation.

Refresh the table metadata

When you use an external Iceberg catalog, you can refresh the table metadata 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 without the leading forward slash (/). The metadata file defines the data in the table after refreshing.

ALTER ICEBERG TABLE my_iceberg_table REFRESH 'metadata/v1.metadata.json';
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
Language: English