はじめに

所属しているプロジェクトにて、稼働中のETLパイプラインの一部をマテリアライズドビューに置き換えることでコストの削減ができないか検討を行いました。
現在はAirflowにてBigqueryInsertJobOperatorを使用してSQLを実行するパイプラインが存在しますが、増分更新にするにはひと手間かかるためほとんどが全件更新で実行されています。
比較的シンプルなデータの正規化・標準化を行っているレイヤーのクエリをマテリアライズドビューに置き換えることができないかを試みました。
結果として移行は断念したのですが、そこまでに行った調査・検討を書いていきます。
移行対象のデータセットはオンラインゲーム各タイトルのプレイログや課金履歴で、それらを共通のKPI分析に利用するために正規化・標準化を行っているレイヤーです。

メリット

マテリアライズドビューを導入することで得られる利点として、以下の点が挙げられます。

1. 増分更新の構築が楽

他のツールを使用して増分更新を構築しようとすると、元データの取得期間の調整や更新の際のマージ処理の設定などを行う必要があります。マテリアライズドビューで行う場合はクエリに制限こそあるものの、通常のテーブルを作成するSQLがそのまま使用でき、それらの作業はBigQueryに任せることができます。

2. 更新スケジュールの管理が不要

Matarialized Viewに対するクエリはベーステーブルへのクエリと常に一致するため、テーブルを更新する頻度やタイミングを考慮する必要がありません。
"他のテーブルと更新タイミングが合わないので新しくスケジュールを設定して～"ということも起きません。
ただし、マテリアライズドビュー自体の更新設定は作成時に考える必要があるかもしれません。特にOPTIONを記述しない場合、デフォルト値は自動更新が有効で30分毎となっています。

デメリット

しかし実際に使用しようとしたところ、以下の理由で現在のプロジェクトにはマッチせず、移行を断念することになりました。

1. 対応クエリの制限

現在プレビュー版でUNION ALLとLEFT JOIN がサポートされているため、かなりできることが増えました。とはいえクエリの制限はまだまだ多く、集計値に基づく計算、フィルタリングは行えないことに注意が必要です。
例えば移行対象に以下のようなクエリが存在するのですが、対象外になります。ほげクエストというゲームの内、3つのモードのプレイログをUNION ALLして、ユーザーが1日に何回モードを遊んだかを集計しています。
daily_playsまではサポートしていますが、最後に集計値に対してcase式によって操作を行っているためサポート外となりました。

1WITH
2  plays AS (
3    SELECT
4      common.user_id AS user_id,
5      play_time_jst
6    FROM
7      hoge_quest.story_mode_play_log
8    UNION ALL
9    SELECT
10      common.user_id AS user_id,
11      play_time_jst
12    FROM
13      hoge_quest.pvp_mode_play_log
14    UNION ALL
15    SELECT
16      common.user_id AS user_id,
17      play_time_jst
18    FROM
19      hoge_quest.pve_mode_play_log
20  ),
21  daily_plays AS (
22    SELECT
23      user_id,
24      DATE(play_time_jst) AS `date`,
25      COUNT(user_id) AS play_cnt
26    FROM
27      plays
28    GROUP BY
29      user_id,
30      `date`
31  )
32SELECT
33  user_id,
34  `date`,
35  play_cnt,
36  CASE
37    WHEN play_cnt >= 5 THEN 1
38    ELSE 0
39  END AS over_5_play_flg,
40  CASE
41    WHEN play_cnt >= 10 THEN 1
42    ELSE 0
43  END AS over_10_play_flg
44FROM
45  daily_plays

2. ワイルドカードテーブルの利用不可

BigQueryでは日付別にシャーディングされたテーブル群に対してワイルドカードテーブルを使用してクエリを行えますが、マテリアライズドビューではサポートされていません。
日付別テーブルはもちろん現在は推奨されていませんが、プロジェクトでは現在も古いゲームタイトルのデータセットなどで使用されています。
ここで、タイトル単位で脱落するものが発生してしましました。

3. パーティショニングの要件

ベーステーブルがパーティション分割されている場合は、マテリアライズドビューも同じパーティショニング列でパーティション分割を行うことができます。
ただし、時間ベースの場合の粒度は一致している必要があります。今回移行対象のレイヤーではタイムゾーンがUTCで記録されたログを、以後の集計のためにJSTに変換して扱うようにしていました。
ここでパーティション列にcreated_at(UTCのタイムスタンプ)を使っていて、TIMESTAMP_ADD(`created_at`, INTERVAL 9 HOUR) AS access_time_jst としてしまうと、マテリアライズドビューではパーティション分割ができなくなります。
以下のようなコードが移行対象にあるのですが、これはパーティションを維持したまま移行することはできません（パーティション分割をしない場合は実行できますが、クエリを叩いた際のスキャン量が増えてしまい元も子もないですよね……）

1-- ベーステーブルはcreated_at で日付別にパーティションされている
2CREATE MATERIALIZED VIEW dataset_phase01.hoge_quest_access
3PARTITION BY
4  TIMESTAMP_TRUNC(access_time_jst, DAY) AS -- 粒度自体は同じ"日付"だが、時間が加工されているのでNG
5SELECT
6  common.user_id AS user_id,
7  TIMESTAMP_ADD(created_at, INTERVAL 9 HOUR) AS access_time_jst
8FROM
9  hoge_quest.access_log