Snowflakeを使って高速に差分更新するようなデータ基盤を作る

はじめに

この記事は ちゅらデータ Advent Calendar 2021 1日目の記事となります。

TL;DR

DWHに対して頻繁に更新をかけない（1日1回程度くらい）であれば、この構成で速度面はかなり改善されるのでおすすめです。
（もっといい改善方法があればコメントお待ちしてます）

背景

Snowflakeにはテーブルの他にビューというものが存在しており、ビューを使用することでクエリの結果にテーブルのようにアクセスできます。つまり加工した結果をテーブルとして扱うことができるので、常に最新のデータを取得できるという所がポイントになります。

BIツールで可視化を実施する際にはBIツールによる自動クエリ生成が行われるので、どういうことが起こるかというとBIツールが最新データを取得しようとしてビューにアクセスしてしまうため、都度分析用DWHもデータマートにクエリを投げる状態ができてしまい、結果として1つのダッシュボードにアクセスするだけでも数十秒以上かかってしまうという問題が発生しています。これはいけませんね。

どうやって解決したか

そこで今回のようなデータ基盤に至るわけです。一度ビューを挟むことによって常に最新のDWHを作成し、差分更新のためのDWHを別途作ることで最新の状態を保ったテーブルを作ります。差分更新DWHはSnowflakeのTASK機能を使うことで定期実行を設定することができるので、これにより分析用DWHと差分更新DWHの内容を同等に保ちます。なのでBIツールが自動クエリをつくって投げることになってもアクセス先はビューではなくテーブルなので、必要なアクセスだけですむという寸法です。すばら。

実際に組んで見る

DWHの作成

ここで作るビューについてはFDM(ファクトディメンジョンマトリクス)に基づいて、必要なファクトテーブルとディメンションテーブルをそれぞれ作成します。（今回はFDMに基づくほうが汎用的に扱えることと、様々なダッシュボードが作れるよねという背景があります。これでなくてもOKです。）

-- 分析用データウェアハウスを作る
CREATE VIEW analytics_hoge_view AS
WITH fuga AS (
...
)
SELECT
  ...
FROM
  ...
INNER JOIN
  ...

-- 初回のみビューの中身を複製する
CREATE TABLE update_hoge_tbl AS (
    SELECT * FROM analytics_hoge_view
);

定期実行タスクを作成する

ここで定期実行するためにSnowflakeのTASK機能を利用してコードを書きます。

create task UPDATE_hoge_task
  warehouse = warehouse_name
  schedule='USING CRON 0 4 * * * Asia/Tokyo'
AS
merge into update_hoge_tbl AS T
USING analytics_hoge_view AS V
ON T.ID=V.ID
when matched then
  update set T.ID=V.ID
            ,...
when not matched then 
insert (ID
        ,...
) 
values (V.ID
        ,...
);

解説

初めの CREATE TASK 文で定期実行するタスクの基本設定をします。スケジュールについてはCRON形式で記載できます。

create task UPDATE_hoge_task
  warehouse = warehouse_name
  schedule='USING CRON 0 4 * * * Asia/Tokyo'
AS

MERGE INTO 文を使うことで2つの対象テーブルのうち、条件に一致するレコード/しないレコードに対して実行するINSERT/DELETE/UPDATE操作を記載することができます。

matched が使えるためこれによって結合条件で分岐できるほか、AND/ORを利用することで、特定カラムを条件に加えることも可能です。またWHENで記載する条件は同じ条件で何段に書いても良いので、INSERT/DELETE/UPDATEの複数回操作もできます。

merge into update_hoge_tbl AS T
USING analytics_hoge_view AS V
ON T.ID=V.ID
when matched then
  update set T.ID=V.ID
            ,...
when not matched then 
insert (ID
        ,...
) 
values (V.ID
        ,...
);

定期実行タスクを登録する

タスクを作成しただけでは実際に動いてくれないので、実行するようにします。

ALTER TASK update_hoge_task RESUME;

登録できているかどうかは SHOW TASKS で確認することができます。STATE列が「started」 になっていればOK。

完成

更新頻度を増やしたければTASKのスケジュール頻度を変えてあげるだけでよいです。ただこの方法はDWHに対して頻繁に更新をかけない（1日1回程度くらい）場合に、この構成を取ることで速度面が改善されます。「俺は毎回アクセスするたびに最新の情報を得たいんだ！」みたいな要望がある場合には勧められないです。

まとめ

BIツールとSnowflakeを組み合わせるなら、ビューで最新データを持ちつつ差分を定期更新するテーブルに対してBIツールからクエリを投げることで速度向上が見込めます。ぜひ条件が合う方はお試しあれ。

ちゅらデータ Advent Calendar 2021 2日目 は @foursue さんによる 「いつものようにSnowflakeへのポエムを書きます」（※記事完成次第置き換え）です！お楽しみに！