【TECH BLOG】INFORMATION_SCHEMAを用いたBigQueryのストレージ無駄遣い調査

こんにちは、『地球の歩き方ムー』創刊のニュースに心を踊らせている、データ基盤ブロックの塩崎です。

本記事では、データ基盤の管理者としてBigQueryのストレージコストの削減に取り組んだ事例を紹介します。

BigQuery費用はクエリ費用だけではない

ZOZOのデータ基盤として利用されているBigQueryは、非常にパワフルなDWH（Data WareHouse）です。しかし、それ故に利用者の意図しないところで費用が高騰することもしばしば発生します。よく問題になるのはクエリ費用の高騰であり、以下のQiita記事はBigQuery利用者の中でも有名です。

このクエリ費用の高騰に対し、我々データ基盤ブロックはこれまでに、いくつもの方法で対処してきました。具体的な取り組みの一部は以下の記事で紹介しているので、併せてご覧ください。

しかし、BigQueryの費用はクエリに関するもののみではありません。以下のドキュメントによると、BigQueryの費用はクエリに関する費用（Analytis）とストレージに関する費用（Storage）の2つがメインであることが分かります。

BigQuery pricing has two main components: Analysis pricing is the cost to process queries, including SQL queries, user-defined functions, scripts, and certain data manipulation language (DML) and data definition language (DDL) statements that scan tables. Storage pricing is the cost to store data that you load into BigQuery.

このストレージに関する費用は、USマルチリージョンの場合、1か月1GBあたり0.020 USDであり、90日間変更のないテーブルはその半額の0.010 USDに自動的に割引されます。

この単価は、Google Cloud StorageやAmazon S3などと比較しても安価であり、BigQueryの導入初期はあまり気にならないことも多いです。しかし、BigQueryをデータ基盤として長年利用すると、徐々にストレージ利用量が増加することもしばしば発生します。

現在のZOZOのデータ基盤は約100のGCPプロジェクト、約1000のデータセット、数十万以上のテーブルにまたがる大規模なものへと成長しました。これらの全てのデータを1つのチームが把握することは非現実的であるため、各GCPプロジェクト毎に管理者を立て分割統治を行っています。そのため、全てのプロジェクトの中にある、全てのテーブルのデータサイズを一覧で表示して可視化を行うダッシュボードを作成しました。そして、そのダッシュボードに基づき、不必要にストレージコストが高騰している疑いのあるテーブルを洗い出しました。それらのテーブルの情報を個別に管理者に連絡することでコストの削減に成功しました。

以降で、その具体的な流れを説明していきます。

ストレージコストの可視化

本章では、ストレージ利用量の調査から、Data Studioで可視化するまでの流れを説明します。

ストレージ利用量の調査方法

はじめに、BigQueryのストレージ利用量をダンプして1つのテーブルに集約します。BigQueryのストレージ利用量は INFORMATION_SCHEMA.PARTITIONS に格納されているので、それを参照します。

このビューの STORAGE_TIER 列を参照すると ACTIVE か LONG_TERM かが分かり、1GBあたりの単価が分かります。しかし、今回は分かりやすさのために、この部分はあえて無視していることをご了承ください。全てのプロジェクトのPARTITIONSビューを一括で取得する方法があれば楽なのですが、現時点ではそのような仕組みがないため、分割して取得します。大量のテーブルの情報を分割して取得するにあたり、特に以下の2点に注意する必要があります。

1. PARTITIONSビューから多くのテーブルの情報を取得するとエラーになる
2. 1つのテーブルに対するINSERTは1日あたり1000回の上限がある

PARTITIONSビューから多くのテーブルの情報を取得するとエラーになる注意点

1点目は、PARTITIONSビューのドキュメントにも記載のない罠であり、特に注意が必要です。多くのテーブルを保持しているデータセットに対して無邪気に以下のようなクエリを実行するとエラーになります。

続きはこちら