🔥

BigQueryのINFORMATION_SCHEMAで、ユーザー毎の料金を算出し、可視化する

2024/03/13に公開

概要

INFORMATION_SCHEMA句で、BigQueryで実行されたジョブのメタデータを取得し、ユーザー毎のクエリ料金を算出します。

料金を算出したら、無料のBIツールであるLooker Studioで時系列で可視化してみます。

最終イメージはこんな感じです。

ユーザー毎のクエリ料金算出は、BigQueryのコストをユーザ単位で可視化してみたのように、監査ログをBigQueryにインポートする方法もありますが、INFORMATION_SCHEMAを使用する方が簡単です。

料金算出のクエリ

料金を算出するクエリを解説します。

INFORMATION_SCHEMAで、プロジェクトで実行された全てのBigQueryジョブのメタデータのうち、ユーザー名・参照データ量・クエリ実行時間を取得します。
取得した全てのジョブのメタデータを、実行済みのクエリジョブだけにフィルタします。
参照データ量に、料金体系をかけて、課金額を算出します。

SELECT
  -- ユーザー名
  user_email,
  -- 課金額($) = 課金対象の参照データ量(B) × 料金体系($6/TB)
  total_bytes_billed / pow(1024, 4) * 6 AS price,
  -- クエリ実行時間
  creation_time
FROM
  -- プロジェクト単位のジョブのメタデータを取得
  `region-US`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  -- 対象のジョブを実行済みのクエリだけにフィルタ
  job_type = "QUERY" AND state = "DONE"

このクエリを実行することで、ユーザー毎のクエリ料金が時系列で取得できます。

Looker Studioで可視化

Google Cloud 備え付けの無料BIツールであるLooker Studio（旧姓：データポータル）で可視化していきましょう。

①Looker Studioにアクセス

リンクから、Looker Studioに飛びます。

②レポート作成

「空のレポート」からレポートを作成します。

③データソースの選択

「BigQuery」をデータのレポートに追加します

④データの追加

カスタムクエリを選択します。
課金プロジェクトには、料金を算出したいプロジェクトを入力します。
カスタムクエリに、上のクエリを入力して、「追加」を押します。

⑤グラフの追加

メニューバーの「グラフを追加」から、積み上げ面グラフを追加します。

⑥グラフの設定

右側のプロパティの「設定」で以下のように設定します。

「期間のディメンション」に、creation_time
「ディメンション」に、creation_time
「内訳ディメンション」に、user_email
「指標」に、priceのsum

右側のプロパティの「スタイル」で以下のように設定します。

面グラフを「累積表示」

これでだいたい完成です。

⑦時系列表示の設定

最後に、メニューバーの「コントロールを追加」から、「期間設定」を追加します。
これで、表示したい期間（creation_time）を自由に変更することができるようになりました。

あとは、見た目を自由に変えたりして、見やすくするといいと思います。

注意点

料金をクエリで算出している点

参照されたデータ量に、料金体系をかけて、課金額を算出しているため、料金体系が変わったら、クエリも見直す必要があります。

以下リンクから、最新の料金体系を確認してください。

参考

Discussion