BigQueryのストレージコストを削減するために実施したこと

データ&AIチームでデータエンジニアを担当している塚田です。

弊社のデータ基盤はさまざまなデータソースからデータを連携しており、そのデータを活用することで全社のデータ基盤として成り立っています。

その中で、Google Analytics for Firebaseの活用をベースにBigQueryのコストダウンした事例をご紹介できればと思います。

概要

改めて、弊社ではGoogle Analytics for Firebaseなど色々な基盤を用いてログを収集していいます。

今回はGoogle Analytics for Firebaseを用いたログの取得とその保存先であるBigQueryを利用した時により良い運用ができないかと考えコスト面での確認を進めました。

方針

一般的にBigQueryは大規模なデータセットに対してクエリを実行する基盤として利用する方が多いかと思いますが、そのデータを保持するのにもコストがかかっている部分が多いのではないでしょうか。 その中でコストがかかるものを低減できる施策として新しい料金モデルで BigQuery の物理ストレージの費用を削減が活用できないかと思い確認を進めました。

発表から数年経っているものとはなりますが、発表前から利用しているプロジェクトだとそのオプションがなかった状況になると思うので、適用した方が良いのかを考える一助となればと考えています。

利用量確認

BigQueryのドキュメントをもとに以下のテーブルを用いて利用量の現状確認を進めました region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT クエリについてはBigQueryのドキュメントにサンプルがあります。

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH
 storage_sizes AS (
   SELECT
     table_schema AS dataset_name,
     -- Logical
     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,
     -- Physical
     SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gib,
     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,
     -- Restorable previously deleted physical
     SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
     SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,
   FROM
     `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
   WHERE total_physical_bytes + fail_safe_physical_bytes > 0
     -- Base the forecast on base tables only for highest precision results
     AND table_type  = 'BASE TABLE'
     GROUP BY 1
 )
SELECT
  dataset_name,
  -- Logical
  ROUND(active_logical_gib, 2) AS active_logical_gib,
  ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
  -- Physical
  ROUND(active_physical_gib, 2) AS active_physical_gib,
  ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
  ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
  ROUND(fail_safe_physical_gib, 2) AS fail_safe_physical_gib,
  -- Compression ratio
  ROUND(SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,
  ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
  -- Forecast costs logical
  ROUND(active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,
  ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,
  -- Forecast costs physical
  ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price, 2) AS forecast_active_physical_cost,
  ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,
  -- Forecast costs total
  ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -
     (((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_cost_difference
FROM
  storage_sizes
ORDER BY
  (forecast_active_logical_cost + forecast_active_physical_cost) DESC;

このクエリを活用し結果を精査することで論理バイト ストレージ課金よりも物理バイト ストレージ課金の方が有利に働くことがあるかと思います。

弊社の環境だと物理バイト ストレージ課金による単価増よりも圧縮率が大きいためコストとしては有利に働く結果となりました。

一部の指標のみになってしまいますが、グラフの青い部分がBigQueryのストレージを含めた課金量になっており大幅にコストが下がる状況となりました。

まとめ

全ての環境で適用できるわけではありませんが、オープンクラウドを利用していく中でコストというのは常に意識していかないとならない部分だと思います。

毎日数多くのアップデートがある中で適用できそうなものがあれば検証の上柔軟に導入していければと考えており、その一例をご紹介しました。

このような改善を積み重ねながら新たな大きいアクションができるように開発を進めていきたいと考えています。