BigQueryのINFORMATION_SCHEMA向けサンプルクエリ集

G-genの min です。本記事では、BigQuery の INFORMATION_SCHEMA に対するクエリ例を紹介します。コスト管理、開発の効率化、運用のために活用してください。

仕様

INFORMATION_SCHEMA とは

INFORMATION_SCHEMA とは、BigQuery のジョブ履歴、テーブルやビューのメタデータ、ストレージ使用量などを保持するシステムビューです。

これらのビューに対して SQL クエリを実行することで、BigQuery に関するメタデータを網羅的に取得できます。コスト管理、パフォーマンスチューニングなど、データ基盤の運用にあたって INFORMATION_SCHEMA の理解は必須です。

• 参考 : INFORMATION_SCHEMA の概要

料金

INFORMATION_SCHEMA のビューに対するクエリは、通常のテーブルへのクエリと同様に課金対象となります。

クエリはキャッシュされないため、同じクエリを繰り返し実行した場合でも、その都度クエリ料金が発生します。またプロジェクトの BigQuery 課金設定がオンデマンドの場合、通常のクエリと同様、最低 10 MB が課金バイトとしてカウントされます。

• 参考 : INFORMATION_SCHEMA の概要 - 料金

必要な権限

INFORMATION_SCHEMA の各ビューをクエリするには、特定の IAM 権限が必要です。本記事に登場するビューとそれに必要な権限は以下の通りです。

対象ビュー	スコープ	必要な権限（Permission）	主な事前定義ロール（Role）
JOBS_BY_USER	プロジェクト	bigquery.jobs.list	BigQuery ユーザー（roles/bigquery.user）
JOBS_BY_PROJECT JOBS_TIMELINE_BY_PROJECT	プロジェクト	bigquery.jobs.listAll	BigQuery リソース閲覧者（roles/bigquery.resourceViewer）
COLUMNS PARTITIONS VIEWS	プロジェクト	bigquery.tables.get bigquery.tables.list	BigQuery データ閲覧者（roles/bigquery.dataViewer）
PARAMETERS	プロジェクト	bigquery.routines.get bigquery.routines.list	BigQuery データ閲覧者（roles/bigquery.dataViewer）
TABLE_STORAGE_BY_ORGANIZATION	組織	bigquery.tables.get bigquery.tables.list	BigQuery データ閲覧者（roles/bigquery.dataViewer）

組織やフォルダレベルのビューをクエリするには、権限がそれぞれのレベルで付与されている必要があります。また、これらのビューは、プロジェクトが組織に所属している場合にのみ利用可能です。

詳細については、公式ドキュメントをご参照ください。

• 参考 : JOBS ビュー - 必要なロール
• 参考 : JOBS_TIMELINE_BY_USER ビュー - 必要な権限
• 参考 : COLUMNS ビュー - 必要な権限
• 参考 : PARTITIONS ビュー - 必要な権限
• 参考 : PARAMETERS ビュー - 必要な権限
• 参考 : TABLE_STORAGE_BY_ORGANIZATION ビュー - 必要な権限

制約事項

INFORMATION_SCHEMA を用いたコスト分析にはいくつかの制約事項があります。これらの点を考慮した上で、本記事のクエリ例をご利用ください。

• 行レベルセキュリティが設定されたテーブルに対するクエリでは、課金対象バイト数などの一部の統計情報が隠される場合があります。
• BigQuery ML のモデル作成ジョブではモデルの種類によって料金が異なりますが、INFORMATION_SCHEMA.JOBS ではモデルの種類を判別できないため、本記事のようなコスト計算は概算値となります。
• Apache Spark プロシージャの利用料金も INFORMATION_SCHEMA.JOBS では total_bytes_billed に含まれる場合がありますが、通常のクエリ利用と区別することはできません。

上記は INFORMATION_SCHEMA.JOBS ビューの注意点を例として挙げました。詳細は各ビューのドキュメントを参照してください。

• 参考 : JOBS ビュー - 制限事項

注意点

多くの INFORMATION_SCHEMA ビューは、region-asia-northeast1.INFORMATION_SCHEMA.JOBS_BY_PROJECT のようにリージョン修飾子を付けてアクセスする必要があります。クエリを実行する際は、対象リソースが存在するリージョンを正しく指定してください。

本記事のサンプルクエリでは、 region-asia-northeast1 と記載している箇所をご自身の環境に合わせて書き換えてください。

• 参考 : INFORMATION_SCHEMA の概要 - 構文

コスト・リソース管理

高額クエリを特定する

ユースケース

予期せぬ高額クエリ（スキャン量が多いクエリ）が実行されていないか定期的にチェックし、コストを最適化したい。

SQL

過去30日間でスキャン量が多かったクエリ TOP 20を、実行ユーザーやクエリ内容とともにリストアップします。

  -- 過去30日間でスキャン量が多かったクエリ TOP 20
SELECT
  user_email,
  job_id,
  -- TB単位に変換
  ROUND(total_bytes_billed / POW(1024, 4), 4) AS terabytes_billed,
  -- オンデマンド料金（東京リージョン: $7.5/TB）でコストを概算 ※2025年8月時点
  (total_bytes_billed / POW(1024, 4)) * 7.5 AS estimated_cost_usd,
  creation_time,
  -- クエリ内容を確認しやすくするために改行をスペースに置換
  REGEXP_REPLACE(query, r'\n', ' ') AS query_oneline
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND CURRENT_TIMESTAMP()
  AND total_bytes_billed > 0
  AND job_type = 'QUERY'
ORDER BY
  total_bytes_billed DESC
LIMIT
  20;

スロット使用量の推移を分析し、ボトルネックを特定する

ユースケース

特定の時間帯にクエリが遅くなることがあるため、時間帯ごとのリソース（スロット）消費量の傾向を分析し、負荷の高い時間帯を特定したい。

SQL

JOBS_TIMELINE_BY_PROJECT を使って、1時間ごとの合計スロット使用時間（total_slot_ms）を集計し、負荷の高い時間帯を特定します。

  -- 過去24時間の時間帯別（1時間ごと）の合計スロット使用時間を集計
SELECT
  -- 時間を切り捨ててグルーピング
  TIMESTAMP_TRUNC(period_start, HOUR) AS usage_hour,
  -- スロット使用時間（秒）に変換
  SUM(period_slot_ms) / 1000 AS total_slot_seconds
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE
  period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
GROUP BY
  usage_hour
ORDER BY
  usage_hour;

組織全体のストレージ使用量をプロジェクト別に把握する

ユースケース

複数のプロジェクトをまたいで、組織全体のストレージ使用量をプロジェクト別に集計し、コスト管理に役立てたい。

SQL

TABLE_STORAGE_BY_ORGANIZATION ビューを使い、組織内のプロジェクト別ストレージ使用量ランキングを作成します。

  -- 組織内のプロジェクト別ストレージ使用量ランキング
SELECT
  project_id,
  ROUND(SUM(total_physical_bytes) / POW(1024, 4), 2) AS total_physical_tb
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION
GROUP BY
  project_id
ORDER BY
  total_physical_tb DESC;

開発・デバッグ効率化

テーブルのスキーマ情報を素早く確認する

ユースケース

開発中に、参照したいテーブルのカラム名、データ型、NULL許容かなどをSQLエディタから離れずに確認したい。

SQL

COLUMNS ビューで、特定テーブルのカラム情報を一覧表示します。

  -- 特定テーブルのカラム詳細を取得
SELECT
  column_name,
  ordinal_position,
  data_type,
  is_nullable,
  column_default
FROM
  `my-project.my_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'my_table';

テーブルの DDL（テーブル作成クエリ）を取得する

ユースケース

既存のテーブル定義を元に新しいテーブルを作成したい場合などに、テーブルの DDL（CREATE TABLE 文）を直接取得します。

SQL

TABLES ビューの ddl カラムから、特定のテーブルのDDLを取得します。

  -- 特定テーブルのDDLを取得
SELECT
  table_name,
  ddl
FROM
  `my-project.my_dataset.INFORMATION_SCHEMA.TABLES`
WHERE
  table_name = 'my_table';

パーティションテーブルの情報を調査する

ユースケース

パーティションプルーニングが意図通りに機能しているか確認したい。また、どのパーティションにどれくらいのデータが入っているか調査したい。

SQL

PARTITIONS ビューで、パーティションごとの行数やデータサイズを確認します。 PARTITIONS ビューは 2025年8月現在、プレビュー機能です。仕様が変更される可能性がある点にご注意ください。

  -- パーティションテーブルのパーティションごとの情報を取得
SELECT
  table_name,
  partition_id,
  total_rows,
  -- MB単位に変換
  ROUND(total_logical_bytes / POW(1024, 2), 2) AS logical_mb,
  last_modified_time
FROM
  `my-project.my_dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE
  table_name = 'my_partitioned_table'
  -- 古いパーティションから表示
ORDER BY
  partition_id ASC;

データセット内のビュー定義を一覧で確認する

ユースケース

データセット内にどのようなビューが存在し、どのテーブルを参照しているのかを、定義 SQL（ソースコード）とあわせて一覧で確認したい。

SQL

VIEWS から、ビューの定義を直接取得します。

  -- 特定のデータセット内のビュー一覧とその定義を取得
SELECT
  table_name AS view_name,
  view_definition
FROM
  `my-project.my_dataset.INFORMATION_SCHEMA.VIEWS`;

クエリのエラー履歴を確認する

ユースケース

「先ほど実行したクエリがエラーになったが、エラーメッセージを見失ってしまった」という状況で、エラーの原因を素早く特定したい。

SQL

JOBS_BY_USER を使い、自分が実行してエラーになったクエリの履歴を遡ります。

  -- 過去7日間で自分が実行し、エラーになったクエリの一覧
SELECT
  creation_time,
  job_id,
  error_result.reason,
  error_result.message,
  query
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND CURRENT_TIMESTAMP()
  AND state = 'DONE'
  AND error_result IS NOT NULL
ORDER BY
  creation_time DESC;

運用

ストアドプロシージャの実行履歴を調査する

ユースケース

特定のバッチ処理として実装されたストアドプロシージャが、いつ、誰によって実行されたか、またはエラーになっていないかを監査したい。

SQL

JOBS_BY_PROJECT の query カラムをフィルタリングして、特定のプロシージャの呼び出し履歴を抽出します。

  -- 特定のプロシージャの実行履歴を取得
SELECT
  job_id,
  creation_time,
  start_time,
  end_time,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS execution_seconds,
  user_email,
  statement_type,
  -- エラーが発生した場合は理由とメッセージを表示
  error_result.reason,
  error_result.message
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  -- 調査したいプロシージャ名でフィルタ
  query LIKE '%CALL `my-project.my_dataset.my_procedure`%'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY
  creation_time DESC;

プロシージャや UDF の定義と引数を調査する

ユースケース

リージョン内にどのようなプロシージャや UDF（ユーザー定義関数）が定義されているか網羅的に棚卸ししたい。また、特定のプロシージャや UDF について、その引数の詳細を調査したい。

SQL

ROUTINES ビューを使うと、リージョン内のプロシージャや関数の一覧を取得できます。一方、PARAMETERS ビューでは、特定のルーチンの引数の詳細を確認できます。

  -- プロジェクト内の全データセットのプロシージャと UDF を一覧表示
SELECT
  routine_schema,
  routine_name,
  specific_name,
  -- パラメータ検索用に specific_name を取得
  routine_type,
  -- PROCEDURE または FUNCTION
  data_type,
  -- 関数の戻り値の型
  routine_definition
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.ROUTINES
ORDER BY
  routine_schema,
  routine_name;

  -- 上記クエリで取得した specific_name を使い、特定の関数の引数情報を取得
SELECT
  parameter_name,
  data_type,
  parameter_mode -- IN, OUT, INOUT
FROM
  `my-project.my_dataset.INFORMATION_SCHEMA.PARAMETERS`
WHERE
  specific_name = 'my_function_name';

佐々木 愛美 (min) (記事一覧)

クラウドソリューション部 データアナリティクス課。2024年7月 G-gen にジョイン。G-gen 最南端、沖縄県在住。最近覚えた島言葉は、「マヤー（猫）」。

関連記事

• Dataformのアサーション機能を解説

  G-gen の min です。Google Cloud のデータ変換パイ…
• BigQuery remote MCP serverを使ってみた

  G-gen の杉村です。BigQuery に対して自然言語で問い…
• BigQueryに自然言語でクエリする10個の方法

  G-genの杉村です。当記事では、BigQuery に対して自…

2025-09-03 09:00

問い合わせ

開催予定・受付中のセミナー一覧はこちら

G-genではGoogle Cloud / Google Workspace の活用に役立つセミナーを開催しております。気になるテーマがありましたら、ぜひご参加ください。

Google Cloud請求代行サービス

Google Cloud が3%OFF〜で利用可能 + 無償の技術サポート窓口。

既に Google Cloud をご利用中の方でも切り替え可能です。

採用ページ

エンジニア・営業ともに、積極採用中！