[Snowflake] Schema Evolutionでテーブルのスキーマ追従を試してみる
はじめに
私が所属しているライフイズテックのデータ基盤グループでは、ELT処理の工数削減ができないかというのを現在調査・検討している中で、スキーマ検出に続いてスキーマ進化について検証してみました。
スキーマ進化について
現在パブリックプレビューで公開されている機能で、ドキュメントによると
半構造化データは、時間の経過とともに進化する傾向があります。データを生成するシステムは、追加された情報に対応するために新しい列を追加し、それに応じて下流のテーブルを進化させる必要があります。
Snowflakeのテーブルの構造は、データソースから受け取った新しいデータの構造をサポートするために、自動的に進化させることができます。Snowflakeは、次をサポートしています。
・新しい列の自動追加。
・新しいデータファイルで欠落している列からの NOT NULL 制約の自動削除。
ENABLE_SCHEMA_EVOLUTIONというパラメータを設定することで、
- カラムが増えたら自動追加
- データロード時にカラムがなくなったら対象のカラムのデータをnullにしてロード
ということが可能になります。
スキーマ進化を実際に試してみる
スキーマ検出の記事の続きとして、ファイルフォーマットや実データなどは同じものを使います。
スキーマ進化を有効にして、テーブルを作成する
今回は新規でテーブルを作成しデータのロードまでを一度に行うため、create時点で、ENABLE_SCHEMA_EVOLUTIONを有効にします。
すでに存在しているテーブルに対しては、ALTER TABLEで有効にします。
テーブル作成時に使うcsv(users.csv)
| id | name | birth_date | followers_count | following_count | email_verified | created_at | |
|---|---|---|---|---|---|---|---|
| 1 | 山田太郎 | yamada@example.com | 1990-01-01 | 100 | 50 | TRUE | 2022-01-10 14:30:00+09 |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | FALSE | 2022-03-05 09:15:00+09 |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | TRUE | 2021-12-20 16:42:00+09 |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | FALSE | 2023-01-01 12:00:00+09 |
create or replace table trial.public.users
enable_schema_evolution = true
using template (
select
array_agg(object_construct(*)) within group (order by order_id)
from
table(
infer_schema(
location=>'@trial.public.csv_import',
files=>'users.csv',
file_format=>'trial.public.csv_format',
ignore_case=>true
)
)
)
;
作成されたテーブルの構造をDESCRIBEで出力した結果が下記になります。
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|---|---|---|---|---|---|---|---|---|---|---|
| ID | NUMBER(1,0) | COLUMN | Y | N | N | |||||
| NAME | VARCHAR(16777216) | COLUMN | Y | N | N | |||||
| VARCHAR(16777216) | COLUMN | Y | N | N | ||||||
| BIRTH_DATE | DATE | COLUMN | Y | N | N | |||||
| FOLLOWERS_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| FOLLOWING_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| EMAIL_VERIFIED | BOOLEAN | COLUMN | Y | N | N | |||||
| CREATED_AT | TIMESTAMP_NTZ(9) | COLUMN | Y | N | N |
列の増加を試す
NOTIFICATIONS_ENABLEDやUPDATED_ATといった列を追加したCSVをロードしてみます。
今回はヘッダー付きのCSVにしており、PARSE_HEADERとMATCH_BY_COLUMN_NAMEを使用しているため、ERROR_ON_COLUMN_COUNT_MISMATCHをFALSEにします。
copy into trial.public.users
from '@trial.public.csv_import'
files = ('users_v2.csv')
file_format= (
format_name='trial.public.csv_format',
error_on_column_count_mismatch=false
)
match_by_column_name='case_insensitive'
;
今回ロードするいくつかの列を追加したCSV(users_v2.csv)
| id | name | birth_date | followers_count | following_count | email_verified | notifications_enabled | created_at | updated_at | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 山田太郎 | yamada@example.com | 1990-01-01 | 100 | 50 | TRUE | TRUE | 2022-01-10 14:30:00+09 | 2022-01-10 14:30:00+09 |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | FALSE | TRUE | 2022-03-05 09:15:00+09 | 2022-03-05 09:15:00+09 |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | TRUE | FALSE | 2021-12-20 16:42:00+09 | 2021-12-20 16:42:00+09 |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | FALSE | FALSE | 2023-01-01 12:00:00+09 | 2023-01-01 12:00:00+09 |
COPY INTOでデータがロードされたあとにテーブルをDESCRIBEした結果がこちらです。
データがロードされた上でNOTIFICATIONS_ENABLEDやUPDATED_ATといったCSVに追加されたカラムが追加されているのが確認できます。
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|---|---|---|---|---|---|---|---|---|---|---|
| ID | NUMBER(1,0) | COLUMN | Y | N | N | |||||
| NAME | VARCHAR(16777216) | COLUMN | Y | N | N | |||||
| VARCHAR(16777216) | COLUMN | Y | N | N | ||||||
| BIRTH_DATE | DATE | COLUMN | Y | N | N | |||||
| FOLLOWERS_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| FOLLOWING_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| EMAIL_VERIFIED | BOOLEAN | COLUMN | Y | N | N | |||||
| CREATED_AT | TIMESTAMP_NTZ(9) | COLUMN | Y | N | N | |||||
| NOTIFICATIONS_ENABLED | BOOLEAN | COLUMN | Y | N | N | |||||
| UPDATED_AT | TIMESTAMP_NTZ(9) | COLUMN | Y | N | N |
ロード後のテーブル
| ID | NAME | BIRTH_DATE | FOLLOWERS_COUNT | FOLLOWING_COUNT | EMAIL_VERIFIED | CREATED_AT | NOTIFICATIONS_ENABLED | UPDATED_AT | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 山田太郎 | yamada@example.com | 1990-01-01 | 100 | 50 | TRUE | 2022-01-10 14:30:00.000 | TRUE | 2022-01-10 14:30:00.000 |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | FALSE | 2022-03-05 09:15:00.000 | TRUE | 2022-03-05 09:15:00.000 |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | TRUE | 2021-12-20 16:42:00.000 | FALSE | 2021-12-20 16:42:00.000 |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | FALSE | 2023-01-01 12:00:00.000 | FALSE | 2023-01-01 12:00:00.000 |
列の減少を試す
今回ロードするEMAIL_VERIFIEDの列を減らしたCSV(users_v3.csv)
| id | name | birth_date | followers_count | following_count | notifications_enabled | created_at | updated_at | |
|---|---|---|---|---|---|---|---|---|
| 1 | yamada@example.com | 1990-01-01 | 100 | 50 | TRUE | 2022-01-10 14:30:00+09 | 2022-01-10 14:30:00+09 | |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | TRUE | 2022-03-05 09:15:00+09 | 2022-03-05 09:15:00+09 |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | FALSE | 2021-12-20 16:42:00+09 | 2021-12-20 16:42:00+09 |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | FALSE | 2023-01-01 12:00:00+09 | 2023-01-01 12:00:00+09 |
| 5 | 中村悠 | nakamura@example.jp | 1995-05-25 | 300 | 100 | FALSE | 2022-05-10 11:20:00+09 | 2022-05-10 11:20:00+09 |
また先程のデータに追加して、COPY INTOでデータをロードした際のテーブルをDESCRIBEした結果がこちらです。
元のCSVから列が減少しているにも関わらず、列は維持されているのがわかります。
またデータの結果から、今回ロードしたデータについては欠損したカラム(EMAIL_VERIFIED)のデータがnullとしてロードされていることもわかります。
| name | type | kind | null? | default | primary key | unique key | check | expression | comment | policy name |
|---|---|---|---|---|---|---|---|---|---|---|
| ID | NUMBER(1,0) | COLUMN | Y | N | N | |||||
| NAME | VARCHAR(16777216) | COLUMN | Y | N | N | |||||
| VARCHAR(16777216) | COLUMN | Y | N | N | ||||||
| BIRTH_DATE | DATE | COLUMN | Y | N | N | |||||
| FOLLOWERS_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| FOLLOWING_COUNT | NUMBER(3,0) | COLUMN | Y | N | N | |||||
| EMAIL_VERIFIED | BOOLEAN | COLUMN | Y | N | N | |||||
| CREATED_AT | TIMESTAMP_NTZ(9) | COLUMN | Y | N | N | |||||
| NOTIFICATIONS_ENABLED | BOOLEAN | COLUMN | Y | N | N | |||||
| UPDATED_AT | TIMESTAMP_NTZ(9) | COLUMN | Y | N | N |
データをロードしたテーブル内容
| ID | NAME | BIRTH_DATE | FOLLOWERS_COUNT | FOLLOWING_COUNT | EMAIL_VERIFIED | CREATED_AT | NOTIFICATIONS_ENABLED | UPDATED_AT | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 山田太郎 | yamada@example.com | 1990-01-01 | 100 | 50 | TRUE | 2022-01-10 14:30:00.000 | TRUE | 2022-01-10 14:30:00.000 |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | FALSE | 2022-03-05 09:15:00.000 | TRUE | 2022-03-05 09:15:00.000 |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | TRUE | 2021-12-20 16:42:00.000 | FALSE | 2021-12-20 16:42:00.000 |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | FALSE | 2023-01-01 12:00:00.000 | FALSE | 2023-01-01 12:00:00.000 |
| 1 | 山田太郎 | yamada@example.com | 1990-01-01 | 100 | 50 | 2022-01-10 14:30:00.000 | TRUE | 2022-01-10 14:30:00.000 | |
| 2 | 鈴木花子 | suzuki@example.com | 1992-04-15 | 250 | 300 | 2022-03-05 09:15:00.000 | TRUE | 2022-03-05 09:15:00.000 | |
| 3 | 佐藤健太 | sato@example.org | 1985-07-30 | 50 | 20 | 2021-12-20 16:42:00.000 | FALSE | 2021-12-20 16:42:00.000 | |
| 4 | 田中美咲 | tanaka@example.net | 1998-02-12 | 400 | 150 | 2023-01-01 12:00:00.000 | FALSE | 2023-01-01 12:00:00.000 | |
| 5 | 中村悠 | nakamura@example.jp | 1995-05-25 | 300 | 100 | 2022-05-10 11:20:00.000 | FALSE | 2022-05-10 11:20:00.000 |
まとめ
スキーマ進化を使って、テーブルのスキーマを手動でメンテナンスすることなくデータに追従されること、またデータをロードすることが可能です。
一方で、スキーマが変更された場合やデータがNULLで入ってきた場合に検知する方法は用意されていないため、検知方法は別で検討する必要があります。
宣伝
ライフイズテック サービス開発部では、気軽にご参加いただけるカジュアルなイベントを実施しております。
イベントの開催予定は connpass のグループ にてご案内しておりますので、興味をお持ちいただけたましたら、ぜひ、ご参加いただけると嬉しいです！
Discussion