sql-server-table-reconciliation

SQL Server テーブル照合

mssql-python ドライバーと Apache Arrow を使って、2 つの SQL Server インスタンス間の同一テーブルを比較します。欠落している行、列の不一致、スキーマの差異を検出し、照合レポートを生成します。

ワークフロー

1. ソースとターゲットの接続情報を収集します
2. 主キー／複合キーを特定します
3. スキーマの差異を検出します
4. 効率的な列指向データ転送のため、Arrow を使ってデータを抽出します
5. 行と列を比較します
6. 照合レポートを生成します

入力の収集

パラメーター	必須	説明
ソースサーバー	はい	ソース SQL Server（例：prod-server.database.windows.net）
ソースデータベース	はい	ソースデータベース名
ターゲットサーバー	はい	ターゲット SQL Server（例：staging-server.database.windows.net）
ターゲットデータベース	はい	ターゲットデータベース名
テーブル	はい	カンマ区切りの schema.table 名、または schema.* ワイルドカード（例：dbo.Orders,dbo.Items または dbo.*）
認証モード	はい	sql（ユーザー名／パスワード）または entra（Azure AD／トークン）
主キー	自動検出	行の識別に使う列。指定されていない場合は、メタデータから自動検出します。
比較する列	すべて	列のサブセット、または主キー以外のすべての列
チャンクサイズ	100000	大きなテーブルでのバッチあたりの行数
出力形式	console	console、csv、parquet、または json

同梱スクリプト

照合ロジックは、スタンドアロンのスクリプト scripts/reconcile.py として提供されています。ユーザーの入力に応じた引数を指定して実行します。

python scripts/reconcile.py \
    --source-server <source_server> \
    --source-database <source_database> \
    --target-server <target_server> \
    --target-database <target_database> \
    --tables "<table_spec>" \
    --auth <sql|entra> \
    --chunk-size <chunk_size> \
    --output <console|csv|json>

オプションの引数

引数	説明
--primary-key	カンマ区切りの主キー列。省略すると自動検出します。
--columns	カンマ区切りの比較対象列。省略すると主キー以外のすべての列を比較します。

実行例

SQL 認証で単一テーブルを指定する場合：

python scripts/reconcile.py \
    --source-server prod-server.database.windows.net \
    --source-database ProdDB \
    --target-server staging-server.database.windows.net \
    --target-database StagingDB \
    --tables "dbo.Orders" \
    --auth sql \
    --output console

Entra 認証と CSV 出力でワイルドカードを指定する場合：

python scripts/reconcile.py \
    --source-server prod-server.database.windows.net \
    --source-database ProdDB \
    --target-server staging-server.database.windows.net \
    --target-database StagingDB \
    --tables "dbo.*" \
    --auth entra \
    --output csv

前提条件

実行前に必要なパッケージをインストールします。

pip install mssql-python pyarrow pandas

比較ルール

• 比較前に型を正規化します：小数を同じ精度にキャストし、文字列の前後の空白を削除し、日時を UTC に正規化します
• NULL の扱い：NULL == NULL は一致とみなします（両側とも欠落している場合は差分なし）
• 行の順序は無視します：必ず主キーを使って結合して比較し、位置による比較は行いません
• 大きなテーブル：OFFSET/FETCH または ROW_NUMBER() によるパーティション分割でチャンクごとに抽出します

ハッシュベースの最適化（大きなテーブル向け）

テーブルの行数が 100 万行を超える場合は、ハッシュによる事前チェックを生成します。

SELECT {pk_cols},
       HASHBYTES('SHA2_256', CONCAT_WS('|', col1, col2, ...)) AS row_hash
FROM {table}

先にハッシュを比較し、ハッシュが一致しない場合にのみ全行を取得します。これによりデータ転送量を大幅に削減できます。

レポート形式

Reconciling dbo.EMPLOYEES...
Reconciling dbo.DEPARTMENTS...
Reconciling dbo.JOBS...

--- dbo.EMPLOYEES ---
  Source: 107  Target: 107
  Missing: 0  Extra: 0  Mismatches: 0
  Result: ✓ IDENTICAL

--- dbo.DEPARTMENTS ---
  Source: 27  Target: 27
  Missing: 0  Extra: 0  Mismatches: 3
  Result: ✗ DIFFERENCES FOUND

--- dbo.JOBS ---
  Source: 19  Target: 19
  Missing: 0  Extra: 0  Mismatches: 0
  Result: ✓ IDENTICAL

=== Summary: 2 passed, 1 failed, 0 skipped / 3 tables ===

テーブルが 1 つだけ指定されている場合は、スキーマの差異、サンプル行、不一致を含む詳細をすべて記載します。複数のテーブルが指定されている場合は、上記のテーブルごとの簡易形式を使い、ステータスが FAIL のテーブルについてのみ詳細を記載します。

パフォーマンスに関する考慮事項

シナリオ	戦略
10 万行未満	Arrow で一括取得し、pandas でメモリ内比較
10 万～100 万行	チャンクごとに抽出（10 万行単位）し、ストリーミング比較
100 万行超	ハッシュで事前チェック → 不一致の行のみ取得
列数の多いテーブル（100 列以上）	まず主キーとハッシュを比較し、不一致がある場合は該当列を詳しく調べます
ネットワーク帯域が限られている場合	Arrow の列指向形式を使用します（行ごとの転送と比べて 10～50 倍小さくなります）

制約事項

• 必ず mssql-python ドライバーを使用します（pyodbc、pymssql は使用しません）
• データ抽出には必ずカーソル経由で Apache Arrow（cursor.arrow()）を使用します
• 接続にはキーワード引数ではなく、必ず接続文字列形式を使用します（encrypt=True のような kwargs はエラーになります）
• 主キーを特定せずに比較してはいけません。自動検出に失敗した場合はユーザーに確認します
• 接続障害には、リトライロジックで適切に対処します
• 生成するスクリプトに認証情報をハードコードしてはいけません。os.environ／getpass を使用します（環境変数：MSSQL_USER、MSSQL_PASSWORD）
• 出力やログに認証情報を表示しません
• メタデータの照会にはパラメーター化クエリ（? プレースホルダー）を使用し、ユーザー入力を f-string で SQL に埋め込んではいけません