この記事は CADDi プロダクトチーム Advent Calendar 2024 の4日目の記事です。

Data Management チームの森岡です。要らなくなったものをすぐに捨てられるデータ基盤を意識して日々開発しています。

今回は、クエリコストを下げるための方針を体系的に整理するのと、BigQueryのクエリコスト削減施策の中でとくに効果が大きかった事例について紹介しようと思います

BigQueryのクエリコストを削減する3つの基本方針

BigQuryの課金体系には、On-demandとCapacityの2つがありますが、CADDi ではOn-demandで利用しています。そのため、以降の話はOn-demand を前提としています。

On-demandの場合、クエリ実行時にスキャンされたデータ量に対して課金されます。このスキャンデータ量を削減するための方針は、大きく3つに分類できます。

行を減らす
列を減らす
回数を減らす

1. 行を減らす

1-1. パーティショニング

BigQueryのコスト削減でまず初めにやることではないでしょうか。WHERE句にパーティション列を指定することで、読み込まれるデータを特定のパーティションのみに絞ることができます。

1-2. クラスタリング

パーティショニングと似ている機能ですが、クラスタリングの方が制限が緩いため適用できるケースは多いです。しかし、制限が緩い分、効果が弱かったり扱いが難しかったりします。例えば、クラスタ化列は最大で4つまで設定できるのですが、その順番がパフォーマンスに影響を与えます。つまり、CLUSTER BY column1, column2;と CLUSTER BY column2, column1;でパフォーマンスが変わります。その他にも気にすべき点がいくつかあるので、公式ドキュメントのベストプラクティスは一読することをお勧めします。

2. 列を減らす

2-1. 必要な列だけを SELECT する

これは単純で、SELECT する列を減らせば、スキャン量は減ります。

2-2. 検索インデックス

非構造化テキストおよび半構造化JSONのカラムは巨大になりがちですが、検索インデックスを貼ることで、特定のデータを検索・抽出する際のスキャン量を減らすことができます。

3. 回数を減らす

3-1. サマリテーブルを利用する

頻繁に実行されるクエリあるいはビューは、集計結果をサマリテーブルとして保存することで効率化できます。たとえば、データ量の多い元テーブルを集計するビューが高頻度で参照されている場合、そのビューを実体化することで元テーブルが参照される回数を削減できます。

3-2. 要件の調整

当たり前ですが、高頻度で定期実行されているクエリなどは、頻度を落とすことができないかステークホルダーと調整することも大事です。

巨大JSONカラムをもつテーブルを最適化し、サービスレベルを下げずにクエリコストを90％カットした話

次に、CADDiで実際に取り組んだ施策を紹介します

リアルタイムでFirestoreと連携しているテーブルがあったのですが、このテーブルを参照するクエリのコストが月額約2,600ドルにも達しており、問題となっていました。

詳細は省きますが、このテーブルは、Stream Firestore to BigQuery という拡張機能を使ってBigQueryに連携されており、Firestoreのdocument作成や更新などの変更履歴をBigQueryの送信するという仕組みでした。送信されるデータには、更新があったdocumentのidとtimestamp、そして更新後の全データがdataというカラムにJSON形式で格納されています。そのため、documet_idに対して最新のtimestampを抽出すれば、Firestoreのデータを再現できるというものでした。

このテーブルはtimestampカラムでパーティションが切られていましたが、実際の利用ケースの多くではcreated_atを基準にフィルタリングされていました。そのため、スキャンする行を効率的に減らすにはcreated_atでパーティションを分割する必要がありますが、created_atはdataカラムのJSON内に含まれているため設定することができません。

また、すべてのデータがdataカラムのJSONに押し込まれているため、JSONの一部だけを利用しようとしても膨大な量のデータがスキャンされてしまいます。

そのため、実際に参照したいデータは少量にもかかわらず、毎回ほぼ全件スキャンされてました。

これだけでも十分に曲者なのですが、さらにユーザからの要求でニアリアルタイムでの連携が必要でした。

課題を整理すると以下になります。

有効なパーティションを設定できない
巨大なJSONカラム
ニアリアルタイム連携

このような巨大なJSONカラムをもつテーブルはFirestoreのようなNoSQLや監査ログのデータでよく見かけるので、似たような問題に直面している方も多いのではないでしょうか。

やったこと

最終的にやったことはこんな感じです。上の3つの方針を全部盛です。

ポイントは2つです

前日までのデータに関しては、JSONのパースやパーティションニングの設定を行った最適化テーブルを作成することで、パーティション分割やカラムでの絞り込みよるスキャン量削減を可能にしました。
直近のデータのビューと union することにより、ニアリアルタイムの要件に対応しました。

dbt で表現するとこんな感じです。

Raw table

version: 2

sources:
  - name: your_dataset
    tables:
      - name: raw_table
        columns:
          - name: timestamp
            description: "Partition Key"
          - name: document_id
            description: ""
          - name: data
            description: ""

Current View

{{
    config(
        materialized="view",
    )
}}

select
    json_value(data, "$.job_id") as job_id,
    json_value(data, "$.status") as status,
    json_value(data, "$.created_at") as created_at,
    json_value(data, "$.updated_at") as updated_at,
from {{ source("your_dataset", "raw_table") }}
where
    timestamp >= timestamp_sub(current_timestamp(), interval 1 day)

Optimized Table

{{
    config(
        materialized="incremental",
        incremental_strategy="merge",
        unique_key=["job_id"],
        partition_by={
            "field": "created_at",
            "data_type": "timestamp",
            "granularity": "day",
        },
        cluster_by=["created_at"],
    )
}}

select
    json_value(data, "$.job_id") as job_id,
    json_value(data, "$.status") as status,
    json_value(data, "$.created_at") as created_at,
    json_value(data, "$.updated_at") as updated_at,
from {{ source("your_dataset", "raw_table") }}
where true
    {% if (is_incremental()) %}
        and timestamp >= timestamp_sub(current_timestamp(), interval 7 day)
    {% endif %}

union_view

with
    union_all as (
        select job_id, status, created_at, updated_at
        from {{ ref("optimized_table") }}
        union all
        select job_id, status, created_at, updated_at
        from {{ ref("current_view") }}
    ),

    latest_data as (
        select *
        from union_all
        qualify row_number() over (
            partition by job_id, created_at  -- パーティションニングやクラスタリングを効かせるために created_at も必要
            order by updated_at desc
        ) = 1
    )

select job_id, status, created_at, updated_at
from latest_data

実際には、optimized_tableとcurrent_viewの加工ロジックを共通化するための中間ビューをなどがあったりしますが、おおむねこんな感じです。

この改修により、サービスレベルは落とさず、毎月2,600ドルかかっていたものを240ドルまで削減できました。

まとめ

この記事では、BigQueryのクエリコスト削減に関する3つの基本方針と、効果が高かった具体的な施策事例をご紹介しました。

dbtなどのツールの普及により、テーブル作成のハードルは大幅に下がり、現在ではさまざまなバックグラウンドを持つ方々がdbtモデルの開発に積極的に携わってくださっています。これは非常に良いことですが、その一方で、非効率なテーブルやクエリが増えることでコストがかさむリスクも生じています。

こうした背景のなかで、クエリコスト削減のための体系的な知見が十分に共有されていないと感じたことが、この記事を書いたきっかけです。

みなさまの参考なれば幸いです。

おまけ

あとから知ったのですが、こういうデータモデリングを lambda architecture と呼ぶそうです。

CADDi Tech Blog

巨大JSONカラムをもつテーブルを最適化し、サービスレベルを下げずにBigQueryのクエリコストを90％カットした話