Enable automatic table schema evolution¶

使用说明¶

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

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

• 架构演化不支持直接使用 Snowflake Ingest :doc:` 的 </user-guide/snowpipe-streaming/data-load-snowpipe-streaming-overview>Snowpipe Streaming SDK 数据加载。:doc:`具有 Snowpipe Streaming 的 Kafka Connector </user-guide/snowpipe-streaming/snowpipe-streaming-classic-kafka-schema-detection> 支持架构检测和演化。

• 默认情况下，此功能在每个 COPY 操作中最多只能添加 100 列，或只能使 1 个架构演化。要请求在每个 COPY 操作中添加超过 100 列或使多于 1 个的架构演化，请联系 Snowflake 支持部门。

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

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

  然而，对于带有 Snowpipe Streaming 的 Kafka 连接器，架构演化不会被 SchemaEvolutionRecord 输出跟踪。SchemaEvolutionRecord 输出始终显示 NULL。

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

• 任务 不支持架构演化。

架构演化支持：引入方法比较¶

特定的元数据字段 SchemaEvolutionRecord 用于跟踪架构演化。您可以使用 INFORMATION_SCHEMA.COLUMNS 视图、DESCRIBE TABLE 命令 以及 SHOW COLUMNS 命令 查看此字段。

下表总结了不同 Snowflake 引入方法的架构演变支持及相应的 SchemaEvolutionRecord 跟踪行为：

示例¶

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

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

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

CopyExpand

-- 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.

Show lessSee more

Scroll to top