자동 테이블 스키마 진화 활성화

자동 테이블 스키마 진화 활성화

반구조적 데이터는 시간이 지나면서 진화하는 경향이 있어요. 데이터를 생성하는 시스템은 추가 정보를 수용하기 위해 새 컬럼을 추가하며, 이에 따라 다운스트림 테이블도 그에 맞게 진화해야 해요.

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로 설정해야 해요.

스키마 진화는 독립형 기능이지만, 클라우드 스토리지의 파일 집합에서 컬럼 정의를 검색하는 스키마 감지 지원과 함께 사용할 수 있어요. 이 기능들을 결합하면 연속 데이터 파이프라인이 클라우드 스토리지의 데이터 파일 집합에서 새 테이블을 만들고, 새 소스 데이터 파일의 스키마가 컬럼 추가나 삭제로 진화함에 따라 테이블의 컬럼을 수정할 수 있어요.

출처: Documentation

본문

사용 참고 사항

• 이 기능은 Apache Avro, Apache Parquet, CSV, JSON, ORC 파일을 지원해요.
• 이 기능은 COPY INTO <table> 문과 Snowpipe 데이터 로드로 제한돼요. INSERT 작업은 대상 테이블 스키마를 자동으로 진화시킬 수 없어요.
• 고성능 아키텍처의 Snowpipe Streaming은 표준 Snowflake 테이블과 Snowflake 관리 Iceberg 테이블에 대한 스키마 진화를 지원해요. 관리형 Iceberg 지원은 지원되는 데이터 유형의 새 최상위 컬럼 추가로 제한돼요. 자세한 내용은 테이블 지원 및 스키마를 참고해요. Snowpipe Streaming Classic과 함께하는 Kafka 커넥터도 스키마 감지 및 진화를 지원해요.
• 기본적으로 이 기능은 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 주제의 예제를 바탕으로 해요.

-- 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 INTO <table>