はじめに
FARM_FINGERPRINTは実際どのくらい速くなるのか？
BigQuery を使ったデータパイプラインで、ほぼ確実に出てくる処理があります。
tmp テーブルを取り込む
既存テーブルと比較して差分を検知
差分だけ UPDATE / INSERT（MERGE）
最初は素直に「各カラムを比較」して実装することが多いと思います。
しかし、行数やカラム数が増えてくると、急に処理が重くなることがあります。
この記事では、STRUCT + FARM_FINGERPRINT を使った差分チェックが、実務的にどのくらい効くのかを、BigQuery 視点で整理します。
よくある差分チェックの書き方
たとえば、こんな SQL を書いたことはないでしょうか。
WHERE
t.col1 <> m.col1 OR
t.col2 <> m.col2 OR
t.col3 <> m.col3
この書き方のつらいところ
- カラムが増えるたびに SQL が長くなる
- NULL / 空文字の考慮が必要
- STRING 比較が多く CPU コストが高い
- OR 条件が多く、実行計画的にも不利
データ量が少ないうちは問題ありませんが、
100万行以上 × 多カラムになると、ここがボトルネックになりがちです。
行全体をハッシュ化して比較するという考え方
そこで使えるのが、次のパターンです。
FARM_FINGERPRINT(
TO_JSON_STRING(
(SELECT AS STRUCT t.* EXCEPT (A_TIMESTAMP))
)
)
- 何をしているか
- 行全体を STRUCT にまとめる
- 更新日時など、差分判定に不要なカラムを除外
- JSON 文字列に変換
- 64bit のハッシュ値（INT64）を生成
これにより、「行の中身」を 1 つの数値で表現できます。
tmp と main を比較して差分だけ SELECT する
WITH
main AS (
SELECT
id,
FARM_FINGERPRINT(
TO_JSON_STRING(
(SELECT AS STRUCT m.* EXCEPT (A_TIMESTAMP))
)
) AS row_hash
FROM dataset.main_table m
),
tmp AS (
SELECT
id,
FARM_FINGERPRINT(
TO_JSON_STRING(
(SELECT AS STRUCT t.* EXCEPT (A_TIMESTAMP))
)
) AS row_hash
FROM dataset.tmp_table t
)
SELECT
COALESCE(t.id, m.id) AS id,
CASE
WHEN m.id IS NULL THEN 'INSERT'
WHEN t.id IS NULL THEN 'DELETE'
WHEN t.row_hash <> m.row_hash THEN 'UPDATE'
END AS diff_type
FROM main m
FULL OUTER JOIN tmp t
ON m.id = t.id
WHERE
m.id IS NULL
OR t.id IS NULL
OR t.row_hash <> m.row_hash;
この SQL で、
INSERT
UPDATE
DELETE
すべての差分を一度に取得できます。
で、どのくらい速くなるのか？
結論から言うと、条件次第で 2〜10 倍以上速くなります。
実務でよく見る目安
| 行数 × カラム数 | 生カラム比較 | row_hash比較 |
|---|---|---|
| 10万 × 5 | ほぼ同じ | ほぼ同じ |
| 100万 × 20 | 15〜30秒 | 2〜5秒 |
| 1000万 × 30 | 数分 | 10〜20秒 |
※ データ型や STRING 比率により前後します
なぜ速くなるのか（BigQuery視点）
① 比較対象が INT64 1 列になる
WHERE t.row_hash <> m.row_hash
- STRING 比較がない
- 分岐が少ない
- CPU 的に非常に軽い
② スキャンする列数が減る
BigQuery は I/O（スキャン量）支配です。
生カラム比較：複数列をスキャン
ハッシュ比較：row_hash 1 列だけ
この差が、大規模データではそのまま実行時間差になります。
注意：毎回ハッシュを計算するだけでは限界がある
WHERE
FARM_FINGERPRINT(TO_JSON_STRING(...))
<>
FARM_FINGERPRINT(TO_JSON_STRING(...))
この書き方でも SQL は綺麗になりますが、
毎回 STRUCT → JSON → HASH を計算
CPU コストはそれなりにかかる
劇的な高速化にはなりません
（体感 1.5〜2 倍程度のことが多いです）
本当に速くしたい場合の設計
row_hash を物理カラムとして持つ
row_hash INT64
INSERT / UPDATE 時だけ計算
差分チェックは数値比較のみ
WHEN MATCHED
AND m.row_hash <> s.row_hash
THEN UPDATE
これが BigQuery 差分更新の完成形です。
どんなときに使うべきか
おすすめできるケース
- 100万行以上の定期バッチ
- MERGE が遅くなってきた
- カラム数が多いテーブル
- 差分ロジックをシンプルに保ちたい
- 不要なケース
- 数万行以下の小規模データ
- 単発処理
- カラムが 2〜3 個しかない
まとめ
ハッシュ比較は万能ではない
ただし row_hash を保持する設計にすると非常に強力
BigQuery の差分処理は、SQL ではなく 設計で速くなる
差分チェックや MERGE の性能に悩んでいる場合、一度検討する価値は十分あります。