BigQueryの新料金プラン見積もり方法 ストレージ料金編

Google Cloudデータエンジニアのはんざわです。

2023年7月5日にBigQueryの新料金プランがついに適用されました。

ご存知の方も多いと思いますが、これによりストレージ料金とコンピュート料金が変更されました。 今回の記事ではストレージ料金にフォーカスし、ストレージ料金の見積もり方法と安くする方法を共有したいと思います。

前置き

この記事ではストレージ料金の見積もり方法と料金を最適化する方法を紹介します。
ストレージ料金を最適化したいエンジニア向けの内容になっていると思います。

また、下記記載事項にも注意してください。

1. 2023年8月25日時点での情報になります。今後仕様が変わる可能性もありますので最新の情報は公式ドキュメントをご確認ください。
2. ここでは東京リージョンでの単価で見積もりを行なっています。他のリージョンの見積もりを行いたい場合は適宜各リージョンの単価を当てはめてください。

簡単なおさらい

従来のLogical Storage（論理ストレージ）基準の課金モデルに加え、新たにPhysical Storage（物理ストレージ）基準の課金モデルを選択することができるようになりました。
基本的に圧縮しているためPhysical Storage（物理ストレージ）の方が容量が小さくなるケースがほとんどです。
しかし、Physical Storage（物理ストレージ）の方が単価が若干高く、圧縮データの容量だけでなくタイムトラベルとフェイルセーフの容量も課金対象になるため注意が必要です。

詳細は公式ドキュメントや各種ベンダーが公開している記事を確認してください。

この記事では分かりやすくするためにLogical Storage（論理ストレージ）を非圧縮データと呼び、Physical Storage（物理ストレージ）を圧縮データと呼びます。

課金モデル毎の計算式

非圧縮データと圧縮データの料金計算式は以下の通りになります。

非圧縮データの計算式

アクティブ非圧縮データ容量 × $0.023 / GiB + 長期保存非圧縮データ容量 × $0.016 / GiB

圧縮データの計算式

（アクティブ圧縮データ容量 + タイムトラベル容量 + フェイルセーフ容量） × $0.052 / GiB + 長期保存圧縮データ容量 × $0.026 / GiB

一見すると圧縮データの方が高そうに見えますが、約4倍から12倍に圧縮されるため後述する一部ケースを除いて非圧縮データより安くなるケースがほとんどな感覚です。

課金モデルについてもっと詳しく

1. 課金モデルの適用範囲と変更方法

これまでに紹介した圧縮データと非圧縮データの課金モデルはデータセット単位で変更することが可能です。
課金モデルの変更方法は公式ドキュメントを参考にしてください。コンソールやSQL、bqコマンドで変更可能です。
課金モデルを変更すると変更が有効になるまで24時間かかり、一度データセットの課金モデルを変更すると再度課金モデルを変更するまでに14日間空ける必要があります。

また、この変更は従来のオンデマンド料金プランを継続している場合でも変更することが可能ですが、既存の定額スロットのコミットメントが残っていると変更できないようです。

2. タイムトラベルとフェイルセーフ

タイムトラベルとフェイルセーフはどちらもBigQueryのバックアップです。
これらの違いは以下の表の通りです。

タイムトラベルのリカバリ可能期間もデータセット単位で期間を設定することが可能です。
フェイルセーフはタイムトラベルのリカバリ可能期間からさらに7日間自動的にバックアップされます。2023年8月25日時点ではこの期間を変更することは出来ず、7日固定です。

圧縮データの課金モデルの場合、タイムトラベルとフェイルセーフのバックアップ容量も課金対象に含まれます。データセット毎のバックアップの重要度を考慮し、適切なタイムトラベル期間を設定することでストレージ料金をさらに最適化することが可能です。

3. どんな時に圧縮データのストレージ料金が非圧縮データより高くなるのか

結論から言うと、自分が確認した範囲ではデータが頻繁にUPSERTやDELETEが発生するテーブルは圧縮データのストレージ容量が高い傾向にありました。
原因は簡単でタイムトラベルやフェイルセーフは期間中の更新が発生する前のデータ情報をバックアップとして保持しておく必要があるためです。
タイムトラベルの期間を短縮することで料金を安くすることも可能ですので短縮した際の料金と従来の料金を比較する良いと思います。

SQLクエリでの見積もり

主に以下のドキュメントを参考にしました。

データセット毎の料金推定

実際に以下のクエリでデータセット毎の料金の推定を行いました。

-- 2023年8月25日時点の東京リージョンの単価です。必要であれば変更する
DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.023;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.016;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.052;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.026;
-- 必要であればタイムトラベルの期間を調整する
DECLARE set_tt_window INT64 DEFAULT 7;
-- SET set_tt_window = 2; -- 必要であれば2から7の数字を設定する

-- データセット毎の各容量
WITH storage_sizes_by_dataset AS (
  SELECT
    table_schema AS dataset_name,
    -- 非圧縮データ
    SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3) AS active_logical_gib,
    SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3) AS long_term_logical_gib,
    -- 圧縮データ
    -- active_physical_bytesにタイムトラベルの容量は含まれるため取り除く
    SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_tt_physical_gib,
    SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,
    -- タイムトラベル容量
    SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
    -- タイムトラベル期間を調整した容量
    SUM(time_travel_physical_bytes) * set_tt_window / 7 / power(1024, 3) AS configured_time_travel_physical_gib,
    -- フェイルセーフ容量
    SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,
  FROM
    -- 必要に応じてリージョンを変更してください。
    `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
  WHERE 
    total_physical_bytes > 0 AND
    -- 一時テーブルを削除
    table_schema NOT LIKE '%_script%'
  GROUP BY 1
),
-- データセット毎のコストの推定
storage_costs_by_dataset AS (
  SELECT
    dataset_name,
    -- 非圧縮コストの推定
    ROUND(active_logical_gib * active_logical_gib_price + long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_total_logical_cost,
    -- 通常の圧縮コストの推定
    ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price + long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_total_physical_cost,
    -- タイムトラベル期間を調整した圧縮コストの推定
    ROUND((active_no_tt_physical_gib + configured_time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price + long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_total_configured_tt_physical_cost
  FROM
    storage_sizes_by_dataset
  ORDER BY
    (active_logical_gib + active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) DESC
)

SELECT
  dataset_name AS `データセット名`,
  forecast_total_logical_cost AS `非圧縮データ費用`,
  forecast_total_physical_cost AS `圧縮データ費用`,
  forecast_total_configured_tt_physical_cost AS `タイムトラベルを削減した圧縮データ費用`,
  ROUND(forecast_total_physical_cost - forecast_total_configured_tt_physical_cost, 2) AS `削減できるタイムトラベル費用`,
  CASE
    WHEN forecast_total_logical_cost > forecast_total_physical_cost THEN 'physical'
    ELSE 'logical'
  END AS `安い方の課金モデル`,
  CASE
    WHEN forecast_total_logical_cost > forecast_total_physical_cost THEN forecast_total_physical_cost
    ELSE forecast_total_logical_cost
  END AS `安い方の課金料金`
FROM
  storage_costs_by_dataset

各テーブルのメタデータをINFORMATION_SCHEMA.TABLE_STORAGEから取得しています。

1点、見積もりを進める過程でわかりづらかった点があったので共有したいと思います。
TABLE_STORAGEのactive_physical_bytesカラムにはタイムトラベルの容量は含まれますが、フェイルセーフの容量は含まれないので注意が必要です。（Google Cloudに確認済み）
余分にタイムトラベルの容量を付け加えてしまったり、フェイルセーフの容量を付け加えなかったりすると誤った料金を見積もりをしてしまうので注意しましょう（実体験）

まとめ

今回はBigQueryのストレージ料金の見積もり方法を紹介しました。
おそらくほとんどのケースでストレージ料金を安くすることが可能だと思いますので是非費用を比較した上で変更してみてください。