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



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](https://qiita-image-store.s3.amazonaws.com/0/27509/36e8ae46-df98-5e20-aaab-c81bb49481bb.png "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データセットの作成

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

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

#### ACLの設定

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

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

#### スクリプトのデプロイ

```bash
$ 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にインポートすることも可能です。

```bash
$ 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](https://qiita-image-store.s3.amazonaws.com/0/27509/c04852a7-dbd9-6380-fc5a-6e7a8820e834.png "bigquery.png")

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

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

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

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

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

## Datalabでの可視化

Datalabは2016.4.18時点でベータ版で、[ベータ版利用ページ](https://datalab.cloud.google.com/)から申し込むことが可能です。

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

![notebook.png](https://qiita-image-store.s3.amazonaws.com/0/27509/446fb779-2057-8e18-8c7a-4d390b9de122.png "notebook.png")


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

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

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

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

表示させてみます

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

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


![datalab2.png](https://qiita-image-store.s3.amazonaws.com/0/27509/c21a84a2-6e1e-c3e4-0034-3086043eb42f.png "datalab2.png")



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

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

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

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

```python
%%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を棒グラフで比べてみます。

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

### 日々の課金変動の可視化

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

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

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


## まとめ

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