1
/
5

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

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

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

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

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


BigQueryで150万円溶かした人の顔 - Qiita
※ ...
https://qiita.com/itkr/items/745d54c781badc148bb9


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


BigQueryの監査ログをリアルタイムに監視して使いすぎを防止してみる - ZOZO TECH BLOG
こんにちは。SRE部の塩崎です。七味唐辛子の粉末を7種類に分類するという趣味を発展させて、おっとっとを新口動物と旧口動物に分類するという趣味を最近発明しました。 BigQueryは非常にパワフルなData WareHouse(DWH) ...
https://techblog.zozo.com/entry/bigquery-overuse-alerting-system
BigQueryでの集計結果をノーコードでSlackに定期投稿してみた - ZOZO TECH BLOG
こんにちは、DATA-SREチームの塩崎です。最近気になるニュースは「ネコがマタタビを好む理由が蚊を避けるためだった」です。 さて、皆さんはデータ基盤で集計した結果をどのようにして確認していますか。LookerやPower ...
https://techblog.zozo.com/entry/bq-to-slack-with-nocode


しかし、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.
Pricing | BigQuery: Cloud Data Warehouse | Google Cloud
BigQuery is a serverless data analytics platform. You don't need to provision individual instances or virtual machines to use BigQuery. Instead, BigQuery automatically allocates computing resources as you need them. You can also reserve compute capacity a
https://cloud.google.com/bigquery/pricing


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

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

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

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

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

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


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

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


Getting table metadata using INFORMATION_SCHEMA | BigQuery
Whether your business is early in its journey or well on its way to digital transformation, Google Cloud can help you solve your toughest challenges.
https://cloud.google.com/bigquery/docs/information-schema-tables#partitions_view


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

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


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

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


続きはこちら

Invitation from 株式会社ZOZO
If this story triggered your interest, have a chat with the team?
株式会社ZOZO's job postings

Weekly ranking

Show other rankings
Like 株式会社 ZOZO's Story
Let 株式会社 ZOZO's company know you're interested in their content