配属3ヶ月目の新人がBigQueryのストレージ課金モデルの調査をした話

はじめまして、広告事業本部に所属しているデータエンジニアの木本です。

今回はチームに配属されたばかりの新人エンジニアである僕がチームで使用しているBigQueryの費用削減の調査タスクを任せていただいた話と、実際の調査結果の共有をしようと思います。

主に僕と同じような新卒エンジニアで、初めて調査系のタスクを受け持った人、またはアドウェイズの新卒エンジニアは最初どんな業務をするのかが気になる人へ向けた記事となります。

背景
BigQuryのストレージ課金モデルの調査
BigQueryの課金モデルの種類と概要
チームのプロジェクトではどちらの課金モデルが安くなるのか検証
良かったところ
改善点
まとめ

背景

今回僕がBigQueryのストレージ課金モデルの調査をしたきっかけは、３ヶ月間のチームの目標に「インフラ費用の見直し」というものが挙がったことです。

この目標達成に向け皆で会議を行なっていたところ、チームで使用しているBigQueryのストレージ課金モデルを変更すれば月々の支払う料金を節約することができるかもしれないという案が出ました。

しかしこの時点では「節約できるかもしれない」程度の認識でしたので、本当に節約することができるのか新人である僕が調査することになりました。

BigQuryのストレージ課金モデルの調査

本件の調査項目は以下のようになります。

BigQueryの課金モデルの種類と概要
どちらの課金モデルが安くなるのか検証

これらの項目に沿って、調査した結果をまとめていこうと思います。

BigQueryの課金モデルの種類と概要

BigQueryのストレージ課金モデルには「論理バイト」と「物理バイト」と呼ばれる2種類があります。

論理バイト

BigQueryの内部ストレージに格納されるデータそのものサイズに基づいて課金されます。

従来からある課金モデルで、僕のチームではこれまでこちらの課金モデルを使ってきました。

物理バイト

データがBigQueryによって圧縮された後のサイズに基づいて課金されます。後から追加された課金モデルで、2023年７月に一般提供が開始されました。

参考URL: 新しい料金モデルで BigQuery の物理ストレージの費用を削減 | Google Cloud 公式ブログ

2つの課金モデルの違い

BigQueryはストレージにデータを保存する際にデータを圧縮してから保存します。２つの課金モデルの違いはデータがBigQueryによって圧縮される前の状態を基準に料金が決定する（論理バイト）か、圧縮後の状態を基準に料金が決定する（物理バイト）かです。

最初にこの違いを調べたとき、「物理バイトの方が圧縮された後の小さくなったデータ量に対して課金されるので、圧縮される前の大きなデータに対して課金される論理バイトよりも絶対安くなるのではないか？」と考えてしまったのですが、そう単純に決めることができない大きな違いが２つの課金モデル間にあります。

１つは保存するデータ量1GBあたりの単価の違いです。下記の表に保存するデータ量1GBあたりの「論理バイト」と「物理バイト」の単価をそれぞれ示します。リージョンはasia-northeast1です。

ストレージタイプ列の「アクティブストレージ」と「長期ストレージ」というのは、BigQueryのストレージに保存したデータの中で９０日間連続で変更がなかったかどうかで分けられたものです。９０日間連続で変化がないデータセットの場合「長期ストレージ」と呼ばれるストレージタイプに分類され、９０日間で変化があった「アクティブストレージ」と比較して料金が安くなります。

ストレージタイプ	課金モデル	月々の料金（1 GiBあたり）
アクティブストレージ	論理バイト	$0.023
アクティブストレージ	物理バイト	$0.052
長期ストレージ	論理バイト	$0.016
長期ストレージ	物理バイト	$0.026

参考URL：料金 | BigQuery: Cloud Data Warehouse | Google Cloud

上記の表を見ると、物理バイトと論理バイトの課金モデルを比較した際にアクティブストレージ、長期ストレージのどちらとも料金が約２倍になっていることがわかります。

さらにもう１つの理由として、物理バイトの基本料金の対象となるデータも論理バイトと異なるという点があります。

論理バイトでは基本料金内に含まれていたタイムトラベル機能やフェイルセーフ機能のデータといったバックアップ用に使用するデータのストレージ料金が物理バイトの基本料金内には含まれておらず、それらを使用する際は追加で料金を支払う必要があります。

参考URL： Introduction to datasets | BigQuery | Google Cloud

このように２つの課金モデルの間には課金対象となるデータの状態や範囲、単価に大きな違いがあることがわかります。

チームのプロジェクトではどちらの課金モデルが安くなるのか検証

上記を踏まえた上で、最終的に「論理バイト」と「物理バイト」のどちらを選択すればチームで使用している全データセットの料金が節約できるのか検証しようと思います。

検証の内容としては、各データセットの「論理バイトのアクティブストレージの月々の料金」と「論理バイトの長期ストレージの月々の料金」の合計から「物理バイトのアクティブストレージの月々の料金」と「物理バイトの長期ストレージの月々の料金」の合計の差額を求めようと思います。

式にすると以下のようになります。

論理バイトと物理バイトの差額＝（論理バイトのアクティブストレージの料金＋論理バイトの長期ストレージの料金）ー（物理バイトのアクティブストレージの料金＋物理バイトの長期ストレージの料金）

論理バイトと物理バイトの差額がプラスになれば論理バイトの方が料金が高く、マイナスになれば物理バイトの方が料金が高いことになります。

４つの料金の求め方はそれぞれ下記の表のようになります。

求めたい月々の料金	料金計算式
論理バイトのアクティブストレージの月々の料金	圧縮前のアクティブストレージのデータ量 × 論理バイトのアクティブストレージの単価
論理バイトの長期ストレージの月々の料金	圧縮前の長期ストレージのデータ量 × 論理バイトの長期ストレージの単価
物理バイトのアクティブストレージの月々の料金	圧縮後のアクティブストレージのデータ量 × 物理バイトのアクティブストレージの単価
物理バイトの長期ストレージの月々の料金	圧縮後の長期ストレージのデータ量 × 物理バイトの長期ストレージの単価

上記の計算式をチームが管理している全データセットに対して行いたいので、GoogleCloudの公式サイトを参考にして特定のプロジェクト配下にある全データセットの各料金を比較するクエリを書きます。

下記が作成したクエリです。

/*月々の料金（1 GiBあたり）の宣言*/
DECLARE active_logical_gb_price FLOAT64 DEFAULT 0.023;/* 論理バイト：アクティブストレージ */
DECLARE long_term_logical_gb_price FLOAT64 DEFAULT 0.016;/* 論理バイト：長期ストレージ */
DECLARE active_physical_gb_price FLOAT64 DEFAULT 0.052;/* 物理バイト：アクティブストレージ */
DECLARE long_term_physical_gb_price FLOAT64 DEFAULT 0.026; /* 物理バイト：長期ストレージ */

WITH
  storage_sizes AS (
    SELECT
      table_schema AS dataset_name,
      SUM(active_logical_bytes) / power(1024, 3) AS active_logical_gb,
      SUM(long_term_logical_bytes) / power(1024, 3) AS long_term_logical_gb,
      SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gb,
      SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gb,
      SUM(total_physical_bytes) / power(1024, 3) AS total_physical_gb,
      SUM(total_logical_bytes) / power(1024, 3) AS total_logical_gb
    FROM
       region-asia-northeast1.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
    WHERE total_logical_bytes > 0
    AND total_physical_bytes > 0
    AND table_schema NOT IN ('対象外にしたいデータセット名を記入')/* 計算対象外にしたいデータセット */
    GROUP BY 1
  )
SELECT
  dataset_name, /* データセット名が入る */
  round(active_logical_gb * active_logical_gb_price) AS `論理バイト_アクティブストレージ 単位_ドル`,
  round(long_term_logical_gb * long_term_logical_gb_price) AS `論理バイト_長期ストレージ 単位_ドル`,
  round(active_physical_gb * active_physical_gb_price) AS `物理バイト_アクティブストレージ 単位_ドル`,
  round(long_term_physical_gb * long_term_physical_gb_price) AS `物理バイト_長期ストレージ 単位_ドル`,
  round(((active_logical_gb * active_logical_gb_price) + (long_term_logical_gb * long_term_logical_gb_price))
  - ((active_physical_gb * active_physical_gb_price) + (long_term_physical_gb * long_term_physical_gb_price))) AS `差額 論理バイト - 物理バイト 単位_ドル`
FROM
  storage_sizes