管理 Apache Iceberg™ 表¶

示例：更新表¶

您可以使用 INSERT 和 UPDATE 语句来修改 Snowflake 管理的 Iceberg 表。

下面的示例将一个新值插入到名为 store_sales 的 Iceberg 表中，而且，如果 cola 列的值当前为 -99，则将该列的值更新为 1。

INSERT INTO store_sales VALUES (-99);

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

Query tables¶

当您已为更新、删除和合并操作配置了 行级删除 (https://iceberg.apache.org/spec/#row-level-deletes) 时，Snowflake 支持对 外部管理的 Iceberg 表 进行查询。

要配置行级删除，请参阅 Apache Iceberg 文档中的 Write 属性 (https://iceberg.apache.org/docs/latest/configuration/#write-properties)。

Write to tables by using positional delete files¶

Snowflake supports position row-level deletes for writing to externally managed Iceberg tables stored on Amazon S3, Azure, or Google Cloud. To turn off position deletes, which enable running the DML operations in copy-on-write mode, set the ENABLE_ICEBERG_MERGE_ON_READ parameter to FALSE at the table, schema, or database level.

Write to tables by using deletion vectors¶

To optimize row-level data modifications, Snowflake supports deletion vectors for writing to externally managed and Snowflake-managed Iceberg tables stored on Amazon S3, Azure, or Google Cloud. With deletion vectors, Snowflake can perform "merge-on-read" (MOR) operations, which improve write performance for the following DML statements:

• DELETE

• UPDATE

• MERGE

Snowflake achieves this performance by writing small vector files instead of rewriting large data files. For more information, see Deletion vectors (https://iceberg.apache.org/spec/#deletion-vectors) in the Apache Iceberg specification.

写时复制与读时合并¶

Iceberg 提供了两种模式来配置计算引擎如何处理外部管理表的行级操作。Snowflake 支持这两种模式。

下表描述了何时可能需要使用每种模式：

要了解有关 Iceberg 行级更改的更多信息，请参阅 Apache Iceberg 文档中的 行级删除 (https://iceberg.apache.org/spec/#row-level-deletes)。

注意事项和限制¶

Consider the following information when you use row-level deletes with Iceberg tables:

• Snowflake supports position deletes (https://iceberg.apache.org/spec/#position-delete-files) only for v2 Iceberg tables, and deletion vectors (https://iceberg.apache.org/spec/#deletion-vectors) for v3 Iceberg tables.

• Snowflake only supports position deletes with externally managed Iceberg tables.

• 为了在使用行级删除时获得最佳读取性能，请执行定期压缩和表维护以删除旧的删除文件。有关信息，请参阅 维护使用外部目录的表。

• 过多的位置删除（尤其是悬空位置删除）可能会阻碍表的创建和刷新操作。要避免此问题，请执行表维护以删除多余的位置删除。

  使用的表维护方法取决于您的外部 Iceberg 引擎。例如，您可以使用带有 delete-file-threshold 或 rewrite-all 选项的 Spark rewrite_data_files 方法。有关更多信息，请参阅 Apache Iceberg™ 文档中的 rewrite_data_files (https://iceberg.apache.org/docs/latest/spark-procedures/#rewrite_data_files)。

Automatic Clustering¶

Automatic Clustering reorganizes data within files or partitions based on frequently queried columns. The file size for Iceberg tables is based on your clustering configuration, unless you set a target file size. If you do, the file size is the specific size you set. For more information, see Set a target file size.

To set Automatic Clustering, specify the CLUSTER BY parameter when you create a Snowflake-managed Iceberg table or modify an existing table. For more information, see:

• CREATE ICEBERG TABLE（使用 Snowflake 作为 Iceberg 目录）

• ALTER ICEBERG TABLE

For more information about Automatic Clustering, see 自动聚类.

Data compaction¶

Data compaction combines small files into larger, more efficient files to manage storage, maintain an optimal file size, and improve query performance.

In most cases, data compaction doesn't have a significant effect on compute costs, but if these costs are a concern, you can disable compaction. For example, you might want to disable compaction on a table if you rarely query it. To disable or enable data compaction, see Set data compaction.

Manifest compaction¶

Manifest compaction optimizes the metadata layer by reorganizing and combining smaller manifest files. This compaction reduces metadata overhead and improves query performance.

This feature is enabled automatically and you can't disable it.

Snapshot expiry¶

Snapshot expiry systematically deletes old snapshots and their unique data and metadata files from the table's history. This deletion is based on predefined retention policies.

This feature is enabled automatically and you can't disable it.

刷新表的元数据¶

以下示例手动刷新使用外部目录（例如，AWS Glue 或 Delta）的表的元数据。刷新表可使表与远程目录中发生的所有更改保持同步。

使用这种类型的 Iceberg 表时，您不需要在命令中指定元数据文件路径。

ALTER ICEBERG TABLE my_iceberg_table REFRESH;

要保持表自动更新，您可以设置 自动刷新。使用 ALTER ICEBERG TABLE 命令。

例如：

ALTER ICEBERG TABLE my_iceberg_table SET AUTO_REFRESH = TRUE;

刷新从 Iceberg 文件创建的表的元数据¶

以下示例手动刷新从外部云存储位置中的 Iceberg 元数据文件 创建的表，并指定元数据文件的相对路径，不带前导正斜杠 (/)。元数据文件定义刷新后表中的数据。

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

Example: Create a table with a default value¶

To create an Iceberg table with default values, use the CREATE ICEBERG TABLE command.

In the following example, you first set a default value for a column when you create a Snowflake-managed Iceberg table. Next, you insert a record into the table without specifying a value for the column with the default value.

1. Create a user_events table, which includes an event_version column with a default value of 2:

   CREATE ICEBERG TABLE user_events (
       event_id INT,
       user_id INT,
       event_type STRING,
       event_time TIMESTAMP,
       event_version INT DEFAULT 2
     )
     CATALOG = 'SNOWFLAKE'
     EXTERNAL_VOLUME = 'my_external_volume'
     BASE_LOCATION = 'database/schema/user_event'
     ICEBERG_VERSION = 3;
   

   Copy

   Setting a default value in the table definition sets an initial default and a write default. Because the column has a write default, the value 2 will be used for new records if the event_version isn't specified during writes.

2. Add a login event with event_version specified:

   INSERT INTO user_events VALUES
     (1, 101, 'login', '2025-11-01 10:00:00', 1);
   

   Copy

3. Add a purchase event, but don't specify an event_version:

   INSERT INTO user_events VALUES
   (1, 101, 'purchase', '2025-11-01 10:01:00');
   

   Copy

   As a result, Snowflake inputs the value for event_version into the table as 2.

4. Query the table:

   SELECT * FROM user_events;
   

   Copy

   输出：

   +-----------+----------+-------------+---------------------+----------------+
   | event_id  | user_id  | event_type  | event_time          | event_version  |
   +-----------+----------+-------------+---------------------+----------------+
   | 1         | 101      | login       | 2025-11-01 10:00:00 | 1              |
   | 1         | 101      | purchase    | 2025-11-01 10:01:00 | 2              |
   +-----------+----------+-------------+---------------------+----------------+
   

Example: Add a column with a default value to an existing table¶

To add a new column with a default value to an Iceberg table, use the ALTER ICEBERG TABLE command.

In the following example, you modify the user_events table by adding an event_version column, which has a default value of 2:

ALTER ICEBERG TABLE user_events ADD COLUMN event_version INT DEFAULT 2;

In addition to setting a write default, adding a column with a default value also sets an initial default for the column. As a result, the default value for existing records for the event_version column is 2.

Example: Change the write default for a column¶

The following example changes the write default for the event_version column of the user_events table to 3:

ALTER ICEBERG TABLE user_events ALTER COLUMN event_version SET WRITE DEFAULT 3;

View the default values defined for a table¶

To view the default value for a table column in a Snowflake-managed or externally managed Iceberg table, run the DESCRIBE ICEBERG TABLE command, and then view the DEFAULT column and WRITE DEFAULT column in the output:

• The DEFAULT column maps to the initial-default value in the Apache Iceberg specification.

• The WRITE DEFAULT column maps to the write-default value in the Apache Iceberg specification.

These columns return in the output, regardless of whether the table is a v2 Iceberg table or a v3 Iceberg table.

The following example describes the columns for the user_events table. This table has an initial default and write default specified for the event_version column:

DESC ICEBERG TABLE user_events
  ->> SELECT
    "name",
    "kind",
    "default",
    "write default"
      FROM $1;

输出：

+-----------------+---------+---------+---------------+
| name            | kind    | default | write default |
+-----------------+---------+-------------------------+
| EVENT_ID        | COLUMN  |         |               |
| USER_ID         | COLUMN  |         |               |
| EVENT_TYPE      | COLUMN  |         |               |
| EVENT_TIME      | COLUMN  |         |               |
| EVENT_VERSION   | COLUMN  | 2       | 3             |
+-----------------+---------+---------+---------------+

Drop the write default¶

To drop the write default for a column, use the DROP WRITE DEFAULT keywords with the ALTER ICEBERG TABLE command.

The following example drops the default write value for the event_version column:

ALTER ICEBERG TABLE user_events ALTER COLUMN event_version DROP WRITE DEFAULT;

Considerations and limitations for default values¶

Consider the following items when you use default values with Snowflake-managed and externally managed Iceberg tables:

Considerations and limitations for row lineage¶

Row lineage is supported in streams with the following considerations:

• Append-only streams and standard streams are supported on Snowflake-managed Iceberg v3 tables.

• Insert-only streams and standard streams are supported on externally managed Iceberg v3 tables.

  • To have standard streams produce the correct results, the external engine must write to Iceberg v3 tables with respect to the Iceberg v3 specification. Specifically, newly inserted rows should have _row_id=NULL. Rows that are copied during copy-on-write should maintain the _row_id.

  • MAX_DATA_EXTENSION_TIME_IN_DAYS doesn't work on externally managed Iceberg v3 tables.

• When DMLs are committed over multi-statement transactions, append-only streams on Iceberg v3 tables have different semantics compared to Iceberg v2 tables:

  • On Iceberg v2, for append-only streams, if a row is added and then deleted in a multi-statement transaction, this row is considered an insertion.

  • On Iceberg v3, for append-only streams, this row isn't treated as an insertion.

These considerations and limitations apply to row lineage, which is a feature from Iceberg v3. For a list of considerations and limitations that apply to all Iceberg v3 tables, see Iceberg v3 功能注意事项和限制.