Journal of University of South China( Science and Technology) Vol 郾 25 No 郾 3 Sep 郾 2011 文 章 编 号 :1673-0062(2011)03-078 - 05 变 化 数 据 捕 获 研 究 及 基 于 SQL SERVER 的 开 发 与 应 用 王 丽 君, 李 摇 萌, 徐 卓 然, 雷 龙 艳, 周 摇 倩, 阳 小 华 ( 南 华 大 学 电 气 工 程 学 院, 湖 南 衡 阳 421001) 摘 摇 要 : 变 化 数 据 捕 获 是 数 据 集 成 的 核 心 内 容, 为 高 效 地 实 现 数 据 集 成, 探 讨 了 变 化 数 据 捕 获 的 常 见 机 制, 重 点 介 绍 了 SQL SERVER 的 变 化 数 据 捕 获 方 案 更 改 跟 踪 和 变 化 数 据 捕 获, 并 以 实 际 开 发 项 目 为 背 景, 介 绍 在 偶 尔 连 接 场 景 下, 如 何 运 用 变 化 数 据 捕 获 技 术, 合 理 设 计 数 据 集 成 方 案, 满 足 业 务 要 求, 实 现 异 构 数 据 源 的 有 效 更 新. 关 键 词 : 变 化 数 据 捕 获 ; 更 改 跟 踪 ; 变 更 数 据 捕 获 中 图 分 类 号 :TP31 摇 摇 摇 文 献 标 识 码 :B Research on the Change Data Capture and Its Application in SQL SERVER Database WANG Li 鄄 jun,li Meng,XU Zhuo 鄄 ran,lei Long 鄄 yan,zhou Qian,YAN Xiao 鄄 hua ( School of Electric Engineering,University of South China,Hengyang,Hunan 421001,China) Abstract:The change data capture is the core content of data integration. For implementing data integration efficiently,this paper introduced the usual methods of change data cap 鄄 ture,laid heavy stress on the change data capture solutions of SQL SERVER and demon 鄄 strated how to use it with an application example. key words:change data capture;change tracking;changing data capture 摇 摇 随 着 信 息 化 建 设 的 不 断 发 展, 各 类 信 息 系 统 的 数 量 快 速 膨 胀, 在 提 供 丰 富 数 据 源 的 同 时, 也 引 发 了 信 息 孤 岛 问 题, 使 得 这 些 数 据 资 源 无 法 共 享 相 互 利 用, 更 无 法 满 足 从 数 据 到 信 息 再 到 知 识 的 应 用, 企 业 应 用 集 成 EAI 应 运 而 生, 它 包 括 多 个 层 面 的 集 成, 其 中 数 据 集 成 最 为 基 础 也 最 为 普 遍, 数 据 集 成 通 常 采 用 ETL 对 各 类 异 构 数 据 源 进 行 抽 取 转 换 和 清 洗. 数 据 集 成 的 核 心 内 容 就 是 变 化 数 据 捕 获, 由 于 信 息 系 统 大 多 属 于 在 线 联 机 事 务 处 理 OLTP, 因 此 其 中 的 数 据 不 断 变 化, 又 因 为 信 息 系 统 多 采 用 数 据 库 管 理 系 统 DBMS 管 理 数 据, 如 常 见 的 ORACLE SQL SERVER IBM DB2 MY SQL 等, 所 以 需 要 充 分 利 用 DBMS 特 性, 及 时 捕 获 变 化 的 数 据, 实 现 数 据 更 新 与 同 步, 为 数 据 集 成 奠 收 稿 日 期 :2011-07 - 12 基 金 项 目 : 湖 南 省 教 育 科 学 十 一 五 冶 规 划 基 金 资 助 项 目 (XJK08BGD017); 南 华 大 学 2010 高 等 教 育 研 究 与 改 革 基 金 资 助 项 目 (2010ZC011) 作 者 简 介 : 王 丽 君 (1979 - ), 女, 湖 南 邵 阳 人, 南 华 大 学 电 气 工 程 学 院 实 验 师, 硕 士. 主 要 研 究 方 向 :ETL EDA DSP 研 究.
王 丽 君 等 : 变 化 数 据 捕 获 研 究 及 基 于 SQL SERVER 的 开 发 与 应 用 79 定 坚 实 基 础. 论 文 首 先 探 讨 了 常 见 的 变 化 数 据 捕 获 机 制, 然 后 介 绍 SQL SERVER 2008 提 供 的 变 化 数 据 捕 获 方 案, 即 更 改 跟 踪 ( Change Tracking) 与 变 更 数 据 捕 获 (Changing Data Capture). 最 后 以 实 际 项 目 中 的 数 据 更 新 模 块 为 例, 说 明 如 何 利 用 SQL SERVER 方 案 进 行 开 发 与 实 现. 改 的 特 定 数 据 行 ( 或 者 经 过 更 改 的 数 据 列 ). 这 是 为 了 解 决 偶 尔 连 接 的 系 统 方 案 而 设 计 的, 图 1 说 明 了 使 用 更 改 跟 踪 的 数 据 同 步 处 理. 1 摇 变 化 数 据 捕 获 机 制 研 究 者 们 对 变 化 数 据 捕 获 机 制 进 行 了 深 入 研 究, 目 前 的 主 题 可 分 为 两 类 : 变 化 数 据 捕 获 机 制 和 特 定 环 境 下 的 应 用. 2002 年, 者 敬 在 其 博 士 论 文 中 将 变 化 捕 获 方 法 归 纳 为 六 种 : 快 照 法 触 发 器 法 日 志 法 API 法 影 子 表 法 和 控 制 表 变 化 法, 并 从 捕 获 方 法 更 新 方 式 适 用 范 围 等 九 个 方 面 对 它 们 进 行 综 合 比 较 [1] ;2004 年, 陆 剑 峰 等 结 合 开 发 示 例, 介 绍 利 用 ORACLE 9i 的 CDC(Changing Da 鄄 ta Capture) 机 制 解 决 数 据 增 量 更 新 的 方 法 [2] ; 2008 年, 杨 鹏 等 在 分 析 现 有 实 现 方 案 不 足 的 基 础 上, 提 出 一 种 基 于 触 发 器 的 高 效 通 用 变 化 数 据 捕 捉 方 法, 设 计 实 现 了 跨 DBMS ( Oracle, Sybase 和 MS SQL Server) 的 相 应 同 步 策 略 [3] ;2009 年, 戴 浩 等 将 ETL 中 数 据 增 量 抽 取 的 常 见 机 制 归 纳 为 7 种 : 触 发 器 时 间 戳 全 表 删 除 插 入 全 表 比 对 日 志 表 系 统 日 志 分 析 和 特 定 数 据 库, 对 这 些 机 制 的 原 理 条 件 方 法 以 及 运 行 效 率 等 方 面 进 行 了 详 细 的 阐 述, 并 从 兼 容 性 完 备 性 性 能 和 侵 入 性 四 个 方 面 分 析 和 比 较 了 各 种 数 据 增 量 抽 取 机 制 的 优 劣 性 [4] ;2010 年, 陈 东 亮 等 提 出 了 一 种 基 于 任 务 的 控 制 表 法, 该 方 法 在 控 制 表 法 的 基 础 上 增 加 了 任 务 和 类 型 等 相 关 信 息, 同 时 以 净 变 化 量 提 取 组 件 作 为 辅 助, 能 够 更 有 效 地 适 用 于 多 节 点 间 异 构 数 据 库 的 数 据 交 换, 并 给 出 了 在 变 化 捕 获 方 法 中 因 使 用 触 发 器 而 带 来 的 数 据 震 荡 问 题 的 解 决 方 法 [5]. 图 1 摇 使 用 更 改 跟 踪 的 偶 尔 连 接 的 系 统 Fig. 1 摇 An occasionally connected system using change tracking data 2. 2 摇 变 更 数 据 捕 获 变 更 数 据 捕 获 使 用 的 是 异 步 机 制, 可 以 跟 踪 表 ( 或 是 表 中 一 组 定 义 的 数 据 列 ) 发 生 的 所 有 更 改, 它 专 为 数 据 仓 库 ETL 过 程 等 情 形 设 计 的. 图 2 说 明 了 不 同 时 间 段 获 取 的 更 改 数 据. 变 更 数 据 捕 获 机 制 会 将 更 改 的 数 据 提 取 到 一 组 表, 最 新 的 更 改 在 表 的 最 上 方. 然 后,ETL 过 程 对 存 储 变 更 数 据 的 表 查 询 在 固 定 时 段 内 发 生 的 所 有 更 改, 允 许 ETL 过 程 限 制 每 批 获 取 的 数 据 量. 2 摇 SQL SERVER 的 变 化 数 据 捕 获 机 制 变 化 数 据 捕 获 的 两 个 的 典 型 应 用 场 景 是 : 支 持 数 据 仓 库 的 更 新, 以 及 支 持 异 构 偶 尔 连 接 的 系 统 进 行 同 步 处 理 [6]. SQL SERVER 2008 提 供 了 两 种 变 化 数 据 捕 获 机 制 [7], 即 更 改 跟 踪 ( Change Tracking) 与 变 更 数 据 捕 获 ( Changing Data Cap 鄄 ture),paul 对 两 者 进 行 深 入 比 较 [6]. 2. 1 摇 更 改 跟 踪 更 改 跟 踪 采 用 同 步 机 制, 只 能 跟 踪 表 中 已 更 图 2 摇 不 同 时 间 段 获 取 的 历 史 更 改 数 据 Fig. 2 摇 Historical change data being consumed in time slices
80 摇 摇 这 两 种 机 制 与 自 定 义 解 决 方 案 的 相 同 之 处, 由 于 必 须 在 某 个 位 置 存 储 更 改 数 据, 因 此 都 会 增 加 I / O 和 记 录 ; 两 者 区 别 在 于, 用 于 存 储 更 改 数 据 的 表 必 须 与 要 跟 踪 的 表 位 于 相 同 的 数 据 库 中. 这 说 明 所 有 更 改 数 据 都 将 包 含 在 备 份 中, 从 而 可 通 过 日 志 传 送 或 数 据 库 镜 像 在 网 络 上 传 输. 就 开 发 而 言, 这 两 种 机 制 可 以 明 显 降 低 变 化 数 据 捕 获 的 复 杂 性. 1) 因 为 无 论 是 哪 一 种 技 术, 都 不 需 要 更 改 表 架 构 或 触 发 器 ;2) 两 者 都 具 有 可 配 置 的 自 动 清 除 过 程, 可 依 据 事 务 提 交 时 间 对 更 改 排 序, 并 且 提 供 内 置 函 数 来 检 索 更 改 信 息. 3 摇 应 用 实 例 本 应 用 实 例 是 某 市 公 安 局 社 区 警 务 系 统 中 数 据 更 新 模 块. 数 据 源 来 自 省 厅 的 人 口 数 据 仓 库, 省 厅 DBMS 采 用 ORACLE, 市 局 使 用 SQL SERVER, 两 者 为 异 构 数 据 源, 采 用 单 向 增 量 抽 取, 即 从 省 厅 更 新 到 市 局, 具 体 要 求 :1) 添 加 市 局 没 有 的 记 录,2) 对 双 方 均 存 在 的 记 录, 只 更 新 部 分 字 段 的 内 容,3) 被 市 局 修 改 过 的 字 段, 其 内 容 不 允 许 更 新. 该 模 块 的 两 个 核 心 内 容 为 定 位 实 体 和 捕 获 变 化 数 据, 捕 获 粒 度 精 确 到 列, 具 体 业 务 逻 辑 如 图 3 所 示. 图 3 摇 项 目 业 务 逻 辑 Fig. 3 摇 Project business logical 3. 1 摇 定 位 实 体 由 于 数 据 源 和 目 标 异 构, 两 者 没 有 统 一 的 实 体 标 识, 为 准 确 地 更 新 数 据, 必 须 为 每 条 记 录 建 立 唯 一 的 主 键, 又 因 为 人 口 数 据 量 大, 还 必 须 减 少 主 键 值 碰 撞 的 几 率, 所 以 项 目 中 采 用 HASH 函 数 SHA1 生 成 主 键. 人 口 数 据 字 段 众 多, 如 果 将 所 有 字 段 均 作 为 HASH 函 数 的 输 入, 虽 然 保 证 了 主 键 的 惟 一 性, 但 是 将 大 幅 增 加 计 算 量, 严 重 影 响 性 能, 因 此, 项 目 中 采 用 候 选 键 作 为 SHA1 的 输 入, 极 大 缩 减 了 计 算 代 价, 减 轻 对 系 统 性 能 的 影 响. 3. 2 摇 捕 获 变 化 数 据 因 为 数 据 抽 取 的 时 间 间 隔 较 长, 通 常 为 1 ~ 4 周 进 行 一 次, 属 于 典 型 的 偶 尔 连 接 场 景, 所 以 项 目 中 采 用 更 改 跟 踪 (Change Tracking) 机 制. 同 时, 业 务 只 关 心 特 定 列 是 否 存 在 变 化 数 据, 并 不 在 意 变 化 的 具 体 内 容, 因 此 需 要 启 用 列 跟 踪 支 持. 利 用 更 改 跟 踪 来 捕 获 变 化 数 据 可 分 为 两 步 : 首 先 启 用 并 配 置 更 改 跟 踪, 然 后 使 用 更 改 跟 踪 函 数 获 取 更 改 [7]. 因 保 密 需 要, 采 用 AdventureWorks 作 为 示 例 数
王 丽 君 等 : 变 化 数 据 捕 获 研 究 及 基 于 SQL SERVER 的 开 发 与 应 用 81 据 库, 库 中 建 有 一 张 表 Product, 结 构 如 表 1 所 示. 表 1 摇 Product 表 结 构 Table 1 摇 Construction of table product 字 段 名 称 是 否 为 空 数 据 类 型 Id NOT NULL INT Name NULL NVARCHAR(100) Price NULL NUMERIC(8,2) 摇 摇 1) 启 用 并 配 置 更 改 跟 踪 对 数 据 库 启 用 更 改 跟 踪 的 执 行 脚 本 如 下 : 对 数 据 库 启 用 更 改 跟 踪 ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON 摇 开 启 自 动 清 理, 数 据 保 存 期 为 两 天 (AUTO_CLEANUP = ON,CHANGE _RETEN 鄄 TION = 2 DAYS) 摇 对 表 启 用 更 改 跟 踪 ALTER TABLE dbo. Product ENABLE CHANGE_TRACKING 跟 踪 列 更 新 WITH( TRACK_COLUMNS_UPDATED = ON) 为 表 配 置 了 更 改 跟 踪 后, 任 何 影 响 该 表 中 的 行 的 DML 语 句 都 将 被 记 录 下 来,SQL SERVER 数 据 库 引 擎 会 将 有 关 哪 些 列 已 更 新 的 额 外 信 息 存 储 到 内 部 更 改 跟 踪 表 中. 使 用 更 改 跟 踪 可 以 获 取 与 每 个 行 所 做 更 改 相 关 的 信 息, 如 : 导 致 更 改 ( 插 入 更 新 和 删 除 ) 的 DML 操 作 的 类 型 或 更 新 操 作 所 更 改 的 列. 2) 获 取 更 改 SQL SERVER 提 供 了 五 个 更 改 跟 踪 函 数, 见 表 2, 用 于 记 录 应 用 于 被 跟 踪 表 的 插 入 更 新 和 删 除 操 作, 同 时 采 用 易 于 使 用 的 关 系 格 式 提 供 有 关 更 改 的 详 细 信 息. 表 2 摇 更 改 跟 踪 函 数 Table 2 摇 Change tracking functions 函 摇 摇 数 说 摇 摇 明 CHANGETABLE(CHANGES) 返 回 自 指 定 版 本 起 对 表 所 做 的 所 有 更 改 的 跟 踪 信 息. CHANGETABLE(VERSION) 返 回 指 定 行 的 最 新 更 改 跟 踪 信 息. CHANGE_TRACKING_MIN_VALID_VERSION() CHANGE_TRACKING_CURRENT_VERSION CHANGE_TRACKING_IS_COLUMN_IN_MASK WITH CHANGE_TRACKING_CONTEXT 返 回 使 用 CHANGETABLE 函 数 时 从 指 定 表 获 取 更 改 跟 踪 信 息 所 使 用 的 最 低 有 效 版 本. 获 取 与 上 次 提 交 事 务 关 联 的 版 本. 可 以 在 下 次 使 用 CHANGETABLE 枚 举 更 改 时 使 用 此 版 本. 用 于 解 释 由 CHANGETABLE ( CHANGES ) 函 数 返 回 的 SYS _ CHANGE _ COLUMNS 值. 用 于 在 应 用 程 序 更 改 数 据 时 指 定 更 改 上 下 文, 例 如 发 起 方 ID. 摇 摇 查 询 数 据 更 改 的 脚 本 如 下 : SELECT CT. Id,P. Name,P. Price,CHANGE_TRACKING_IS_COLUMN_IN_MASK 摇 摇 ( 摇 摇 摇 摇 COLUMNPROPERTY ( OBJECT _ ID ( 忆 Product 忆 ), 忆 Name 忆, 忆 ColumnId 忆 ) 摇 摇 摇 摇,CT. SYS_CHANGE_COLUMNS 摇 摇 )AS NameIsChanged,CHANGE_TRACKING_IS_COLUMN_IN_MASK 摇 摇 ( 摇 摇 摇 摇 COLUMNPROPERTY ( OBJECT _ ID ( 忆 Product 忆 ), 忆 Price 忆, 忆 ColumnId 忆 ) 摇 摇 摇 摇,CT. SYS_CHANGE_COLUMNS 摇 摇 )AS PriceIsChanged,CT. SYS_CHANGE_VERSION, CT. SYS _ CHANGE _ OPERATION, CT. SYS _ CHANGE_COLUMNS FROM Product AS P RIGHT OUTER JOIN CHANGETABLE( CHANGES Product, @ last _ syn 鄄 chronization_version) AS CT ON P. Id = CT. ID 查 询 更 改 跟 踪 结 果 如 图 4 所 示, 其 中 SYS _ CHANGE_OPERATION 有 三 种 取 值 :I U D, 对 应 的 DML 操 作 为 insert update 和 delete, SYS _ CHANGE_COLUMNS 为 受 update 影 响 的 字 段 编 码,SYS_CHANGE_VERSION 表 示 DML 的 操 作 顺 序, 图 3 可 解 读 为 : 先 更 新 记 录 Id = 1 的 Price 字
82 段, 然 后 更 新 记 录 Id = 5 的 Name 字 段, 接 下 来 插 入 记 录 Id = 7, 最 后 删 除 Id = 6 的 记 录. 图 4 摇 更 改 跟 踪 结 果 Fig. 4 摇 Result of change tracking 摇 摇 需 要 注 意 的 是, 启 用 了 更 改 跟 踪 的 数 据 库 具 有 一 个 版 本 计 数 器, 在 对 启 用 了 更 改 跟 踪 的 表 进 行 更 改 时, 该 计 数 器 会 随 之 递 增. 每 个 更 改 的 行 都 有 一 个 关 联 的 版 本 号. 将 请 求 发 送 到 应 用 程 序 以 查 询 更 改 时, 将 调 用 一 个 函 数 以 提 供 版 本 号. 该 函 数 返 回 在 该 版 本 之 后 所 做 的 所 有 更 改 的 相 关 信 息. CHANGE_RETENTION 指 定 的 时 间 决 定 了 所 有 应 用 程 序 必 须 每 隔 多 长 时 间 从 数 据 库 中 请 求 一 次 更 改. 如 果 应 用 程 序 使 用 的 版 本 值 早 于 表 的 最 低 有 效 同 步 版 本, 将 无 法 执 行 有 效 的 更 改 枚 举, 必 须 重 新 初 始 化 所 有 数 据. 4 摇 结 摇 论 变 化 数 据 捕 获 是 数 据 集 成 的 核 心 内 容. 由 于 数 据 源 结 构 各 异, 业 务 要 求 千 差 万 别, 故 实 现 方 案 的 设 计 需 要 灵 活 应 对. 论 文 探 讨 了 变 化 数 据 捕 获 的 常 见 机 制, 并 结 合 实 例 说 明 了 SQL SERVER 的 更 改 跟 踪 机 制, 它 较 好 的 解 决 了 偶 尔 连 接 场 景 下 的 变 化 数 据 捕 获 问 题. 参 考 文 献 : [1] 者 敬. 开 放 式 异 构 数 据 库 复 制 框 架 的 研 究 与 实 现 [D]. 北 京 : 中 国 科 学 院 软 件 研 究 所,2002:19 鄄 23. [2] 陆 剑 峰, 张 浩. 数 据 仓 库 数 据 更 新 的 研 究 与 基 于 Oracle 数 据 库 的 开 发 与 应 用 [J]. 计 算 机 工 程 与 应 用, 2004(26):168 鄄 170. [3] 杨 鹏, 杨 海 涛, 王 正 华. 异 构 数 据 库 变 化 捕 捉 及 同 步 策 略 [J]. 计 算 机 工 程,2008,34(16):53 鄄 59. [4] 戴 浩, 杨 波. ETL 中 数 据 增 量 抽 取 机 制 研 究 [ J]. 计 算 机 工 程 与 设 计,2009,30(23):5552 鄄 5555. [5] 陈 东 亮, 孙 静. 数 据 交 换 系 统 中 变 化 捕 获 方 法 的 研 究 与 实 现 [J]. 计 算 机 工 程 与 设 计,2010,31(1):94 鄄 97. [6] Paul S Randal. Tracking Changes in Your Enterprise Da 鄄 tabase [ J / OL ]. Microsoft TechNet Magazine, 2008, [2008-11 - 28]. http:/ / technet. microsoft. com/ en 鄄 us / magazine / 2008. 11. sql. aspx. [7] Tracking Data Changes[J / OL]. Microsoft MSDN library, 2011,[2011-02 - 11]. ttp:/ / msdn. microsoft. com/ en 鄄 us / library / bb933994. aspx.