CN116975159A

CN116975159A - 一种增量数据同步的处理方法

Info

Publication number: CN116975159A
Application number: CN202311238540.9A
Authority: CN
Inventors: 向才锋; 张江波; 张自平
Original assignee: Yunzhu Information Technology Chengdu Co ltd
Current assignee: Yunzhu Information Technology Chengdu Co ltd
Priority date: 2023-09-25
Filing date: 2023-09-25
Publication date: 2023-10-31
Anticipated expiration: 2043-09-25
Also published as: CN116975159B

Abstract

本发明公开了一种增量数据同步的处理方法，包括创建待同步表对应的变更记录追踪表；在待同步表上创建触发器，通过触发器将待同步表的变更记录写入变更记录追踪表中；创建SQL Server的一个内联表值函数，基于变更记录追踪表并通过内联表值函数读取待同步表的增量数据；生成并运行同步任务，将增量数据同步到消息队列；消费并解析消息队列中数据，获取数据的schema信息，根据schema信息生成对应的插入语句，将插入语句写入目标数据库。本发明不需要额外部署agent服务，不需要存储数据变更前后的数据，减轻了数据库压力；可以准实时同步数据的增删改场景，并且不需要业务表存在时间戳字段。

Description

一种增量数据同步的处理方法

技术领域

本发明属于数据同步技术领域，具体涉及一种增量数据同步的处理方法。

背景技术

随着现代信息科技的快速发展以及大数据技术日益完善与普及，越来越多的行业与公司使用大数据对自身数据进行处理分析。其中由于各种业务数据存在不同的数据库，不同类型的数据库中，数据同步成为大数据技术应用中十分重要的一环。SQL Server是微软推出的一种具有可扩展、高性能等特性的关系型数据库系统。作为发布比较早的数据库，SQL Server在市场中应用广泛。

目前SQL Server增量数据同步常用的方式包括：1、使用查询方式，通过系统时间与待抽取表的时间戳字段判断需要抽取的数据，但当业务表无时间戳字段时，就无法增量同步数据，并且对于物理删除数据，该方式也不能获取到删除数据；2、使用SQL Server CDC(Change Data Capture)，即变换数据捕获变更数据，该方案通过开启SQL Server CDC功能，从数据库中提取数据库表的增量变化数据，从而达到增量同步的目的，但是该方案需要额外部署agent服务，并且会记录每次数据变更前后的数据记录在变更表中，这无疑增加了数据库的压力。因此，提供一种不受源表表结构限制能捕获到数据物理删除的更加轻量级并的增量同步方式十分重要。

发明内容

本发明要解决的技术问题是：提供一种增量数据同步的处理方法，以至少解决上述部分技术问题。

为实现上述目的，本发明采用的技术方案如下：

一种增量数据同步的处理方法，包括以下步骤：

步骤1、创建待同步表对应的变更记录追踪表；

步骤2、在待同步表上创建触发器，通过触发器将待同步表的变更记录写入变更记录追踪表中；

步骤3、创建SQL Server的一个内联表值函数，基于变更记录追踪表并通过内联表值函数读取待同步表的增量数据；

步骤4、生成并运行同步任务，将增量数据同步到消息队列；

步骤5、消费并解析消息队列中数据，获取数据的schema信息，根据schema信息生成对应的插入语句，将插入语句写入目标数据库。

进一步地，所述变更记录追踪表中至少包含待同步表的变更版本、业务主键、操作类型和操作时间。

进一步地，所述变更版本的版本号为一个自增的整数，每次待同步表发生变更时，触发器将变更记录写入变更记录追踪表时，变更版本的版本号自动增加一。

进一步地，所述步骤3包括：步骤31、创建一个内联表值函数，输入传入版本；步骤32、内联表值函数通过查询变更记录追踪表中变更版本的版本号大于传入版本的版本号，根据业务主键分组查询每个业务主键最大的版本号及操作时间；步骤33、通过业务主键关联对应变更版本的待同步表，将当前待同步表的数据作为增量数据。

进一步地，所述步骤4包括：步骤41、创建增量同步任务和增量同步任务模板；步骤42、运行增量同步任务，基于增量同步任务模板生成同步任务；步骤43、运行同步任务，生成增量查询语句；步骤44、执行所述增量查询语句进行查询：将变更追踪表的变更版本输入内联表值函数，查询版本号大于传入版本的变更版本对应增量数据，将查询到的增量数据转换json格式并发送至消息队列中，供下游实时消费。

进一步地，所述步骤4还包括创建版本维护模块，通过所述版本维护模块记录并维护当前增量同步任务对应待同步表的变更版本，并且将每个待同步表变更版本的版本号持久化到外部存储设备中；通过所述版本维护模块记录当前查询的待同步表的最大变更版本的版本号。

进一步地，所述步骤4还包括创建schema版本维护模块，通过所述schema版本维护模块对比并维护当前增量同步任务对应待同步表的schema信息，每次执行增量查询后，从查询结果获取待同步表当前schema信息，与schema版本维护模块中最新版本的schema信息对比，如果schema信息发生变化，则将生成新的schema版本号并维护对应的schema信息，并且将每一个待同步表的schema版本及对应的schema持久化到外部存储设备中；通过所述schema版本维护模块记录每一条增量数据的schema信息，用以后续消费时获取增量数据的schema信息。

进一步地，所述步骤5包括：步骤51、配置数据写入任务模板；步骤52、启动flink程序，加载解析数据写入任务模板并生成实时消费应用；步骤53、运行所述实时消费应用，消费消息队列的数据；步骤54、解析消费到的消息队列的数据，获取消息队列的数据的变更版本和schema信息；步骤55、基于所述schema信息并根据目标数据库类型生成对应的插入语句，将插入语句写入目标数据库。

进一步地，所述步骤5还包括从消息队列的数据获取到变更版本后，从本地缓存获取当前变更版本对应schema信息，如果本地缓存中不存在当前变更版本对应的数据，则从schema版本维护模块中获取当前变更版本对应的schema信息，并将schema信息及变更版本维护到本地缓存中，方便后续相同版本号的数据直接从本地缓存获取schema信息。

与现有技术相比，本发明具有以下有益效果：

本发明基于SQL Server触发器更改记录跟踪功能，以创建的内联表值函数查询传入版本号大于变更版本号对应的数据记录，并通过创建增量同步任务自动生成对应的同步任务达到准实时同步SQL Server增量数据的目的。本发明与时间戳方式查询相比，解决了获取增量数据必须存在时间戳字段的限制且不能同步数据物理删除的场景；本发明与SQLServer CDC查询相比，不需部署agent服务，并且更改跟踪与数据更改同步进行，不需要异步读取事务日志，不需要记录每次变更前后的数据，大大降低了数据库的运行压力。

附图说明

图1为本发明的方法流程图。

具体实施方式

技术名词：

SQL Server：一种关系型数据库管理系统。

SQL Server CDC：记录SQL Server表的插入、更新和删除操作。

SQLServer触发器（Triggers）：SQL Server中的一种数据库对象，它是一段存储过程的特殊类型，可以在表上注册，并在表中的数据发生插入、更新或删除操作时自动触发执行。通过使用触发器，可以在数据发生变化时捕获并执行自定义的逻辑。触发器有两种类型：AFTER 触发器和INSTEAD OF 触发器。AFTER 触发器是在数据修改后执行，而INSTEADOF 触发器是在数据修改前执行，并允许你在触发器内部对数据进行修改。

agent服务：具有自主性、交互性、反应性和主动性的计算实体，能够根据环境和用户输入做出相应的行为调整。

flink应用：分层架构的分布式计算引擎。

schema：是一种数据结构。

json：是一种轻量级的数据交换格式。

key值：是数据变更版本号。

value值：是版本号对应的schema信息。

为了使本发明的目的、技术方案及优点更加清楚明白，以下结合附图，对本发明进一步详细说明。显然，所描述的实施例仅仅是本发明一部分实施例，而不是全部的实施例。基于本发明中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都属于本发明保护的范围。

如图1所示，本发明提供的一种增量数据同步的处理方法，包括以下步骤：

步骤1、创建与待同步表对应的变更记录追踪表（create table {待同步表}_sna(dbversion int IDENTITY(1,1) PRIMARY KEY,action_type varchar(10),action_timeDATETIME,业务主键varchar(50)。其中dbversion为自增主键，用于记录待同步表的变更版本号；action_type 为待同步表的操作类型，action_time表示记录的操作时间，业务主键与待同步表的业务主键名一致，用于存储待同步表的变更记录的业务主键。

步骤2、在待同步表上创建after触发器捕获待同步表的变更记录，并将变化记录写入变更记录追踪表中；

create trigger {待同步表}_after

on 待同步表}

AFTER INSERT,UPDATE,DELETE

as

BEGIN

IF EXISTS(select * from inserted )

BEGIN

INSERT INTO {待同步表}_sna(action_type,action_time,业务主键) select‘U’,getdate(),业务主键 from inserted；

end

else

BEGIN

INSERT INTO {待同步表}_sna(action_type,action_time,业务主键) select‘D’,getdate(),业务主键 from deleted；

END

END；

步骤3、创建SQL Server的一个内联表值函数f_dba_ct_{table}，基于变更记录追踪表并通过内联表值函数读取待同步表的增量数据。

步骤4、生成并运行同步任务，将增量数据转化为json数据并同步到消息队列。

步骤5、启动一个flink应用作为数据消费模块消费消息队列中数据，解析json数据、获取schema信息，根据schema信息生成对应插入语句写入目标数据库。

本发明基于SQL Server触发器更改记录跟踪功能，自定义内联表值函数，通过变更记录追踪获取数据表某个版本后的记录变更，不需要额外部署agent服务，不需要存储数据变更前后的数据，减轻了数据库压力；可以准实时同步SQL Serve数据的增删改场景，并且不需要业务表存在时间戳字段。

所述变更记录追踪表为创建的一张与待同步表一一对应的记录待同步表变更记录的表。在部分实施例中，所述变更记录追踪表中至少包含待同步表的变更版本、业务主键、操作类型和操作时间，待同步表的变更版本存储于自增主键中。所述变更版本的版本号是在创建触发器后，自动记录待同步表每次记录变更的版本号为一个自增的整数，每次待同步表中记录发生变更时，对应的变记录追踪表中增加一条记录，同时变更版本的版本号增加一。首次同步任务运行时，可以根据所需选择从某个版本开始同步，也可默认从待同步表对应的变更记录追踪记录表最小变更版本开始同步，后续同步任务会根据前一次同步任务返回结果维护各自当前待同步表对应的变更版本。所述变更操作类型为：D（删除）、U(更新或插入)，后续数据同步时就可以根据变更追踪表中的记录，通过主键关联待同步表获取每一个变更对应待同步表中的记录数据。

在部分实施例中，所述步骤3包括：步骤31、创建一个自定义的内联表值函数f_dba_ct_{table}，输入传入版本；步骤32、内联表值函数f_dba_ct_{tabl通过查询变更记录追踪表中版本号大于传入版本的变更版本，并根据业务主键分组，查询每个业务主键最大的版本号及操作时间等信息，并通过主键关联对应变更版本的待同步表，将该待同步表的数据作为增量数据。

在部分实施例中，所述步骤4包括：步骤41、创建增量同步任务和增量同步任务模板，所述增量同步任务用于加载增量同步任务模板，并且向增量同步任务模板填充源数据库的信息（包括数据库地址，账户，密码等）、待同步数据表的信息（表名，主键等）和消息队列的信息（topic前缀，发送数据间隔等）等；步骤42、运行增量同步任务，基于增量同步任务模板自动生成同步任务；步骤43、运行所述同步任务，生成增量查询语句select * from f_dba_ct_{待同步表}(dbVersion)；步骤44、执行所述增量查询语句select * from f_dba_ct_{待同步表}(dbVersion)进行查询：将变更记录追踪表的变更版本输入内联表值函数f_dba_ct_{table}，查询版本号大于传入版本的变更版本对应的增量数据，将查询到的增量数据发送至消息队列中，供下游实时消费。所述自定义内联表值函数f_dba_ct_{table}创建内容如下：

CREATE FUNCTION f_dba_ct_{待同步表}(@dbVersion bigint)

RETURNS TABLE AS RETURN (

SELECT action_type __op,dbversion __ver,D.* from

(select {业务主键},max(dbversion) dbversion,min(action_type) action_type from {table}_sna where dbversion>@dbVersion group by {业务主键})

AS temp

LEFT JOIN

dbo.@tableName AS D

ON temp.{业务主键} = D.{主键})；

其中__op为数据表的操作类型（包括U：update(插入或更新)，D:delete(删除)），__ver为数据变更表的变更版本号，其为一个递增的整数。其中，内联表值函数是以表的形式返回一个返回值，即它返回的是一个表。内联表值函数没有由BEGIN-END 语句括起来的函数体，其返回的表是由一个位于RETURN 子句中的SELECT 命令从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。

在部分实施例中，所述步骤4还包括创建版本维护模块：通过所述版本维护模块记录、维护当前同步任务对应待同步表的变更版本，并且将每个待同步表的变更版本的版本号持久化到外部存储中，避免同步服务宕机产生版本号遗失的风险；通过所述版本维护模块记录当前查询的待同步表的最大变更版本的版本号。查询语句select * from f_dba_ct_{待同步表}(dbVersion)，其中的dbVersion首次执行时默认为0，后续则从版本维护模块获取。

在部分实施例中，所述步骤4还包括创建schema版本维护模块：通过所述schema版本维护模块对比并维护当前增量同步任务对应待同步表的schema信息，每次执行增量查询后，从查询结果中获取待同步表当时的schema信息，与模块中维护的最新版本的schema信息对比，如果schema信息发生变化，则将生成新的schema版本号并维护对应的schema信息，并且将每一个待同步表的schema版本及对应的schema持久化到外部存储中，避免同步服务宕机导致schema版本遗失；通过所属schema版本维护模块记录每一条增量数据的schema信息可以在下游消费增量数据时通过数据记录中的版本号获取数据的schema信息。

在部分实施例中，增量同步任务中设置有同步任务的间隔查询时间，根据配置的间隔查询时间，间隔执行查询语句，并且将查询的结果与当前记录的schema版本号构造成json格式的数据发送至消息队列中，并记录该次查询最大的变更版本到版本维护模块中。

在部分实施例中，所述步骤5包括：步骤51、配置数据写入任务模板；步骤52、启动flink程序，加载解析数据写入任务模板并生成实时消费应用；步骤53、运行所述实时消费应用，消费消息队列的数据；步骤54、解析消费到的消息队列的数据，获取消息队列的数据的变更版本和schema信息；步骤55、基于所述schema信息并根据目标数据库类型生成对应的插入语句，将插入语句写入目标数据库。

所述数据写入任务模板包括不限于消息队列的连接信息，目标数据库的连接信息，消息队列的topic与目标数据库的目标表之间映射关系等。

在部分实施例中，所述步骤5还包括从消息队列的数据获取到变更版本后，从本地缓存获取当前变更版本对应schema信息，本地缓存由哈希表（concurrenthashmap）实现，变更版本为key值，schema信息为value值；如果本地缓存中不存在当前变更版本对应的数据，则从schema版本维护模块中获取当前变更版本对应的schema信息，并将schema信息及变更版本维护到本地缓存中，方便后续相同版本号的数据直接从本地获取schema信息。

作为优选，所述步骤1还包括设置跟踪结果保持期，该保持期主要为了设置变更追踪表中记录的保存周期，超过周期的记录会被数据库自动清除，主要是为了节省数据库存储空间。

作为优选，所述步骤2还包括设置是否要求记录待同步数据表已更新的列数据，如果设置记录待同步数据表已更新的列数据，那么在变更追踪记录表中会存储该次变更待同步表有哪些列发生了变更。

最后应说明的是：以上各实施例仅仅为本发明的较优实施例用以说明本发明的技术方案，而非对其限制，当然更不是限制本发明的专利范围；尽管参照前述各实施例对本发明进行了详细的说明，本领域的普通技术人员应当理解：其依然可以对前述各实施例所记载的技术方案进行修改，或者对其中部分或者全部技术特征进行等同替换；而这些修改或者替换，并不使相应技术方案的本质脱离本发明各实施例技术方案的范围；也就是说，但凡在本发明的主体设计思想和精神上作出的毫无实质意义的改动或润色，其所解决的技术问题仍然与本发明一致的，均应当包含在本发明的保护范围之内；另外，将本发明的技术方案直接或间接的运用在其他相关的技术领域，均同理包括在本发明的专利保护范围内。

Claims

1.一种增量数据同步的处理方法，其特征在于，包括以下步骤：

步骤1、创建待同步表对应的变更记录追踪表；

步骤4、生成并运行同步任务，将增量数据同步到消息队列；

2.根据权利要求1所述的一种增量数据同步的处理方法，其特征在于，所述变更记录追踪表中至少包含待同步表的变更版本、业务主键、操作类型和操作时间。

3.根据权利要求2所述的一种增量数据同步的处理方法，其特征在于，所述变更版本的版本号为一个自增的整数，每次待同步表发生变更时，触发器将变更记录写入变更记录追踪表时，变更版本的版本号自动增加一。

4.根据权利要求2所述的一种增量数据同步的处理方法，其特征在于，所述步骤3包括：步骤31、创建一个内联表值函数，输入传入版本；步骤32、内联表值函数通过查询变更记录追踪表中变更版本的版本号大于传入版本的版本号，根据业务主键分组查询每个业务主键最大的版本号及操作时间；步骤33、通过业务主键关联对应变更版本的待同步表，将当前待同步表的数据作为增量数据。

5.根据权利要求4所述的一种增量数据同步的处理方法，其特征在于，所述步骤4包括：步骤41、创建增量同步任务和增量同步任务模板；步骤42、运行增量同步任务，基于增量同步任务模板生成同步任务；步骤43、运行同步任务，生成增量查询语句；步骤44、执行所述增量查询语句进行查询：将变更追踪表的变更版本输入内联表值函数，查询版本号大于传入版本的变更版本对应增量数据，将查询到的增量数据转换json格式并发送至消息队列中，供下游实时消费。

6.根据权利要求5所述的一种增量数据同步的处理方法，其特征在于，所述步骤4还包括创建版本维护模块，通过所述版本维护模块记录并维护当前增量同步任务对应待同步表的变更版本，并且将每个待同步表变更版本的版本号持久化到外部存储设备中；通过所述版本维护模块记录当前查询的待同步表的最大变更版本的版本号。

7.根据权利要求5所述的一种增量数据同步的处理方法，其特征在于，所述步骤4还包括创建schema版本维护模块，通过所述schema版本维护模块对比并维护当前增量同步任务对应待同步表的schema信息，每次执行增量查询后，从查询结果获取待同步表当前schema信息，与schema版本维护模块中最新版本的schema信息对比，如果schema信息发生变化，则将生成新的schema版本号并维护对应的schema信息，并且将每一个待同步表的schema版本及对应的schema持久化到外部存储设备中；通过所述schema版本维护模块记录每一条增量数据的schema信息，用以后续消费时获取增量数据的schema信息。

8.根据权利要求7所述的一种增量数据同步的处理方法，其特征在于，所述步骤5包括：步骤51、配置数据写入任务模板；步骤52、启动flink程序，加载解析数据写入任务模板并生成实时消费应用；步骤53、运行所述实时消费应用，消费消息队列的数据；步骤54、解析消费到的消息队列的数据，获取消息队列的数据的变更版本和schema信息；步骤55、基于所述schema信息并根据目标数据库类型生成对应的插入语句，将插入语句写入目标数据库。

9.根据权利要求8所述的一种增量数据同步的处理方法，其特征在于，所述步骤5还包括从消息队列的数据获取到变更版本后，从本地缓存获取当前变更版本对应schema信息，如果本地缓存中不存在当前变更版本对应的数据，则从schema版本维护模块中获取当前变更版本对应的schema信息，并将schema信息及变更版本维护到本地缓存中，方便后续相同版本号的数据直接从本地缓存获取schema信息。