プログラマのはしくれダイアリー: Snowflake

SnowflakeのTransform機能はどの程度か調査したい

ETL、ELTなどといったときの文脈のTransformだが一体Snowflake側でどれほどの機能を提供しているのだろうか。

「データウェアハウスだけどTransformもちょこっと出来るよ」なのか「データウェアハウスだけじゃない。ETLすべてお任せあれ」なのか。

前提

Transformの定義はwikipediaのTransformの項に委ねる
https://ja.m.wikipedia.org/wiki/Extract/Transform/Load

Snowflakeで使える手札

Transform関数 https://docs.snowflake.com/en/sql-reference/functions/transform
MERGE https://docs.snowflake.com/en/sql-reference/sql/merge
UDF（ユーザー定義関数） https://docs.snowflake.com/ja/developer-guide/udf/udf-overview
Snowpark https://www.snowflake.com/en/data-cloud/snowpark/
Snowflake Script https://docs.snowflake.com/en/developer-guide/snowflake-scripting/use-cases

TaskとStreamを組み合わせ、変更差分をUDFでTransform

Snowflakeでよく見る、Streamで差分を確認し、Taskで定期実行し差分をTransformするやり方。この記事を書いた人が特徴的なのはUDF（ユーザー定義関数）をゴリゴリ使ってるところ https://dev.to/rnubel/transform-data-in-snowflake-with-streams-tasks-and-python-l71

Transformはdbtにすべて託すパターン

ExtractやLoadの部分はSnowflakeに任せTransformはdbtだ！という記事も見るそういう選択肢を取る人も居るらしい https://zenn.dev/dataheroes/articles/bcced35e8156ba

この記事内にも書いてあったが、Snowflakeで完結するメリットもあるといえばありそう

コストやテーブルの変更容易性
dbtの学習コスト、導入コストとのトレードオフ

Snowflakeをデータウェアハウスとして扱いTransformは各プロダクトでdbtで賄うパターン

Snowflakeのテーブルは使うのだが、Transformはdbtでやっているぽい。各プロダクトのチームがそれぞれ要件に合わせてdbtモデルを作ることでTransformする。プロダクト横断でdbtを使うぞ！となってる場合これで良さそう

https://techblog.cartaholdings.co.jp/entry/snowflake-data-platform-vision

その他参考

https://www.snowflake.com/guides/modern-etl-process/?amp https://www.snowflake.com/ja/guides/etl-vs-elt/?amp https://zenn.dev/circle_yukiyama/books/snowflake-zero2prod

まとめ

Transformationに絡みそうなSnowflakeの手札を調べ、世の中でどんなふうに使われているか色々ネット記事を漁ってみた。できることにはある程度限りはあるものの、 SnowpipeとTaskを組み合わせて流れを組めばできることは色々ありそう。あと、TransformなのかLoadなのか微妙な位置な気がするけど Dynamic Table (https://docs.snowflake.com/en/user-guide/dynamic-tables-about)も良さそう。結論、このSnowflakeで組めるジョブフローや機能の範囲であればSnowflakeを使う。そうすると、Snowflakeの特徴である計算リソースがうまく並列で使えることやRole定義などの恩恵が受けれそう。 Snowflakeの提供する機能でユースケースが満たせなさそうであれば、DigdagやAirflowなどのワークフローエンジンやdbtとかでゴニョゴニョする感じだろうか

Snowpipeとは

https://docs.snowflake.com/ja/user-guide/data-load-snowpipe-intro

COPYコマンドとかとは異なり、ファイル単位で小さい粒度のロードが出来るということらしい。

用語の整理

Snowpipe→機能名
パイプ→名前付きファーストクラスSnowflakeオブジェクト
Snowflakeテーブル→Snowflakeの世界のテーブル
内部ステージ→Snowflakeのテーブルとか、名前付きステージ
外部ステージ→S3とかのファイルの置き場所のこととか
半構造化データ→JSONとかAVROとか

使い方

パイプを作ることでなんやかんやするらしい

CREATE PIPE ALTER PIPE DROP PIPE DESCRIBE PIPE SHOW PIPES

一旦CREATE PIPEさえ理解すれば、使い始められそうではある。

基本、RDBでいうところのTABLEがPIPEに置き換わった構文のようだ。

CREATE PIPE

https://docs.snowflake.com/ja/sql-reference/sql/create-pipe

こう書くらしい。

create pipe mypipe_s3
  auto_ingest = true
  aws_sns_topic = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket'
  as
  copy into snowpipe_db.public.mytable
  from @snowpipe_db.public.mystage
  file_format = (type = 'JSON');

このように、ステージからsnowflakeテーブルへと、データを流し込むことになる。
データがS3にputされる→SNS経由でSQSにキュー送信→Snowflake側でサブスクライブ→PIPEの定義通り、ステージからテーブルにCOPYという流れが組まれるということらしい。

ややこしいのは、Snoflake側がSQSを作成、管理しており、S3側がその送信先を知っていないといけないこと。

このあたり、日本語ドキュメントがよく分からなかったので結局英語の公式ドキュメントを読むことになった

英語版 https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3

日本語版 https://docs.snowflake.com/ja/user-guide/data-load-snowpipe-auto-s3

差分ロードなどの仕組み

上記のようなイベント通知を使った自動ロードを使えば、差分を日時で取り込むワークフローなども作りやすそうである。
S3以外にもCloud StorageやAzure Blobなどメインどころのクラウドのストレージサービスに対応してる。

全量をドカンとロードしたあと、日時データをS3にアップロードし、毎日S3に1日分をputしてサブスクライブ→PIPEで差分をロードとか出来そう。

あとは、REST APIも用意されてるらしいので
それでよしなに自分でロードするためのバッチでも作るのも出来そう。

取り込む内容についてはfile formatが対応していればゴニョゴニョ出来そう。
例えば「csvファイルの特定ファイルだけSELECTしてCOPYする」など

https://docs.snowflake.com/en/user-guide/querying-stage

まとめ

SnowpipeでS3からSnowflakeへのファイル単位のロードが出来る
PIPEのDDLでSnowpipeを構築できる（CREATE PIPEなど）
SQSのサブスクライブかRESTのAPIコールによってPIPEを動かすことが出来る
ロードの単位はファイルっぽい。ファイルある程度SELECT文で条件つけて取り込むことも出来るが、あまり難しいことは出来ない（COPY構文の範囲内のことしか出来ない）

プログラマのはしくれダイアリー

cssmenu

2024/08/19

Snowflakeで実現できるTransformとは何か？調べてみた