DataStudioの課金レポートを新しいスキーマ対応させる #GoogleCloud

きっかけ

GCPの課金データをBigQueryにエクスポートする機能があるのですが、近くBetaが取れてGAになる予定です。
予定では2018年1月には既存の課金データのスキーマにはデータがエクスポートされなくなります。
このため、課金データを参照している色んな処理に影響が出てきます。

とはいえ、たぶんほとんどの人はDataStudioで課金データをレポートにしたりとかその程度だと思うので今回はこのDataStudioのレポートを移行したいと思います。

https://cloud.google.com/billing/docs/how-to/export-data-bigquery (英語表示にして見てください)

色々あるけどレポート作りたいだけだったら

最初に。
色々説明してるけどこれコピってデータソース自分のテーブルに変えればできるよ。

変更の内容

テーブル名の変更

Before
- gcp_billing_export_BILLING_ACCOUNT_ID
After
- gcp_billing_export_v1_BILLING_ACCOUNT_ID

テーブル名にv1と付いてるものが新しいものになります。

カラムの変更

変更
- product -> service.description
- resource_type -> sku.description
- start_time -> usage_start_time
- end_time -> usage_end_time
追加
- service.id
- sku.id
- export_time

変更されたカラムについては、今まで使ってた列をこれに変えればいいでしょう。
追加されたカラムについて、ほとんど影響がないとは思いますが、以下のようにあるので価格を正確に把握するために必要になる事もあるかもしれません。

You can use the sku.id column to map each of your line items to the list prices published on the Google Cloud Platform pricing pages and through the cloud billing catalog API. Use the export_time column to understand when the billing data was last updated.

export_timeはデータが出力された時間なのでこちらはあまり使うことはなさそう。

For v1, the partition date matches the date of the export time in UTC. For the original version, the date will not necessarily match the partition date, which is an internal processing date.

ただ、これまでは_PARTITIONTIMEが必ずしも一致しなかったのがエクスポート時間と一致するようになったみたいです。
地味にv1とレガシーの方で同一_PARTITIONTIMEでカウント取ると値が一致しないのはこういうことなんでしょう。

一番最後の方にこう書いてます。

If you need to determine which data is new, you can do so by querying export_time (v1 only). If you're using the original version, you can use a query like the following example:

SELECT partition_id, MSEC_TO_TIMESTAMP(last_modified_time)
FROM [dataset_name.table_name$__PARTITIONS_SUMMARY__]

For the above example query, you must use legacy SQL because standard SQL does not support the partition decorator separator ($).

新しいデータを判別する必要があるならexport_timeを照会しようとのことです。

とりあえず今回はそこら辺はあまり深くツッコまずに課金レポートを変更するところをやりたいと思います。

レポート作成(移行)

前のを残しつつやろうと思うので、基本コピーから作成していこうと思います。

データソースのコピー
レポートのコピー
データソース置き換え
フィールド置き換え

ベースはこちらのレポートです。

データソース

Billing

まずは課金データの本体をv1にします。

元のテーブルを参照してるデータソースを開いて、コピー。

XXXXXのコピーって名前で新しくデータソースが出来るので、XXXXX_v1とでも変更しておきましょう。

コネクションを編集します。

v1の方のテーブルを選択して再接続すると、カラムが変わってるので以下のような注意が出てきます。
普通に適用してください。

これで大元の修正は終わり。

Prediction

元のグラフでは料金の予測ビューを作ってるのでそれもv1に対応させます。
これは簡単で、元のビューのSQLで以下を変更するだけ。

テーブルをv1のテーブルに変更する
end_timeをusage_end_timeに変更する

これでv1と付いたビューを作成すれば完了です。

このビューを作った状態でこれまたPredictionのデータソースをコピーしてテーブルを再接続します。

こっちはカラムの変更がないのでそのまま適用でOKです。

BigQuery Audit

こっちはv1とか関係ないのでそのまま。

レポート

元のレポートをコピーします。

ぶっちゃけ一個ずつポチポチやるのはめんどくさすぎるのでデータソースだけ上記の方法で作って、僕のレポートコピーしてデータソースだけ入れ替えればOKです。
https://datastudio.google.com/open/1fQtvBdH_zPhDtWW84cWMWBVT68ftnSS7

新しいデータソースの所を先ほど作ったv1のデータソースに変更します。

v1にリネーム。
当然だけどend_time, product, resource_typeを参照しているオブジェクトはエラーになっています。
これらを対応するカラムに変更していけばOK。

この時コピー元を見ながら確認していくといいです。
ここらへんは地道な作業です。
ディメンションとか以外にもフィルタなどもカラム変更の影響があると思うので出てこない場合はそこら辺も確認しましょう。

まとめ

残った課題

sku.description(旧resource_type)とservice.description(旧product)がこのままだとクエリ実行時に重複したカラムとしてエラーになるので一部出力するカラム削りました
過去データの統合

カラム重複問題

1つめはけっこうめんどくさくてたぶんそのままだと解決できないし今回使ってるレポートで困ったのは2ページ目のリソースの所だけだったのでservice.descriptionを外して対応しました。
普通にカラムをポチポチ変更していくと以下のようなエラーが発生します。

Duplicate column names in the result are not supported. Found duplicate(s): description

裏側の実行されたクエリを確認すると、こういうクエリです。

SELECT
SUM(t0_copy1.cost) AS t0_copy1__cost_,
t0_copy1.project.name,
t0_copy1.service.description,
t0_copy1.sku.description
FROM (
SELECT
*
FROM
`project.dataset.gcp_billing_export_v1_0000000000
WHERE
_PARTITIONTIME BETWEEN TIMESTAMP('2017-10-25')
AND TIMESTAMP('2017-10-31')) AS t0_copy1
GROUP BY
t0_copy1.project.name,
t0_copy1.service.description,
t0_copy1.sku.description;

DataStudioはStandard SQLを組み立てて実行しているのですが、Standard SQLだとserviceの型がRecordなのでその下のdescriptionがskuのdescriptionと競合します。
そのため発生しているエラーなんですが、SQLを直すのは別に簡単なのですがここはDataStudioが生成しているクエリ。
根本的に直さないとどうしようもないので誰か対応方法分かる人いたら教えてください。

過去データの統合

これ最初に書いたとおり、レガシースキーマだと_PARTITIONTIMEがずれてたりすることがあるため日によるパーティショニングで抽出しても全てをカバーできるとは限りません。
たぶん一月トータルで見れば両方計算が合うんだろうと思うんですが、1日単位で見るとほぼ合わないです。
v1とラップして出力していることもあって、ヘタに統合すると同一のデータが出来てしまって課金額がおかしな事になるので、もうちょっと抽出方法を考える必要があります。
幸い、弊社はエクスポート始めたのが9月からで1ヶ月の差分しかないので割り切ろうと考えてます。

でも個人的に使ってる方は1年近くデータが溜まってるので出来れば統合したい。
なのでこちらはまたそのうち考えようと思います。

過去のやつはBQのデータが消えるわけではないのでレポート別で見ればまあそのままでもいいんじゃない？って気はしてます。

追記

sku.idはここのIDを参照してるので細かく見たいならこっちも見て見るといいでしょう。