sql-server-table-reconciliation

SQL Server 表数据核对

使用 Python 配合 mssql-python 驱动和 Apache Arrow，比较两个 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/token）
主键	自动检测	构成行标识的列。如果未提供，则从元数据自动检测。
要比较的列	全部	列的子集，或所有非主键列
分块大小	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

比较规则

• 比较前规范化类型：将 decimal 转换为相同精度，去除字符串首尾空白，将 datetime 规范化为 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 ===

当提供单个表时，包含完整详细信息（架构漂移、样本行、不匹配项）。当提供多个表时，使用上述按表紧凑格式，仅对状态为 FAIL 的表提供完整详细信息。

性能注意事项

场景	策略
< 100K 行	单次 Arrow 获取，内存中 pandas 比较
100K–1M 行	分块提取（100K 批次），流式比较
> 1M 行	哈希预检查 → 仅获取不匹配的行
宽表（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 中