BigQuery活用序章（モニタリング・スキャン量抑制）

m-kume
2025/07/24に公開
各事業サイドでのデータ利活用推進を目的として、BigQuery × Looker Studio の統合環境整備を進めています。

 当社の状況当社ではBI・ダッシュボード系のツールとして、スクラッチ開発のアプリケーション、Redash、Tableau、Looker Studioなど様々なツールを利用しています。その中でLooker Studioは浸透度が高いものの、データソースを自身で収集してきたスプレッドシートとしている方も多く、データ収集の手間やレスポンスが悪いといった運用課題を抱える方もいます。BigQueryの活用を進め、そういった課題解決に繋げたい狙いがあります。

 BigQueryについてBigQueryの知見はまだまだ浅く手探り状態で、BigQueryへ格納しているデータもローデータ（データレイク） ⇒ データウェアハウス ⇒ データマートへと昇華させていく道半ばにあり、最適解を見い出せていない状況にあります。そんな中でデータ活用を進めていくために、モニタリングやスキャン量抑制の工夫を模索しているので、備忘録的に記録しておきたいと思います。

 BigQueryのモニタリング非常に便利なBigQueryですが、通常のRDBのような感じでテーブル構造やクエリへの考慮なく運用してしまうと、思わぬ課金が発生してしまう可能性があります。課金体系は簡単に言うと「ストレージ料金（保存データ量）※毎月10GBまで無料」と「コンピューティング料金（クエリの実行量）※毎月1TBまで無料」で構成されます。 よって早めにモニタリング手法を確立しておくことが重要です。

 Metrics ExplorerMetrics Explorer は、Google Cloud が提供するモニタリングツール Cloud Monitoring の一部で、さまざまなサービスから収集されるメトリクス（性能指標）を視覚化・分析するための強力なツールです。グラフを作成したり、特定の期間のデータを比較したりすることで、サービスの健全性やパフォーマンスの変化を把握できます。
BigQueryも例外ではなく、ジョブの実行状況、クエリのバイト数、スロットの使用状況など、様々なメトリクスが Cloud Monitoring に自動的に送信されています。
特に以下の指標を参照するようにしています。

BigQuery Dataset > Storage > Stored bytes

BigQuery Project > Query > Statement scanned bytes


 Metrics Explorer の強みリアルタイムに近いデータ: 比較的短い間隔で収集されたメトリクスをほぼリアルタイムで確認できます。
視覚的なダッシュボード: グラフやチャートで直感的に状況を把握しやすいです。
アラート設定: 異常なメトリクスを検知した場合に自動的に通知する設定が簡単です。
簡易的な傾向把握: 特定の期間の傾向やスパイク（急増）を素早く見つけるのに向いています。

 INFORMATION_SCHEMAINFORMATION_SCHEMA は、BigQueryのメタデータ（テーブル、ビュー、ジョブ、予約など）にアクセスするための、標準SQLインターフェースを提供する特別なビューセットです。これらのビューを通常の BigQueryクエリのように実行することで、BigQuery環境の利用状況やパフォーマンスに関する詳細な情報を取得できます。
例: 過去24時間で処理されたバイト数が最も多いクエリを特定する。
SELECT
  job_id,
  project_id,
  user_email,
  query,
  total_bytes_processed,
  start_time,
  end_time,
  state
FROM
  `region-REGION`.INFORMATION_SCHEMA.JOBS
WHERE
  start_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) AND CURRENT_TIMESTAMP()
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY
  total_bytes_processed DESC
LIMIT 10;
REGIONは利用中のリージョンに置き換え

 INFORMATION_SCHEMA の強み詳細な履歴データ: 過去180日間のジョブ実行履歴、クエリの詳細、スロット使用状況など、Metrics Explorer よりも詳細な粒度でデータを提供します。
柔軟な分析: SQL クエリを使って、Metrics Explorer では表現できないような複雑な条件でデータをフィルタリングしたり、集計したりできます。
特定の課題の深掘り:
コスト分析: どのクエリがどれだけのバイトを処理し、どれくらいのコストがかかったか。
パフォーマンス分析: 遅いクエリの原因特定（どのステージで時間がかかっているかなど）。
ユーザーごとの利用状況: 特定のユーザーや部門がどのくらいリソースを使っているか。
テーブルの利用状況: どのテーブルが頻繁にクエリされているか、アクセスされていないテーブルはどれか。

自動化されたレポート作成: 定期的に INFORMATION_SCHEMA をクエリして、カスタムレポートを生成するスクリプトを作成できます。

 INFORMATION_SCHEMA 利用時の注意点課金対象となる: INFORMATION_SCHEMA へのクエリも通常の BigQuery クエリと同様に、処理されたバイト数に応じて課金が発生します。大量のデータをスキャンするクエリには注意が必要です。
キャッシュされない: INFORMATION_SCHEMA へのクエリはキャッシュされないため、同じクエリを繰り返し実行すると、その都度課金されます。

 モニタリング機能の使い分けMetrics Explorer が「リアルタイムに近い状況を視覚的に把握し、異常をアラートする」のに向いているのに対し、INFORMATION_SCHEMA は「過去の履歴を詳細に掘り下げ、複雑な分析やカスタムレポート作成を行う」のに非常に強力なツールです。
これら二つを組み合わせて利用することで、BigQuery のモニタリングをより効果的に行うことができます。例えば、Metrics Explorer でスロット使用率の急増を検知し、その後 INFORMATION_SCHEMA を使って、どのクエリがその急増の原因となったのかを詳細に分析する、といった連携が可能です。

 BigQueryのスキャン量抑制前述のとおり、BigQueryではコンピューティング料金、例えばクエリの実行量によって課金額が変わってきます。つまりクエリのスキャンコストはできる限り抑制できた方がよいです。運用に支障のない範囲で工夫をしていく必要があります。

 パーティション分割パーティション分割とは、大きなテーブルを、特定の日付や整数範囲、または列の値に基づいて、より小さく管理しやすい「パーティション」に分割することです。これにより、BigQuery はクエリを実行する際に、テーブル全体をスキャンするのではなく、必要なパーティションのみをスキャンするようになります。またスキャンするデータ量が減るため、クエリの実行速度が向上します。特に大規模なテーブルでは、この効果は顕著です。

 パーティションの種類BigQuery では主に以下の3種類のパーティションが利用できます。
日付/タイムスタンプパーティション (Date/Timestamp-partitioned tables): 最も一般的で推奨される方法です。 テーブル内の DATE, TIMESTAMP, DATETIME 型の列、または取り込み時間（_PARTITIONTIME、_PARTITIONDATE）に基づいて分割します。当社でのニーズとしてはこちらを採用することが多く、単位は「月」としています。
整数範囲パーティション (Integer-range partitioned tables): 整数型の列の範囲に基づいて分割します。ユーザーIDの範囲、商品カテゴリIDの範囲など。
列パーティション（外部テーブルのみ）(Column-partitioned external tables): Cloud Storage 上の外部データソースで、ファイルパスに特定の列の値が含まれている場合に適用されます。BigQuery のマネージドストレージのテーブルでは使用できません。

 パーティション分割の注意点適切なパーティションキーの選択: クエリのフィルタリング条件として頻繁に使用される列をパーティションキーに選ぶことが重要です。そうしないと、パーティション分割の恩恵を最大限に受けられません。
パーティションの数が多すぎないか: パーティション数が極端に多くなりすぎると、メタデータのオーバーヘッドが増え、管理が複雑になる可能性があります。通常、日付パーティションであれば日次や月次が適切です。
パーティションフィルタの必須化: 一部のクエリでは、パーティションフィルタなしでのスキャンを禁止する設定（requirePartitionFilter）を適用することも可能です。これにより、意図しないフルスキャンを防げます。
既存テーブルへの適用: 既存の非パーティションテーブルをパーティションテーブルに変換するには、通常、データを新しいパーティションテーブルに再ロードする必要があります。テーブルを構築する段階である程度パーティションのイメージを持っておくことが重要です。データ量が多いほど、再ロードは手間です。
パーティションの数: 1つのテーブルに作成できるパーティションの最大数は10,000パーティションです。上限に達した状態で新しいパーティションを作成しようとすると「Too many partitions in the table. The limit is 10000.」のようなエラーが発生し、新しい日付や範囲のデータをテーブルに挿入できなくなります。

 Looker Studio データ抽出データマート整備を進めつつ、データ活用も推進していきたい、といった状況で当座のしのぎとして有効活用しているのが、Looker Studioの「データ抽出」です。BigQueryのデータ構造化がまだイケていない状況下で、BigQueryのデータソースを直接都度参照すると、スキャンコストが膨大になる危険があります。Looker Studioのデータ抽出は、既存のデータソースから特定のフィールドだけを含んだ「抽出済みデータソース」を作成することのできる機能となり、BigQueryへの直接参照を控えスキャン量を抑制できます。
注意点としては、データのリアルタイム性がどこまで求められるかで、その要件に応じて「データ抽出」を実施するタイミング・頻度を定める必要があります。もうひとつクリティカルなのが「データ抽出」で取得できるスナップショット容量が100MB上限であるということです。基本的には定期的に自動更新でデータを取得しますが、上限超過のアラートも出ないので、より注意が必要です。抽出データ量の増加により100MBを超過する未来も見えてきていますので、それまでにデータマートの整備を行っていきます。
LookerStudioにて「データソース」を新規作成

Google Connectorsから「BigQuery」を選択

該当するプロジェクトを選択し、必要データを取得するSQLを書き、活用するディメンションや指標の設定を行う

Google Connectorsから「データの抽出」を選択

利用可能なデータソースからあらかじめ作成しておいたカスタムクエリを選択

利用したいディメンションや指標を選択

自動更新「ON」とし、取得タイミングを選択

Looker Studioのデータソースをこの「データ抽出」に切り替えることでスキャン量を抑制できます

 おわりに当社のメイン事業となるリユース事業においては、EC・店頭の両システムを内製化していることもあり、オンライン・オフライン双方で大量の1st Party Dataを保持しています。
お客様は「販売」「買取」と2軸で当社をご利用いただいており、また取り扱う商品がすべて1点ものであるため、小売業の中でもデータ分析の幅はとても広く、興味深いデータを眺めることのできる環境です。
BigQuery × Looker Studioの環境整備により、こういった膨大で有益なデータの可視化をさらに進めていければと思います。