CN114036119A - 一种基于kettle和数据库日志的数据同步方法 - Google Patents

Abstract

本发明公开了一种基于kettle和数据库日志的数据同步方法，包括以下步骤：确定数据源与目标数据库；确定数据同步方式即全量同步和增量同步；根据同步方式执行相应的数据同步操作。其中，全量同步包括：初始化kettle配置，清空目标数据库中的数据，获取各数据源处数据，将数据加载至目标数据库；增量同步包括：获取并解析源数据库日志文件得到数据库的详细操作序列等信息，更新同步时间戳记录表，过滤无关信息并抽取整合事务操作信息，将增量序列发送到Kafka消息中间件队列，转换数据格式并批量写入目标数据库。本发明可以指定多个数据来源，对数据进行动态更新，配置方便，更新速度较快。

Description

一种基于kettle和数据库日志的数据同步方法

技术领域

本发明属于计算机软件领域，具体的涉及一种基于kettle和数据库日志的数据同步方法。

背景技术

随着信息技术的不断发展，互联网行业来到了一个新的阶段，每时每刻都有大量数据产生，政府单位和企事业单位均部署了数据库管理系统。然而随着企业规模的扩大和业务发展，相关联的业务系统也逐渐增多，这些业务系统在运行时需要进行数据共享或集成到数据中心里，因此持续的数据同步就成了关联业务系统中必不可少的一部分。但业务系统使用的数据库系统之间架构和版本都可能存在差异，这造成了所存储的数据的异构问题，数据库成为了与其他数据库不能相互交流的数据孤岛。随着企业的扩张和业务的升级，部门之间相互协作，部门开发的业务系统经常性地会出现需要接入其他部门的业务系统的情况，现有的数据中心为了业务系统能够正常地对外提供服务，就必须要集成和同步异构的数据库系统，来保证业务数据的一致性。

目前使用较多的数据同步方案有数据库语句数据同步、应用程序之间的定时同步、通过系统间RPC调用等方法，数据库语句导入工作量大效率低，应用程序之间的同步则会限制应用之间的扩展，远程调用会加重服务器的压力，且集成相对复杂。还有采用一些Etl工具在本地做数据转换，有一个名为kettle的开源Etl工具，由纯Java编写，在数据抽取上具有高效、稳定的特点。利用kettle工具，可以任意指定多个源数据库之间的数据流转，可以显著提升数据同步效率。现在常用Kettle做数据同步，其也适用于多数据源的数据同步，解决大多数的多源数据更新效率较低，更新结构灵活性较差的问题。此外，针对不同类型、不同更新频率、不同数据源的数据，需要根据不同场景需求采用不同的数据同步方式，例如监测数据通常数据量大且更新频率较快，若采用全量迁移，则会耗时耗力，导致资源浪费，因此希望以增量的方式进行数据同步。同时，若数据源对已进行增量同步的数据进行了修改，会造成元数据和目标数据库的不一致，此时需要对修改过的数据进行识别并重新加载到目标库，对于海量数据而言实现难度大。数据库日志是保证数据库正常运行的一种机制，用户对数据库所有的改动信息都会被记录，包括对数据库对象的增删改的操作以及事务的开始和回滚。现有的变更数据捕获技术大多依赖数据库本身的体制，例如影子表法和触发器法，这都需要数据库花费额外的时间或空间去维护，也因此对数据库自身的正常运行影响较大。而基于日志解析的同步技术是独立于数据库进程的，它只需读取日志文件的增量数据，对数据库的影响最小。如今市场上大多数数据库都具备日志记录的功能，可以通过日志解析的手段获取用户对数据库的操作信息并将操作序列在目的数据库进行复现。

发明内容

发明目的：为了克服现有技术的不足，本发明的目的是提供一种基于kettle和数据库日志的数据库同步方法。

技术方案：一种基于kettle和数据库日志的数据同步方法，包括以下步骤：

(1)获取数据源和目标数据库连接信息；

(2)确定数据同步方式；

(3)根据同步方式执行相应的数据同步操作。

进一步的，步骤(1)中获取数据源和目标数据库连接信息，包括以下步骤：

(11)确定数据源类型，包括：手工填写表格数据源、数据库源；

(12)根据数据源类型，确定数据源访问方法；

(13)确定目标数据库类型、访问方法和数据库读写方式；

(14)创建数据库基本信息表。

进一步的，步骤(2)中根据数据源数据的数量级、数据类型和数据更新频率确定数据同步方式。

进一步的，所述步骤(2)中确定的数据同步方式为全量同步时步骤(3)执行全量同步，在数据库同步更新时首先清空目标库中的数据，然后将数据源的数据全部重新加载到目标数据库，包括以下步骤：

(31)根据数据库基本信息初始化kettle配置；

(32)清除目标库中的数据；

(33)获取手工填写表格数据源的数据；

(34)获取数据库源的数据；

(35)将数据加载至目标数据库；

(36)定时重复执行步骤(32)到(35)。

更进一步的，所述步骤(31)中全量同步时在kettle图形界面中进行初始化配置，包括以下步骤：

(31-1)根据源数据库类型获取驱动程序；

(31-2)初始化源数据库和目标数据库连接与映射；

(31-3)初始化手工填写表格文件的存储路径；

(31-4)初始化数据同步间隔周期。

更进一步的，所述步骤(33)中获取手工填写表格源数据，包括以下步骤：

(33-1)根据手工填写表格文件的存储路径判断是否有数据文件；

(33-2)如果有文件，则进一步判断文件的类型；

(33-3)根据文件类型判断数据存储方式并进行数据存储。

更进一步的，所述步骤(34)中获取数据库源数据，根据查询条件获取数据库中需要同步的全部数据。

更进一步的，所述步骤(35)中利用kettle工具的作业和转换将数据加载至目标数据库，包括以下步骤：

(35-1)对手工填写的表格文件的数据进行规范化处理；

(35-2)读取获取的手工填写表格数据源和数据库源的数据；

(35-3)数据写入，根据映射关系将获得的数据加载到目标数据库。

进一步的，所述步骤(2)中确定的数据同步方式为增量同步时步骤(3)执行增量同步，增量同步的源数据为数据库类型，当源数据库中执行了增加、删除以及修改数据的操作时，源数据库只需要将更改的数据传同步到目的数据库，此外，还需要对同步的时间进行记录以便下次更新时定位到上次更新的数据范围。包括以下步骤：

(37)初始化增量同步时间戳记录表；

(38)日志获取与解析；

(39)更新增量同步时间戳记录表；

(310)对解析得到的数据进行过滤；

(311)将数据传输到Kafka消息队列；

(312)转换数据格式并写入到目标库；

(313)定时重复执行步骤(38)到(312)。

更进一步的，所述步骤(38)中获取源数据库的日志文件并进行解析，包括以下步骤：

(38-1)读取增量同步时间戳记录表，获取上一次日志捕获的范围；

(38-2)利用数据库官方的日志解析工具获取日志文件；

(38-3)生成数据库的数据字典文件并加入到解析配置；

(38-4)执行日志解析；

(38-5)将解析得到的SQL操作序列存入日志更新记录文件。

更进一步的，所述步骤(39)中增量同步时间戳记录表需要在日志解析工作完成之后进行更新，设置时间戳为当前时间。

更进一步的，所述步骤(310)中由于日志捕获和解析获得的信息包括数据库系统变更记录等不相关信息，需要对解析得到的数据进行过滤，以获得关于数据库增删改查相关的操作信息，包括以下步骤：

(310-1)保留日志更新记录文件中的DML操作，包括CRAETE(创建)，INSERT(插入)，DELETE(删除)，UPDATE(更新)，DROP(删除表)等操作；

(310-2)根据COMMIT(提交)和ROLLBACK(回滚)操作对日志更新记录文件进行过滤。当发现ROLLBACK语句时，说明用户对事务执行了回滚操作，数据库系统将撤销当前事务里所有已完成的操作，恢复到事务开始时的状态。当记录的操作类型为COMMIT时，事务所有操作被提交，保留事务已完成的操作。

(310-3)构建最终的日志记录，成功提交的事务被组织成事务链表，每个事务对应唯一的事务序列号。

更进一步的，所述步骤(311)中将过滤后的数据出传输到Kafka消息队列，包括以下步骤：

(311-1)在分布式消息队列Kafka中创建主题；

(311-2)将数据过滤得到的SQL操作序列作为消息发送到Kafka队列等待写入。

更进一步的，所述步骤(311-1)中在分布式消息队列Kafka中创建主题，Kafka队列以主题来划分不同的订阅内容，可以认为每一个主题代表一条子队列。当消息过多时，Kafka会拓展该主题的分区以容纳更多的消息。队列的一端是生产者，通过绑定主题，将SQL操作序列作为消息发送到Kafka队列中，队列通过主题和消息在分区中的偏移量来表示每一条消息。

更进一步的，所述步骤(312)中转换数据格式，由于需要同步的数据库之间可能存在异构问题，源数据库中可以执行的操作在目的数据库中可能会出现语法或语义错误而无法执行。在写入数据库之前对SQL序列中中的命名冲突、格式冲突和结构冲突等进行处理。

更进一步的，所述步骤(312)中转换数据格式并写入到目标库，包括以下步骤：

(312-1)从Kafka队列中拉取固定数量的消息记录；

(312-2)格式化SQL操作序列为目标数据库可写入格式；

(312-3)根据SQL序列在队列中的顺序依次执行SQL写入操作，将数据加载到目标数据库。

附图说明

图1是本发明所述方法的算法流程图。

图2是本发明所述方法的数据库源同步示意图。

具体实施方式

为了详细的说明本发明所公开的技术方案，下面结合说明书附图及具体实施例，做进一步的阐述。

参照图1，本发明提供了一种基于kettle和数据库日志的数据同步方法，包括如下步骤：

S1：获取数据源和目标数据库连接信息；

S2：确定数据同步方式；

S3：根据同步方式执行相应的数据同步操作。

其中，步骤S1中获取数据源和目标数据库连接信息，包括以下步骤：

S101：确定数据源类型Datasource_type，包括：手工填写表格数据源、数据库源；

S102：根据数据源类型，确定数据源访问方法；

S103：确定目标数据库类型、访问方法和数据库读写方式；

S104：创建数据库基本信息表，字段包括：数据源名称sname、数据源IP地址sip、数据源端口号spt、数据源类型stype、目标数据库名称tname、目标数据库IP地址tip、目标数据库端口号tpt、目标数据库用户名tuser、目标数据库表名ttable、目标数据库连接密码tpsw。

其中，步骤S2中根据数据源数据的数量级、数据类型和数据更新频率确定数据同步方式。

其中，步骤S2中确定的数据同步方式为全量同步时步骤S3执行全量同步，在数据库同步更新时首先清空目标库中的数据，然后将数据源的数据全部重新加载到目标数据库，包括以下步骤：

S301：根据数据库基本信息初始化kettle配置；

S302：清除目标库中的数据；

S303：获取手工填写表格数据源的数据；

S304：获取数据库源的数据；

S305：将数据加载至目标数据库；

S306：定时重复执行S302到S305。

其中，步骤S301中全量同步时在kettle图形界面中进行初始化配置，包括以下步骤：

S301-1：根据源数据库类型获取驱动程序；

S301-2：初始化源数据库和目标数据库连接与映射；

S301-3：初始化手工填写表格文件的存储路径；

S301-4：初始化数据同步间隔周期。

步骤S303中获取手工填写表格源数据，包括以下步骤：

S303-1：根据手工填写表格文件的存储路径判断是否有数据文件；

S303-2：如果有文件，则进一步判断文件的类型；

S303-3：根据文件类型判断数据存储方式并进行数据存储。

其中，手工填写表格数据是指手动收集或填写的结构化表格数据文件。手动收集或填写的结构化数据指xls、xlsx、csv等具有清晰规范数据组织结构的数据文件，存储时不改变其结构与内容。

步骤S304中获取数据库源数据，根据查询条件获取数据库中需要同步的全部数据。

步骤S305中利用kettle工具的作业和转换将数据加载至目标数据库，包括以下步骤：

S305-1：对手工填写的表格文件的数据进行规范化处理；

S305-2：读取获取的手工填写表格数据源和数据库源的数据；

S305-3：数据写入，根据映射关系将获得的数据加载到目标数据库。

其中，步骤S2中确定的数据同步方式为增量同步时步骤S3执行增量同步，增量同步的源数据为数据库类型，当源数据库中执行了增加、删除以及修改数据的操作时，源数据库只需要将更改的数据传同步到目的数据库，此外，还需要对同步的时间进行记录以便下次更新时定位到上次更新的数据范围。包括以下步骤：

S307：初始化增量同步时间戳记录表；

S308：日志获取与解析；

S309：更新增量同步时间戳记录表；

S310：对解析得到的数据进行过滤；

S311：将数据传输到Kafka消息队列；

S312：转换数据格式并写入到目标库；

S313：定时重复执行步骤S308到S312。

其中，步骤S307中初始化增量同步时间戳记录表，字段包括：数据库名称dbname、数据库IP地址dbip、数据库用户名dbuser、数据库表名dbtable、时间戳timestamp，时间的格式被设置为yyyy-mm-dd hh24:mi:ss；

其中，步骤S308中获取源数据库的日志文件并进行解析，包括以下步骤：

S308-1：读取增量同步时间戳记录表，获取上一次日志捕获的范围；

S308-2：利用数据库官方的日志解析工具获取日志文件；

S308-3：生成数据库的数据字典文件并加入到解析配置；

S308-4：执行日志解析；

S308-5：将解析得到的SQL操作序列存入日志更新记录文件。

步骤S309中增量同步时间戳记录表需要在日志解析工作完成之后进行更新，设置时间戳为当前时间。

步骤S310中由于日志捕获和解析获得的信息包括数据库系统变更记录等不相关信息，需要对解析得到的数据进行过滤，以获得关于数据库增删改查相关的操作信息，包括以下步骤：

S310-1：保留日志更新记录文件中的DML操作，包括CRAETE(创建)，INSERT(插入)，DELETE(删除)，UPDATE(更新)，DROP(删除表)等操作；

S310-2：根据COMMIT(提交)和ROLLBACK(回滚)操作对日志更新记录文件进行过滤。当发现ROLLBACK语句时，说明用户对事务执行了回滚操作，数据库系统将撤销当前事务里所有已完成的操作，恢复到事务开始时的状态。当记录的操作类型为COMMIT时，事务所有操作被提交，保留事务已完成的操作。

S310-3：构建最终的日志记录，成功提交的事务被组织成事务链表，每个事务对应唯一的事务序列号。

步骤S311中将过滤后的数据出传输到Kafka消息队列，包括以下步骤：

S311-1：在分布式消息队列Kafka中创建主题；

S311-2：将数据过滤得到的SQL操作序列作为消息发送到Kafka队列等待写入。

其中，步骤S311-1中在分布式消息队列Kafka中创建主题，Kafka队列以主题来划分不同的订阅内容，可以认为每一个主题代表一条子队列。当消息过多时，Kafka会拓展该主题的分区以容纳更多的消息。队列的一端是生产者，通过绑定主题，将SQL操作序列作为消息发送到Kafka队列中，队列通过主题和消息在分区中的偏移量来表示每一条消息。

步骤S312中转换数据格式，由于需要同步的数据库之间可能存在异构问题，源数据库中可以执行的操作在目的数据库中可能会出现语法或语义错误而无法执行。在写入数据库之前对SQL序列中中的命名冲突、格式冲突和结构冲突等进行处理。

步骤S312中转换数据格式并写入到目标库，包括以下步骤：

S312-1：从Kafka队列中拉取固定数量的消息记录；

S312-2：格式化SQL操作序列为目标数据库可写入格式；

S312-3：根据SQL序列在队列中的顺序依次执行SQL写入操作，将数据加载到目标数据库。

结合图2可知，本实施例中获取的具体同步框架可部署在单台计算机上，其中包含全量同步和增量同步两种同步方式。针对不同数量级、不同数据类型和不同更新频率的数据采用不同的数据同步方式。

全量同步主要通过kettle工具来实现，kettle连接源数据库和目的数据库，通过执行多个转换和作业，将源数据库的表和目的数据库的表一一映射，从源数据库表中查询所有数据写入到目的数据库表中，保证数据源和目标数据库数据的一致性。增量同步根据增量同步时间戳记录表的上次记录时间查询增量更新的日志信息，并解析得到SQL操作序列，同时更新增量时间戳记录表的记录时间；然后过滤得到的序列，将用户对源数据库所做的数据库变更操作还原，得到最原始的SQL执行语句；再经由Kafka消息中间件进行传输；对获取的SQL序列进行格式转换后写入数据库实现增量数据的同步。

Claims (10)

1.一种基于kettle和数据库日志的数据同步方法，其特征在于，该方法包括以下步骤：

(1)获取数据源和目标数据库连接信息；

(2)确定数据同步方式；

(3)根据同步方式执行相应的数据同步操作。

2.根据权利要求1所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，步骤(1)中获取数据源和目标数据库连接信息，包括以下步骤：

(11)确定数据源类型，包括：手工填写表格数据源、数据库源；

(12)根据数据源类型，确定数据源访问方法；

(13)确定目标数据库类型、访问方法和数据库读写方式；

(14)创建数据库基本信息表。

3.根据权利要求1所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，步骤(2)中根据数据源数据的数量级、数据类型和数据更新频率确定数据同步方式，所述同步方式包括全量同步和增量同步。

4.根据权利要求1所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(2)中确定的数据同步方式为全量同步时步骤(3)执行全量同步，在数据库同步更新时首先清空目标库中的数据，然后将数据源的数据全部重新加载到目标数据库，包括以下步骤：

(31)全量同步时在kettle图形界面中进行初始化配置，包括以下步骤：

(31-1)根据源数据库类型获取驱动程序；

(31-2)初始化源数据库和目标数据库连接与映射；

(31-3)初始化手工填写表格文件的存储路径；

(31-4)初始化数据同步间隔周期；

(32)清除目标库中的数据；

(33)获取手工填写表格数据源的数据，包括以下步骤：

(33-1)根据手工填写表格文件的存储路径判断是否有数据文件；

(33-2)如果有文件，则进一步判断文件的类型；

(33-3)根据文件类型判断数据存储方式并进行数据存储；

(34)获取数据库源的数据；

(35)利用kettle工具的作业和转换将数据加载至目标数据库，包括以下步骤：

(35-1)对手工填写的表格文件的数据进行规范化处理；

(35-2)读取获取的手工填写表格数据源和数据库源的数据；

(35-3)数据写入，根据映射关系将获得的数据加载到目标数据库；

(36)定时重复执行步骤(32)到(35)。

5.根据权利要求4所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(34)中获取数据库源数据，根据查询条件获取数据库中需要同步的全部数据。

6.根据权利要求1所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(2)中确定的数据同步方式为增量同步时步骤(3)执行增量同步，增量同步的源数据为数据库类型，当数据库源中执行了增加、删除以及修改数据的操作时，源数据库将更改的数据传同步到目的数据库，记录同步时间以确定下次更新时定位到上次更新的数据范围，包括以下步骤：

(37)初始化增量同步时间戳记录表；

(38)获取源数据库的日志文件并进行解析，包括以下步骤：

(38-1)读取增量同步时间戳记录表，获取上一次日志捕获的范围；

(38-2)利用数据库的日志解析工具获取日志文件；

(38-3)生成数据库的数据字典文件并加入到解析配置；

(38-4)执行日志解析；

(38-5)将解析得到的SQL操作序列存入日志更新记录文件；

(39)更新增量同步时间戳记录表；

(310)对解析得到的数据进行过滤，包括以下步骤：

(310-1)保留日志更新记录文件中的DML操作，包括创建(CRAETE)，插入(INSERT)，删除(DELETE)，更新(UPDATE)，删除表(DROP)操作；

(310-2)根据提交和回滚操作对日志更新记录文件进行过滤，当发现回滚语句时，则用户对事务执行了回滚操作，数据库系统将撤销当前事务里所有已完成的操作，恢复到事务开始时的状态，当记录的操作类型为提交时，事务所有操作被提交，保留事务已完成的操作；

(310-3)构建最终的日志记录，成功提交的事务被组织成事务链表，每个事务对应唯一的事务序列号；

(311)将过滤后的数据出传输到Kafka消息队列，包括以下步骤：

(311-1)在分布式消息队列Kafka中创建主题；

(311-2)将数据过滤得到的SQL操作序列作为消息发送到Kafka队列等待写入；

(312)转换数据格式并写入到目标库；

(313)定时重复执行步骤(38)到(312)。

7.根据权利要求6所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(39)中增量同步时间戳记录表在日志解析工作完成之后进行更新，设置时间戳为当前时间。

8.根据权利要求6所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(311-1)中在分布式消息队列Kafka中创建主题，Kafka队列以主题划分不同的订阅内容，每一个主题代表一条子队列，当消息数目超过预设阈值时，Kafka拓展该主题的分区以容纳更多的消息，队列的一端是生产者，通过绑定主题，将SQL操作序列作为消息发送到Kafka队列中，队列通过主题和消息在分区中的偏移量表示每一条消息。

9.根据权利要求6所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(312)中转换数据格式，由于需要同步的数据库之间可能存在异构问题，源数据库中可以执行的操作在目的数据库中可能会出现语法或语义错误而无法执行，在写入数据库之前对SQL序列中的命名冲突、格式冲突和结构冲突进行预先处理。

10.根据权利要求6所述的一种基于kettle和数据库日志的数据同步方法，其特征在于，所述步骤(312)中转换数据格式并写入到目标库，包括以下步骤：

(312-1)从Kafka队列中拉取固定数量的消息记录；

(312-2)格式化SQL操作序列为目标数据库可写入格式；

(312-3)根据SQL序列在队列中的顺序依次执行SQL写入操作，将数据加载到目标数据库。

Images