Looker StudioでBigQueryクエリコストを可視化

最新版のコードは https://github.com/ajaxbarcelonacruyff/bigquery_cost に格納しています。

Looker StudioでBigQueryクエリコストを可視化する手順

Google Cloud全体のコストを知るには課金データをBigQueryにエクスポートする作業が必要で、課金の権限などがあるユーザーにエクスポートの設定をして貰う必要があります。しかし、BigQueryで

どのクエリがお金がかかっている？
誰が使っているか？
どの時間帯にクエリが多く走っているか？

といったクエリに関することだけであれば、BigQueryの権限さえあれば可視化することが可能です。
このドキュメントでは、BigQueryクエリコストを算出し、その結果をLooker Studioで可視化する手順を説明します。

ステップ 1: クエリの理解

今回のSQLクエリは以下を目的としています：

対象期間: クエリは@DS_START_DATEと@DS_END_DATEのパラメータを使用して、指定された期間のデータをフィルタリングします。これらのパラメータはLooker Studioの「期間パラメータを有効にする」オプションと統合可能です。
クエリジョブ: INFORMATION_SCHEMA.JOBSからクエリジョブデータを取得し、実行されたクエリとそのコストを分析します。
コスト計算: 以下に基づいてコストをUSDとJPYで計算します：
- total_bytes_billed: クエリで請求されたバイト数。
- fee_tb: 1TBあたりの料金（USD、デフォルトは6.25 USD/TB）。
- rate: USDからJPYへの為替レート（デフォルトは150 JPY/USD）。※Googleシートが使えれば、=GOOGLEFINANCE("CURRENCY:USDJPY")を入れたGoogleシートを使ってテーブルを作成し、そのテーブルを参照する,
ラベルとメタデータ: looker_studio_report_idやlooker_studio_datasource_idなど、スケジュールされたクエリに関連するメタデータを含みます。

WITH target AS(
SELECT 
  -- (SELECT rate FROM Googleシートを参照するテーブル LIMIT 1) AS rate, -- ドル円レート　※Googleシートが使えれば、=GOOGLEFINANCE("CURRENCY:USDJPY")を入れたGoogleシートを使ってテーブルを作成し、そのテーブルを参照する,
  150 AS rate,
  6.25 AS fee_tb, -- 1TBあたりの料金（USD）
  -- Looker StudioのBigQueryを参照する際、「期間パラメータを有効にする」オプションにチェックをいれると、Looker Studioのレポート上で指定した期間のデータを抽出可能になる。
  PARSE_DATE("%Y%m%d", @DS_START_DATE) AS start_date,  --  期間の開始日
  PARSE_DATE("%Y%m%d", @DS_END_DATE) AS end_date, --  期間の終了日
--  PARSE_DATE("%Y%m%d", "20240901") AS start_date,
--  PARSE_DATE("%Y%m%d", "20241202") AS end_date, 
),
-- クエリ費用算出用
query_jobs AS(
  -- MOLTS
  SELECT *,
  FROM `<project_id>`.`region-us`.INFORMATION_SCHEMA.JOBS	-- リージョンがUSの場合
  WHERE job_type = "QUERY" AND state = "DONE"
  AND statement_type != 'SCRIPT' -- INFORMATION_SCHEMA.JOBS にクエリを実行してクエリジョブのコストの概算を確認する場合は、SCRIPT ステートメント タイプを除外します。こうしないと、一部の値が 2 回カウントされます。SCRIPT 行には、このジョブの一部として実行されたすべての子ジョブの概要値が含まれます。
-- 別のプロジェクトでのクエリ実行も含めたい場合
--  UNION ALL
--  SELECT *,
--  FROM `<project2_id>`.`region-us`.INFORMATION_SCHEMA.JOBS	
--  WHERE job_type = "QUERY" AND state = "DONE"
--  AND statement_type != 'SCRIPT' -- INFORMATION_SCHEMA.JOBS にクエリを実行してクエリジョブのコストの概算を確認する場合は、SCRIPT ステートメント タイプを除外します。こうしないと、一部の値が 2 回カウントされます。SCRIPT 行には、このジョブの一部として実行されたすべての子ジョブの概要値が含まれます。
),
query_cost AS(
  SELECT 
  project_id,	-- クエリが実行されたプロジェクトIDなので、<project_id>か<project2_id>
  COALESCE(parent_job_id, job_id) AS parent_job_id, -- job_idの親がない場合はparent_job_idがNULLとなるため、job_idをparent_job_idとする
--  parent_job_id, -- 1つのスケジュールされたクエリに対して1つ
  job_id, -- 1つのスケジュールされたクエリ内に複数のクエリがある場合は、1つ1つにJOBIDが割り当てられる
  query, -- 実行されたクエリ（SQL文）
  user_email, -- クエリ実行者、サービスアカウント
  (SELECT value FROM UNNEST(labels) WHERE key = "looker_studio_report_id") AS looker_studio_report_id, --  --  https://lookerstudio.google.com/reporting/xxxxxx
  (SELECT value FROM UNNEST(labels) WHERE key = "looker_studio_datasource_id") AS looker_studio_datasource_id,  --  https://lookerstudio.google.com/datasources/xxxxxx
  total_slot_ms,  -- 全期間におけるスロット（ミリ秒）
  SAFE_DIVIDE(total_slot_ms,TIMESTAMP_DIFF(end_time,start_time,MILLISECOND)) AS avg_slot_ms, -- 実行されたクエリの平均使用スロット（これの合計がtotal_slot_ms）
  SAFE_DIVIDE(total_bytes_billed, POW(1024, 4)) * fee_tb *  rate AS charges_jpy, -- クエリコスト（円） ※ total_bytes_billedは1レコードに1つしか入っていないため、referenced_tablesやlabelsをUNNESTすると値がおかしくなる
  SAFE_DIVIDE(total_bytes_billed, POW(1024, 3)) AS total_gb_billed, -- クエリコスト（GB）
  SAFE_DIVIDE(total_bytes_billed, POW(1024, 4)) * fee_tb AS charges_usd, -- クエリコスト（ドル）
  DATETIME(creation_time, 'America/Los_Angeles') AS creation_time,
  DATE(creation_time,'America/Los_Angeles') AS creation_date,
  referenced_tables, -- クエリコストはテーブル別では入っていないので、UNNESTすると各テーブルにtotal_bytes_billedが入るのでtotal_bytes_billedの合計値などは重複されてしまう。
  -- referenced_tables.project_id, --参照しているテーブルのプロジェクトID。複数テーブルがあるため配列　UNNESTが必要
  -- referenced_tables.dataset_id,--参照しているテーブルのデータセットID。複数テーブルがあるため配列　UNNESTが必要
  -- referenced_tables.table__id,--参照しているテーブルのテーブル名。複数テーブルがあるため配列　UNNESTが必要
  labels	-- key='datasource_id'and value ='scheduled_query'がスケジュールされたクエリ※labelsは配列
  FROM query_jobs
  CROSS JOIN target
  -- CROSS JOIN analysis
  WHERE DATE(creation_time,'America/Los_Angeles') BETWEEN start_date AND end_date -- リージョンがUSの場合はアメリカ時刻にしておく。リージョンが東京の場合は 'Asia/Tokyo'
  AND (total_bytes_billed IS NOT NULL AND total_bytes_billed != 0)
  AND DATE(creation_time,'America/Los_Angeles') BETWEEN start_date AND end_date --  請求テーブルのデータが存在する期間のみを対象とする。リージョンがUSの場合はアメリカ時刻にしておく
),
sq AS(
  SELECT DISTINCT parent_job_id,
  FROM query_cost,
  UNNEST(labels) l
  WHERE l.key = 'data_source_id' AND l.value = 'scheduled_query'
)
SELECT q.*,
IF(sq.parent_job_id IS NOT NULL, TRUE, FALSE) AS scheduled_query -- スケジュールされたクエリか否か
FROM query_cost q LEFT JOIN sq USING(parent_job_id)

ステップ 2: Looker StudioからBigQueryに接続

Looker Studioにログイン
- Looker Studioを開き、アカウントにログインします。
新しいデータソースを追加
- 「作成」 > 「データソース」をクリックします。
- 「BigQuery」をコネクタとして選択します。
カスタムクエリオプションを選択
- BigQueryコネクタ内で「カスタムクエリ」を選択します。
- クエリが関連付けられているプロジェクトを選択します。
クエリを入力
- カスタムクエリエディタにSQLクエリを貼り付けます。
- パラメータ（例: @start_date, @end_date）を固定値に置き換えるか、Looker Studioの期間フィルターを有効にして動的フィルタリングを設定します。
認証を行う
- プロンプトが表示された場合、BigQueryプロジェクトとデータセットへのアクセスを認証します。
スキーマを確認
- クエリ内のすべてのフィールド（列）が正しく認識されていることを確認します。
- 必要に応じてフィールドのデータ型を調整します（例: charges_jpyは通貨型、creation_dateは日付型）。

ステップ 3: レポートを作成してカスタマイズ

可視化を追加
- 以下のようなチャートを使用します：
  - 棒グラフ: プロジェクトまたはユーザーごとのコストを比較。
  - 折れ線グラフ: 時間ごとのコストを可視化。
  - 円グラフ: データソースまたはプロジェクトごとのコスト分布を表示。
フィルタとコントロールを適用
- 日付範囲コントロールを追加し、クエリコストを動的にフィルタリング。
- ユーザー、プロジェクト、クエリタイプごとの次元フィルタを追加。
レポートのスタイルとフォーマットを調整
- 読みやすさと洞察の向上のため、色、フォント、レイアウトをカスタマイズします。

ステップ 4: レポートを共有

共有可能なリンクを生成
- 「共有」 > 「リンクの共有を有効にする」をクリックして共有リンクを生成します。
アクセス権を設定
- 特定のユーザーにアクセス権を付与するか、全員が閲覧可能に設定します。

クエリロジックに関する追加情報

スケジュールされたクエリ
- ラベル（例: data_source_id = 'scheduled_query'）を使用してスケジュールされたクエリを識別します。
コストの正確性
- クエリコスト（charges_jpyとcharges_usd）は、非クエリジョブを除外し、請求されたバイト数を考慮して正確に計算されます。
カスタムメトリクス
- total_slot_msやavg_slot_msのようなメトリクスは、スロット使用効率を分析するために提供されます。

これらの手順を実行することで、BigQueryのクエリコストを効果的に算出し、可視化することが可能になります。