🎃

[Snowflake] Schema Detectionを使ったテーブル作成を試す

2023/09/15に公開

はじめに

私が所属しているライフイズテックのデータ基盤グループでは、ELT処理の工数削減ができないかというのを現在調査・検討しています。
そんな中先週金曜日に参加したSnowflakeのDATA CLOUD WORLD TOURでAsk the expertでSnowflakeのテーブル定義の自動化ができないかという相談したところ、スキーマ検出(schema detection)とスキーマ進化(schema evolution)というデータ定義の検出と適用が使えるということがわかりました。ということで、さっそくスキーマ検出について試してみました。

スキーマ検出

半構造化データはINFER_SCHEMAというテーブル関数を使ってスキーマを検出することができます。
出力されるのは列名やデータ型、ファイルの列の順序などになります。
INFER_SCHEMAは、Apache Parquet、Apache Avro、 ORC、 JSON、 CSVをサポートしています。

今回はCSVファイルをサンプルとしてスキーマを検出し、検出したスキーマに従ってテーブルを作成するところまで試してみたいと思います。

元データの準備とステージング・ファイルフォーマットの作成

下記のユーザーテーブルをサンプルとして使い、スキーマ検出を試します。

id	name	email	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

まずは、Snowflake上にTRIALというデータベースとPUBLICスキーマ内に、CSV_IMPORTというステージを作成します。そしてステージ上に上記のデータをusers.csvというCSVファイルにして、ローカルからアップロードしておきます。スキーマ検出からテーブルを作成する際に、カラム名もCSVファイルをそのまま踏襲するために、CSVファイルはヘッダー行ありにします。

CSVファイルを検出するために、下記SQLでファイルフォーマットも作成します。(ファイルフォーマット名は今回は CSV_FORMAT)

create file format trial.public.csv_format
    type = csv
    parse_header = true;

ここまででスキーマ検出の準備が整ったので、スキーマ検出を実行していきます。

スキーマ検出の実行

スキーマ検出はINFER_SCHEMAという関数を用います。

select 
　　*
from
    table(
        infer_schema(
            location=>'@trial.public.csv_import',
            files=>'users.csv',
            file_format=>'trial.public.csv_format'
        )
    )
;

ステージやファイルフォーマット、ファイルを指定して実行することで下記の結果が出力されました。
TYPEについて求める精度や文字数については、データからの推測になるため完璧ではなかったり、タイムスタンプの設定はTIMEZONE_NTZとして取得されることなどは注意が必要です。

COLUMN_NAME	TYPE	NULLABLE	EXPRESSION	FILENAMES	ORDER_ID
id	NUMBER(1, 0)	TRUE	$1::NUMBER(1, 0)	users.csv	0
name	TEXT	TRUE	$2::TEXT	users.csv	1
email	TEXT	TRUE	$3::TEXT	users.csv	2
birth_date	DATE	TRUE	$4::DATE	users.csv	3
followers_count	NUMBER(3, 0)	TRUE	$5::NUMBER(3, 0)	users.csv	4
following_count	NUMBER(3, 0)	TRUE	$6::NUMBER(3, 0)	users.csv	5
email_verified	BOOLEAN	TRUE	$7::BOOLEAN	users.csv	6
created_at	TIMESTAMP_NTZ	TRUE	$8::TIMESTAMP_NTZ	users.csv	7

スキーマ検出を使ったテーブルの作成

CREATE TABLEでテーブルを作成する際に、USING TEMPALTE句でINFER_SCHEMAを呼び出すことで、スキーマ検出をベースに作成できます。
また、ENABLE_SCHEMA_EVOLUTIONをTRUEとして設定することで、スキーマ進化を有効にすることができます。

今回のサンプルで作成したcsvのヘッダーは小文字で列名を決めているため、IGNORE_CASEの設定が必要になります。デフォルトではFALSEになっているため、大文字・小文字を区別します。
IGNORE_CASEをデフォルトのままにした場合、 "id"のようにダブルクオーテーションで囲まれた小文字のカラム名になるため、テーブル作成時にはIGNORE_CASEをTRUEにしておきます。

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

作成されたテーブル

name	type	kind	null?	primary key	unique key
ID	NUMBER(1,0)	COLUMN	Y	N	N
NAME	VARCHAR(16777216)	COLUMN	Y	N	N
EMAIL	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

最後に

スキーマの定義がスキーマ検出により完全に手を加える必要がなくなるということはないですが、増加していくデータへの対応工数はかなり下がりそうな印象を受けました。
今回は長くなってしまったので、別記事でスキーマ進化(schema evolution)についてもまとめたいと思います。

宣伝

ライフイズテックサービス開発部では、月毎に気軽にご参加いただけるカジュアルなイベントを実施しています。開催予定のイベントは、 connpass のグループからご確認ください。興味のあるイベントがあったらぜひ参加登録をお願いいたします。皆さんのご参加をお待ちしています！