エムスリーテックブログ

エムスリー(m3)のエンジニア・開発メンバーによる技術ブログです

テーブルとテーブルをざっくりと比較する

データ基盤

こんにちは。エンジニアリンググループGMで、データ基盤チームリーダーの木田です。この記事はマネジメントチームブログリレー6日目の記事です。 前回は藤原さんの Claude Codeを手懐けてAndroidアプリ高速リファクタリング でした。

はじめに

今年、エンジニアリンググループでは、オンプレミスのOracleデータベース環境からのクラウド移行をはじめ、多くのデータ移行プロジェクトをチーム横断で推進して来ました。また、データ基盤チームではBigQuery上で稼働しているデータ処理バッチをdbtに移行する取り組みも進みました。活動の一端はこのテックブログでもご紹介してきました。

www.m3tech.blog

www.m3tech.blog

www.m3tech.blog

こうしたデータ移行プロジェクトにおいては、データを互換性を保ちつつ新しいシステムへ移行した際に移行前後でデータの内容が正しく移行されたかを確認する作業が必要になります。

具体的にはテーブルごとに移行元と移行先が完全に一致していることを確認したい1。しかし、データ量が膨大、データベースの更新を停止できない、投下できる工数に限りがある等、さまざまな事情で厳密な完全一致の確認が難しいケースも現実にはありました。

このような状況下でもある程度の精度で「大きな問題がなさそう」と判断してマイグレーションを進めていきたいところです。本稿では、ドメイン知識（ユニークキーや重要カラムの知識など）がなくても、DBのカタログ情報等から機械的に得られる情報の範囲で実施できるテーブル間比較手法について紹介します。

一般的なテーブル間比較の方法とその課題

まず、よくあるテーブル比較の方法として、SQLを使った完全一致の確認方法を見てみましょう。例えば BigQueryの場合は以下のようなクエリで、2つのテーブルの差分を抽出できます2。SQLの出力が0件であれば、両テーブルは一致していると言えます。 PostgreSQLであれば EXCEPT 演算子、 Oracleであれば MINUS 演算子を使って同様の比較が可能です。

-- テーブルAにのみ存在するレコード
SELECT * FROM table_a
EXCEPT DISTINCT
SELECT * FROM table_b;

-- テーブルBにのみ存在するレコード
SELECT * FROM table_b
EXCEPT DISTINCT
SELECT * FROM table_a;

上記では完全一致の確認が取れるのは理想的ですが、次に挙げるようないくつかの理由で難しい場合があります。

データ量の多いテーブル

数億件、数十億件といった大規模なテーブルでは、全件比較に膨大な計算リソースが必要になります。RDBMSであればクエリが応答しなくなってしまいますし、BigQueryのようなクラウド型データウェアハウスといえども実行時間や金銭的コストは無視できないレベルになります。

タイミングによるズレ

稼働中のシステムをデータソースとした派生テーブル作成やデータ連携の場合、処理のタイミングの違いなどからどうしてもデータにズレが生じてしまうケースがあります。例えば テーブルAは 0時に更新され、テーブルBは1時に更新される場合、0時から1時の間に追加・更新されたレコードがテーブルAに反映されていないため、完全一致しません。

また、 updated_time 列のような値が処理タイミングに依存するカラムが存在する場合、1秒ずれただけでも膨大な不一致レコードが差分として検出されてしまい、他の原因での差分と切り分けが困難になるという問題もあります。

段階的にテーブルをざっくり比較していく

そこで、差分発生時の切り分けのしやすさにも配慮した、段階的に比較の精度を上げながらテーブル同士を比較する手法を紹介します。 対象テーブルに対する深いドメイン知識がなくても、INFORMATION_SCHEMA等のカタログ情報から機械的に得られる情報だけで実施できることを目標にします3。

大まかに３ステップに分けて比較を進めます。

まずスキーマ定義などメタデータ同士を比較しデータ内容の比較をする前提条件が満たされているかを確認します。次に、レコード数や各カラムのユニークな値の数など、テーブルに集計クエリをかけた結果の統計値を比較します。最後に、実データ内容の一部を完全一致するかどうか比較します。

各ステップで問題が見つからなければ次のステップへ進み、問題が見つかった場合は詳細調査に移ります。早期に問題を検出してそこで処理を打ち切ることで多くのケースで無駄な比較処理のコストやトータル実行時間を削減できることが期待されます。

ステップ1: メタデータ(スキーマ情報)同士の比較

最初に、テーブル構造そのものを比較します。カラム数、カラム名、データ型、NULL許容などの情報を確認することで、テーブル定義の不一致を検出できます。 DBによりますが、パーティション情報や権限情報などもこのステップで確認すると良いでしょう。

-- BigQueryの例: テーブルスキーマを取得
SELECT
  column_name,
  data_type,
  is_nullable
FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your_table'
ORDER BY ordinal_position;

この段階で不一致が見つかれば、データを比較する前に構造的なマイグレーション不備の問題を解決します。意外とNOT NULL制約の有無などが差分として検出できます。

ステップ2: テーブルのサマリ情報間での比較

スキーマが一致していることを確認したら、次にテーブルの統計的な属性情報を比較します。COUNT、MIN、MAXなどの集計関数を使って、データ全体の傾向をテーブルに対するサマリ情報として抽出し、それを比較します。

このステップの狙いは、データ量の制約で全件比較が難しい場合でも、集計結果のみを比較することで大まかなデータの一致を確認することや、レコード単位での細かな不一致を検出する前に大きなズレがないかを確認することです。

SELECT
  COUNT(*) as record_count,
  -- 全部のカラムに対して同様の集計を実施
  COUNT(created_at) as non_null_created_at_count,
  MIN(created_at) as min_created_at,
  MAX(created_at) as max_created_at,
  ... 
FROM your_table

サマリ情報の生成に新旧テーブル全体の走査が各1回必要ですが、比較処理自体はシンプルなのでデータそのものを突合するよりも高速に実行できることが期待できます。差が一定の閾値を下回れば次のステップに進みます。閾値の設定に関してはテーブルのサイズや、業務要件を踏まえてケースバイケースで決める必要があります。とはいえ、前提知識がない時は例えばレコード数差が1%未満などを初期条件にスタートしてみてはいかがでしょうか。

この手法をエムスリーで最初に試した時使用した統計値はレコード数、各カラムの非NULL値の件数、(数値・日付・タイムスタンプ型等の) 最大値、最小値のみです。クエリ生成のためのPythonスクリプトを準備して自動化しました。機械的な検出基準としては、レコード数の差分および各カラムの非NULL値件数の差分のみで、最大値・最小値は調査のための参考情報にとどめました(当初は最大・最小値も比較対象に含めたのですが、偽陽性が多くテーブル個別の知識が必要になったので対象から外しました)。これだけでもタイムゾーンの変換ミスや、結合条件の不備など実装ミスでテーブル全体に影響するような典型的なデータ差分を検知できました。

この辺りは型に応じて取得する統計値を変えたり、比較処理をチューニングすることで機械的に対応できる範囲を広げる余地がまだまだあると感じています。

ステップ3: データのサンプリング突合

最後にデータの一部を実際に比較します。サンプリングと書いてしまいましたがランダムに抽出するのではなく、経験則に基づき、突合可能な形でデータの一部を恣意的に抽出して比較しています。なお、件数が少ないテーブルの場合は全件比較でも問題ありませんし、むしろ全件比較を推奨します。

主キーがわかっている場合は、ランダムに主キーのセットを抽出して、双方のテーブルからデータを取得し、データの一致を確認することも可能です。 しかし、主キーに関する情報が無い場合や、（不幸なことに） 主キーが無いテーブルあっても、先頭の数カラムでソートしたデータ同士を比較することで、擬似的にテーブルの一部を突合できます4。

-- 先頭5列でソートして上位k件 (この例ではK=10000件) 同士を比較。件数はテーブルのサイズや処理スピードに応じて調整してください。
with table_a_sample as (
  SELECT * FROM table_a
  ORDER BY 1 desc , 2 desc , 3 desc, 4 desc, 5 desc
  LIMIT 10000
),
table_b_sample as (
  SELECT * FROM table_b
  ORDER BY 1 desc , 2 desc , 3 desc, 4 desc, 5 desc
  LIMIT 10000
)
SELECT * FROM table_a_sample
EXCEPT DISTINCT
SELECT * FROM table_b_sample;

これは、テーブル設計において主キーや重要なカラムを先頭側に配置することが多いという経験則に基づいています。 また、降順でソートすることで、IDを連番で振っているようなケースで新しいデータを優先的に比較対象に含めることも期待しています5。

ステップ2,3で検出した不一致は最終的にはテーブルのデータ構造を深掘りしたり、個別に許容できる差異であるかどうかの評価が必要になります。しかし、機械的にチェックできる事項を段階的に確認した上での判断になるため、有識者の負荷は軽減できますし、差分を許容する際の定量的な判断材料にもなります。

ざっくり比較の限界・制約

本稿で紹介した手法は、文字列や数値などの典型的なデータ型と、ある程度正規化されたテーブルを前提としています。バイナリやARRAY、STRUCTなどのカラムを含むテーブルに対しては、それらを除外して比較するなどの工夫が必要となります。また、除外したカラムが重要な意味を持つ場合は、個別に対応を検討する必要があります。完全にドメイン知識なしで済むわけではなく、状況に応じた対応が必要なケースもあることをご承知おきください。

まとめ

データ量の多いテーブルや、テーブル構造に関する詳細知識がないテーブルに対しても「ざっくり」とテーブルの一致を比較するための段階的なアプローチを紹介しました。 見慣れないデータに対しても恐れずに検証を進めるための一助になれば幸いです。

We are hiring!

エムスリーでは、データを活用してプロダクト開発を支えるエンジニアを絶賛募集中です。 新しいプロダクト・サービスが次々と生み出される環境だからこそ、多種多様なデータに触れる機会があります。 興味のある方はぜひ以下のリンクから是非カジュアル面談等、お申し込みください！

jobs.m3.com

  1. できればバリエーションや量が十分にある本番相当のデータで確認したいところです↩
  2. ARRAY型やSTRUCT型などの複雑なデータ型には適用できないなど制限もあります↩
  3. もちろんテーブルに対する知識、特にユニークキーや重要カラムの知識があればより効率的な比較ができます。テーブルごとに主キーを与えて比較するツールを作っているチームもあります。本稿では対象のテーブル数が多い場合やより汎用的に通用する方法を志向しました↩
  4. BigQueryをはじめ多くのDBMSでは、ORDER BY 1, 2, 3 のようにカラム番号でソート順を指定できます。これにより、カラム名の前提知識なしでソートが可能で、テーブル名以外のクエリが共通化できます。↩
  5. BigQuery上で一連の処理を実行したので性能トラブルには当たりませんでしたが、ソート処理が可能な規模で、kをワーキングメモリに収まる程度に小さく取れればRDBMS上でも同様のことができる見込みです。また、上述の経験則によって何らかの索引にヒットしやすいことも期待されます。↩