CN113495890A

CN113495890A - 一种批量异构数据源自动比对系统、方法及设备

Info

Publication number: CN113495890A
Application number: CN202110774008.3A
Authority: CN
Inventors: 高利平
Original assignee: Shanghai 2345 Network Technology Co ltd
Current assignee: Shanghai 2345 Network Technology Co ltd
Priority date: 2021-07-08
Filing date: 2021-07-08
Publication date: 2021-10-12
Anticipated expiration: 2041-07-08
Also published as: CN113495890B

Abstract

本申请公开了一种批量异构数据源自动比对系统、方法及设备，所述系统包括为每种数据源单独定义的数据源模块，用于创建临时表、并借助临时表对源表、目标表的总量及明细进行比对的数据比对模块，批量比对脚本模块以及比对结果形成模块。本申请基于总量查询和分页明细查询逐一将源表数组和目标表数组中的待比对的源表以及目标表进行比对，进而将批量比对结果进行txt文件存储，实现了异构数据源的混合批量自动比对。本申请的技术方案将数据源代码与比对代码分离，将网状比对链路变成星型比对链路，并借助临时表分批处理数据，解决了大数据量查询内存溢出的问题。

Description

一种批量异构数据源自动比对系统、方法及设备

技术领域

本发明涉及数据处理领域，尤其涉及一种批量异构数据源自动比对系统、方法及设备。

背景技术

异构数据源(disparate data source)广义上讲是指数据结构、存取方式、形式不一样的多个数据源。例如，在一个系统中，同时包含由关系型数据库Oracle/SQL SERVER/MYSQL、EXCEL文件或txt文本数据、XML数据等不同的数据源的一个整体，就是一个异构的数据源。

在数据测试过程中，经常需要对关系型数据库(MySQL、Oracle等)、非关系型数据库(Hive、ClickHouse等)等各种异构数据源的表数据做数据比对。异构数据不能直接通过一条SQL实现比对，需要通过脚本方式比对。

申请人的在先专利“一种表数据批量自动比对的控制方法及装置”(申请号为202011368859.X)可以实现任意数据源的比对(例如，Hive与MySQL)，但如果需要比对ClickHouse与Hive、ClickHouse与MySQL数据时，需要增加两个比对脚本。即通过脚本方式比对时，对于新增的数据源，不能复用已有脚本，脚本数目会成倍增长，而且不能实现异构数据源混合批量比对。

此外，在先专利“一种表数据批量自动比对的控制方法及装置”中，需要每次查询表的全部数据，然后借助Python中的pandas进行数据比对。当表数据量比较大时，一次查询全部数据会出现内存溢出，造成比对失败。

发明内容

本发明的目的在于提供一种批量异构数据源自动比对系统、方法及设备，以解决上述技术背景中提出的问题。

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

本申请第一个方面提供了一种批量异构数据源自动比对系统，包括：

多个数据源模块，每个数据源模块分别对应一种数据源，每个数据源模块被配置为用于创建数据源连接，关闭数据源连接，获取表结构，调取待比对的源表与目标表进行总量查询所获取的总量结果集，以及调取所述源表与目标表进行分页明细查询所获取的明细结果集；

数据比对模块，被配置为用于创建临时表，将数据源模块调取的总量结果集插入所述临时表中，得到源临时表和目标临时表，对源临时表和目标临时表进行比对，获取所述源表、目标表的总量比对结果和差异数据，以及将数据源模块调取的明细结果集插入所述临时表中，得到源临时表和目标临时表，对源临时表和目标临时表进行比对，获取所述源表、目标表的明细比对结果和差异数据；其中，所述临时表包括单字段临时表和双字段临时表；

批量比对脚本，被配置为将批量待比对的源表和目标表分别存储在数组中，调用所述数据比对模块对所述源表和目标表进行数据比对；

比对结果形成模块，被配置为根据所述源表和目标表的比对结果生成结果文件，以便于用户查看。

优选地，所述数据比对模块，还被配置为用于删除临时表。

优选地，所述数组的元素是存储数据源类型、数据源连接信息和表名的元组。

优选地，所述数据源的数据类型包括MySQL、Oracle、PostgreSQL、ClickHouse、Hive、OceanBase、HBase、OTS及ODPS中至少一种。

优选地，所述数据比对模块包括：

第一处理子模块，用于创建两字段临时表，作为多张待比对表数据中的至少一对源表以及目标表对应的总量临时表，将获取的源表的总量结果集、目标表的总量结果集插入到总量临时表中，比对源表和目标表对应的总量临时表，获取总量比对结果和差异数据；

第二处理子模块，用于创建所述源表和目标表对应的单字段临时表，将获取的源表的分页明细结果集、目标表的分页明细结果集插入到单字段临时表中，获取单字段临时表的分组明细数据；以及创建所述源表和目标表对应的两字段明细临时表，将获取的单字段临时表的分组明细数据插入到两字段明细临时表中，比对源表和目标表对应的两字段明细临时表，获取明细比对结果和差异数据。

优选地，所述数据源模块包括：

数据源连接子模块，用于获得所述数据源模块与其对应的数据源之间的通讯链路；

数据源关闭子模块，用于断开所述数据源模块与其对应的数据源之间的通讯链路；

第一获取子模块，用于查询多张待比对的表数据的表结构，并获取多张待比对的表数据的表字段，对所述表字段进行处理以获得总量查询SQL以及分页明细查询SQL。

更优选地，所述数据源模块还包括：

第二处理子模块，用于根据预设的每页返回的分页明细的最大条目数，以及根据所述第一获取子模块获取表结构，将表全部字段使用指定拼接符进行拼接，将拼接后的数据进行MD5加密，使之变为固定长度，返回拼接数据的MD5值作为分页明细查询的分页明细结果集。

本申请第二个方面提供了一种批量异构数据源自动比对方法，采用上述的一种批量异构数据源自动比对系统实现，所述方法包括：

步骤S1：为每种数据源单独定义一个数据源模块；

步骤S2：从存储有源表的源表数组和存储有目标表的目标表数组中取出一对待比对的源表和目标表；

步骤S3：根据源表、目标表的数据源类型，分别获取源表、目标表的总量结果集；

步骤S4：创建两字段临时表，作为源表和目标表对应的总量临时表；

步骤S5：将获取的源表的总量结果集插入到总量临时表中，将获取的目标表的总量结果集插入到总量临时表中；

步骤S6：比对源表和目标表对应的总量临时表，获取总量比对结果和差异数据，若比对一致，则在结果文件中将总量比对结果作为第一标记进行记录，执行步骤S7；若比对不一致，则在结果文件中将总量比对结果和差异数据作为第二标记进行记录，以及删除为源表、目标表创建的总量临时表，并返回步骤S2；

步骤S7：创建源表和目标表对应的单字段临时表；

步骤S8：根据步骤S1获取的源表、目标表的表总量，以及预设的每页返回的分页明细的最大条目数，分别获取源表、目标表的分页明细结果集，将获取的源表的分页明细结果集插入到单字段临时表中，将获取的目标表的分页明细结果集插入到单字段临时表中；获取单字段临时表的分组明细数据；

步骤S9：创建源表和目标表对应的两字段明细临时表，将单字段临时表的分组明细数据插入到两字段明细临时表中；

步骤S10：比对源表和目标表对应的两字段明细临时表，获取明细比对结果和差异数据，若比对一致，则在结果文件中将明细比对结果作为第三标记进行记录；若比对不一致，则在结果文件中将明细比对结果和差异数据作为第四标记进行记录；

步骤S11：删除创建的单字段临时表和两字段临时表；

步骤S12：对源表数组和目标表数组中的每一对待比对的源表和目标表执行步骤S1至步骤S11，直至完成所有源表和目标表的比对，并将总体比对结果进行输出。

优选地，所述源表数组、所述目标表数组的元素是存储数据源类型、数据源连接信息和表名的元组。

优选地，在步骤S3中，所述方法还包括：执行总量查询SQL，获取源表和目标表的总量结果集。

优选地，在步骤S8中，所述方法还包括：执行分页明细查询SQL，获取源表和目标表的分页明细结果集。

优选地，在步骤S6中，若所述源表以及所述目标表的总量结果集的比对结果不一致，将比对结果不一致的差异数据导出并存储至txt文件中。

优选地，在步骤S10中，若源表以及所述目标表的分页明细结果集的比对结果不一致，将比对结果不一致的差异数据导出并存储至txt文件中。

优选地，在步骤S10之后，还包括：将所述第一标记、和/或所述第二标记、和/或所述第三标记、和/或所述第四标记作为所述源表以及所述目标表的比对结果。

优选地，在步骤S12中，所述总体比对结果至少包括所有源表数组和目标表数组中待比对的源表和目标表的比对结果。

优选地，在步骤S3之前，还包括步骤S3’：组装字符串，获得总量查询SQL。

优选地，在步骤S8之前，还包括步骤S8’：查询多张待比对的表数据的表结构，并获取多张待比对的表数据的表字段，对所述表字段进行处理以获得分页明细查询SQL。

更优选地，所述步骤S8’还包括：根据预设的每页返回的分页明细的最大条目数，以及根据所获取的表结构，将表全部字段使用指定拼接符进行拼接，将拼接后的数据进行MD5加密，使之变为固定长度，返回拼接数据的MD5值作为分页明细查询的分页明细结果集。

本申请第三个方面公开了一种批量异构数据源自动比对设备，包括：

存储器，用于存储计算机程序；

处理器，用于执行所述计算机程序时实现上述的批量异构数据源自动比对方法的步骤；以及

显示器，用于对处理器生成的比对结果进行显示。

本申请第四个方面公开了一种计算机可读存储介质，所述计算机可读存储介质中存储有计算机执行指令，所述计算机执行指令被处理器执行时用于实现上述的批量异构数据源自动比对方法。

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

本申请公开了一种批量异构数据源自动比对系统、方法及设备，基于总量查询和分页明细查询逐一将源表数组和目标表数组中的待比对的源表以及目标表进行比对，进而将批量比对结果进行txt文件存储，实现了异构数据源的混合批量自动比对。本申请技术方案具有以下特点：

1)本申请将数据源代码与比对代码分离，将网状比对链路变成星型比对链路。本申请为每种数据源建立一个独立的数据源模块，当需要接入一种新的数据源时，只需要定义一个关于新数据源的数据源模块，就能复用已有的数据源，实现与已有数据源数据的自动比对，解决了现有技术中异构数据比对中，对于新增的数据源，不能复用已有脚本，脚本数目成倍增长的问题。

2)本申请借助临时表分批处理数据，解决了大数据量查询内存溢出的问题。本申请采用分页查询表数据，每次最多查询指定条目的数据，然后将分页处理的数据写入临时表中，最后直接比对源表和目标表对应的临时表数据，解决了现有技术中，当表数据量比较大时，一次查询全部数据会出现内存溢出，造成比对失败的问题。

附图说明

构成本申请的一部分附图用来提供对本申请的进一步理解，本申请的示意性实施例及其说明用于解释本申请，并不构成对本申请的不当限定。在附图中：

图1是本申请的一种批量异构数据源自动比对系统的组成结构示意图；

图2是本申请的一种批量异构数据源自动比对方法的流程示意图；

图3是现有技术中的数据源比对所形成的网状比对链路示意图；

图4是本申请中的数据源比对所形成的星型比对链路示意图。

具体实施方式

为使本发明的目的、技术方案及效果更加清楚、明确，以下参照附图并举实例对本发明进一步详细说明。应当理解，此处所描述的具体实施例仅用以解释本发明，并不用于限定本发明。

需要说明的是，本发明的说明书和权利要求书及上述附图中的术语“第一”、“第二”等是用于区别类似的对象，而不必用于描述特定的顺序或先后次序，应该理解这样使用的数据在适当情况下可以互换。此外，术语“包括”和“具有”以及它们的任何变形，意图在于覆盖不排他的包含，例如，包含了一系列步骤或单元的过程、方法、系统、产品或设备不必限于清楚地列出的那些步骤或单元，而是可包括没有清楚地列出的或对于这些过程、方法、产品或设备固有的其它步骤或单元。

如背景技术部分所述，现有的数据比对方法中，异构数据通过脚本方式比对时，对于新增的数据源，不能复用已有脚本，造成脚本数目成倍增长，而且不能实现异构数据源混合批量比对。

有鉴于此，本申请在已有的现有技术的基础上进行了优化。

参阅图1，本申请的一种批量异构数据源自动比对系统，包括如下模块：

数据源模块1，每个数据源模块分别对应一种数据源，每个数据源模块被配置为用于创建数据源连接，关闭数据源连接，获取表结构，调取待比对的源表与目标表进行总量查询所获取的总量结果集，以及调取所述源表与目标表进行分页明细查询所获取的明细结果集；

数据比对模块2，被配置为用于创建临时表，将数据源模块调取的总量结果集插入所述临时表中，得到源临时表和目标临时表，对源临时表和目标临时表进行比对，获取所述源表、目标表的总量比对结果和差异数据，以及将数据源模块调取的明细结果集插入所述临时表中，得到源临时表和目标临时表，对源临时表和目标临时表进行比对，获取所述源表、目标表的明细比对结果和差异数据；其中，所述临时表包括单字段临时表和双字段临时表；

批量比对脚本3，被配置为将批量待比对的源表和目标表分别存储在数组中，调用所述数据比对模块对所述源表和目标表进行数据比对；

比对结果形成模块4，被配置为根据所述源表和目标表的比对结果生成结果文件，以便于用户查看。

具体地，为每种数据源单独定义一个所述数据源模块1，所述数据源的数据类型包括MySQL、Oracle、PostgreSQL、ClickHouse、Hive、OceanBase、HBase、OTS及ODPS中至少一种，每个所述数据源模块1被配置为可实现如下的方法：

(1)创建数据源连接的方法(get_connection)：用于获得所述数据源模块与其对应的数据源之间的通讯链路。

(2)关闭数据源连接的方法(close_connection)：用于断开所述数据源模块与其对应的数据源之间的通讯链路。

(3)返回表结构的方法(table_name_column)；用于查询多张待比对的表数据的表结构，并获取多张待比对的表数据的表字段。

(4)返回总量查询SQL和总量查询结果的总量方法(count_sql_data)。

总量查询SQL：

select'count'as pk_col,count(*)as cnt from库名.表名

其中，返回列名可以自定义设置，但是需要保持一致(下面以pk_col和cnt列名进行说明)。

(5)返回分页明细查询SQL和分页明细查询结果的分页明细方法(detail_sql_data_page)。

其中，分页处理过程为：通过limit offset限制每页返回的最大条目数，调用返回表结构的方法获取表的结构，然后将表全部字段使用指定拼接符($)拼接，最后返回拼接数据的MD5值。

其中，分页明细查询SQL(以Hive为例)：

select MD5(concat_ws('$',cast(`{字段1}`as string),cast(`{字段2}`asstring),cast(`{字段3}`as string)))as pk_col

from库名.表名limit{自定义分页条目数}offset{页码}

具体地，所述数据比对模块2被配置为可实现如下的方法：

(1)创建两字段临时表方法(create_temp_table)。

创建两字段临时表SQL：

create table if not exists{临时表表名}(`pk_col`string,`cnt`string)

(2)创建单字段临时表方法(create_temp_table_single_col)；

创建单字段临时表SQL：

create table if not exists{临时表表名}(`pk_col`string)

(3)删除临时表方法(drop_temp_table)；

删除临时表SQL：

drop table if exists{临时表表名}

(4)返回单字段临时表分组查询SQL和查询结果的分组明细方法(detail_sql_data)；

分组查询SQL：

select pk_col,count(*)as cnt from{临时表表名}group by pk_col

(5)向临时表插入数据方法(insert_into_table)；

插入SQL：

insert into{临时表表名}values{分批获取的明细数据}

(6)返回源临时表和目标临时表的比对结果和差异数据的表对比方法(compare_two_table)；

比对SQL：

select t1.pk_col,t1.cnt,t2.cnt

from{源临时表}t1

left join{目标临时表}t2

on t1.pk_col＝t2.pk_col

where t2.pk_col is null or coalesce(t1.cnt,-99)！＝coalesce(t2.cnt,-999)

limit 1

需要说明的是：因Hive可以存储和处理大数据量的表，所以中间临时表选择使用Hive表，也可以为其它类型表。

具体地，所述批量比对脚本3被配置为可实现：

(1)定义存储批量比对结果的txt文件；

(2)将批量待比对源表和目标表分别存储在数组中，数组的元素是存储数据源类型、数据源连接信息和表名的元组，即数据源类型、数据源连接信息和表名以元组形式存储在数组中；

(3)创建数据比对模块对象，用于调用数据比对模块的方法；

(4)设置分页条目数上限；

(5)进行表数据比对。

参阅图2，本申请的一种批量异构数据源自动比对方法，包括如下步骤：

步骤S1：为每种数据源单独定义一个数据源模块；其中，所述数据源的数据类型包括MySQL、Oracle、PostgreSQL、ClickHouse、Hive、OceanBase、HBase、OTS及ODPS中至少一种。

步骤S2：从zip打包的源表数组和目标表数组中取出一对待比对的源表和目标表。

步骤S3：根据表的数据源类型调用对应数据源模块的总量方法(count_sql_data)，获取源表和目标表的总量数据以及总量查询SQL。

步骤S4：调用数据比对模块的创建两字段临时表方法(create_temp_table)，创建源表和目标表对应的总量临时表。

步骤S5：调用数据比对模块的插入数据方法(insert_into_table)，将步骤S3获取的总量数据插入步骤S4创建的总量临时表中。

步骤S6：调用数据比对模块的表对比方法(compare_two_table)，比对源表和目标表对应的总量临时表，获取总量比对结果和差异数据。

步骤S7：判断总量比对结果是否为数据一致。如果总量比对结果为“数据不一致”，则执行步骤S8；如果为“数据一致”，则执行步骤S9进行明细数据比对。

步骤S8：将源表和目标表总量查询SQL以及差异数据写入txt文件中，然后执行步骤S18。

步骤S9：调用数据比对模块的创建单字段临时表方法(create_temp_table_single_col)，创建源表和目标表对应的单字段临时表。

步骤S10：根据步骤S2获取的表总量和预设的分页条目数上限，循环调用对应数据源模块的分页明细方法(detail_sql_data_page)，获取源表和目标表的分页明细数据以及分页明细查询SQL，然后将分页明细数据插入步骤S9创建的单字段临时表中。

步骤S11：调用数据比对模块的分组明细方法(detail_sql_data)，获取单字段临时表的分组明细数据。

步骤S12：调用数据比对模块的创建两字段临时表方法(create_temp_table)，创建源表和目标表对应的两字段明细临时表。

步骤S13：调用数据比对模块的插入数据方法(insert_into_table)，将步骤S11获取的单字段临时表分组明细数据插入步骤S12创建的两字段明细临时表中。

步骤S14：调用数据比对模块的表对比方法(compare_two_table)，比对源表和目标表对应的两字段明细临时表，获取明细比对结果和差异数据。

步骤S15：判断明细比对结果是否为数据一致。如果比对结果为“数据不一致”，则执行步骤S16，如果为“数据一致”，则执行步骤S17。

步骤S16：将源表和目标表的分页明细查询SQL以及差异数据写入txt文件中。

步骤S17：调用数据比对模块的删除临时表方法(drop_temp_table)，删除创建的单字段临时表。

步骤S18：调用数据比对模块的删除临时表方法(drop_temp_table)，删除创建的两字段临时表，然后返回执行步骤S2。

另一方面，本申请还公开了一种批量异构数据源自动比对设备，包括：存储器，用于存储计算机程序；处理器，用于执行所述计算机程序时实现上述的批量异构数据源自动比对方法的步骤；以及显示器，用于对处理器生成的比对结果进行显示。

另一方面，本申请还公开了一种计算机可读存储介质，所述计算机可读存储介质中存储有计算机执行指令，所述计算机执行指令被处理器执行时用于实现上述的批量异构数据源自动比对方法。

综上所述，本申请的技术方案在现有技术的基础上进行了两点优化：

1、将网状比对链路变成了星型比对链路。

参阅图3和图4，现有技术可以实现任意数据源的比对(例如，Hive与MySQL)，但如果需要比对ClickHouse与Hive、ClickHouse与MySQL数据时，需要增加两个比对脚本，即通过脚本方式比对时，对于新增的数据源，不能复用已有的脚本，脚本数目成倍增长，而且不能实现异构数据源混合批量比对。本申请将数据源代码与比对代码分离，每种数据源配置一个独立的数据源模块。当需要接入一种新的数据源时，只需要定义一个关于新数据源的数据源模块(例如，ClickHouse)，就能复用已有的数据源，实现与已有数据源数据的自动比对(例如，ClickHouse与Hive、ClickHouse与MySQL)，即本申请的技术方法将网状比对链路变成了星型比对链路。

2、借助临时表分批处理数据，解决了大数据量查询内存溢出的问题。

现有技术需要每次查询表全部数据，然后借助python中pandas进行数据比对。当表数据量比较大时，一次查询全部数据会出现内存溢出，造成比对失败。本申请借助临时表分批处理数据，只需提供表的数据源类型、数据源连接信息和表名即可实现异构数据源混合批量比对(例如，Hive与Hive，Hive与MySQL，Hive与ClickHouse，MySQL与ClickHouse)。本申请还采用分页查询表数据，每次最多查询指定条目的数据，然后对查询出的数据进行MD5加密，避免因超长被截断且减少了存储量，之后将分页处理的数据写入临时表中，最后直接比对源表和目标表对应的临时表数据，解决了大数据量查询内存溢出的问题。

以上对本发明的具体实施例进行了详细描述，但其只是作为范例，本发明并不限制于以上描述的具体实施例。对于本领域技术人员而言，任何对本发明进行的等同修改和替代也都在本发明的范畴之中。因此，在不脱离本发明的精神和范围下所作的均等变换和修改，都应涵盖在本发明的范围内。

Claims

1.一种批量异构数据源自动比对系统，其特征在于，包括：

2.根据权利要求1所述的一种批量异构数据源自动比对系统，其特征在于，所述数组的元素是存储数据源类型、数据源连接信息和表名的元组。

3.根据权利要求1所述的一种批量异构数据源自动比对系统，其特征在于，所述数据源的数据类型包括MySQL、Oracle、PostgreSQL、ClickHouse、Hive、OceanBase、HBase、OTS及ODPS中至少一种。

4.根据权利要求1所述的一种批量异构数据源自动比对系统，其特征在于，所述数据比对模块包括：

5.根据权利要求1所述的一种批量异构数据源自动比对系统，其特征在于，所述数据源模块包括：

6.根据权利要求5所述的一种批量异构数据源自动比对系统，其特征在于，所述数据源模块还包括：

7.一种根据权利要求1～6任一项所述的批量异构数据源自动比对系统的自动比对方法，其特征在于，包括：

步骤S1：为每种数据源单独定义一个数据源模块；

步骤S7：创建源表和目标表对应的单字段临时表；

步骤S11：删除创建的单字段临时表和两字段临时表；

8.根据权利要求7所述的一种批量异构数据源自动比对方法，其特征在于，在步骤S3之前，还包括步骤S3’：组装字符串，获得总量查询SQL；以及

在步骤S8之前，还包括步骤S8’：查询多张待比对的表数据的表结构，并获取多张待比对的表数据的表字段，对所述表字段进行处理以获得分页明细查询SQL。

9.根据权利要求8所述的一种批量异构数据源自动比对方法，其特征在于，所述步骤S8’还包括：根据预设的每页返回的分页明细的最大条目数，以及根据所获取的表结构，将表全部字段使用指定拼接符进行拼接，将拼接后的数据进行MD5加密，使之变为固定长度，返回拼接数据的MD5值作为分页明细查询的分页明细结果集。

10.一种批量异构数据源自动比对设备，其特征在于，包括：

存储器，用于存储计算机程序；

处理器，用于执行所述计算机程序时实现如权利要求7～9任一项所述的批量异构数据源自动比对方法的步骤；以及

显示器，用于对处理器生成的比对结果进行显示。