こんにちは。データ戦略室の小宮です。

データ戦略室では主に、弊社の各プロダクトのデータを活用しやすいようにデータを集めたり、分析基盤の構築を行なったりしています。今回は分析基盤構築の一環で作成した「BigQueryコスト管理ダッシュボード」の作り方を一部ご紹介します。

作ろうと思った背景

データ戦略室は2021年2月に立ち上がった新しい部署で、現在はデータパイプラインを構築し、必要なデータを弊社各プロダクトから収集しているフェーズです。

ですので現段階では分析基盤であるBigQueryの利用ユーザはそれほど多くはないのですが、今後データが豊富に集まって来くると「他プロダクトのデータと連携して分析したい」などのニーズが出てきて利用ユーザが増えることが想定されます。

そこで気になるのがコストです。

BigQueryはオンデマンド課金モデルなので、使った分だけ請求されます。どの部署のどのユーザがどのくらい利用しているのか分かったほうがいいよね。というわけで今回作るに至ったというわけです。

概要

前提として

GCPにはお支払いレポートというものがあります。

これはGCP内の各サービス（BigQuery, GCS, CloudFunction etc...）単位でコストを確認できます。ですが、今回見たいユーザ単位のコストは確認できないため、いくつかのサービスを併用して実現します。

利用するサービス

GCP ロギング
BigQuery
GoogleDataPortal

手順

手順は以下の4ステップです。

BigQueryのログ出力設定を行う
ログからコスト換算
GoogleDataPortalからカスタムクエリで接続する
グラフを作成する

やってみよう

1.BigQueryのログ出力設定

BigQueryのログを出力する設定を行います。

GCPの[ロギング]→[ログルータ]からシンクを作成します。

f:id:so-technologies:20210922111328p:plain — 任意のシンク名を入力します。

f:id:so-technologies:20210922111517p:plain — 任意のログの出力先を入力します。今回はBigQueryに出力します。

クエリJobのログはcloudauditのdata_accessから取得できますので、以下フィルタを入力します。 f:id:so-technologies:20210922111606p:plain

resource.type="bigquery_resource"
logName="projects/my-project/logs/cloudaudit.googleapis.com%2Fdata_access"

これでログの出力設定は完了です。

参考：

cloud.google.com

2.ログからコスト算出

ログの出力設定後、大体1〜2時間後くらいに指定したテーブルにログが吐き出されます。

f:id:so-technologies:20210922112502p:plain — cloudaudit_googleapis_com_data_accessを使用します。

あとはGoogleDataPortalに繋いで完成！と言いたいところなんですが、このログデータは結構複雑な構造をしているためそのままだと繋いでも集計できません。

ユーザごとの利用料金（USD）を算出してみます。

  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail,
    FORMAT('%9.2f',5 * (SUM(CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64))/POWER(2, 40))) AS Estimated_USD_Cost
  FROM
    `my-project.bigquery_log.cloudaudit_googleapis_com_data_access`
  WHERE JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobConfig.type") = 'QUERY'
  GROUP BY
    principalEmail

ポイントはここです

FORMAT('%9.2f',5 * (SUM(CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
        "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64))/POWER(2, 40))) AS Estimated_USD_Cost

何やらごちゃごちゃ書いていますが、 totalBilledBytesに使用したデータ量が入っているので、テラバイトに変換してコストを算出しています。（USD及びUSリージョン想定）

参考： cloud.google.com