表模式演化

半结构化数据往往会随着时间的推移而演化。生成数据的系统会添加新列以容纳更多信息，这就要求下游表也要相应地演化。

Snowflake 中的表结构可以自动演化，以支持从数据源接收的新数据结构。Snowflake 支持以下操作：

  • 自动添加新列。

  • 自动从新数据文件中缺少的列删除 NOT NULL 约束。

要启用表模式演化，请执行以下操作：

  • 如果要创建新表，请在使用 CREATE TABLE 命令时将 ENABLE_SCHEMA_EVOLUTION 参数设置为 TRUE。

  • 对于现有表，请使用 ALTER TABLE 命令修改表，并将 ENABLE_SCHEMA_EVOLUTION 参数设置为 TRUE。

当满足以下所有条件时，从文件加载数据会演化表列：

  • Snowflake 表的 ENABLE_SCHEMA_EVOLUTION 参数已设置为 TRUE。

  • COPY INTO <table> 语句使用 MATCH_BY_COLUMN_NAME 选项。

  • 用于加载数据的角色对表具有 EVOLVE SCHEMA 或 OWNERSHIP 权限。

此外，对于 CSV 的架构演化，当与 MATCH_BY_COLUMN_NAME 和 PARSE_HEADER 一起使用时，必须将 ERROR_ON_COLUMN_COUNT_MISMATCH 设置为 false。

架构演化虽是一项独立功能，但可以与架构检测支持结合使用，以从云存储中的一组文件中 检索列定义的。这些功能结合使用时，允许连续数据管道从云存储中的一组数据文件创建新表，然后，随着新源数据文件的架构演化（增加或删除列）修改表中的列。

使用说明

  • 此功能支持 Apache Avro、Apache Parquet、CSV、JSON 和 ORC 文件。

  • 此功能只能用于 COPY INTO <table> 语句和 Snowpipe 数据加载。INSERT 操作不能自动使目标表架构演化。

  • 架构演化不支持直接使用 Snowflake Ingest SDK 的 Snowpipe Streaming 数据加载。具有 Snowpipe Streaming 的 Kafka Connector 支持架构检测和演化。

  • 默认情况下，此功能在每个 COPY 操作中最多只能添加 10 列，或只能使 1 个架构演化。要请求在每个 COPY 操作中添加超过 10 列或使多于 1 个的架构演化，请联系 Snowflake 支持部门 (https://community.snowflake.com/s/article/How-To-Submit-a-Support-Case-in-Snowflake-Lodge)。

  • 删除 NOT NULL 列约束没有限制。

  • 架构演化通过以下视图和命令中的 SchemaEvolutionRecord 输出进行跟踪：INFORMATION_SCHEMA COLUMNS 视图、ACCOUNT_USAGE COLUMNS 视图、DESCRIBE TABLE 命令 以及 SHOW COLUMNS 命令。

  • 当在架构演化后手动重命名或修改列时，架构演化记录将被清除。

示例

下面的示例创建一个表，其列定义来自一组 Parquet 数据。如果为该表启用自动表模式演化，则进一步从包含其他名称/值对的 Parquet 文件加载数据会自动在该表中添加列：

请注意，语句中引用的 mystage 暂存区和 my_parquet_format 文件格式必须已存在。一组文件必须已暂存在暂存区定义中引用的云存储位置中。

本例以 INFER_SCHEMA 主题中的一个示例为基础：

-- Create table t1 in schema d1.s1, with the column definitions derived from the staged file1.parquet file.
USE SCHEMA d1.s1;

CREATE OR REPLACE TABLE t1
  USING TEMPLATE (
    SELECT ARRAY_AGG(object_construct(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/file1.parquet',
          FILE_FORMAT=>'my_parquet_format'
        )
      ));

-- Row data in file1.parquet.
+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
| a    | b    | c    |
+------+------+------+

-- Describe the table.
-- Note that column c2 is required in the Parquet file metadata. Therefore, the NOT NULL constraint is set for the column.
DESCRIBE TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------|
| COL1 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL2 | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
| COL3 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+

-- Use the SECURITYADMIN role or another role that has the global MANAGE GRANTS privilege.
-- Grant the EVOLVE SCHEMA privilege to any other roles that could insert data and evolve table schema in addition to the table owner.

GRANT EVOLVE SCHEMA ON TABLE d1.s1.t1 TO ROLE r1;

-- Enable schema evolution on the table.
-- Note that the ENABLE_SCHEMA_EVOLUTION property can also be set at table creation with CREATE OR REPLACE TABLE
ALTER TABLE t1 SET ENABLE_SCHEMA_EVOLUTION = TRUE;

-- Load a new set of data into the table.
-- The new data drops the NOT NULL constraint on the col2 column.
-- The new data adds the new column col4.
COPY INTO t1
  FROM @mystage/file2.parquet
  FILE_FORMAT = (type=parquet)
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

-- Row data in file2.parquet.
+------+------+------+
| col1 | COL3 | COL4 |
|------+------+------|
| d    | e    | f    |
+------+------+------+

-- Describe the table.
DESCRIBE TABLE t1;
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | schema evolution record                                                                                                                                                                  |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| COL1 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL                                                                                                                                                                                     |
| COL2 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | {"evolutionType":"DROP_NOT_NULL","evolutionMode":"COPY","fileName":"file2.parquet","triggeringTime":"2024-03-15 23:52:59.514000000Z","queryId":"01b303b8-0808-c9ed-0000-0971491b5932"}   |
| COL3 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL                                                                                                                                                                                     |
| COL4 | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | {"evolutionType":"ADD_COLUMN","evolutionMode":"COPY","fileName":"file2.parquet","triggeringTime":"2024-03-15 23:52:59.514000000Z","queryId":"01b303b8-0808-c9ed-0000-0971491b5932"}      |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-- Note that since MATCH_BY_COLUMN_NAME is set as CASE_INSENSITIVE, all column names are retrieved as uppercase letters.
Copy
语言: 中文