追記
料金体系の修正によりこの裏技は 2024/11/1 から使えなくなります。
詳しくはこちらのページをごらんください。
Beginning November 1, 2024, BigQuery users will start seeing charges for Cloud Storage usage, as per pricing documentation, which was not metered before due to a billing bug:
バグのような仕様だと思ってたら上記の通りほんとにバグだったようですね。
注意事項に記載していたとおり料金体系の修正が入ってしまいました。
まだ猶予があるので裏技を使っている方はお早めにご対応ください。
以下もともと書いていた記事です。
裏技のような方法で BigQuery のストレージ料金を 1/10 に抑えることができます。
この記事ではその方法を紹介しつつ、どのような用途で使うべきか、検証結果とともにお伝えします。
TL;DR
BigQuery にはデータを置かず、 GCS のアーカイブストレージにデータを置き、アーカイブストレージに対してクエリをかけることでストレージ料金が節約できます。
料金比較
次の通り約 1/10 の料金になります。
| 保存先 | 場所 | 料金 |
|---|---|---|
| BigQuery | US | $0.020 per GB/月* |
| BigQuery | asia-northeast1 | $0.023 per GB/月 |
| GCS アーカイブ | US | $0.0024 per GB/月 |
| GCS アーカイブ | asia-northeast1 | $0.0025 per GB/月 |
*BigQuery のストレージ料金は 90 日間データの更新がないと安くなる。
実行時間と消費スロット比較
ファイル数１万程度までは BigQuery と遜色なく実行可能です。
それ以上になると消費スロットも実行時間も増えていくので注意が必要です。詳しくは後述します。
| フォーマット | ファイル数 | クラス | 実行時間(秒) | 消費スロット(分) |
|---|---|---|---|---|
| bigquery | - | - | 13.3 | 17.8 |
| csv | 100 | archive | 19.6 | 29.4 |
| csv | 1000 | archive | 20.0 | 32.3 |
| csv | 10000 | archive | 15.7 | 39.0 |
| csv | 100000 | archive | 24.6 | 264.8 |
節約になる理由
ここから考察や料金体系の確認、検証内容など長文が始まります。興味のある方は最後までお付き合いください。
節約になる理由は BigQuery を経由すると GCS からの読み取り料金がかからないからです。本来アーカイブストレージはデータ保存の料金が安い代わりに読み書きの料金が高くつきます。しかしこのバグのような仕様によって読み取りの料金を無視して安い保存料金の恩恵だけを受けることができます。
GCS からの読み取り料金が無料な理由はおそらくバッチ読み込みオペレーションが無料なためではないでしょうか。
BigQuery の料金#データ取り込みの料金
注意事項
上記の裏技ですがもちろんいつでも使えるわけではありません。下記の理由から利用については慎重に検討しなければなりません。
- Google がいつまでも BigQuery への読み込みオペレーションを無料にするとは限らない。
- 仮に有料になって BigQuery に戻そうとした場合に、アーカイブストレージは最小保存期間が設定されているため１年間データを保管したとみなされた料金がかかってしまいます。
- BigQuery は 3 ヶ月間変更されていないテーブルは長期保存料金に自動的に移行するので、戻した際にはまた通常の料金に戻ってしまいます。
- GCS オブジェクトに対するクエリには機能制限がある。
- 特にファイル数が増えた時に読み取りの時間が大きくなり、実用的な速度が出なくなる可能性があります。
- この記事ではこの速度について検証しています。
- 詳細は外部テーブルの概要#制限事項をご参照ください
- 特にファイル数が増えた時に読み取りの時間が大きくなり、実用的な速度が出なくなる可能性があります。
仮にアーカイブストレージからの読み込みが有料になる場合、予告されて最短でも 3 ヶ月は猶予があるはずです。事前に把握するためにはおそらく Blog を読んでおくとよくて、以前 BigQuery editions が発表されたときも Blog にて発表されています。
このように料金改定の可能性があるため、業務として予算を取るときは多めに見積もっておいたほうがよいです。
利用が現実的な場面
以下の３つを満たす処理です。
- GCS に置くファイル数が少ない。
- ファイルの読み取り数が少なければ BigQuery からアーカイブストレージへの読み込みが有料化されても課金が少なくて済むことが予想されます。
- また実行速度への影響も少なくて済みます。
- 実行頻度が少ない。
- 処理頻度が少なければ例えばアーカイブストレージからの呼び出しが有料になっても課金が少なくて済むことが予想されます。
- 実行速度が遅くてもよい。
- 実行速度が速くなければならない場合、後述の検証でお伝えするように実行が間に合わない可能性が出てきます。速度が遅くても良ければ問題ありません。
より具体的なケースとしてはファイル数が少ない日次や月次のバッチ処理が考えられます。
逆に分析用のデータについては頻繁に読み込まれる可能性が高いため、このやり方は推奨しません。
料金体系の確認
どれくらいのインパクトがあるのか料金体系を確認します。
ご存知の方は検証までとばしてください。
BigQuery のストレージ料金
BigQuery のストレージ料金にはアクティブストレージと長期保存の２つがあります。
90 日間連続して変更されていないテーブルは自動的に長期保存になります。
料金表の一例としては次のとおりです。単位は per GB/月 です。
| 場所 | アクティブ | 長期保存 |
|---|---|---|
| US | $0.020 | $0.015 |
| asia-northeast1 | $0.023 | $0.016 |
GCS のデータストレージ料金
GCS のデータストレージ料金はストレージクラスとバケットのロケーションによって異なります。
一例としては次の通りです。単位は per GB/月 です。
| 場所 | Standard | Nearline | Coldline | Archive |
|---|---|---|---|---|
| US | $0.026 | $0.015 | $0.007 | $0.0024 ～ |
| ASIA | $0.026 | $0.015 | $0.00875 ～ | $0.0030 |
| asia-northeast1 | $0.023 | $0.016 | $0.006 | $0.0025 |
asia-northeast1 であれば BigQuery のアクティブと Standard は同じ、長期保存と Nearline が同じになります。
また BigQuery のアクティブストレージと GCS のアーカイブストレージの料金を比較すると約 1/10 になっていることがわかります。
注意点として各クラスには最小保存期間があります。最小保存期間が経過する前にデータを削除すると、最小保存期間の期間だけ保存したとみなされる料金が課金されます。
| ストレージ クラス | 最小保存期間 |
|---|---|
| STANDARD | なし |
| NEARLINE | 30 日 |
| COLDLINE | 90 日 |
| ARCHIVE | 365 日 |
アーカイブストレージは安いとはいえ、一度置いてしまうとすぐに削除しても BigQuery のアクティブストレージに 1 ヶ月半程度置いたのと同等の金額が課金されてしまいます。
GCS のデータ処理料金
GCS に対してリクエストを投げるときにデータ処理料金がかかります。
特に Archive クラスの場合はこのデータ処理料金が大きくかかります。
単一リージョンにある場合で次の料金体系になります。（すべて 1000 オペレーションあたり）
| ストレージ | クラス A オペレーション | クラス B オペレーション | 無料のオペレーション |
|---|---|---|---|
| Standard | $0.005 | $0.0004 | 無料 |
| Nearline, Durable Reduced Availability（DRA） | $0.01~ | $0.001 | 無料 |
| Coldline | $0.02 | $0.01 ～ | 無料 |
| Archive | $0.05 | $0.05 | 無料 |
- クラス A オペレーション
- オブジェクトの追加などが該当します。
- クラス B オペレーション
- オブジェクトへのアクセスなどが該当します。
- 無料のオペレーション
- オブジェクトの削除などが該当します。
特に注目すべきはアーカイブストレージのクラス B オペレーションです。
仮にアーカイブストレージからの読み込み料金が上記の金額で課金されるようになると、BigQuery にデータを置いている場合であれば GCS からのデータの読み取り料金は当然かかりませんが、アーカイブストレージにデータを置いている場合は 1000 オペレーションごとに 0.05 $ もかかってしまいます。
アーカイブストレージに大量のファイルを置いて頻繁にアクセスすると想定外に大きな課金になる可能性があるため注意してください。
詳細は 公式ドキュメント Cloud Storage の料金 をご参照ください。
検証
注意事項の 1 つにファイル数が多いと実行速度が遅くなる可能性があると書きました。
実際のところどうなのか検証しました。
検証内容
BigQuery テーブルと GCS データのクエリ実行速度を比較します。
検証するデータは次のとおりです。
- データサイズ
- 一律 10GB（BigQuery 上のデータサイズ）
- バケットに置くファイル数
- 100KB × 100000 ファイル
- 1MB × 10000 ファイル
- 10MB × 1000 ファイル
- 100MB × 100 ファイル
- バケット上のファイルフォーマット
- CSV
- parquet
- JSON
- バケットのクラス
- Standard
- Coldline
- Archive
全部で 36 パターンになります。
検証結果
先に検証結果を紹介します。
まず各処理の処理バイト数は次のとおりです。
- BigQuery
- 11.04 GB
- Parquet
- 11.04 GB
- CSV
- 16.93 GB
- json
- 26.66 GB
Parquet であれば BigQuery に対して実行するのと同じバイト数になっています。
続いて BigQuery 上のデータと GCS 上のデータにクエリを投げた場合の実行時間や消費スロットは次のとおりです。
| フォーマット | ファイル数 | クラス | 実行時間(秒) | 消費スロット(分) |
|---|---|---|---|---|
| bigquery | - | - | 13.3 | 17.8 |
| csv | 100 | standard | 16.4 | 26.5 |
| csv | 100 | coldline | 20.7 | 28.4 |
| csv | 100 | archive | 19.6 | 29.4 |
| csv | 1000 | standard | 20.6 | 31.1 |
| csv | 1000 | coldline | 13.0 | 34.5 |
| csv | 1000 | archive | 20.0 | 32.3 |
| csv | 10000 | standard | 11.6 | 33.1 |
| csv | 10000 | coldline | 11.8 | 31.5 |
| csv | 10000 | archive | 15.7 | 39.0 |
| csv | 100000 | standard | 23.6 | 286.3 |
| csv | 100000 | coldline | 28.7 | 388.3 |
| csv | 100000 | archive | 24.6 | 264.8 |
| json | 100 | standard | 20.2 | 31.2 |
| json | 100 | coldline | 20.4 | 28.0 |
| json | 100 | archive | 19.9 | 28.6 |
| json | 1000 | standard | 18.1 | 32.4 |
| json | 1000 | coldline | 18.9 | 35.6 |
| json | 1000 | archive | 21.8 | 37.5 |
| json | 10000 | standard | 12.1 | 46.9 |
| json | 10000 | coldline | 11.1 | 41.0 |
| json | 10000 | archive | 11.8 | 37.6 |
| json | 100000 | standard | 19.6 | 242.6 |
| json | 100000 | coldline | 18.8 | 261.4 |
| json | 100000 | archive | 19.8 | 334.5 |
| parquet | 100 | standard | 11.9 | 17.1 |
| parquet | 100 | coldline | 13.2 | 16.1 |
| parquet | 100 | archive | 11.9 | 15.6 |
| parquet | 1000 | standard | 15.5 | 19.9 |
| parquet | 1000 | coldline | 16.8 | 18.0 |
| parquet | 1000 | archive | 11.7 | 19.2 |
| parquet | 10000 | standard | 12.0 | 33.6 |
| parquet | 10000 | coldline | 12.8 | 40.1 |
| parquet | 10000 | archive | 12.8 | 44.0 |
| parquet | 100000 | standard | 21.7 | 399.7 |
| parquet | 100000 | coldline | 24.2 | 374.3 |
| parquet | 100000 | archive | 20.5 | 290.8 |
ファイル数が 10000 までだとあまり差はありませんが、100000 になると実行時間や消費スロットが増えていることがわかります。
ストレージクラスの違いによる有意な差はなさそうです。特に archive では一番時間がかかると予想していましたが、なんなら parquet はむしろ archive が最速です。
したがってファイル数がどんどん増えるようなデータの保存をする場合は実行時間が増えていくことを念頭に置く必要があります。
検証手順
ダミーデータの準備
まずはダミーデータを BigQuery に生成します。
CREATE OR REPLACE TABLE
COST_TEST_SAMPLE.sample_table_10mb AS
SELECT
id,
MOD(id, 2) = 0 AS bool_example,
RAND() AS random_number1,
RAND() AS random_number2,
CONCAT('example_string_', CAST(RAND() * 1000000 AS STRING)) AS random_string1,
CONCAT('example_string_', CAST(RAND() * 1000000 AS STRING)) AS random_string2,
TIMESTAMP_ADD(TIMESTAMP '2023-01-01 00:00:00 UTC', INTERVAL CAST(RAND() * 100000 AS INT64) SECOND) AS random_timestamp1,
TIMESTAMP_ADD(TIMESTAMP '2023-01-01 00:00:00 UTC', INTERVAL CAST(RAND() * 100000 AS INT64) SECOND) AS random_timestamp2
FROM
UNNEST(GENERATE_ARRAY(1, 100000)) AS id;
これで 10 MB 程度のデータを生成できます。
GENERATE_ARRAY は 1000000 以上は指定できなかったのでここからは CROSS JOIN でデータをかさ増しします。
CREATE TABLE
COST_TEST_SAMPLE.cross_join_x_1000 AS
SELECT
id
FROM
UNNEST(GENERATE_ARRAY(1, 1000)) AS id;
CREATE OR REPLACE TABLE
COST_TEST_SAMPLE.sample_table_10gb AS
SELECT
a.*
FROM
COST_TEST_SAMPLE.sample_table_10mb AS a
CROSS JOIN
COST_TEST_SAMPLE.cross_join_x_1000;
これで約 10GB のテーブルを作成しました。
バケット の準備
今回はスタンダード、コールドライン、アーカイブの３つのバケットを用意します。
それぞれコンソールから準備しました。
バケットのデータの準備
データフォーマットの種類の準備とファイルの分割
GCS からの読み込み速度を測るために各フォーマットのデータを各バケットに用意します。
データとしては CSV, JSON, Parquet を用意します。
理由としては次のようになります。
- CSV
- 定番のデータフォーマットのため。
- JSON
- ログ出力のフォーマットとしてよく使われるため。
- Parquet
- データ自身がメタ情報を持つケースを検証したいため。
BigQuery から GCS へのエクスポートで簡単に用意できればいいのですが、残念ながらデータの分割数を指定することはできません。
今回はファイル数をコントロールしたいため、一旦 GCS に細かくデータを吐き出して、Python 経由で GCS にデータをアップロードします。
まずは GCS へデータをエクスポートします。
bq extract --destination_format CSV \
COST_TEST_SAMPLE.sample_table_10gb gs://bucket_name/export_bq_table/*.csv
続いて Python で エクスポートしたデータをダウンロードし、ファイル数をコントロールしたうえで CSV, Parquet, JSON 形式でスタンダードストレージにアップロードします。
ファイル数は 100, 1000, 10000, 100000 の場合を用意しました。
各ストレージへの移動
最後にスタンダードストレージのデータをコールドラインとアーカイブのバケットにデータをコピーします。
gsutil -m cp -r gs://source-bucket/* gs://destination-bucket/
これで GCS 上のデータの準備が整いました。
外部テーブルの作成
GCS に対してクエリを実行するためには外部テーブルを作成します。
CREATE OR REPLACE EXTERNAL TABLE
COST_TEST_SAMPLE.external_standard_csv_100_files OPTIONS (
format = 'CSV',
uris = ['gs://bucket_name/csv_1000000/*.csv']
)
;
こんな感じで 36 個外部テーブルを作成します。
クエリの実行
元データのテーブルと 各 external テーブル に対して次のような SELECT 文だけの単純なクエリを発行します。
SELECT
*
FROM
`project_name.COST_TEST_SAMPLE.sample_table_10gb`
SELECT
*
FROM
`project_name.COST_TEST_SAMPLE.external_archive_csv_1000_files`
手動では面倒なので python で各テーブルに次のように実行します。
query = f"SELECT * FROM `project_name.COST_TEST_SAMPLE.external_{storage_class}_{data_format}_{file_num}_files`"
job_config = bigquery.QueryJobConfig(use_query_cache=False)
# クエリの実行と結果の取得
query_job = client.query(query, job_config=job_config)
results = query_job.result()
# 実行時間と消費スロット時間を取得
execution_time = query_job.ended - query_job.started
slot_time = query_job.slot_millis
まとめ
繰り返しになりますが、この方法の利用については慎重に検討しなければなりません。
少なくとも BigQuery からアーカイブストレージの読み取り料金は有料の前提で考えたほうが良いのではないでしょうか。
有料になっても十分使えるような処理であれば積極的にアーカイブストレージを使ってストレージ料金を安く抑えたいところです。
検証した感想
スタンダードストレージとアーカイブストレージで有意な差がないのは驚きました。
また 1 万ファイルくらいまでは実行速度にも大きな影響がないことにも驚きました。
ただし今回はあくまで個人レベルの小さいファイルで検証したに過ぎません。
実業務で節約したくなるのは少なくとも数百 TB はあるはずで、１ファイルの大きさが 1GB だとしても数十万ファイルになるはずです。この場合は実行速度に影響が出てくるので、実際に使えるかどうかは別途検証が必要です。
こういった前提も含めて共有している検証記事が日本に増えることで日本のエンジニアの技術力が増し、結果的に国際的な競争力につながると思っています。今後も興味のあるネタがあればどんどん検証していきたいと思います。
本当はもっと大量データかついろんなパターン（例えば圧縮したり）でやってみたかったんですが、馬鹿にできない料金がかかってきます。個人でできる範囲だとこんなものかと思います。
もっと踏み込んで言えば Tech 企業が実施した本格的な検証内容を公開していただけると日本のためになると思います。
ストレージコストを真剣に考えている企業様なら必ずコストの試算や実行速度を検証したうえでこのような方法を導入するかしないか決めるはずで、どっちに転んでも公開していただければ日本のためになると真剣に思います。何ヶ月後かに本格的な検証記事が公開されることを期待しています。
Google さんへ
裏技を公開してしまいましたが値上げしないよう何卒、何卒・・・!
他社の Cloud に比べて GCS のアーカイブストレージの凄さがよく分かりました。これからも Google Cloud の良さを広めていくので何卒、何卒・・・！
おまけ
今回の検証にかかる料金は事前に計算し、少ないデータ量での料金が事前に計算したものと同じになることを確認してからより多くのデータを処理しました。
アーカイブストレージは操作するのにもお金がかかるし、データを一度置くとかなりの料金がかかるのでけっこうドキドキしました。
今回かかった料金は主にストレージの保存料金と GCS バケットに対する書き込みの料金です。
どれくらいの料金がかかったのか紹介します。
ストレージの保存料金
作ってすぐに消したときに料金がかからなければ嬉しいのですが、記事内でも紹介した通りスタンダードストレージ以外は最小保存期間が存在します。
最小保存期間の再掲です。
| ストレージ クラス | 最小保存期間 |
|---|---|
| STANDARD | なし |
| NEARLINE | 30 日 |
| COLDLINE | 90 日 |
| ARCHIVE | 365 日 |
今回の検証では COLDLINE, ARCHIVE を使用しており、各ストレージに保存したデータは約 200GB で、
COLDLINE: 200GB × 90日 / 1月 × 0.006 $ ≒ 3.6 $
ARCHIVE: 200GB × 365日 / 1月 × 0.0025 $ ≒ 6 $
で合計約 10 $ となります。
ストレージに対する書き込み料金
今回はスタンダード、コールドライン、アーカイブにそれぞれ 333300 ファイル + α を追加しています。
ファイルの追加はクラス A オペレーションに該当します。
したがって料金としては大体
(333000 / 1000 + α) * (0.005 + 0.02 + 0.05)$ ≒ 30 $
となります。
なお、処理のミスやスタンダードストレージからのコピーの料金もあるので実際にはもう少しかかっています
ということでストレージの保存料金との合計で大体 40$ くらいかかりました。
さすがに個人で万単位の金をかけるのはちょっといやだなあということで今回はこれくらいにしました。
この 40$ とクリスマス前後に技術検証をしていたことに報いたいという方は、いいねしていただけるととっても嬉しいです。