スキャン費用を最大89%削減！BigQueryの ARRAY 活用で大容量ログ分析を最適化

1. はじめに
2. 背景
3. BigQueryのARRAYによる解決
- 【Before】イベント1件＝1行（親カラムが重複してスキャンされる）
- 【After】キーごとに1行＋ARRAY
4. ARRAY運用の工夫
- 工夫1. 1行100MBの壁を chunk_number で回避
- 工夫2. 複雑化するクエリをメタデータで解決
5. 結果
6. まとめ

1. はじめに

こんにちは！ピクシブ株式会社でデータ基盤を担当しているkashiraです。

この記事では、BigQueryのARRAYを活用して、大容量ログ分析におけるスキャン費用（オンデマンド課金）を抑えつつ柔軟な分析を可能にする方法と、その効果を解説します。

対象読者は、たとえば次のような方です。

BigQueryで大容量ログを分析しており、スキャン費用やクエリ性能に課題を感じている方
キーで絞り込む分析が多く、イベント粒度のデータを直接扱いたいものの、コストがネックになっている方
LLMにSQL生成を任せたいが、データ量のガードレールに引っかかりやすく、運用に悩んでいる方

具体的には、ログのスキーマ設計とクエリの変化に加えて、1行100MB制限への対応や、UNNESTが必要な集計と不要な集計をLLMが誤りにくくするためのメタデータ設計まで扱います。

2. 背景

データマートは分析のリードタイムを下げる

近年、GeminiなどのLLMとAgent基盤の進化により、自然言語からSQLが生成できる時代になり、データ分析のハードルは下がり続けています。

従来は、ダッシュボードの表示を目的に集計済みのデータマートを用途ごとに作り込むのが主流でした。

しかし、分析の切り口や指標が増えるたびにデータマートを足していく運用は、似たようなテーブルの乱立を招きやすく、定義の揺れやメンテナンス負荷も雪だるま式に増えていきます。

その結果、データの入口が分散して「どのテーブルを参照すればよいか」が分かりにくい基盤になり、LLMにSQL生成をさせると、参照先の選択ミスや意図しない集計を誘発しやすくなります。

私たちは、LLMがさまざまな切り口で柔軟に分析できるようにするためには、データマートの追加で吸収するのではなく、DWH層のデータを「後から切れる」形で整備しておくことが重要だと考えています。 inside.pixiv.blog

pixivにおけるデータマートでは対応できない具体例

pixivに関する分析では「特定の作品に対してユーザーがどのようなアクションをしたか」を、後から自由な切り口で分析したいという要望が多くあります。

たとえば「『オリジナル』かつ『背景』のタグが付いた作品の閲覧ユニークユーザー数（UU）を知りたい」といったアドホックな要望が出た場合、データマートでは事前にその軸（タグの組み合わせ）で集計していなければ対応できません。

ここで問題になるのは、タグの絞り込みが単体ではなく組み合わせで要求されやすく、その組み合わせが事前に列挙できない点です。つまり「日付×作品」のような固定の集計粒度だけでは足りず、要望に応じて「タグの組み合わせ×作品×日付」といった粒度で集計したくなります。さらに、最初はUUだけが欲しくても、PV、リピート率、平均閲覧回数など別の指標が後から追加で求められることも珍しくありません。

このように、分析軸も指標も後出しで増える状況では、事前集計を積み上げるデータマート設計は相性が悪くなります。こうした課題を解決するには、DWH層の粒度が細かいテーブルを直接クエリできる状態にしておくのが望ましいです。

とはいえイベント粒度を直接叩くと、スキャン費用が辛い

閲覧イベント粒度のデータはデータ量が膨大になりやすく、安易にクエリを投げるのには適していません。特にLLMに自律的にクエリを生成させる場合、スキャン費用の高騰を防ぐためのシステム的なガードレールにすぐ引っかかってしまいます。その結果、『制限のせいで過去1週間分のデータしかクエリできず、長期的なトレンド分析が実行できない』といった本末転倒な事態に陥ってしまいます。

補足:
BigQueryのオンデマンドモデルでは、処理にあたってスキャンしたデータ量に応じて料金が発生します。当社では、社員のクエリ実行にオンデマンドモデルを使用しています。

Capacityモデル（定額制）を主に利用している環境では、かえってスロットを圧迫し、コストが高くなるケースもあるため、ご留意ください。 inside.pixiv.blog

3. BigQueryのARRAYによる解決

私たちは、この「柔軟な分析」と「スキャンコスト」のジレンマを、ARRAYを活用したデータの非正規化によって解決しました。

この方法は、BigQuery公式のベストプラクティスでも推奨されています。 docs.cloud.google.com

具体的にデータ構造とクエリがどう変わるか、Before/Afterを見てみましょう。

【Before】イベント1件＝1行（親カラムが重複してスキャンされる）

1回の閲覧ごとに1行作られるため、access_date や work_id のような親カラムが膨大に重複して記録・スキャンされます。

▼ サンプルデータ

access_date	work_id	user_id	view_time
2024-01-01	1001	9991	10:00:00
2024-01-01	1001	9992	10:05:00
2024-01-01	1001	9993	10:10:00

▼ 閲覧数を集計するクエリ

SELECT access_date, COUNT(*) AS view_count
FROM `project.dataset.flat_workview_logs`
WHERE work_id = 1001
GROUP BY access_date

従来は、この数TBにおよぶレコードをすべてスキャンしてカウントする必要がありました。

【After】キーごとに1行＋ARRAY

「作品×日付」で絞る分析が多い場合は、イベント行を「作品×日付の1行」に圧縮し、閲覧イベントをARRAYとしてまとめるのが効きます。

PVのような単純集計は、ARRAY を UNNEST せずに ARRAY_LENGTH の合計だけで計算できるため、イベント1件ずつをスキャンして数える必要がなくなります。

また、日付と作品IDの繰り返し入力されるデータが減ることで閲覧UUのような UNNEST する処理でもフラットなテーブルと比べてスキャンデータ量を減らすことが出来ます。

具体的な例を挙げると、access_date と work_id を主キーとし、その日に閲覧したユーザー情報などを view_user_arrayとして1行に押し込みます。

▼ サンプルデータ

access_date	work_id	view_user_array
2024-01-01	1001	`[{user_id: 9991, view_time: "10:00:00"}, {user_id: 9992, view_time: "10:05:00"}, ...]`
2024-01-01	1002	`[{user_id: 8881, view_time: "11:00:00"}]`

▼ 閲覧数を集計するクエリ

SELECT access_date, SUM(ARRAY_LENGTH(view_user_array)) AS view_count
FROM `project.dataset.array_workview_logs`
WHERE work_id = 1001
GROUP BY access_date

4. ARRAY運用の工夫

パフォーマンスが良いARRAYにもいくつか課題があります。これを解決するために、スキーマとメタデータに工夫を凝らしました。

工夫1. 1行100MBの壁を `chunk_number` で回避

BigQueryには1行あたりの最大データサイズ上限（100MB）が存在します。

pixivにはアクセスが極端に集中するメガヒット作品が存在するため、1つの作品の閲覧ユーザーをすべて1つのARRAYに詰め込むと、この上限を超えてエラーになってしまいます。

そこで、ARRAYの恩恵を維持しつつこの上限を回避するため、chunk_number という列を導入しました。

▼ 最終的なテーブル構造（サンプル）

access_date	work_id	chunk_number	view_user_array
2024-01-01	1001	1	`[{user_id: 1...}, ...]` (100万件)
2024-01-01	1001	2	`[{user_id: 1000001...}, ...]` (続きのデータ)

ARRAY内のデータ量（1ユーザーあたり time 8バイト + user_id 8バイト = 16バイト）から逆算
100MB上限に対し、理論上は1行に最大625万件まで格納可能
将来のカラム追加などの拡張性も見越し、安全マージンを取って1チャンクあたり100万件を閾値に設定

1作品の view_user_array が100万件を超えたタイミングで chunk_number をインクリメントし、新しい行として分割保存するようにしました。

工夫2. 複雑化するクエリをメタデータで解決

ARRAY化によるもう一つの課題は、クエリの複雑化です。

ARRAYを使ってクエリを作成する場合、UNNESTの要否を判別するのが難しいです。

そのため、代表的なクエリパターンはBigQueryのdescriptionに埋め込み、BigQueryのスキーマを見るだけでLLMが期待したクエリを作成してくれるようにチューニングしています。

▼ dbt descriptionの工夫（サンプル抜粋）

description: |
  このモデルは、作品詳細画面の閲覧ログを作品軸で分析しやすく加工したものです。
  ## サンプルクエリ（LLM向け）
  閲覧数を取得する場合（UNNEST不要）:
    select 
      workview_date,
      sum(array_length(user_workview_array)) as view_count
    from `project.dataset.fact_workview_logs` 
    group by workview_date
  UU数を取得する場合（UNNEST必須）:
    select
      workview_date,
      count(distinct user_workview.user_id) as unique_user_count
    from project.dataset.fact_workview_logs, unnest(user_workview_array) as user_workview
    group by workview_date

このように、UNNESTが不要なパターンと必須なパターンの具体的なサンプルクエリをテーブルdescription に持たせることで、LLMが参照すべきクエリの「型」を理解しやすくし、意図したクエリを生成できるようにしました。

5. 結果

カラムのスキーマ以外の条件(PartitionやClustering)を完全に同じにした上で、作品テーブルをjoinし、作品種別・期間を絞り込む集計において、以下の効果が出ています。

単発の閲覧数集計（UNNEST不要の ARRAY_LENGTH パターン）：
- スキャン量: 約177 GB → 約19 GB（約89%削減）
- スロット時間: 13,708,888 slot ms → 3,958,319 slot ms（約71%削減）
単発のUU数集計（UNNEST必須パターン）：
- スキャン量: 約225 GB → 約67 GB（約70%削減）
- スロット時間: 15,865,801 slot ms → 19,769,279 slot ms （約25％増加）