BigQueryでリファクタリング前後のテーブルが一致するか検証する｜件数が多いときのハッシュ比較

2026.09.19

この記事で分かること

  • SQLリファクタリング後に「結果テーブルが一致するか」を確認するときの考え方
  • 件数が多いと全カラム比較が現実的でない理由
  • BigQueryで 各カラムをつなげてハッシュ化し、比較する 手順
  • NULL・型・列順でハッシュがズレる典型ミスと回避策

結論（先に要点）

リファクタリングの成功は、コードの見た目ではなく 結果として出力されるテーブルが一致するか で判断することが多いです。
件数が少ないうちは EXCEPT DISTINCT や全列比較でもよいですが、件数・列数が多いとコストも時間も膨らみます。

実務では次の順が扱いやすいです。

  1. 件数が一致するか
  2. 行を一意に識別できるなら、キーで突合して 行ハッシュが一致するか
  3. キーが弱い／全体の一致だけ見たいなら、行ハッシュを集約して 集合として一致するか

ハッシュは「中身が同じか」の近道です。ただし NULL・型・列の並び・空白 で値が変わるので、ルールを固定してから使います。

※ 本稿のプロジェクトID・データセット名・テーブル名・列名は、すべて説明用の仮名です。実案件の識別子はそのまま載せていません。

想定シーン：リファクタリング前後のTBLを比べる

最近、BigQuery上の開発でSQLをリファクタリングしました。
変更の意図は「読みやすく・保守しやすく・同じ結果」です。だから検証のゴールは次です。

変更前SQLで作ったテーブルと、変更後SQLで作ったテーブルが一致する

ここでいう「同じ」は、少なくとも次を満たす状態です。

  • 行数が同じ
  • （キーがあるなら）同じキーの行の中身が同じ
  • 余計な行・欠けた行がない

全てのカラムを結合し、各列を個別に比較する方法は確実ですが、列が多い・件数が多いと現実的ではありません。そこで使ったのが 各カラムを文字列化してつなぎ、ハッシュ化して比較 するやり方です。

なぜ全カラム比較は現実的でないのか

  • 列が増えるたびに比較条件が膨らむ
  • スキャン量・スロット・時間が大きくなりやすい
  • 「どの列が違うか」を見る前に、まず 違う行があるか を知りたい場面が多い

ハッシュ比較は、「行の中身を1つの指紋にまとめる」ことで、差分の有無を先に見ます。
差分が出た行だけ、あとから個別列を掘れば十分です。

手順の全体像

[変更前SQL] → table_before
[変更後SQL] → table_after
        ↓
  ステップ1：件数の比較
        ↓
  ステップ2：各行の row_hash を計算
        ↓
  ステップ3：キーによる突合またはハッシュ集合の比較

検証用テーブルは、本番を直接いじらず 一時テーブル／検証用データセット に出すのが安全です。

BigQueryでの行ハッシュの作り方

基本形（カラムをつなげて MD5）

ポイントは次です。

  • 各列を CAST(... AS STRING) する
  • NULL は空文字や番兵に置換する（CONCAT は NULL が混ざると結果が NULL になりやすい）
  • 列の順番を before / after で揃える
-- 例: 検証用。列名は実テーブルに合わせて書き換える
CREATE OR REPLACE TABLE `project.dataset.table_before` AS
SELECT * FROM ( /* 変更前SQL */ );

CREATE OR REPLACE TABLE `project.dataset.table_after` AS
SELECT * FROM ( /* 変更後SQL */ );

-- 行ハッシュを付与したビュー／テーブル（before）
CREATE OR REPLACE TABLE `project.dataset.table_before_hashed` AS
SELECT
  -- 業務キーがあるなら残す（例）
  id,
  TO_HEX(MD5(CONCAT(
    IFNULL(CAST(id AS STRING), '\\N'),
    '|',
    IFNULL(CAST(col_a AS STRING), '\\N'),
    '|',
    IFNULL(CAST(col_b AS STRING), '\\N'),
    '|',
    IFNULL(CAST(col_c AS STRING), '\\N')
    -- 必要な列を同じ順で続ける
  ))) AS row_hash
FROM `project.dataset.table_before`;

after も 同じ式・同じ列順・同じ NULL 置換 で table_after_hashed を作ります。

区切り文字 | は、データに含まれにくいものを選びます。列の中に | が多いなら、タブや稀な記号に変えてください。番兵 \\N は「NULLだった」印です。空文字と NULL を区別したいときの保険です。

ステップ1：まず件数

SELECT
  (SELECT COUNT(*) FROM `project.dataset.table_before`) AS cnt_before,
  (SELECT COUNT(*) FROM `project.dataset.table_after`) AS cnt_after;

件数が異なる場合、内容が一致しません。先に件数を揃える（または差分原因を潰す）方が早いです。

ステップ2：キーがあるとき：行単位でハッシュ突合

SELECT
  COALESCE(b.id, a.id) AS id,
  b.row_hash AS hash_before,
  a.row_hash AS hash_after
FROM `project.dataset.table_before_hashed` AS b
FULL OUTER JOIN `project.dataset.table_after_hashed` AS a
  USING (id)
WHERE
  b.row_hash IS NULL
  OR a.row_hash IS NULL
  OR b.row_hash != a.row_hash
LIMIT 100;
  • 片方だけ存在する → 行の増減
  • ハッシュ値が異なる場合 → データの内容に違いがある

差分が0行なら、キー付きの意味で一致と見てよいです。

ステップ3：キーが弱い場合や全体の一致のみを確認したい場合：ハッシュ集合の比較

-- before にだけあるハッシュ
SELECT row_hash
FROM `project.dataset.table_before_hashed`
EXCEPT DISTINCT
SELECT row_hash
FROM `project.dataset.table_after_hashed`;

-- after にだけあるハッシュ
SELECT row_hash
FROM `project.dataset.table_after_hashed`
EXCEPT DISTINCT
SELECT row_hash
FROM `project.dataset.table_before_hashed`;

両方とも0行なら、「行の指紋の集合」としては一致です。
（同一ハッシュの重複行の扱いは後述の注意を見てください。）

集約で見るなら、次も簡潔です。

SELECT
  COUNT(*) AS cnt,
  COUNT(DISTINCT row_hash) AS distinct_hash,
  BIT_XOR(FARM_FINGERPRINT(row_hash)) AS xor_fingerprint
FROM `project.dataset.table_before_hashed`;

before / after で cnt と xor_fingerprint（および必要なら distinct_hash）が一致するかを見ます。
手早いスクリーニング向きです。食い違ったら、ステップ2の突合や EXCEPT に戻ります。

実務での進め方（判断基準）

  1. 件数不一致 → フィルタ・JOIN・粒度（1対多）を疑う（JOINで行が増える話 が近い）
  2. 件数一致・ハッシュ不一致 → 型変換、NULL、端数、タイムゾーン、列の抜けを疑う
  3. 不一致行が少数 → その id だけ全列を抜き出して目視／列ごとの差分
  4. 一致 → リファクタリング採用。検証SQLと前提（NULL番兵・列順）をメモに残す

よくあるミス（失敗例と回避策）

NULL の扱いが before / after で違う

CONCAT に NULL が混ざると、行ハッシュ自体が NULL になり、比較が壊れます。
必ず IFNULL / COALESCE で揃える。空文字と NULL を区別したいなら番兵文字を使う。

型の文字列化がズレる

FLOAT64 や TIMESTAMP は表示ゆれが起きやすいです。
比較用には、必要に応じて ROUND や FORMAT_TIMESTAMP で 桁・書式を固定 してから CAST します。

列順・列の抜け

ハッシュは列順に依存します。before で a|b|c、after で a|c|b だと中身が同じでも不一致になります。
リファクタリングで列を足した／除いたなら、検証対象列を明示リスト化します。

ハッシュ衝突を過大視しすぎる／過小視しすぎる

ハッシュ比較は「違う行がありそうか」を早く見る手段です。
同じ指紋なら中身も同じ、と実務ではほぼ見てよいですが、理論上は 別の中身が同じ MD5 になる（衝突） ことがあります。だから「ビット単位で完全に同じ」ことの証明にはしません。
リファクタリング検証では、件数一致＋行ハッシュに加え、金額合計など 業務的な合計値の一致 を併用すると、衝突を気にしすぎず、見落としも減らせます。

重複行があるテーブル

キーがなく、全く同じ行が2行ある場合、ハッシュ集合の EXCEPT だけでは重複個数の違いを見逃すことがあります。
その場合は GROUP BY row_hash して件数も比べるか、一意キーを検証用に付与します。

SELECT row_hash, COUNT(*) AS cnt
FROM `project.dataset.table_before_hashed`
GROUP BY 1
QUALIFY COUNT(*) != (
  SELECT COUNT(*)
  FROM `project.dataset.table_after_hashed` AS a
  WHERE a.row_hash = table_before_hashed.row_hash
);

（書き方は環境に合わせて簡略化して構いません。要点は hash ごとの件数も一致させる ことです。）

まとめ

  • リファクタリング検証の本命は「SQLが綺麗か」ではなく 出力テーブルが一致するか
  • 件数が多いときは、全列比較より 行ハッシュ比較 が現実的
  • BigQueryでは IFNULL(CAST(col AS STRING), 番兵) を CONCAT → MD5 → TO_HEX が使いやすい
  • NULL・型・列順を before/after で固定しないと、偽の差分が出る
  • 差分行だけ深掘りし、業務合計も併用するとリリースが安心

同じ「結果が意図どおりか」をSQL側で確かめる入口として、SQL学習ロードマップ や ORDER BY / LIMIT も併せてどうぞ。

次に読む

タイトルとURLをコピーしました