Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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

export_billing_setting.png

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を投げて解析してみます。
bigquery.png

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

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

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を作ります。

notebook.png

関連する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)

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

datalab2.png

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

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で可視化するところまで行いました。その他、面白いクエリ等ありましたら教えていただけると幸いです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした