データ基盤の処理最適化によるBigQueryコスト削減

こんにちは。データエンジニアの谷元です。
この記事は Enigmo Advent Calendar 2022 の21日目の記事です。

目次

はじめに

皆さんどのようにお過ごしでしょうか。

私は昨年に猫ちゃんの可愛さに目覚めてしまい、リモートワーク中も近くで愛猫に見守られる生活を過ごしております。 今年、ねこ検定初級に合格し、愛猫と仲良くなれた気がします🐱

さて、データ基盤の開発運用保守やBI上でのデータ整備などを対応をしてまして、
今回は "データ基盤の処理最適化によるBigQueryコスト削減" の話をさせていただきたいと思います。

どうしてデータ基盤を最適化する必要があるの?

エニグモは全社的にデータを活用する文化が強く、誰でもデータ活用がしやすいように様々なデータをBigQuery(以下、BQ)に自動集約しております。

規模でいうとデータ基盤上のクエリスキャン量はペタバイト[PB]規模/月であり、
前年同月比 約300%での増加傾向にあります。

しかし、データ活用が進むにつれて下記の課題を感じていました。

  • 昨今の円安事情も重なり、BQクエリコストが大幅に増加傾向にある
  • データ参照が遅く、シームレスに分析やドリルダウンがしづらい (BIやアドホッククエリ)
    • 思考の妨げにつながってしまい、分析機会の損失につながっていると感じる
  • 連携元で履歴が残っていないテーブルの場合、複数のテーブルを用いた過去のとある時点のデータが遡りにくい

さらに下記の現状もあり、データ基盤の最適化を進めようと思いました。

  • 基幹データでも数千万〜数億レコードあるテーブルが複数存在する
  • 現状の仕組みでは時間経過と共にBQクエリスキャン量も増加してしまう

どうしたら改善できるの?

現状のデータ基盤のおさらい

バッチ処理でBQ連携しており、データ鮮度は原則、最大1時間遅延です。

BQ連携時は差分更新分を連携し、それらのデータから重複や物理削除などを考慮したビュー(以下、最新ビュー)で連携元と同じ最新状態のデータを再現してます。

参考: Apache Airflow で実現するSQL ServerからBigQueryへのデータ同期

主要なBUYMA基幹データの最新ビューに着目してみる

汎用的に全ての最新ビューを最適化する方針にしました。理由は下記となります。

  • BQ連携している基幹データだけでも200テーブル以上あり、今後も含めてテーブルの利用状況はわからない
  • 現状のアクセス内容を調査して個々に性能改善するとなると
    • テーブル数や処理量も多い
    • 調査や対応方法を考えるのも大変だし、運用保守負担も大きそう
  • ビュー内部の改善なので、BQ利用者への影響を考慮する必要がなく、サイレントでリリースできるのも良さそう

最新ビューをどう変更するの?

最新ビューは差分更新データから再現していたため、不要なスキャンが多すぎる状態でした。

なので、基幹DB全体を定期的にテーブル化し、最新ビューでそのテーブルを利用してスキャン量を改善しようと考えました。

最新ビューの変更方針は以下のとおりです。

現状. 月次差分+時間次差分のBQデータを組み合わせる
提案. 日次全件(※)+時間次差分のBQデータを組み合わせる
     ※ 月次差分+時間次差分でテーブル化、AM0:00固定、以下、BQ履歴テーブルと呼ぶ

システム概要としてはどんな感じ?

図にすると下記のようになります。
尚、白色の箇所は既にあり、今回新規に対応したのは黄色の箇所になります。

データ基盤のシステム概要

この方針で思ったこと

  • リアルタイム連携が実現できた場合でもBQに入れる処理の置換で済み移行もスムーズそう
  • 基幹DB全体をBQ履歴テーブルとして残すことで、過去に遡ってデータ活用しやすそう
  • BQなのでデータ量をあまり気にしなくて良いメリットを活かせる

BQ履歴テーブルの作成方針だけど

  • 既にBQ上に取得済みの更新差分データを用いることで後からでも過去の任意時点まで遡ってデータを再現できるのも良さそう
  • 基幹DBが古いため、できるだけ影響を与えないようにしたかった

尚、BQのマテビューも少し検討したのですが現状では利用制約に引っかかり見送りました。

本当にその方法で改善するの?

仕組みからいうと性能は改善するとは思ってましたが、以下の懸念がありました。

  • 最新ビューがどの程度使われているのかがわからなかった
  • BQ履歴テーブル作成など、今回の対応により増加するBQクエリコストもある

運用保守する上で気になっていたこと

  • データ基盤の仕組みが現状より複雑になりトラブルシューティングが大変になる
  • BQ履歴テーブルが最新ビューに組み込まれるため、よりデータ品質に注意を払う必要あり
  • データ基盤の運用保守で新規テーブル取り込み時は一部手動運用している
    • 本対応後はオペレーションがより複雑化する
    • 手動スクリプト実行や設定ファイル修正、データの初期移行など
    • 愛猫の可愛い顔をみながらだとオペミスするかもしれない
  • 社内外でBQデータが既に多方面で活用されている
    • 対応後のデータ不備は影響が大きそう
    • データの復旧も大変そう

見込み効果はどうなの?

あまり時間をかけたくなかったのもあり、下記の確認に留めました。

  • 現状のBQクエリコスト確認
  • 主要テーブルを用いたSQL単体での効果検証

改善版の最新ビューを用いた効果検証では スキャン量と実行時間共に1/2程度削減 を確認できました。 1/5や1/8程度改善しているケースもあり想定より効果があるのかもと思いました。

尚、定期的なBQ利用状況の効果測定はLooker上のMarketplaceでBQ利用状況が簡単に見れたため、そちらを利用しました。慣れてる方でしたら1日で作成できるかと思います。

実装する上で意識したところ

実際やってみるとたくさんあった気がするのですが、パッと思い出した話をしてみます。

  • BQ履歴テーブル作成前提となるDAG依存関係
  • 本番データの確認期間をできるだけ長めにとろう
  • 手動スクリプトをAirflowに移植しようと思ったら
  • データ品質担保はどうしよっかな

BQ履歴テーブル作成前提となるDAG依存関係

BQ履歴テーブルを作成開始するにあたり、 その抽出するクエリ内部で参照されているテーブルで必要な情報が揃っている必要があります。

最新ビューは時間軸の異なるデータを複数組み合わせて物理削除も考慮してるため、テーブル毎に依存先DAGや時間軸、依存先のテーブル数も異なります。
これらの情報を設定情報として事前に定義した情報から自動で構成しています。

図にすると下記となります。
左がExternalTaskSensor、右がBigQueryExecuteQueryOperatorとなり、
右の数だけテーブルがあるようなイメージです。

参考: DAG間の依存の話

DAG依存関係

本番データを使った確認期間をできるだけ長めにとろう

本番データでの確認期間を少しでも長めに取るため、 本番影響がないよう部分的に先行リリースを繰り返しました。

そうすることで、本番データで確認を進めつつ開発も進められますし、 別件の対応も並行して進めやすくすることも可能でした。

確認期間は2,3週間ほど動かしてエラー検知されなければ大丈夫だろうぐらいで考えてました。

データ品質担保はどうしよっかな

経験上あまり手を抜かない方が良いと考え、バリデーションを入れました。

現状では気になる所だけにしておき、今後必要に応じて増やしていけば良いと考えました。

  • 主要テーブルの意味合いチェック
  • 新旧の最新ビューで件数比較 など

尚、BigQueryCheckOperatorがシンプルで汎用性が高かったので採用しています。

開発途中でも想定外にエラー検知をしたり、今後の運用でもデータ品質の監視にもなるので、やはり大事だと感じました。

今回は見送ったデータ品質対応

昨今の技術進歩だともっと賢くやる方法があるはずだ!と調べてたら、 Great Expectationsというのがありました。

今回、時間の兼ね合いで検証はできなかったのですが、BQテーブルを自動診断してデータの品質診断結果までファイル出力してくれるようです。

GreatExpectationsOperatorもあるようですし、機会があれば触ってみようかなと思います。

既存の手動実行スクリプトをAirflowに移植しようと思ったら

最新ビューには個人情報列あり/なしの2つ存在しており、今までは新規テーブル追加時のオペレーション時はrubyスクリプトを用いてローカルから手動実行して作成してました。

バリデーションで新旧の最新ビューを比較するのに必要でしたし、手動でやるのも手間でしたので、 これを気にAirflow(python)に移植して自動で実行しようと考えました。

対応途中で、よくみると内部でgsutilコマンド使ってることに気づき、
  "現行のAirflow環境だとgsutilコマンド(CLI)が入ってなさそうやから使えへんやん!"
となりました。

目標リリース期限の2週間前を切っており、効果測定やこのブログを書くタイミングのこともあったので期限をずらしたくなかったのです。

CLI入れるとなると「あれ、間に合うかな。。後回しにしすぎた」となりました(汗

ただ、よく考えると全く同じ方法でやらなくてもいいかと頭を切り替え、
BQのINFORMATION_SCHEMAやBigQueryHookを使う方法で対応を進めて乗り切りました。

蓋開けたらCLIが移行先で使えなくて、対応期限も近づいてたので慌てたという話ですねw

そろそろリリース後の話をしよう

無事に予定通り、10月末にリリースできまして、その後の話をしたいと思います。

効果はどうだったの?

BQクエリコストとそのクエリ数を月次単位で比較してみる(課金対象のみ)

BQクエリコストですが米ドル比較で "前月比 約25%削減" となりました。
BQクエリスキャン量だとPB(ペタバイト)レベルでの削減です。

桁が大きすぎてよくわからないですね..

月別BQクエリコストとクエリ数

効果測定するにあたって

  • もちろん比較月で使われ方が変更になったり増減した分もある
  • エニグモ内のほぼ全てのプロジェクトを含んだ数値である
    • つまり、今回改善した最新ビュー以外の数値も含んでいる
  • クエリ数ですが、BQ履歴テーブル自体は7月には動かしていた
  • 10月からバリデーションも入れて先行リリースしてた影響もありクエリ数が増えてそう
    • 約14万回は今回追加した処理が原因そう

BQクエリ平均実行時間と処理量を月次単位で比較してみる(課金対象のみ)

BQクエリ平均実行時間ですが、"前月比 約40%削減"されておりました。

最新ビューが絡まないクエリも多数あると思うのですが、 単純な平均でこの数値は思ってた以上に効果があったのかもしれません。

後、10月時点で少し下がってるのはなんでだろう。
先行リリースでバリデーションとかで軽いクエリを流し始めた影響か、 または重たいのが別件で改善されたのですかね。

月別BQクエリ平均実行時間

とあるLookerダッシュボード内部のクエリ実行時間を日次で見てみる

LookerではSystem Activity が用意されているので、そちらで確認してみました。
やはり半分程度の性能改善がされてそうです。

私もダッシュボードをいくつか触ってみましたが、 体感でも「お、ちょっと早くなったな」とわかるぐらいでした。

とあるLookerダッシュボードの日別クエリ実行時間

過去のとある時点のデータを遡ってみる

こんな感じのクエリかな

下記のようにすると、過去のとある時点のデータ参照もできそうでした。
本来のテーブルリレーションだけ意識すれば良いので楽ですね。

DECLARE target_date string;
SET target_date = '20221201';

WITH
 _table_a AS (
   SELECT _TABLE_SUFFIX AS tabledate, id, value, times
   FROM table_a__*
   WHERE target_date <= _TABLE_SUFFIX -- 見たい履歴の期間を指定
 ),
 _table_b AS (
   SELECT _TABLE_SUFFIX AS tabledate, id, value2
   FROM table_b__*
   WHERE target_date <= _TABLE_SUFFIX -- 見たい履歴の期間を指定
 )
SELECT ta.tabledate, ta.id, ta.value, ta.times, tb.value2
FROM _table_a ta -- BQ履歴テーブル
 INNER JOIN _table_b tb -- BQ履歴テーブル
  ON ta.id = tb.id -- 本来のテーブル結合条件
  AND ta.tabledate = tb.tabledate -- _TABLE_SUFFIX同士で結合する
ORDER BY ta.tabledate, ta.id

タイムリープして思ったこと

見たい履歴の期間を大きく取りすぎると利用するテーブルによってはデータの参照が遅いですし、やはりスキャン量がやばいことになりそうです。

また、日次のAM0:00時点のみしかBQ履歴テーブルを残してないのもありますので、 傾向を掴むような分析や他に見る参照する方法がない時などの利用用途で向いてそうです。

Looker上でもexploreを作成してタイムリープしてみた

やはり少し重いですね...
こんな感じで使うのが良さそうですかね。

  1. 他のexploreで探索的に分析しつつ
  2. 過去のあの時のデータはどうだったんだろうと気になる
  3. 今回作成したBQ履歴テーブルを用いたexploreで確認する
  4. 定期的に確認しそうな場合はダッシュボード化して性能改善もする

所感

全体を通して概ね計画通りに進められ大きな問題も発生せず、ほっとしました。
懸念してたBQ履歴作成分などで増えるコストもありましたが、それ以上に減少してくれたので良かったです。

スキャン量も右肩上がりだったので、実施タイミングも良かったのかなと思います。
また、この機会に手動で実施していた箇所を少し自動化できたのも良かったです。

ただ、今回作成したオペレータ数が約数千あって、AirflowのWebUI画面でログ表示しようとしたら少し重くて開きづらくなってました(ぇ)

今後、データ基盤の活用が進むほど、今回対応したコスト削減効果も増えると思います。

最後に

データ基盤の開発運用保守に携われている方に少しでも参考になればと思い、このテーマで記事を書いてみました。本記事を通して少しでもイメージを掴めて頂けますと幸いです。

また、お忙しい中、設計の相談やコードレビューをして頂けた上司には感謝しております。

私からは以上となります。最後までお読み頂きありがとうございました。

明日の記事の担当はデータアナリストの井原さんです。お楽しみに。


株式会社エニグモ 正社員の求人一覧

hrmos.co