エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

BigQueryのINFORMATION_SCHEMAを利用してクエリコストを集計する

エムスリーエンジニアリンググループAI・機械学習チームの木田です。これはエムスリー Advent Calendar 2020の18日目の記事です。

Advent Calendar 2020 15日目の笹川の記事 でも紹介があったとおり、弊社ではBigQueryを中心としたデータ基盤を活用しています。BigQueryの利用コスト管理の一環で、BigQueryのベータ版機能であるINFORMATION_SCHEMAを利用したクエリコストの集計を試みましたので、それを紹介させていただきます。

INFORMATION_SCHEMAとは

INFORMATION_SCHEMAはBigQueryにおける様々なメタデータを検索するために提供された一連のビュー群です。取得できるメタデータには例えば下記のようなものがあります。

  • データセット、テーブルの一覧
  • 各テーブルのカラム定義 (カラム名、データ型、descriptionなど)
  • ジョブ(クエリ等)の実行記録 (実行ユーザー、SQL、スキャンデータ量など)

本稿執筆時点ではベータ版での提供ですが、個人的にはぜひともGAになってほしい機能の1つです。詳しくはBigQueryのドキュメントをご覧ください。 今回は3番目に挙げた "ジョブ(クエリ)の実行記録"のメタデータを使ってクエリコストの計測を試みました。

なぜ使おうと思ったのか

弊社では、各システムのクラウド化に伴い、データ基盤としてのBigQuery利用も拡大しており、利用者・管理テーブル数が共に急増しています。また、コンソール上でSQLを実行するだけではなくTableauのようなBIツール経由の利用、機械学習バッチからの利用、各種アプリケーションからの利用など、バリエーションも増えています。必然的に利用コストの方も増加しており、コスト管理がデータ基盤の運用管理上も重要な課題となっています。

f:id:takumikid2:20201217161951p:plain
cloud.google.comより BigQueryのアイコン

既にプロジェクト全体でのコスト監視や、AuditLogの集計によるユーザー単位の利用コストの把握をしてますが、それに加えて、いつ、誰が、どれくらいのコストを使っているか、どんなクエリが実行されたか、そのクエリで参照しているテーブルは何か、などをつぶさに見られるようになれば、

  • データ基盤管理者の立場としては、日々のコスト監視や、集計済データマート提供の検討に
  • 利用者の立場としては、BIツール経由等も含め、自身が発行しているクエリのコストを把握することに

役立つのではないかと考えます。これを前述のAuditLogで実現する場合の課題として、AuditLogの複雑なデータ構造が挙げられます。 BigQueryのAuditLogデータは、Cloud Logging LogEntry MessageというGCPの汎用ログフォーマット形式で格納されており、構造体が深くネストしたようなフォーマットになっています。必要な項目を探すためには、protoPayload.serviceData などの中を辿って必要なデータを取得する必要があり、項目数も多いため、取っつきにくいと感じていました。また、そのクエリで参照しているテーブルは何かを知るためには私の知る限り、発行されたSQLを解析する必要があります。

一方で、INFORMATION_SCHEMAには必要な情報がコンパクトにまとまっているため、インタラクティブなクエリによる集計・分析で使いやすいと感じており、また読取元テーブル、書込先テーブルもわかる点が利用のモチベーションとなります。

どのように計測するか

例えば以下のSQLで、あるプロジェクトにおけるユーザー別のスキャン量 (GB)とスロット数を取得可能です。 リージョン名(region-us)、プロジェクト名(project_name)、取得期間 (@from_time, @to_time)は、その時必要な条件に応じて適宜設定してください。

#standardSQL
-- 日別ユーザー別の利用スロット量、利用料金一覧を取得する
SELECT
  date (creation_time,
    'Asia/Tokyo') AS query_date_jst,
  user_email,
  sum ( total_slot_ms) AS total_slot_ms,
  sum ( total_bytes_billed / (1024 * 1024 * 1024)) AS total_gigabytes_billed,
  -- クエリコストはUSリージョンであれば $5.00 / TB
  sum ( total_bytes_billed / (1024 * 1024 * 1024 * 1024)) * 5.00 AS total_cost_usd
FROM
  `project_name`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  total_bytes_billed >0
  AND creation_time BETWEEN @from_time
  AND @to_time
GROUP BY
  1,
  2
ORDER BY
  total_gigabytes_billed DESC

これを実行するためには、bigquery.jobs.listAll 権限が必要です(「オーナー」や「BigQuery管理者」ロールが相当するため、管理者権限が必要と言えます)。

しかし一利用者の立場で、上記のような強い権限を持っているケースは稀だと思います。その際は、INFORMATION_SCHEMAのJOBS_BY_USERビューを使うことで、自分が発行したクエリに限ればユーザー集計・分析が可能です。例えば下記は自分が発行したクエリの中でコストが高いもの上位100件を取得するSQLです。

#standardSQL
-- 自分が発行した高コストクエリ上位100件を取得する
SELECT
  creation_time,
  project_id,
  job_type,
  statement_type,
  total_slot_ms,
  total_bytes_billed / (1024 * 1024* 1024) AS total_gigabytes_billed,
  sum ( total_bytes_billed / (1024 * 1024 * 1024 * 1024)) * 5.00 AS total_cost_usd,
  query,
FROM
  `project_name`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  total_bytes_billed >0
  AND creation_time BETWEEN @from_time
  AND @to_time
ORDER BY
  total_gigabytes_billed DESC
LIMIT
  100

こちらは bigquery.jobs.list権限があれば良く、「プロジェクト閲覧者」や「BigQueryユーザー」ロールを持っていれば実行可能です。

制限・注意点

現在ベータ版であることに加え、以下の2点は利用する上で特に留意しておくべきポイントと考えます。

  • INFORMATION_SCHEMAに対するクエリはキャッシュされないため、同じクエリであっても実行の度に課金が発生する点 (利用状況にもよりますが、期間指定が無いとスキャン量がそれなりに多くなる可能性があるため、creation_timeによる期間指定を推奨します)

  • ジョブの履歴は過去180日分に限定される点

したがって、運用上の要請に応じてINFORMATION_SCHEMAの取得内容を別のテーブルに保存しておく等の考慮が必要です。

まとめ

BigQueryのINFORMATION_SCHEMAを使ったクエリコスト計測の試みについて紹介しました。INFORAMTION_SCHEMAの提供するViewはAuditLogに比べ扱いやすい構造であり、比較的単純なSQLで集計結果を得ることができます。

We are hiring!

エムスリーでは、多種多様なデータが日々BigQueryに集まっており、データ分析やデータ用いた施策に活用されています。 そんな多種多様なデータを活用したサービス開発やデータマネジメントに興味がある方、よろしければぜひ以下からご応募ください!

jobs.m3.com