この記事は GMOアドマーケティング Advent Calendar 2019 23日目の記事です。
こんにちは、GMOアドマーケティングの星野です。
GMOアドマーケティングでは、色々なログデータをBigQueryに保存して解析を行っています。
その中でも広告プロダクトはリクエスト数が多いだけに気がついたら保持しているデータが膨大な量になっていたことになりがちです。
BigQueryの課金はクエリ毎にも掛かりますが、保存しているデータに応じても課金されます。
クエリに関してのコスト最適化については色々な所で記事になっていますがストレージについては元々安いからか、あまり見かけません。
ストレージ利用料金が安いとは言っても、いつの間にかデータ量が増えて、コストが嵩んでいるということにならないように、今回は、BigQueryのストレージに関してのコスト最適化について紹介していきます。
BigQueryのストレージ利用料金
米国リージョンでのストレージの利用料金は以下になります。
| ストレージ種別 | 1ヶ月の料金/月 |
|---|---|
| アクティブストレージ | $0.020 |
| 長期保存 | $0.010 |
※ 日本リージョンやその他細かい条件などは公式の料金ページを参照して下さい。
長期保存は連続する 90 日間にわたってテーブルが編集されていない場合のテーブルのことを指します。
逆に、アクティブストレージは上記外の90日以内に編集したテーブルのことを指します。
使っていないと自動的に割引いてくれるのはありがたいですが、90日間更新しないデータは必要なのかどうかは検討する余地があります。
BigQueryのストレージ利用料金を把握する
どの程度のストレージを利用しているのか、90日以上更新していないデータがどの程度あるのかなどを把握していきたいと思います。
いろいろな方法があるかと思いますが、今回は頑張って、BigQueryのコンソール上で完結する方法を記載していきます。
テーブル・データセットの利用料金を把握する
まずは、テーブルの利用料金を把握していきます。
テーブルのサイズや最終更新日などはメタテーブルに格納されています。
メタテーブルはメタデータを含む特別なテーブルです。
メタテーブルには データセット名.__TABLES__ でアクセスが出来ます。
メタテーブルにて参照が可能な情報は以下になります。
| カラム名 | 型 | 説明 |
|---|---|---|
| project_id | STRING | プロジェクトID |
| dataset_id | STRING | データセット名 |
| table_id | STRING | テーブル名 |
| creation_time | INTEGER | テーブル作成日（UNIX TIME：ミリ秒） |
| last_modified_time | INTEGER | 最終更新日（UNIX TIME：ミリ秒） |
| row_count | INTEGER | 行数 |
| size_bytes | INTEGER | テーブル利用バイト数 |
| type | INTEGER | テーブルタイプ。1が通常のテーブル、2がview |
size_bytes と last_modified_time を利用すればストレージ利用料金が算出ができます。
90日を境に利用料金が変わるので last_modified_time を利用して、90日以内かどうかを判断して、利用料金を計算します。
SQLにすると以下のようになります。
|
1 2 3 4 5 6 |
SELECT CAST((CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time <= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS under_90days_size_GB, CAST((CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time > 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS over_90days_size_GB, CAST((CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time < 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*2 + (CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time >= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*1 AS INT64) as price_yen, dataset_id FROM `データセット名.__TABLES__` |
テーブル名に日付を付けていたり、テーブルの数が多いと、判断がつきにくいのでデータセットの合計に変更しましょう。
データセットの合計に変更するには、単純に dataset_id で GROUP BY をしてあげるだけです。
|
1 2 3 4 5 6 |
SELECT CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time <= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS under_90days_size_GB, CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time > 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS over_90days_size_GB, CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time < 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*2 + SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time >= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*1 AS INT64) as price_yen, dataset_id FROM `test_cvr.__TABLES__` GROUP BY dataset_id |
全データセットのストレージ利用料金を把握する
データセットのストレージ利用料金を把握することが出来ましたが、データセットがたくさんあると、一つ一つSQLを実行するのが手間なので、全データセットのストレージ利用料金を出力したくなりますよね。
__TABLES__ とは別に INFORMATION_SCHEMA という名前のビューにアクセスすることで、テーブルやデータセットなどのメタデータを取得することが出来ます。
データセットの情報は INFORMATION_SCHEMA.SCHEMATA に格納されており、以下のカラムがあります。
| 列名 | データ型 | 値 |
|---|---|---|
CATALOG_NAME |
STRING |
データセットを含むプロジェクトの名前 |
SCHEMA_NAME |
STRING |
データセットの名前（datasetId と呼ぶこともある） |
SCHEMA_OWNER |
STRING |
値は常に NULL |
CREATION_TIME |
TIMESTAMP |
データセットの作成日時 |
LAST_MODIFIED_TIME |
TIMESTAMP |
データセットの最終更新日時 |
LOCATION |
STRING |
データセットの地理的なロケーション |
INFORMATION_SCHEMA のもっと詳しい情報を知りたい場合は、公式ページも参照してみて下さい。
さて、SCHEMA_NAME を利用すればデータセット名の一覧が取得できます。
|
1 |
select SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA |
ただ、一覧は取得できますが、このままではSQLとして使うことは出来ません。
pythonなどでスクリプトを書けばSQLを生成して、実行することが出来ますが今回はBigQueryのコンソール上だけで完結させたいので、SQLを生成するSQLを書くことします。
INFORMATION_SCHEMA.SCHEMATAから取得したデータセット名をSTRING_AGGを使うことで、文字列として連結させることができます。
データセット名文字列を 「データセット合計のストレージ利用料金取得SQL」の「データセット名」にCONCATを使って、連結します。
また、このときに単に連結しただけだと、複数のSQLが生成されてしまうだけなので、UNION ALLで各データセット毎の利用料金を１つのSQLとしてまとめます。
上記をSQLで記載すると以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT CONCAT( "SELECT ", "s.under_90days_size_GB,s.over_90days_size_GB, s.price_yen ", "FROM ( \n", STRING_AGG( CONCAT( "SELECT ", "CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time <= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS under_90days_size_GB,", "CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time > 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000 AS INT64) AS over_90days_size_GB, ", "CAST(SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time < 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*2 + SUM(CASE WHEN UNIX_MILLIS(CURRENT_TIMESTAMP) - last_modified_time >= 90*24*60*60*1000 THEN size_bytes ELSE 0 END) / 1000000000*1 AS INT64) as price_yen, ", "dataset_id ", "FROM `", schema_name, ".__TABLES__` ", "GROUP BY ", "dataset_id " ), "UNION ALL \n" ),"\n) s ", "ORDER BY s.price_yen DESC") FROM INFORMATION_SCHEMA.SCHEMATA |
このSQLを実行するとSQLが出力されます。
生成されたSQLをコピペして再度実行することで、全データセットでのストレージ利用料金を確認することが出来ます。
※ コピペしたあとに余計な文字列が付いていた場合は削除しましょう
この結果をもとに利用料金が多いデータセットや90日以上利用していないデータセットなどを絞り込み、無駄なデータがないかを調べていけば、不要なデータの整理をすることが出来ます。
まとめ
今回は、データセットのストレージ利用料金を一覧化することで無駄なデータの削減をする方法をご紹介しました。
INFORMATION_SCHEMAを使えば、データセットの有効期限も取得できるので、今回のデータと合わせてみることで、効果的に無駄なデータの整理が出来ますので、こちらも機会があれば、紹介したいと思います。
明日は、S.Rさんによる「Word2Vecの紹介」です。
引き続き、GMOアドマーケティング Advent Calendar 2019 をお楽しみください！
■エンジニア採用ページ ～福利厚生や各種制度のご案内はこちら～
https://www.gmo-ap.jp/engineer/
■Wantedlyページ ～ブログや求人を公開中！～
https://www.wantedly.com/projects/199431
■エンジニア学生インターン募集中！ ～就業型インターンでアドテクの先端技術を体験しよう～
https://hrmos.co/pages/gmo-ap/jobs/0000027
