本記事は2024年11月時点での内容になります。最新情報については公式サイトなどを確認願います。

Google Cloudの使用料をLooker Studioで可視化する手順

BigQueryのコストを可視化する方法については以前紹介しましたが、今回はGoogle Cloud Billing Exportを利用して、Google Cloud全体の使用料をBigQueryにエクスポートし、Looker Studioで可視化するための手順を紹介します。

ステップ 1: Google Cloud Billing Exportの設定

Google Cloud Consoleにログイン
Google Cloud Console にアクセスして、使用しているプロジェクトを選択します。
Billing Exportの有効化
- メニューから「課金」 > 「課金エクスポート」を選択。
- 「BigQueryエクスポート」をクリック。
- 使用データをエクスポートするBigQueryプロジェクトとデータセットを指定。
- 「エクスポートを有効にする」をクリックして設定完了。
  ※ 上記を行うには下記の権限（ロール）が必要です。
  - Billing Account Administrator（課金アカウント管理者）このロールは、課金アカウントの設定を変更するために必要です。
  - Billing Account Viewer（課金アカウント閲覧者）課金情報を閲覧するために必要です。
  - BigQuery Data Editor（BigQueryデータ編集者）データセットやテーブルを作成し、データを書き込む権限が必要です。
  - BigQuery Job User（BigQueryジョブユーザー）BigQuery内でのジョブ（クエリやデータの読み書き）を実行するために必要です。
データ確認
数時間から24時間以内に、エクスポートされた使用料データがBigQueryの指定したデータセット内に保存されます。主に以下のテーブルが作成されます：
- gcp_billing_export_v1_<BillingAccountID>（リソース別の課金詳細）

ステップ 2: BigQueryでのデータ確認とカスタマイズ

BigQueryにアクセス
BigQueryコンソールで、課金データが正しくエクスポートされていることを確認します。

カスタムクエリの作成（必要に応じて）
使用状況データを整理し、Looker Studioでの可視化に適した形式にするためのクエリを作成します。例えば：

SELECT
    TIMESTAMP_TRUNC(usage_start_time, DAY) AS usage_date,
    service.description AS service_name,
    sku.description AS sku_name,
    SUM(cost) AS total_cost
FROM
    `your_project_id.your_dataset_id.gcp_billing_export_v1_*`
WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20241231'
GROUP BY
    usage_date, service_name, sku_name
ORDER BY
    usage_date;

ステップ 3: Looker Studioでのデータ接続

Looker Studioにログイン
Looker Studio にアクセス。
新しいデータソースを追加
- 「データソースを追加」をクリック。
- 「BigQuery」を選択。
- 使用データを保存したプロジェクト、データセット、テーブルを選択。
スキーマ確認
フィールド（列）が正しく認識されているか確認。必要に応じてフィールドタイプを変更します（例: 日付フィールドは日付型に設定）。

ステップ 4: レポートの作成と可視化

新しいレポートを作成
- 「新しいレポートを作成」をクリック。
- 先ほど作成したデータソースをレポートに接続。
グラフやテーブルを追加
- 折れ線グラフ: 日付ごとの使用料を可視化。
- 円グラフ: サービスごとのコスト割合を表示。
- 棒グラフ: SKUごとの使用料比較。
フィルタとコントロールの追加
- フィルタ: 特定の期間やサービスに絞り込む。
- コントロール: ユーザーがインタラクティブにデータを操作できるようにする。
スタイルとフォーマット調整
見た目を整え、読みやすいデザインにする。

ステップ 5: レポートの共有

共有リンクを生成
「共有」 > 「リンクを共有可能にする」でレポートへのリンクを生成。
アクセス権の設定
特定のユーザーにのみアクセスを許可するか、リンクを知っているすべての人が閲覧できるように設定します。

クエリの詳細解説

以下は、Google Cloud BillingデータをBigQueryで加工する際のクエリ例とその解釈です。

WITH target AS(
  SELECT 
    150 AS rate, -- ドル円レート（固定値）
    6.25 AS fee_tb, -- 1TBあたりの料金（USD）
    PARSE_DATE("%Y%m%d", @DS_START_DATE) AS start_date,
    PARSE_DATE("%Y%m%d", @DS_END_DATE) AS end_date
) 
,billing as(
  SELECT 
      service.description AS service,
      TRIM(REGEXP_EXTRACT(TRIM(COALESCE(REGEXP_EXTRACT(REPLACE(sku.description,'Long-Term','Long Term'),r'^(.*?)[(-]'), sku.description)), r'^[A-Z][A-Za-z]*(?:\s[A-Z][A-Za-z]*)*')) AS sku_description, 
      sku.description AS original_sku_description,
      DATE(usage_start_time,'Asia/Tokyo') AS usage_date,
      project.id AS project_id,
      project.name AS project_name,
      CASE WHEN sku.description LIKE '%Replication%' THEN REGEXP_EXTRACT(resource.name, r'/datasets/([^\/]+)/') ELSE resource.name END AS resource_name, -- sku_descriptionがAnalysisの場合はジョブID、Storageの場合はデータセット名
      ROUND(SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)),2) AS cost_jpy,
      MIN(DATE(DATETIME(usage_start_time,'America/Los_Angeles'))) AS usage_start_date,    
      MAX(DATE(DATETIME(usage_start_time,'America/Los_Angeles'))) AS usage_end_date
  FROM `<project_id>.all_billing_data.gcp_billing_export_resource_v1_<billing_id>` 
  CROSS JOIN target
  WHERE 
    DATE(DATETIME(usage_start_time,'Asia/Tokyo')) BETWEEN start_date AND end_date
  GROUP BY ALL
  HAVING cost_jpy > 0
)
SELECT 
  project_id,
  resource_name,
  service,
  sku_description,
  usage_date,
  cost_jpy
FROM billing;

クエリの目的

BigQueryの使用料（ストレージ、クエリ、レプリケーションなど）を日付、リソース、プロジェクト単位で集計。
ドル円レートを考慮し、費用を日本円で計算。
クレジットを適用して実際のコストを反映。

重要なポイント

為替レート:
クエリ内でrateを固定値として設定していますが、Googleスプレッドシートなど外部ソースと連携することで動的な為替レートも可能。
期間指定
@DS_START_DATE、@DS_END_DATEとすることで、Looker Studioの期間フィルターで指定した期間が対象となる
リージョン:
リージョンに応じてusage_start_timeのタイムゾーンを切り替える仕組みがあります（日本時間またはアメリカ時間）。
クレジットの考慮:
UNNEST(credits)を利用して適用済みのクレジット額を費用に反映。

これで、Google Cloudの使用料をLooker Studioで可視化し、プロジェクトごとの費用をより詳細に分析できます！