こんにちは。千葉県の特産品として真っ先に思い浮かぶものがヨウ素*1な、データシステム部データ基盤ブロックの塩崎です。
この記事ではBigQueryストレージの費用を計算する方法と、費用を節約するための戦略について説明します。BigQueryストレージの費用計算をするために、まずストレージを2軸・8種類に分類し、それぞれの軸の視点から費用節約をする方法を紹介します。特にTime travel機能やFail-safe機能が関わると計算ミスをしやすくなるため、それらについても説明します。
ストレージの分類
最初にBigQueryストレージを分類するための2つの軸を説明します。1つ目の軸はライフサイクルで、これはテーブルの更新・変更・削除等の操作によって変化するものです。2つ目の軸は課金モデルで、これは非圧縮状態のデータ量で費用を計算するか圧縮済み状態のデータ量で費用を計算するかを決めるものです。
ライフサイクルは4種類の状態、課金モデルは2種類の状態が存在するため、これらの組み合わせは2*4=8種類になります。BigQueryストレージの費用の計算ミスはこれら8種類を正しく認識できていないことに起因して起こることがしばしばあります。そのため、まずはこれら8種類の分類を正しく理解することが大事です。
ライフサイクル
ここから4種類のライフサイクルの状態について説明します。
Active Current Storage
最初に説明するのはActive Current Storageです。この用語は公式ドキュメントには載っていませんがとても重要な概念です。似た用語であるActive Storageという用語が公式ドキュメントで使われている一方で、その用語の意味が文脈によってまちまちであるため、いろいろな誤解が生じています。そのため、この記事では定義が曖昧なActive Storageという用語を極力使わずにActive Current Storageという用語で説明をします。
90日以内に作成・更新されたテーブルがActive Current Storageという状態になります。テーブルをロードして作成したり、既存のテーブルにINSERT・UPDATEをすると、この状態になります。
公式ドキュメントに載っているActive Storageと本記事のActive Current Storageは似ているものの別物であることを再度強調しておきます。
Long-term Storage
Active Current Storageのテーブルを90日間更新せずにおくと、Long-term Storageに自動的に移行されます。また、Long-term Storageのテーブルに対してINSERT・UPDATE・DELETE等の更新処理を行うとActive Current Storageに戻ります。
Time travel Storage
Time travel Storageは削除されたデータが一時的に配置される場所です。ファイルシステムにおける「ゴミ箱」のような場所です。
DELETE文で削除されたデータだけではなく、UPDATE文で上書きされたデータもTime travel Storageに送られます。これは1つのUPDATE文をDELETE文+INSERT文が組み合わさったものだと考えればわかりやすいです。
Time travel Storageに配置されているデータは、テーブルがまだ存在する場合には、FOR SYSTEM_TIME AS OF 構文で参照できます。
テーブルが削除されている場合は FOR SYSTEM_TIME AS OF の代わりにテーブルデコレーターを使って参照できます。
Time travel Storageに配置されているデータは一定期間が経つと後述するFail-safe Storageに移動します。移動するまでの期間はTime travel windowという設定値で決まっています。Time travel windowの期間はデフォルトで7日で、最小2日〜最大7日の範囲で設定できます。
Fail-safe Storage
Fail-safe Storageは削除されたデータが一時的に保管される場所です。前述したTime travel Storageに保存されていたデータが次に送られる場所です。
Time travel Storageとよく似ていますが、以下の2点が異なります。
- SQLやbqコマンドなどで復元できず、復元のためにサポート問い合わせが必要
- 保存期間を上書きできる設定はなく、7日間で固定されている
Fail-safe Storageに送られてから7日間経過したデータは本当の意味で削除されます。
ライフサイクル
先ほど説明したライフサイクルの状態の変化を以下の図にまとめました。
課金モデル
ライフサイクルの次に2つの課金モデルについて説明します。
Logical課金
Logical課金は非圧縮状態のデータ量が計算式に使われる課金方法です。データ型毎のバイト数は以下のドキュメントに記載されており、テーブル内の全データに対してこの値を合算した値が使われます。
Physical課金
Physical課金は圧縮済み状態のデータ量が計算式に使われる課金方法です。データの圧縮アルゴリズムなどの詳細は公開されていませんが、こちらの記事によるとデータによっては容量が1/20程度になることもあるそうです。
なお、課金モデルはあくまで費用計算の時にのみ影響を与えるという点に注意が必要です。どちらの課金モデルを選んだとしても、実際には圧縮状態でデータが保存されています。そのため、以下のAthenaのパフォーマンスチューニングの記事にあるような圧縮・非圧縮によるパフォーマンスの差異は発生しません。
変更方法
Logical課金を採用するかPhysical課金を採用するのかはデータセット単位で変更できます。変更するためのコマンドを実行してから実際に変更されるまでには24時間かかることと、一旦変更した後14日間は再変更ができない点に注意する必要があります。
費用
ここからストレージの分類毎の費用計算について説明していきます。ライフサイクル4種類、課金モデル2種類の組み合わせは8種類あります。
それぞれの費用を以下の表にまとめました。単位はUSD / GB / monthです。この記事ではUSマルチリージョンの費用を採用しています。他のリージョンの費用は異なりますが、傾向はUSマルチリージョンと同じです。
| Logical課金 | Physical課金 | |
|---|---|---|
| Active Current Storage | 0.02 | 0.04 |
| Long-term Storage | 0.01 | 0.02 |
| Time travel Storage | 0 | 0.04 |
| Fail-safe Storage | 0 | 0.04 |
この表を使って考察をしていきます。
まずはActive Current Storageの行に着目して、Logical課金とPhysical課金について比較をしてみます。Logical課金はPhysical課金と比較して単価が半分であると分かります。しかし、Physical課金を採用すると圧縮済みのデータ量で計算されるためその影響も考慮する必要があります。圧縮の効果でデータのサイズが半分以下になるならば、Physical課金を採用したほうが安価になります。このことはLong-term Storageに対しても同様に言えます。実際に当社のBigQuery環境内にある多くのデータセットでは圧縮により半分以下のデータサイズになることが多いです。
次にTime travel Storageの行のLogical課金とPhysical課金の比較をしてみます。こちらについては先程よりも大きな違いがあります。課金モデルがLogical課金の場合はTime travel Storageの費用が無料な一方で、Physical課金の場合はActive Current Storageと同様の費用が発生します。Fail-safe Storageについても同様です。この違いを考慮せずに圧縮率だけを見てPhysical課金の方が安そうだから変更してしまうと、かえって費用を増加させてしまう可能性があります。
公式ドキュメントに載っている用語との対応
この記事で紹介した用語と公式ドキュメントの用語の対応を説明します。公式ドキュメントに書かれているActive Storageという用語の指し示すものが文脈によって異なるため注意が必要です。
料金表の用語との対応
BigQueryの料金表の用語との対応を考えます。以下の料金表には4つの用語が載っています。
- Active storage
- Long-term storage
このActive Storageという用語は、本記事のActive Current Storageのみを指し示すようにも見えますが実際には異なります。Logical課金の場合はActive Current Storageのみを指します。Physical課金の場合はActive Storageに加えてTime travel StorageとFail-safe Storageのデータ量も合計したものを指します。特にPhysical課金の場合の後半2つが抜けやすいので注意が必要です。
INFORMATION_SCHEMA.TABLE_STORAGE 系のカラムとの対応
以下の記事の INFORMATION_SCHEMA.TABLE_STORAGE 系のカラムとの対応についても説明します。
それぞれのカラムで取得できる情報が本記事のどれに対応しているのかを以下の表に示します。
| カラム名 | 非圧縮か圧縮済か | 定義 |
|---|---|---|
TOTAL_LOGICAL_BYTES |
非圧縮 | Active Current Storage + Long-term Storage |
ACTIVE_LOGICAL_BYTES |
非圧縮 | Active Current Storage |
LONG_TERM_LOGICAL_BYTES |
非圧縮 | Long-term Storage |
CURRENT_PHYSICAL_BYTES |
圧縮済 | Active Current Storage + Long-term Storage |
TOTAL_PHYSICAL_BYTES |
圧縮済 | Active Current Storage + Long-term Storage + Time travel Storage |
ACTIVE_PHYSICAL_BYTES |
圧縮済 | Active Current Storage + Time travel Storage |
LONG_TERM_PHYSICAL_BYTES |
圧縮済 | Long-term Storage |
TIME_TRAVEL_PHYSICAL_BYTES |
圧縮済 | Time travel Storage |
FAIL_SAFE_PHYSICAL_BYTES |
圧縮済 | Fail-safe Storage |
この表から注意が必要なことを読み取ってみます。
まず、Logical課金でのTime travel StorageとFail-safe Storageのデータ量を取得する方法がありません。取得できたところで結局これらの費用の単価はゼロなため取得できなくても実用上問題ないです。
次にPhysical課金にも目を向けます。Logical課金の場合の注意点は1つしかないですが、Physical課金では2つの注意点があります。まず、TOTAL_PHYSICAL_BYTESはカラム名にTOTALという名前がついているものの、Fail-safe Storageを含んでいないことに注意が必要です。また、ACTIVE_PHYSICAL_BYTESにはFail-safe Storageが含まれていないことも要注意です。特に先ほど紹介した料金表におけるActive Storageの定義と異なる点が罠になっています。料金表のActive StorageはFail-safeを含む一方で、INFORMATION_SCHEMA.ACTIVE_PHYSICAL_BYTESは含んでいません。
Data retention with time travel and fail-safe内の用語との対応
以下のドキュメントに書かれているactive bytes/active storageという用語と本記事の用語の対応について説明します。ここでのactive bytes/active storageという用語は、本記事でのActive Current Storageに該当します。
文脈によるActive Storageという用語のばらつきについて
ここまでで説明したようにActive Storageという用語（もしくはそれに似た用語）はドキュメントによって指し示すものが異なります。ある時にはActive Current StorageとTime Travel StorageとFail-safe Storageの3つを含みます。しかし、前者1つのみを指す場合も前者2つのみを指す場合もあります。そのため、Active Storageという単語を見かけた時には、その用語はどれを指しているのかを注意深く確認する必要があります。
テーブル毎・データセット毎の料金計算
この記事で紹介した知見を実践するために、テーブル毎・データセット毎の料金計算をしてみます。
まずは、以下のようにしてこの記事で紹介した4つのライフサイクルの状態毎、2つの課金モデル毎の容量をテーブル単位で取得します。Logical課金の場合のTime travel StorageとFail-safe Storageの容量は取得できませんが、それらは費用がゼロのため問題ないです。
select table_schema as dataset_name, table_name, deleted, active_logical_bytes / pow(2, 40) as active_current_logical_tb, long_term_logical_bytes / pow(2, 40) as long_term_logical_tb, (current_physical_bytes - long_term_physical_bytes) / pow(2, 40) as active_current_physical_tb, long_term_physical_bytes / pow(2, 40) as long_term_physical_tb, time_travel_physical_bytes / pow(2, 40) as time_travel_physical_tb, fail_safe_physical_bytes / pow(2, 40) as fail_safe_physical_tb, from `<プロジェクトID>`.`region-<リージョン>`.INFORMATION_SCHEMA.TABLE_STORAGE
次にそれぞれのテーブルの課金モデルがLogicalかPhysicalかを以下のクエリで取得します。課金モデルはテーブル毎ではなくデータセット毎に決定され、INFORMATION_SCHEMA.SCHAMATA_OPTIONS に格納されています。この SCHEMATA_OPTIONS はSQLアンチパターンのEntity Attribute Valueのような構造をしているのでクエリを実行する時に注意が必要です。実際に、古くからあるデータセットは SCHEMATA_OPTIONS に storage_billing_model が格納されていないため、その場合の考慮を以下のクエリでは行ってます。
select dataset_name, ifnull(storage_billing_model, "LOGICAL") as storage_billing_model, from ( select schema_name as dataset_name, from `<プロジェクトID>`.`region-<リージョン>`.INFORMATION_SCHEMA.SCHEMATA ) left join ( -- SCHEMATA_OPTIONSにstorage_billing_modelが存在しないデータセットのためにleft joinをする select schema_name as dataset_name, option_value as storage_billing_model from `<プロジェクトID>`.`region-<リージョン>`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS where option_name = 'storage_billing_model' ) using(dataset_name)
最後に上記2つのクエリの結果をデータセットで結合させ、単価を掛け算するとテーブル毎の費用となります。実際にかかっている費用だけではなく、もしもLogical課金だったらPhysical課金だったらいくらになるのかも出力します。これによって、どちらの課金モデルを採用すると費用が安くなるのかを判断できます。
with storages as ( select dataset_name, table_name, deleted, storage_billing_model, ifnull(active_current_logical_tb, 0) as active_current_logical_tb, ifnull(long_term_logical_tb, 0) as long_term_logical_tb, ifnull(active_current_physical_tb, 0) as active_current_physical_tb, ifnull(long_term_physical_tb, 0) as long_term_physical_tb, ifnull(time_travel_physical_tb, 0) as time_travel_physical_tb, ifnull(fail_safe_physical_tb, 0) as fail_safe_physical_tb, from <2つめのクエリの結果> left join <1つめのクエリの結果> using(dataset_name) ) select *, if(storage_billing_model = 'LOGICAL', monthly_logical_cost_usd, monthly_physical_cost_usd) as monthly_actual_cost_usd, from ( select *, active_current_logical_tb * 20 + long_term_logical_tb * 10 as monthly_logical_cost_usd, (active_current_physical_tb + time_travel_physical_tb + fail_safe_physical_tb) * 40 + long_term_physical_tb * 20 as monthly_physical_cost_usd, from storages ) order by monthly_logical_cost_usd + monthly_physical_cost_usd desc
そして、このクエリの実行結果をデータセットでGROUP BYするとデータセット毎のストレージ費用を計算できます。先程のクエリ結果をGROUP BYするだけなので、詳細なクエリはここでは省略します。
費用を節約するための戦略
最後にストレージ費用を削減するための戦略について紹介します。この章で書かれている内容を実践するためには開発工数が必要になることもあります。そのため、そもそもストレージ費用を削減することがベストなのかということを考える必要もあります。ストレージ費用ではなくクエリ実行費用を削減するほうが、コストパフォーマンス良く費用を削減できる可能性もあります。
パーティション分割
最初に紹介する戦略はパーティション分割です。ライフサイクルの状態がActive Current StorageになるのかLong-term Storageになるのかはテーブル単位ではなくパーティション単位で決定されます。そのため、データの変更が頻繁に発生する領域とそうでない領域を別のパーティションにすることによってLong-term Storageの比率を大きくできます。特にログを追記して蓄積するようなテーブルの場合はログのタイムスタンプでパーティション分割をすると、90日以上古いログの費用が半分になります。
また、このように設定したパーティション分割カラムはデータを参照するときのWHERE句に登場することも多いため、クエリ実行費用を削減する効果も見込めます。
Logical課金とPhysical課金の切り替え
Logical課金かPhysical課金かはデータセット単位で設定できるため、先程のクエリでどちらのほうが安いのかを判断して切り替えると費用を削減できます。一旦切り替えた後14日間は切り替えできないため、作成された直後のデータセットに対して切り替えをすることには慎重になるべきです。ある程度の期間運用し、ストレージの傾向が安定してから切り替えを行ったほうが良いです。
圧縮率が高くなるようなデータ形式への変更
以下の資料によるとデータを取り込む前に前処理を施すと圧縮率の向上が見込めるそうです。確かに圧縮率を高めることでストレージ費用を削減できます。しかし、多くのケースでは先にクエリ実行費用を削減したほうがコスパよく費用を削減できると思います。
Logical課金の方が安いテーブルとPhysical課金の方が安いテーブルのデータセットを分離
Logical課金の方が安いのかPhysical課金の方が安いのかはテーブル単位で決まる一方、どちらを採用するのかはデータセット単位でしか行えません。そのため、Logical課金のほうが安くなるテーブルとPhysical課金のほうが安くなるテーブルのデータセットを分離すると費用を削減できます。しかし、この方法は以下のような多くの副作用をもつため、基本的には採用しないほうが良いかと思っています。
- データ利用者にとって分かり辛いデータセット分類になる危険性がある
- データセットの移動をする時、Long-term Storageに配置されていたデータがActive Current Storageに移動する。そのため、かえって費用が増加するかもしれない
まとめ
この記事ではBigQueryのテーブル費用をライフサイクルと課金モデルという2軸から体系的に説明しました。特にActive Current Storageという公式ドキュメントに載っていない概念を意識するとBigQueryの料金計算に関する解像度が上がります。公式ドキュメントに載っているActive Storageという曖昧な表現を見たときにはその用語の意味を明確にして読み進める必要もあります。
ZOZOではデータアナリスト、データマネージャー等のさまざまなポジションで一緒に働く仲間を募集中です。カジュアル面談も実施しておりますので、ご興味のある方は以下のリンクからぜひご応募ください。
