G-gen のタナです。Google Cloud (旧称 GCP) で、BigQuery へエクスポートした Google Analytics 4 (GA4) のデータを Looker Studio レポートのデータソースとして使用した際に、BigQuery の料金がスパイク (想定以上に膨らむこと) してしまいました。同じ問題に直面した方のために、私の経験と解決策を共有します。

やりたかったこと
事象
原因
- 1. BigQuery のキャッシュが効かなかった
  - 症状
  - 仕様
  - 原因
- 2. Looker Studio のキャッシュが効かなかった
解決方法

やりたかったこと

Google Analytics 4 (GA4) のデータを BigQuery にエクスポートし、そのデータをカスタムクエリで Looker Studio に取り込んでからレポートで可視化しようとしました。

Google Analytics 4 (GA4) のデータをBigQueryにエクスポート
Looker Studio データソースを作成。このときカスタムクエリを使用して BigQuery からデータを抽出・集計
Looker Studio レポートでデータソースを可視化

カスタムクエリとは、Looker Studio で、単一のテーブルをデータソースとして利用する代わりに任意の SQL クエリを書いてその結果をデータソースとする機能です。

これにより、ユーザーはより柔軟にデータソースをレポート上で活用できます。例えば、特定のフィールドを選択したり、データをフィルタリングしたり、複数のテーブルを結合したりすることができます。

事象

通常では、Looker Studio が参照する BigQuery テーブルはデータマートと呼ばれる中間テーブル（特定の目的で集計済みのテーブル）にすることが推奨されます。しかしこの検証当時は開発段階だったため、データマートのスキーマが未確定でした。

そのため BigQuery 側でデータを事前に集計するのではなく、Looker Studio のカスタムクエリで集計することにしました。コストを最適化する作業は開発が落ち着いてからにしようと考えていました。

...事件は、翌日おきました。数円のはずだった BigQuery の課金が、数十万円に膨れ上がっていたのです。

原因

1. BigQuery のキャッシュが効かなかった

症状

BigQuery のクエリ履歴を検証した結果、同一のクエリが複数回実行されたのにも関わらず、キャッシュが適用されずに料金が発生していることが確認されました。

仕様

通常、BigQuery は以前に実行したクエリと全く同じものが再度実行される際は、新たに計算処理を行わず、以前のクエリ結果をキャッシュから返します。これにより、追加の料金は発生しません。

しかしながら、以下のようなシナリオではキャッシュが機能しない仕様です。

テーブルのデータに変更が生じた場合（例えば、ストリーミング挿入が受信されたり、新しい行が追加された場合）
キャッシュの有効期限である24時間が経過した場合（ただしベストエフォートでありこれより早い場合もある）

これらの詳細については、以下のドキュメントをご覧ください。

参考 : クエリのキャッシュの例外

原因

参照した対象の GA4 の BigQuery テーブルは、日付によるシャーディング（分割）が行われています。つまり GA4 の仕様で、テーブルは events_${YYYYmmdd} という形式のテーブル名で、毎日テーブルが増えていきます。このため SQL の FROM 句は events_* のように記述していました。

前述の BigQuery 公式ドキュメントでキャッシュが無効になる条件を確認したところ、条件の一つに「ワイルドカードを使用して複数のテーブルに対してクエリを実行する場合」がありました。

今回は SQL の FROM 句で events_* を指定したため、キャッシュが効かなかったのです。

2. Looker Studio のキャッシュが効かなかった

症状

BigQuery のクエリ履歴を検証したところ Looker Studio から同じクエリが何度も送信されていることが確認されました。

仕様

Looker Studio のキャッシュはコンポーネントごとに保持されます。

通常の動作では、レポートのコンポーネントがデータを要求する際、そのクエリが以前に受信したクエリと一致する場合、新たなデータ要求はキャッシュから提供されます。これにより、基本的には同じクエリが短期間で複数回、BigQuery に送信されることはありません。

参考 : キャッシュ保存の仕組み

しかし今回は、同じクエリが何度も BigQuery に送信されるという状況が発生しています。

原因 (推測)

BigQueryのクエリ履歴を確認したところ、短期間で何度も受信されたクエリの文字数が数千文字であることが判明しました。

カスタムクエリが長すぎる場合、レポートのコンポーネントがデータを要求する際に、そのクエリが以前に受信したクエリと一致するかどうか比較できず、BigQuery へクエリ結果のリクエストを送信してしまった可能性が考えられます。

ただしこの点については公式には明記されておらず、推測に過ぎません。正確な原因は判明していませんが、いずれにせよ Looker Studio レポート上のキャッシュが正しく効いているかを注意ポイントとして確認する必要があるとご理解ください。

解決方法

シャーディング分割テーブルをパーティション分割テーブルへ統合

シャーディングにより分割された複数のテーブル（events_${YYYYmmdd}）を1つのテーブルに統合することで、BigQuery のキャッシュ機能を活用することが可能となります。

また現在はシャーディングよりもパーティション分割テーブルを使うほうが推奨されています。

パーティション分割テーブルでは、パーティション列を事前に指定しておくことで、その列を WHERE 句で指定したときにスキャンデータの量を削減し、キャッシュが適用されない場合でも効率的にクエリを実行することができます。

詳細は以下の記事もご参照ください。

blog.g-gen.co.jp

データマートテーブルを利用

開発段階でも元のデータを事前集計して一度中間テーブル（データマート）に保存し、カスタムクエリからはこのデータマートテーブルからデータを参照するよう変更しました。

レポートを参照するたびに集計が行われることがなくなり、また Looker Studio 側のキャッシュも効くようになりました。これにより BigQuery のコストは安価になりました。

「開発段階ではデータマートのスキーマはころころ変わってしまう。頻繁にデータマートのスキーマ変更があると、それに合わせてカスタムクエリも変更する必要になり、開発スピードに影響する」という心配があるかもしれませんが、コストやレポートの表示パフォーマンスなどにも影響しますので、開発段階から十分にデータマートの検討をすることが推奨されます。