BigQueryのMaterialized Viewにパイプラインを移行したかった【株式会社ライトコード】

はじめに

所属しているプロジェクトにて、稼働中の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式によって操作を行っているためサポート外となりました。

WITH

  plays AS (

    SELECT

      common.user_id AS user_id,

      play_time_jst

    FROM

      hoge_quest.story_mode_play_log

    UNION ALL

    SELECT

      common.user_id AS user_id,

      play_time_jst

    FROM

      hoge_quest.pvp_mode_play_log

    UNION ALL

    SELECT

      common.user_id AS user_id,

      play_time_jst

    FROM

      hoge_quest.pve_mode_play_log

  ),

  daily_plays AS (

    SELECT

      user_id,

      DATE(play_time_jst) AS `date`,

      COUNT(user_id) AS play_cnt

    FROM

      plays

    GROUP BY

      user_id,

      `date`

  )

SELECT

  user_id,

  `date`,

  play_cnt,

  CASE

    WHEN play_cnt >= 5 THEN 1

    ELSE 0

  END AS over_5_play_flg,

  CASE

    WHEN play_cnt >= 10 THEN 1

    ELSE 0

  END AS over_10_play_flg

FROM

  daily_plays

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

…

記事の続きは下のURLをクリック！

https://rightcode.co.jp/blogs/48689

エンジニア積極採用中です！

現在、WEBエンジニア、モバイルエンジニア、デザイナー、営業などを積極採用中です！

採用ページはこちら：https://rightcode.co.jp/recruit

社員の声や社風などを知りたい方はこちら：https://rightcode.co.jp/blogs?category=life

社長と一杯飲みながらお話しませんか？（転職者向け）

特設ページはこちら： https://rightcode.co.jp/gohan-sake-president-talk

もっとワクワクしたいあなたへ

現在、ライトコードでは「WEBエンジニア」「モバイルエンジニア」「ゲームエンジニア」、「デザイナー」「WEBディレクター」「営業」などを積極採用中です！

ライトコードは技術力に定評のある受託開発をメインにしているIT企業です。

有名WEBサービスやアプリの受託開発などの企画、開発案件が目白押しの状況です。

• もっと大きなことに挑戦したい！
• エンジニアとしてもっと成長したい！
• モダンな技術に触れたい！

現状に満足していない方は、まずは、エンジニアとしても第一線を走り続ける弊社代表と気軽にお話してみませんか？

ネット上では、ちょっとユルそうな会社に感じると思いますが（笑）、
実は技術力に定評があり、沢山の実績を残している会社ということをお伝えしたいと思っております。

• ライトコードの魅力を知っていただきたい！
• 社風や文化なども知っていただきたい！
• 技術に対して熱意のある方に入社していただきたい！

一度、【Wantedly内の弊社ページ】や【コーポレートサイト】をのぞいてみてください。

【コーポレートサイト】https://rightcode.co.jp/

【採用募集】https://rightcode.co.jp/recruit（こちらからの応募がスムーズ）

【wantedlyぺージ】https://www.wantedly.com/companies/rightcode