BigQueryにおけるデータ整合性チェックの代表的な方法とは：Google Cloudチートシート（3）

Google Cloudを活用する上でのさまざまなコツを、できるだけ分かりやすく説明する連載「Google Cloudチートシート」。今回はBigQueryのテーブル間でのデータの一致をチェックする代表的な方法を紹介します。

» 2024年07月10日 05時00分公開

[川口拓也，ジーアイクラウド株式会社]

この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。

　Google Cloudの代表的なサービスの一つにBigQueryがあります。高性能なデータウェアハウスサービスで、PB（ペタバイト）級のデータを扱うことができます。とはいえ、大規模なデータを扱うことには課題も伴います。

　その一つはシステムの新規開発や追加開発、データ移行などにおけるデータの検証です。

　小規模なデータでは、カラム1個1個を検証していくこともできますが、大規模なデータではこれが時間的に難しくなります。従って、テーブルなどの単位で間違いのあるなしを判定していく必要があります。例えば以下のようなケースが考えられます。

システム連携時のデータ整合性検証
シビアなデータ整合性が求められるアプリケーションの構築時に、アプリケーションでデータの整合性を検証し、保証する必要があります
システムを改修した時の現新比較
リファクタリングなどを行った際の現新比較を行うケースです。アウトプットとなるデータが修正前後で変わっていないことの検証を行う必要があります
データの移行
他のデータストアからデータを移行したとき、移行前後でデータが一致しているかどうかの検証を行う必要があります
データのリストアの検証
バックアップデータおよびログからデータをリストアしたとき、想定通りのデータが復元できているかどうかの検証を行う必要があります

　このようなデータ検証は、システムの信頼性や品質を確保するために非常に重要です。データの不一致は、システムの誤動作やデータの損失につながる可能性があります。

　今回は膨大なデータがある場合でも対応できる、BigQueryのコンソール上のSQLエディタを使ったデータ比較の代表的な方法を紹介します。

比較演算子と集計関数を使う方法

比較方法

　比較したいテーブル同士のカラムレベルでの比較を行います。

　全ての列のデータを比較するのではなく、特定の列のデータを比較したいケースに適しています。複数列チェックを行う場合にはチェック列を追加することで対応できます。どの列にデータ差分があるのかが一目で判別できるため、全体の簡易的なチェックができます。

注意点

　データに `null` が含まれる場合は正しい結果を返しません。 `null = null` は偽と判断されるためです。

例

　下では、全体の行数と、比較した結果の一致件数を返しています。

SELECT 
  COUNT(*) AS total_rows, 
  SUM(CASE WHEN table1.column_name = table2.column_name THEN 1 ELSE 0 END) AS matching_rows 
FROM 
  table1 
    FULL OUTER JOIN 
  table2 
    ON table1.primary_key = table2.primary_key

チェックサムを使う方法

比較方法

　対象項目をハッシュ化し、排他的論理和でチェックサムを取得し、比較を行います。厳密なデータ一致チェックや異なるデータベース間でのデータ整合性を確認するケースに適しています。

注意点

　データ全体をハッシュ化すること、さらに排他的論理和を取るため、データ量によっては膨大な計算資源が必要になる場合があります。そのため、パーティションやクラスタ単位で処理を分割するなどの工夫が必要になってきます。

例

　BigQuery同士での比較例です。

　TO_JSON_STRINGでテーブル全体をJSON構造体に変換し、 FARM_FINGERPRINT（ Fingerprint64でハッシュ化し、結果をINT64で戻すようにしたBigQueryの独自関数）でハッシュ化、結果をBIT_XORで排他的論理和を取っています。

　データが一致していれば同じ値が取得できます。

SELECT 
  BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(table1))) AS checksum1,
  BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(table2))) AS checksum2
FROM table1, table2