こちらはバイセルテクノロジーズ Advent Calendar 2024 の20日目の記事です。昨日は辻岡さんによる「品質とスピードを両立するためにチーム体制を変えている話」でした。

こんにちは。データエンジニアの遠藤です。現在、私はデータサイエンス部に所属して「Pocket」と呼ばれる全社データ基盤の開発・運用に従事しています。

本記事では、ジョブエクスプローラを用いることでBigQueryのボトルネックを容易にリサーチできるようになった件について報告します。

Pocketについて
ダッシュボードでのBigQueryモニタリング
- Google Cloudが標準で提供するダッシュボードの利用
- ダッシュボードの自作
ジョブエクスプローラ
まとめ

Pocketについて

バイセルでは、バイセルグループのデータをBigQueryにすべて集約し、スタッフが安心して利用できるデータ基盤「Pocket」を展開しています。

Pocketの全社での利用を促進する施策に昨年から取り組んでおり、現在、ビジネス職・子会社を含めて約600名のスタッフがPocketのデータを利用しています。

ダッシュボードでのBigQueryモニタリング

BigQueryは、柔軟な記法のSQLを用いてクエリを作成・実行することで、大量のデータを自由に抽出・集計できるツールです。

ただし、クエリ実行時のデータスキャン量かスロット使用量に応じて料金が発生し（どちらで費用が発生するかは契約している請求モデルによります）、クエリによっては高額な請求を招く恐れがあります。加えて、スロット使用量が多いクエリは、利用しているBigQuery全体の負荷を高めるだけでなく、他のクエリのレスポンス遅延を引き起こすリスクがあります。

そのため、BigQueryの管理者は、ダッシュボードやアラートを活用して、請求費用やリソース使用状況をモニタリングする必要があります。

ダッシュボードを用いたBigQueryのモニタリングには、主に以下の2つの方法があります。

Google Cloudが標準で提供するダッシュボードの利用
ダッシュボードの自作

Google Cloudが標準で提供するダッシュボードの利用

Google Cloudでは、BigQuery全体の状態をモニタリングできるダッシュボードが既にGoogle Cloudコンソール上に用意されています。

BigQueryに関連した情報をGoogle Cloudコンソール内で確認できる主なダッシュボードを以下の図表に示します（2024年12月現在）。

モニタリング内容	ナビゲーションメニュー内で選択するプロダクト・項目	項目選択後に行うブラウザ上の操作	Google CloudドキュメントURL	実際の画面
日ごと・月ごとの費用実績	課金（Cloud Billing）＞レポート	トップに表示されるダッシュボードの「フィルタ」でサービスごとにグループ化する	レポートを使用して請求データと費用の傾向を分析する	図1
各リソース使用状況の時系列サマリ	BigQuery ＞モニタリング（BigQuery Monitoring）	「リソース活用（Resource utilization）」タブを選択すると表れるダッシュボード内の「グラフオプション」でリソース項目などを選択する	リソースの使用状況を表示する	図2

これらのダッシュボードで、BigQueryの費用やリソース消費状況の時系列データの概要をブラウザから容易に読み取ることができます。

しかし、ダッシュボードから異常な箇所に気づいた場合、Google Cloudが標準で提供するダッシュボードのみでは「そのときBigQueryで何が起きていたのか？」といった詳細を追うことはできません。

ダッシュボードの自作

BigQueryをさらに詳細にモニタリングするには、管理者がダッシュボードを自らカスタマイズして作成する必要があります。具体的には、主に以下の方法があります（各手法の詳細は省略します）。

Cloud MonitoringでBigQuery専用のダッシュボードを設定
BIツールでBigQueryのINFORMATION_SCHEMAをデータソースとしたダッシュボードを作成
Google Cloudデータアクセス監査ログ（Cloud Audit Logs）をBigQueryやログ解析ツール（Kibana・New Relicなど）に転送してダッシュボードを作成
Cloud LoggingのLog Analytics機能を用いてCloud Audit Logsの内容をダッシュボード化

「自作ダッシュボード」は、Google Cloudが標準で提供するダッシュボードよりもBigQueryの運用効率を高める効果があります。

しかし、オリジナルのダッシュボードは、データの準備や画面作成に多くの手間がかかることに加え、その完成度によっては期待通りのモニタリング成果を上げる保証がないといった問題があります。

以上を踏まえると、ダッシュボードでBigQueryをモニタリングする現在の方法では管理者が「手軽かつ十分にモニタリングする」という理想にはまだ十分に達していないのが現状です。

ジョブエクスプローラ

ジョブエクスプローラ（別名：管理ジョブエクスプローラ、英語名：administrative jobs explorer）は、今年（2024年）7月にBigQuery上でGenerally Available（GA）になった機能です。つまり、Google Cloudが標準で提供するダッシュボードに新たにジョブエクスプローラが仲間入りしました。

ジョブエクスプローラはユーザーに以下の権限があればすぐに利用できます。

組織・プロジェクトに対するBigQueryリソース閲覧者（bigquery.resourceViewer）（どちらか必須）
組織・プロジェクトに対するbigquery.jobs.listAll（どちらか必須）
組織に対するbigquery.reservations.list

ジョブエクスプローラを実際に使ってみる

BigQueryのナビゲーションパネルから「ジョブエクスプローラ」を選択すると、以下の画面が表示されます。

ジョブエクスプローラの画面からは以下の情報が取得できます。

指定期間内にBigQueryで実行されたジョブの総数とステータス別の内訳
ジョブ詳細情報一覧

「ジョブ詳細情報一覧」は以下の項目で構成されています（2024年12月現在）。

項目名	説明
ジョブID	各ジョブに発行された一意のID
タイムスタンプ	ジョブの作成日時
オーナー	ジョブを発行したGoogle Cloudアカウント
プロジェクトID	ジョブを発行したプロジェクトID
予約ID	ジョブ実行時に割り当てられたBigQuery ReservationのID
合計スロット時間	ジョブ実行で消費されたスロット量
時間	実際にジョブ実行にかかった時間
処理されたバイト数	ジョブ実行でスキャンされたデータ量
分析情報	クエリのパフォーマンス分析情報
クエリハッシュ	クエリを識別するために生成される一意のハッシュ値
ジョブラベル	ジョブの特性ごとに表示されるkey:value形式の情報

また、ジョブ詳細情報一覧の画面右部分にはフィルタが用意されていて、各項目による一覧リストのフィルタリングが可能です。

ジョブエクスプローラのメリット

ジョブエクスプローラを実際に使ってみて、メリットと不得意な点がそれぞれ見えてきました。まず、ジョブエクスプローラのメリットをご紹介します。

ボトルネックが容易に特定できるようになった

ジョブエクスプローラのジョブ詳細情報一覧から下表のように情報を洗い出すことで、ボトルネックとなっているクエリのジョブをブラウザ上で探索的に探せるようになりました。

取得したい情報	ジョブ詳細情報一覧で適用するフィルタ	ジョブ詳細情報一覧で見る項目	備考
いつクエリを実行したか	画面右上にある期間を指定するフィルタ	タイムスタンプ	フィルタは過去180日以内であれば期間を自由に設定できる
誰がクエリを実行したか	オーナー	オーナー
どこからクエリを実行したか	ラベル	ジョブラベル	ジョブラベルの項目構成からクエリ発行箇所の特定が可能（例：ジョブラベルに「requestor:looker_studio」があれば Looker studioからのクエリ）
クエリの消費データスキャン量	処理バイト数が次の値より多い	処理されたバイト数
クエリの消費スロット量	スロット時間が次の値を超えています	合計スロット時間

さらに、ジョブ詳細情報一覧では「タイムスタンプ」「合計スロット時間」「時間」「処理されたバイト数」の各項目で昇順・降順に並べ替えができる仕様になっています。各項目を降順に並べ替えることで、複数のクエリの中から相対的にボトルネックとなったクエリも検出できます。

このようなボトルネックの探索は、以前はダッシュボードを自作しないとできませんでしたが、Google Cloudが標準提供するダッシュボードのみで完結できるようになりました。

クエリコストをかけずにボトルネックを探索できる

BigQueryのジョブをモニタリングするダッシュボードはINFORMATION_SCHEMAを用いたクエリが最低限必要ですが、INFORMATRION_SCHEMAクエリは課金やスロット消費が一般的なクエリと同様に発生します。

ところが、ジョブエクスプローラは、INFORMATION_SCHEMAクエリを利用しているのにもかかわらず、課金やスロット消費は一切発生しません。このことはGoogle Cloud公式でも言及されています。

そのため、ユーザーはクエリコストを意識する必要なくBigQuery上で発生したボトルネックの調査を進めることができます。

ジョブエクスプローラが現在不得意なこと

次に、ジョブエクスプローラが「現在不得意なこと」を紹介します。なお、ここで紹介する事項は2024年12月現在の状態であり、以降のバージョンアップで改善される場合があります。

ジョブ詳細情報一覧での柔軟なフィルタリング

先述のとおり、ジョブ詳細情報一覧の画面右部分にフィルタが用意されていますが、文字列を使ったフィルタは完全一致のみの適用となります。

文字列によるフィルタは「ジョブID」「クエリハッシュ」「ラベル」の3項目が該当しますが、完全一致のみのフィルタではどの項目も使いこなすことは難しく、柔軟なフィルタの実現が必要です。

例えば、もしジョブIDのフィルタに文字列による前方一致が実現すれば、以下の書式ルールがあるジョブIDで「クエリ実行元によるフィルタ」が可能になるでしょう。

「scheduled_query_」から始まるジョブIDはScheduled Queryから実行されたクエリ
「sheets_data_connector_」から始まるジョブIDはGoogleスプレッドシートから実行されたクエリ
「bquxjob_」から始まるジョブIDはBigQuery Studioエクスプローラから実行されたクエリ

ボトルネック探索中に実際に書いたSQL文のチェック

ジョブ詳細情報一覧を見ていると、それぞれのジョブでどのようなクエリが実行されたのか知りたい場合があります。もちろん、ジョブエクスプローラから実際に実行されたクエリを確認することは、以下の手順で実質的に可能です。

まず、リスト中のジョブIDにはそれぞれリンクが貼られているので、その中から任意のジョブIDのリンクをクリックして「クエリ結果」というフッターを表示させます。「クエリ結果」には各ジョブの詳細情報が表示されますが（表示項目の具体的な説明はここでは割愛します）、実際に書いたSQL文は残念ながらここには表示されません。

次に、「クエリ結果」の右上にある「操作」をクリックして「ワークスペースでジョブを開く」をクリックします（下図参照）。