Google Cloudの請求データをBigQuery＋スプレッドシートでいい感じに管理する

BQとスプシの連携機能を使うと、BQの強力な検索機能とスプシの手頃な操作感を兼ね備えた、ちょっとした請求画面をお手軽に作成することが出来ます

クラスメソッド Google Cloud Advent Calendar 2021

#Google BigQuery

#Google Spreadsheet

#Google Cloud (GCP)

渡辺聖剛

2021.12.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

本記事はクラスメソッドGoogle Cloud Advent Calendar 2021の21日目の記事です。

Google Cloudの請求データは、「お支払い（Billing）」コンソールで確認が可能です。

Cloud Billing レポート | Google Cloud

こちらで利用状況ふくめ確認が可能なのですが、より詳細に分析したかったり、外部のデータと連係したかったりする場合には、BigQueryにエクスポートして利用することになります。

Cloud Billing データを BigQuery にエクスポートする | Google Cloud

ちなみに「外部のデータと連係」というのは、以下のようなことを想定してます：

プロジェクトに対する担当者や管理者の名前や部署・連絡先などのメタ情報を別途登録・管理しておく
- 別途登録する先 = Googleスプレッドシート
部署に基づいてプロジェクトをグルーピングして利用費の合計を出す（集計）
社内情シスの担当者は、その集計された情報をもとに社内手続きを行う

図にするとこんな感じです：

以前下記のブログを書いたんですが、それはこの図でいうところの「外部テーブル」のみをCLIで如何に作るか、というものでした。

今回は実際に、この仕組み全体を組み立ててみたいと思います。
手順は以下のようになります：

請求データをBigQuery (BQ) にエクスポートする
メタ情報が記載されたスプレッドシートをBQの外部テーブルにする
ふたつをJOINして集計したビューを作る
ビューをコネクテッドシートにする
コネクテッドシートをピボットテーブルで整形して表示する

順をおって説明します。

なお今回、話を簡単にするために、スプレッドシートやGoogle Cloud上の権限周りの話は省略しています。企業所有のリソースで行う場合には必要な権限がない場合もあり得ますのでご注意ください。

1. 請求データのエクスポート

こちらは、下記公式ドキュメントに従って進めていくかたちで概ね問題無いと思います。

Cloud Billing データを BigQuery にエクスポートする | Google Cloud

ただし「3. BigQuery Data Transfer Service API を有効にする」の手順は、今回のケースだけでいえばスキップが可能です。
また、エクスポートするデータも「標準的な使用料金データ」で十分でしょう。詳細なリソースレベルでの集計が必要になる場合は「詳細な〜」を選択して下さい。

こんな感じでBQ上にデータがエクスポートされてきます。

エクスポートされてきたデータのスキーマなどの情報は、以下のドキュメントを参照ください。
また当然この時点のデータに対してクエリを行うことも可能です。例を参考に思う存分クエリをかけてみてください。

この手順で、注意したい点をいくつか。

BQのテーブルを作成するプロジェクト

既に管理用のGoogle Cloudプロジェクトがあれば良いですが、ない場合は用意しましょう。

気をつけるべき点としては、情報を収集したい請求アカウントに、そのプロジェクトもリンクされている必要がある、というところです。
ドキュメントから該当箇所を引用します：

重要: データセットの格納に選択する Cloud プロジェクトは、BigQuery データセットへのエクスポートを行うデータを格納する同じ Cloud 請求先アカウントにリンクする必要があります。

請求データは過去にさかのぼってエクスポートされない

設定をした以降のデータしか収集できません。つまり、もし1ヶ月分まるまるのデータが見たい場合は、その前月のうちにエクスポートしておかなければならない、ということです。

なので、もし手順 2. 以降の準備ができていなかったとしても、先にこの手順（エクスポート）だけはやっておくことをオススメします。

エクスポートとクエリにはBQの料金が発生する…？

実はBQには無料枠が設定されています。

無料枠 - 料金 | BigQuery: クラウドデータウェアハウス | Google Cloud

よっぽど大量のプロジェクトに対して大量のクエリをするなら分かりませんが、現に十数プロジェクトで月に合計十数万程度の利用費が発生している環境で、日に数回程度のクエリ（自動含む）をしている状態では、無料枠の範囲で収まっています。

2. スプレッドシートを外部テーブルに

まずは、キーとなる「プロジェクトID」を含む、こんな感じのシートを用意します。いわゆる「管理台帳」的なヤツです。

これを、Googleドライブをデータソースとする「外部テーブル」としてBigQueryに持っていきます。手順は長くなるので、下記ドキュメントやブログを参照して下さい。

コンソールで作った場合はこんな感じです。

外部テーブルは、手順 1. で作成したデータセットと同じところに作成する、でいいと思います。

完成したら、普通にSQLでクエリができるようになります。

3. ふたつをJOINし集計ビューを作る

SQLでクエリできるテーブルふたつをJOINするだけなので、普通にSQLの話です。
ただ折角なので、ここで利用費の集計もしてしまいましょう。作りたい表のイメージはこんな感じです：

集計年月ごとの利用費(cost)と値引き額(credit)¹を
各部門ごとに
税別で表示

実際のコスト（ cost + credit ）をここで計算していないのは、それは簡単なのでスプレッドシート上でやればいいかな、と思ったからです。
同様に、例えばこの段階ではプロジェクトごとに計算しておいて、部門ごとの集計はピボットテーブル上で行う、でもいいと思います。

SQLはこんな感じになりました：

SELECT
  BILL.invoice.month AS invoice_month,
  LIST.division AS division,
  LIST.section as section,
  SUM(BILL.cost) AS cost,
  SUM(IFNULL(
      (SELECT SUM(c.amount) FROM UNNEST(credits) c),
      0)) AS credit
FROM
  `.<データセット名>.gcp_billing_export_v1_XXXXX_XXXXX_XXXXX` AS BILL
FULL JOIN
  `.<データセット名>.project-table` AS LIST
ON
  BILL.project.id = LIST.project_id
WHERE
  LIST.project_id != ""
  AND BILL.cost_type != "tax"
GROUP BY
  invoice_month,
  division,
  section
ORDER BY
  invoice_month DESC,
  division,
  section

SQLが読める方ならお分かりの通り、1. でエクスポートしてきた請求データを BILL 、2. の外部テーブルを LIST とし、それぞれのプロジェクトIDカラムでJOINさせています。その際、

BILL.cost_type が tax のものは、税金なので計算から除外
LIST.project_id が空のものは、スプレッドシートの空行なので除外

とし、

BILL.costの合計を「利用費 (cost)」
BILL.credit.amount の合計を「値引き額 (credit)」

としています。
この計算は公式ドキュメントのサンプルを参考にさせて頂きました。

ちなみに「集計年月 (invoice.month)」というのは「何年何月分の請求書に載るデータか」というカラムです。公式ドキュメントより引用します：

費用項目を含む請求書の年と月（YYYYMM）。たとえば、「201901」は 2019 年 1 月に相当します。
このフィールドを使用して、請求書の合計金額を取得できます。

このSQLを実行して得られる結果は以下のような感じです：

Row	invoice_month	division	section	cost	credit
1	202112	Aaa部門	部署A	11457.552891999936	-10924.475987999975
2	202112	Aaa部門	部署B	10242.300844999976	-872.8940169999879
3	202112	Ccc部門	部署C	1680.8547760000026	-196.73818999999108
:	:	:	:	:	:

問題なさそうであれば、ビュー (View) として保存しておきます。ここでは適当に per-section-view としました。

4. ビューをコネクテッドシートに

そうしてビューが出来上がったら、それをスプレッドシート上で触れるようにします（コネクテッドシート）。

権限さえ通っていれば、操作はめちゃくちゃ簡単です。
スプレッドシートから「データ」>「データコネクタ」>「BigQueryに接続」をクリックし、Google Cloudのプロジェクト > データセット > テーブル（ビュー）と順に選択して最後に「接続」をクリックするだけです。問題なければ数秒程度でデータが接続され、利用できるようになります。

5. ピボットテーブルで整形して表示

あとはExcelが得意なひとなら問題ないと思います。みんな大好きピボットテーブルを作っていくだけです。

行
- division, section
列
- invoice_month (降順)
値
- cost, credit
- 計算フィールド : sum(cost)+sum(credit)

素組みした状態から、少し項目名を整えたりするとこんな感じです。

あとは、必要な項目を足すようビュー（SQL）を調整したり、ピボットテーブルでごにょごにょしたりして、使いやすいシートを構築してみて下さい。

Enjoy! の前に

その他、説明で省いた話をいくつかフォローしていきます。

金額が合わない？

実際の請求書と比較すると、小数点の丸め誤差がどうしてもつきまといます。
利用費の部門ごとの按分を考え出すと、どんなケースでも必ずつきまとう問題だと思うので、予めルールを決めておくといいと思います。

ちなみに手元の環境でいろいろ数字を見比べた結果、小数点以下は「普通に四捨五入」するのがいちばん誤差が少なかったです。

当月のデータは要らない

当月データ = 請求前のデータなので、表示する必要はないかも知れません。その場合はビューの段階で結果に含めないよう、SQLのWHERE句のところに以下を追加すると良さそうです。

  :
  AND
  BILL.invoice.month < FORMAT_DATE("%Y%m",CURRENT_DATE())
  :

あるいは「先月の分だけで良い」となった場合は、以下で良さそうです。

  :
  AND
  BILL.invoice.month = FORMAT_DATE("%Y%m",DATE_ADD(CURRENT_DATE(),INTERVAL -1 month))
  :

定期的にアップデートしたい

定期的に更新させることができます。「データ」>「データコネクタ」>「更新オプション」をクリックしてみて下さい。
個人的には「毎日朝10時〜11時の間に更新」という設定を入れて運用しています。

それからどうする？

まだ試してないんですが、BigQueryのデータをCloud Monitoringで監視させることで、予算アラートより柔軟・高機能な通知機能が出来そうな予感がしています。

いずれ試してみたいと思います！

まとめ

BQ + スプレッドシートで、ちょっとしたGoogle Cloudの社内向け請求画面を作ってみました。
もし今回でいうところの「情シス担当者」がExcelのエキスパートなら、コネクテッドシートにあげる情報は多めにして本人達でガシガシ作り込んでもらってもいいし、ただ値を見て手続きするだけなら、メンテ性を考えてSQLに担わせる部分を多くする、、などの設計が可能かと思います。

ともあれ、これだけ手軽にBigQueryのデータをスプレッドシートに相互に接続出来る環境というのは、初めて使ってみるとなかなかに感動できます。請求処理じゃなくてもいいので、「こんなことが出来る」という知識を是非脳裏に保存しておいてください！