管理 Apache Iceberg™ 表¶

查询表¶

若要查询 Iceberg 表，用户必须获授予或继承以下权限：

• 对包含该表的数据库和架构的 USAGE 权限

• 对表的 SELECT 权限

您可以使用 SELECT 语句查询 Iceberg 表。例如：

SELECT col1, col2 FROM my_iceberg_table;

使用 DML 命令¶

使用 Snowflake 作为目录的 Iceberg 表支持完整 数据操作语言 (DML) 命令，包括以下内容：

• INSERT

• MERGE

• UPDATE

• DELETE

• TRUNCATE TABLE

Snowflake 管理的表还支持使用 COPY INTO <table> 和 Snowpipe 等功能进行高效的批量加载。有关更多信息，请参阅 将数据加载到 Apache Iceberg™ 表中。

INSERT INTO store_sales VALUES (-99);

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

生成 DML 变更的快照¶

对于使用 Snowflake 作为目录的表，Snowflake 会自动生成 Iceberg 元数据。Snowflake 将元数据写入外部卷上名为 metadata 的文件夹。要查找 metadata 文件夹，请参阅 数据和元数据目录。

或者，您可以调用 SYSTEM$GET_ICEBERG_TABLE_INFORMATION 函数为新变更生成 Iceberg 元数据。

对于不受 Snowflake 管理的表，该函数将返回有关最新刷新快照的信息。

例如：

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

输出：

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

使用行级删除¶

Snowflake supports querying tables with row-level deletes and writing to tables by using row-level deletes.

Set a target file size¶

To improve query performance for external Iceberg engines such as Spark or Trino, you can configure a target file size for both Snowflake-managed and externally managed Iceberg tables with write support. You can either set a specific size (16MB, 32MB, 64MB, or 128MB), or use the AUTO option. AUTO works differently, depending on the table type:

• Snowflake-managed tables: AUTO specifies that Snowflake should choose the file size for the table based on table characteristics such as size, DML patterns, ingestion workload, and clustering configuration. Snowflake automatically adjusts the file size, starting at 16 MB, for better read and write performance in Snowflake.

• Externally managed tables: AUTO specifies that Snowflake should aggressively scale to a larger file size.

You can set the target file size when you create an Iceberg table, or run the ALTER ICEBERG TABLE command to change the target file size for an existing Iceberg table. Snowflake attempts to maintain file sizes close to the target size when writing Parquet files for a table.

After you set a target file size, Snowflake immediately starts to create larger files for new Data Manipulation Language (DML) operations. Snowflake's table maintenance operations asynchronously change the existing table files according to the target file size.

The following example uses TARGET_FILE_SIZE to set a target file size of 128 MB for a Snowflake-managed table:

CREATE ICEBERG TABLE my_iceberg_table (col1 INT)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'my_iceberg_table'
  TARGET_FILE_SIZE = '128MB';

Alternatively, use ALTER ICEBERG TABLE to set the TARGET_FILE_SIZE property for an existing table:

ALTER ICEBERG TABLE my_iceberg_table
  SET TARGET_FILE_SIZE = '32MB';

To check the value of the TARGET_FILE_SIZE property for a table, use the SHOW PARAMETERS command:

SHOW PARAMETERS LIKE 'target_file_size' FOR my_iceberg_table;

Table optimization for Snowflake-managed Iceberg tables¶

Table optimization automatically performs maintenance to improve the performance and reduce the storage costs of your Snowflake-managed Iceberg tables.

Snowflake supports the Iceberg table optimization features summarized in the following table:

_{[1] Unlike the other table optimization features, Automatic Clustering is billed separately as a standalone feature.}

维护使用外部目录的表¶

您可以对使用外部目录的 Iceberg 表执行维护操作。

维护操作包括以下内容：

• 使快照过期

• 移除旧的元数据文件

• 压缩数据文件

要了解不受 Snowflake 管理的 Iceberg 表的维护信息，请参阅 Apache Iceberg 文档中的 维护 (https://iceberg.apache.org/docs/latest/maintenance/)。

刷新表元数据¶

当您使用外部 Iceberg 目录时，可以使用 ALTER ICEBERG TABLE ... REFRESH 命令刷新表元数据。刷新表元数据会将元数据与最近的表更改同步。

ALTER ICEBERG TABLE my_iceberg_table REFRESH;

ALTER ICEBERG TABLE my_iceberg_table SET AUTO_REFRESH = TRUE;

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

检索存储指标¶

Snowflake 不会向您的账户收取 Iceberg 表存储费用。但是，您可以通过 Snowflake Information Schema 或 Account Usage 架构中的 TABLE_STORAGE_METRICS 和 TABLES 视图进行查询，以跟踪 Iceberg 表占用了多少存储空间。

下面的示例查询连接了 ACCOUNT_USAGE.TABLE_STORAGE_METRICS 视图和 ACCOUNT_USAGE.TABLES 视图，并根据 TABLES.IS_ICEBERG 列进行筛选。

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';

Set data compaction¶

You can set data compaction on Snowflake-managed Iceberg tables when you create a database, schema, or table, or run the ALTER command to change the setting for an existing database, schema, or table. You can also set data compaction at the account level by using the ALTER ACCOUNT command. For more information about data compaction, see Data compaction.

The following example uses ENABLE_DATA_COMPACTION to disable data compaction for a Snowflake-managed table:

CREATE ICEBERG TABLE my_iceberg_table (col1 INT)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'my_iceberg_table'
  ENABLE_DATA_COMPACTION = FALSE;

Alternatively, use ALTER ICEBERG TABLE to disable it for an existing table.

ALTER ICEBERG TABLE my_iceberg_table
  SET ENABLE_DATA_COMPACTION = FALSE;

For more information, see:

• ENABLE_DATA_COMPACTION

• ALTER ACCOUNT

• CREATE DATABASE

• ALTER DATABASE

• CREATE SCHEMA

• ALTER SCHEMA

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

• ALTER ICEBERG TABLE