✍️

BigQueryのコストと利用状況を可視化してみた

はじめに

データ基盤チームの三井です
フェズでは購買データの分析等にGoogle BigQueryを利用しています
 
Google BigQuery とは
ビッグデータを素早く活用したい場合に選択肢にあがるフルマネージド型のデータウェアハウスです
標準SQLを利用して、実行したクエリが利用したデータ量に応じて課金されます
 
フェズのデータ基盤は、主に分析用途で複数のチームから利用されているため以下のような課題がありました
  • 誰がどんな用途でBigQueryを利用しているのか可視化できていない
  • 組織毎にコストを按分したいけど実行者や利用用途が判別できない
  • BigQueryが複数存在して集計に手間がかかる
今回は上記のような課題を解消するために試みた事例をご紹介しようと思います

背景

この記事を書いてる日からは少し前の事になりますが
データ基盤チームでGoogle Cloud の請求アカウントのレポートを眺めていると
8月のBigQueryのコストがほぼ倍増している事に気づきました
 
データ基盤チームで原因を調査しましたが、
誰がいつどんなクエリを実行していくらコストが発生したのか
を把握するためにそれなりに時間がかかってしまいました
そこで、これらをすぐに把握できる仕組みを構築する事にしました

どんな構成

BigQueryには INFORMATION_SCHEMA というビューが存在します。
そのなかでも JOBS_BY_PROJECT を利用すればジョブの実行履歴が取得できます。
 
フェズのデータ基盤では、複数ProjectにBigQueryのデータセットが存在します。
また、INFORMATION_SCHEMAの有効期限は180日でそれ以前の履歴は閲覧できません。
そのデータセットに各Projectのジョブの実行履歴を日次で蓄積する仕組みを考えました。
簡単ですが絵にするとこんな感じです。

JOBメタデータの集約

JOBメタデータ

JOBS_BY_* ビューのスキーマ定義は公式ドキュメントに書いています。
その中でも、今回欲しいカラムはこのあたりです。
カラムデータ型
user_emailSTRINGジョブを実行したユーザーのメールアドレスまたはサービス アカウント
project_idSTRINGプロジェクトの ID
querySTRINGSQL クエリテキスト 注: query 列が表示されるのは JOBS_BY_PROJECT ビューのみです。
total_bytes_billedINTEGERジョブに対して課金された合計バイト数
total_slot_msINTEGERジョブの全期間のスロット(ミリ秒)
start_timeTIMESTAMPこのジョブの開始時間

集約用BigQueryデータセット

集約用のBigQueryは以下のようなスキーマ構成にしています
  • Dataset:
  • Table:
カラムデータ型モード説明
userSTRINGNULLABLEUser who executed the query
project_idSTRINGNULLABLEGoogle Cloud Project ID
querySTRINGNULLABLEExecuted Query
total_bytes_billedINTEGERNULLABLECapacity used by executing Query (Byte)
total_slot_msINTEGERNULLABLESlot by executing Query (ms)
charges_dollerFLOATNULLABLECost used by executing Query (Doller)
slot_usageFLOATNULLABLESlot used by executing Query
start_timeTIMESTAMPNULLABLETime when the query was executed (JST)

Scheduled Query

メタデータを取得するために Scheduled Query で実行されるクエリは以下のようになります
これを対象のProjectの数だけ設定して、日次で定期実行されるように設定します
※ クエリ実行するユーザもしくはサービスアカウントに の権限が必要です
  • [project] と [region] はProjectごとに適宜書き換え
  • 対象期間は実行日の前日

データポータルによる可視化

ここまでで、集約用BigQueryに各Projectのジョブ履歴が蓄積されるようになりました
ここから、データポータルから集約用BigQueryにつないで可視化してみます
データポータルを作成するアカウントには集約用BigQueryへの権限が必要になります

データポータルからBigQueryに接続する

データポータルから集計用BigQueryに接続します
  1. データのレポートへの追加で を選択
  1. 集約用BigQueryデータセットを選択して、右下の ボタン

データポータルでグラフや表を作成

あとはお好みでグラフや表を作成していきます
画面でポチポチするだけなので、作成方法は割愛します
参考までにフェズで作成したデータポータルのキャプチャと簡単な説明を書いておきます
  • ①Filter
    • 日付、Project、Userでフィルターをできます
  • ②Cost
    • コストに関するサマリ
  • ③Queries
    • クエリに関するサマリ
  • ④Ranking By User
    • User毎の課金額ランキング
  • ⑤Ranking By Query
    • Query毎の課金額ランキング

通知

あとはこのデータポータルを定期的にチェックしつつ問題があるクエリや運用を改善。。。
できればいいのですが、人間なので能動的な運用ではついつい忘れてしまう事があります
ということで、利用状況を定期的にSlack通知しています
こちらは今回のメインテーマではないため詳しい説明は割愛しますが、以下のような仕組みになっています
  • Google Apps Script を利用して、集計用BigQueryからスプレッドシートに集計結果を保存
  • Slack ワークフローでスプレッドシートからデータを取得してチャンネルにメッセージ送信
 

まとめ

上記のような仕組みを導入することで
BigQueryコストの内訳が簡単に把握できるようになりました
また
想定していないようなクエリ実行を発見し運用を改善する動きも取れるようになりました
 
当初予定していた課題は達成できましたが、運用を続けると以下のような課題が出てきました
  • 個人がBigQueryのUIから実行したクエリと、データポータル等のツール類から実行されたクエリと区別がつかない (実行ユーザはINFORMATION_SCHEMA的には同一になる)
    • ツール類からの実行を区別できるように実行アカウントを整備したい
  • 想定外の利用が発生していても、そのタイミングで気づくことができない
    • 即座に通知する仕組みを整備したい
データ基盤チームでは上記のような課題も少しずつ解決できるよう、日々改善を続けていきます

Appendix

今回ご紹介した環境はConsoleやgcloudコマンドで構築可能ですが、せっかくなのでTerraformによりIaC対応しています
参考にコードを載せておきます

ファイル構成

bigquery.tf

  • BigQuery および Scheduled Query の設定

service_account.tf

  • Service Account を作成して を付与する

locals.tf

  • 変数を定義
  • Projectを追加したい場合は、 のリストに追加すればOK
 

templates/query_history.sql

  • Scheduled Query で実行されるSQLのテンプレートファイル

templates/query_history_schema.json