INFORMATION_SCHEMAでBigQueryの利用状況を確認

BigQueryを利用していく上でユーザの利用状況は非常に重要な要素です。
今回はBigQueryのINFORMATION_SCHEMAを用いて私が便利だと思ったものを紹介したいと思います。

INFORMATION_SCHEMAとは

INFORMATION_SCHEMAとはBigQueryの様々なメタデータへアクセスするためのビューとなります。
ビューを通して過去180日のデータへアクセスが可能です。
またこれらのビューは通常のクエリと同様クエリ処理量により課金が発生します。キャッシュは効かないためクエリの実行ごとに課金が発生するので注意が必要です。

INFORMATION_SCHEMAは実装後何度も改善されて来ています。
最近いろいろなビューが追加されています。
2020年9月現在以下のビューが提供されています。 今回はジョブのメタデータを中心にご紹介します。

• dataset metadata
• job metadata
• job timeline metadata
• reservation metadata
• streaming metadata
• routine metadata
• table metadata
• view metadata

INFORMATION_SCHEMAの構造

ビュー毎に異なりますが基本的にデータセットもしくはリージョンを指定する必要があります。

データセットを指定する場合

以下のクエリはパブリックデータセットのNCAAバスケットボールの一般公開データセット内のマスコットにおけるテーブルのカラム一覧を参照しています。
FROM句にデータセット(この場合はクエリを実行するプロジェクトとは別のプロジェクトにあるデータセットを参照するためプロジェクト名も)を指定します。

-- データセットを指定
SELECT
  *
FROM
  `bigquery-public-data`.ncaa_basketball.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
TABLE_NAME = 'mascots'

リージョンを指定する場合

以下のクエリはクエリ実行時にクエリ処理量が多いユーザのトップ5をクエリしています。 従量課金の場合はイコールもっともお金を使用しているユーザでもあります。
リージョンを指定する場合はFROM句の最初にリージョンを指定します。
下記ではUSリージョンとなるので例えば東京(asia-northeast1)にて実行されたジョブは取得できません。
また下記はJOBS_BY_PROJECTとしているので実行中のプロジェクトのジョブを取得します。
ここをJOBS_BY_ORGANIZATIONとするとプロジェクトの所属する組織全体、JOBS_BY_USERとするとプロジェクト内で実行ユーザがしたもののみ、といったように抽出範囲が変わります。

-- リージョンを指定
SELECT
 user_email,
 SUM(total_bytes_processed) AS total_bytes_processed,
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 DATE(creation_time) = CURRENT_DATE('Asia/Tokyo')
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

パーティションとクラスタリング

INFORMATION_SCHEMAのビューはそれぞれパーティションやクラスタリングが指定されています。
これらのカラムをうまく利用することでクエリ処理量をへらす事ができます。

例えばジョブのメタデータであるJOBS_BY_*はcreation_timeがパーティション列、project_idとuser_emailがクラスタリング列となります。これらをうまく使うことで費用を抑える事ができます。

便利なクエリ

ユーザ毎のクエリ処理量の確認

だれがどれだけ使用しているかという情報は利用料金に直接影響してくる部分であり非常に重要です。
以下のSQLでは直近30日間に実行刺されたジョブから処理量を合計し、料金を計算しています。
asia-northeast1を想定し$6/TBをもとに算出しています。

SELECT
 user_email,
 SUM(total_bytes_processed) AS total_bytes_processed,
 -- Byte to TB
 SUM(total_bytes_processed) / 1024 / 1024 / 1024 /1024 AS total_TB_processed,
 -- TB to Dollar
 SUM(total_bytes_processed) / 1024 / 1024 / 1024 /1024 * 6.0 AS Charges_Dollar,
FROM
`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
 DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY ) AND CURRENT_DATE('Asia/Tokyo')
GROUP BY 1
ORDER BY 2 DESC

クエリ毎の処理量の確認

先程はユーザ毎でしたので今度はクエリ毎に集計してみます。

SELECT
  user_email,
  query,
  total_bytes_processed AS total_bytes_processed,
 -- Byte to TB
  total_bytes_processed / 1024 / 1024 / 1024 /1024 AS total_TB_processed,
 -- TB to Dollar
  total_bytes_processed / 1024 / 1024 / 1024 /1024 * 6.0 AS Charges_Dollar,
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY ) AND CURRENT_DATE('Asia/Tokyo')
ORDER BY 3 DESC
LIMIT 5

query列はユーザが実行したクエリが格納されます。
処理量が多いクエリからパーティションやクラスタリング等のテーブル構造の改善や、効率の良いSQLに変更するために注意喚起を行うといった行動につなげることができます。

特定のデータセット/テーブルを参照しているクエリの取得

上記のようなデータが取れるのであればたとえば特定のテーブルを参照しているかといった内容も抽出できます。
以下のクエリは直近30日以内にCOVID19オープンデータセットのテーブル(bigquery-public-data.covid19_open_data.*)がクエリ文に含まれるものをカウントします。

SELECT
  user_email,
  COUNT(user_email) as query_count
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  DATE(creation_time) BETWEEN DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -30 DAY ) AND CURRENT_DATE('Asia/Tokyo')
  AND REGEXP_CONTAINS(query, r'`bigquery-public-data.covid19_open_data.*`')
 GROUP BY 1
 ORDER BY 2 DESC

使い方としては少し力技感がありますがヒアリングを行うにはなかなか難しいこともあるのでこのような見方も役に立つかと思います。

まとめ

INFORMATION_SCHEMAは機能拡張も多く便利な機能です。
従来ながらの監査ログでも同様にスロット処理量等を求めるといったことが可能ですが、INFORMATION_SCHEMAのほうが非常にシンプルになります。
もちろん180日以内のデータという制限はありますが、これらの情報をユーザレベルからアクセスできるというのは非常に便利だと思います。

また今年のGoogle Nextのセッション(BREAKOUT DA200/BREAKOUT DA200-JP)にて管理パネルによるネイティブモニタリングという機能がBigQueryのロードマップとして発表されていました。
INFORMATION_SCHEMAも便利ですがGUIベースということで非常に期待が高いです。

今年10周年を迎えたBigQueryですが新たな機能がどんどん追加されていくので今後も楽しみですね。