GCPの日々の課金データをBigQueryで分析、Datalabで可視化する #GoogleCloud

Google Cloud PlatformのBilling exportを有効にすると、JSONまたはCSVで日々の課金データをCloud Storageにエクスポートできます。この課金データをBigQueryやDatalabに読み込んでクラウド利用料を分析、可視化してみます。

Billing exportから直接、BigQueryにエクスポートできないようなのでApp EngineのCron Jobsを用いて自動でインポートするようにしています。

Billing exportの有効化

Billing -> Billing export と進んで、Billing export機能を有効化します。この際に、バケット名とプレフィックスを指定できますが、後に利用するので控えておいてください。

Billing exportデータをBigQueryにインポート

現在のBilling exportデータでは日付のフォーマットがBigQueryの日付のフォーマットと異なるので変換してインポートする必要があります。

GCP Service	日付例
Billing export	2016-02-26T00:00:00-08:00
BigQuery	2016-02-26 08:00:00

Billing exportは毎日、2日程度前の課金データをCloud Storageに保存するので、定期的にBigQueryにインポートする必要があります。
App EngineのCron Jobsを用いてこのインポートを自動化するスクリプトを書いたので、よろしければお使いください。課金データは月ごとに別のテーブルに分けて保存されるようになっております。

スクリプトの利用方法

BigQueryデータセットの作成

既存のデータセットを使う場合は飛ばしてください。

$ bq mk --data_location US {データセット名}

ACLの設定

App Engineのアプリに課金データが入ったバケットへのアクセス権を与えます。

$ gsutil acl ch -u {プロジェクト名}@appspot.gserviceaccount.com:READ gs://{Billing exportで設定したバケット名}/

スクリプトのデプロイ

$ git clone https://github.com/noralife/gcp-export-billing-bq
$ cd ./gcp-export-billing-bq
$ pip install -t ./lib -r requirements.txt
$ appcfg.py update . -A {プロジェクト名} -V 1 -E BUCKET_NAME:{Billing exportで設定したバケット名} -E DATASET_ID:{データセット名} -E TABLE_HEADER:{BigQueryで利用するテーブル名のprefix} -E PROJECT_ID:{プロジェクトID} -E REPORT_HEADER:{Billing exportで入力したprefix}

このスクリプトは1日に1回、03:00(JST)に回るので、データが集まるまで数日お待ち下さい。
もし、動かないようでしたらLoggingを確認ください。

手動インポート

手元にあるBilling exportデータを手動でBigQueryにインポートすることも可能です。

$ gsutil cp gs://{Billing exportで設定したバケット名}/{billing_export_csv_filename}.csv .
$ python formatter.py {billing_export_csv_filename}.csv
$ # 新しくテーブルが必要な場合
$ bq mk --schema schema.json -t {データセット名}.{BigQueryで利用するテーブル名のprefix}_YYYYMM
$ bq insert {データセット名}.{BigQueryで利用するテーブル名のprefix}_YYYYMM {billing_export_csv_filename}.csv.json

BigQueryでの解析

BigQueryにデータインポートが完了したら、Queryを投げて解析してみます。

いくつか役に立ちそうなクエリを書いてみます。

各プロジェクトごとの利用料

SELECT project_name, sum(cost) as cost FROM [dataset.table_prefix_201603]
GROUP BY project_name

コストの高い課金アイテムの分析

SELECT description, sum(cost) as cost FROM [dataset.table_prefix_201603]
GROUP BY description
ORDER BY cost DESC

Datalabでの可視化

Datalabは2016.4.18時点でベータ版で、ベータ版利用ページから申し込むことが可能です。

デプロイ完了後、Datalabにアクセスして新しいNotebookを作ります。

関連するPythonライブラリを読み込みます

import gcp.bigquery as bq
import pandas as pd
import re
import matplotlib.pyplot as plt

BigQueryの節にもあった、プロジェクトごとの利用料をデータフレームに読み込みます。

%%sql --module cost_per_project
SELECT project_name, SUM(cost) AS cost
FROM [dataset.table_prefix_201603] 
GROUP BY project_name

表示させてみます

df = bq.Query(cost_per_project).to_dataframe()
df.head(5)

ここまでできると下記のようになると思います。

さらにこれを円グラフなどで可視化することもできます。

plt.pie(df.cost, labels=df.project_name)

その他に下記のような可視化を考えてみました。

コストの高い課金アイテムの可視化

%%sql --module billing_detail
SELECT line_item, description, SUM(cost) AS cost
FROM [dataset.table_prefix_201603]
GROUP BY line_item, description
ORDER BY cost DESC
LIMIT 20

コストの高い課金アイテムトップ20を棒グラフで比べてみます。

df = bq.Query(billing_detail).to_dataframe()
df.set_index('description').plot(kind='bar')

日々の課金変動の可視化

%%sql --module daily_billing
SELECT start_time, SUM(cost) AS cost
FROM [dataset.table_prefix_201603]
GROUP BY start_time
ORDER BY start_time

日々の課金変動を棒グラフで可視化します。

df = bq.Query(daily_billing).to_dataframe()
df.set_index('start_time').plot(kind='bar')

まとめ

Billing exportからApp Engineを利用して、BigQueryに課金データをインポートして、Datalabで可視化するところまで行いました。その他、面白いクエリ等ありましたら教えていただけると幸いです。