CN112035468A

CN112035468A - 基于内存计算、web可视化配置的多数据源ETL工具

Info

Publication number: CN112035468A
Application number: CN202010856829.7A
Authority: CN
Inventors: 王一君; 陈灿; 王长振; 谢鸿旺
Original assignee: Hangzhou Lanzhong Data Technology Co ltd
Current assignee: Hangzhou Lanzhong Data Technology Co ltd
Priority date: 2020-08-24
Filing date: 2020-08-24
Publication date: 2020-12-04

Abstract

本发明公开了基于内存计算、web可视化配置的多数据源ETL工具，其特征在于包括数据源层、作业层及目标数仓层，数据源层用于连接客户的数据关系数据库；作业层包括数据源层与目标数仓层的映射配置以及基于spark的数据传输模块；映射配置体现在目标数仓层中数仓目标表与数据源层中数据来源业务表之间的关系；基于spark的数据传输模块能够使用spark的dataframe写表的自动建表功能，提高数据传输效率；目标数仓为公司研发的使用经典的4层架构标准数仓。本发明全部基于内存实现，性能相比现有工具和方法更高效。本发明通用性好，基本所有数据源通过Java接口都通用。本发明针对公司内部需要针对性研发，适用于拥有标准的大数据产品及后台表结构的大数据服务公司。

Description

基于内存计算、web可视化配置的多数据源ETL工具

技术领域

本发明属于数据处理领域，具体涉及一种基于内存计算、web可视化配置的多数据源ETL工具系统及其应用。

背景技术

作为一家大数据服务公司，拥有标准的大数据产品及后台表结构，但在服务各个客户时，面对的业务系统五花八门、数据库各不相同，在数据对接环节费时费力。

因此，需要一种能够快速实现数据对接的自动化ETL工具来加速这一步骤。

发明内容

为了克服现有技术和方法上的不足，本发明的目的在于一种基于内存计算、web可视化配置的多数据源ETL工具，用于提升数据对接效率。

本发明包括数据源层、作业层及目标数仓层，数据源层用于连接客户的数据关系数据库，可支持常用的关系数据库；作业层包括数据源层与目标数仓层的映射配置以及基于spark的数据传输模块；

映射配置主要体现在目标数仓层中数仓目标表与数据源层中数据来源业务表之间的关系；基于spark的数据传输模块能够使用 spark的dataframe写表的自动建表功能，提高数据传输效率；

目标数仓为公司研发的标准数仓，该标准数仓使用经典的4层架构，分别为ods、edw、dm和rst，其中edw为按照标准Kimball模型创建的维度表和事实表，无论客户的业务系统什么结构，全部统一到标准edw表，edw表结构可提前初始化，同时也支持自定义目标表；初始化的目标表target_table最后会体现在各个作业的目标映射中。

进一步的，目标数仓中的商品维表的字段包括：款号 Product_code、颜色Color_code、尺码Size_code、尺码顺序号Size_order，均分别来自于业务表。

进一步的，通过本发明工具，能够在web页面上直接拖拽配置出上述映射关系。

进一步的，web页面配置是从左到右依次将相关信息存储到对应表中；反过来从右到左则可以推导出其来源表、来源字段，以及来源表的关联关系，后续的自动生成sql脚本步骤遵循此流程。

数据源信息会保存在datasoure和datasource_tree表， datasoure存储各个数据源的连接信息，datasource_tree则存储全部数据源的表及字段，涵盖库、schema、表、字段4级结构，当被引用时，在web页面中的最左边显示为数据源树，可点击展开；且在 web页面即可读取源表及其字段，并通过拖拽的可视化手段能够形成源表的关联关系以及与目标表的字段映射；连接生成目标表所需要的源数据库，并在源数据库中选择所需的源表；完成字段映射后，自动化生成ETL抽数的ods脚本以及加工dim和fct的数据脚本。

进一步的，可视化手段的实现如下：

将web页面最左侧第1栏中的表拖至第2栏，并选择所需字段，即形成图中的每个图形块，同时将有关联关系的表进行连线，形成由一张主表为核心的星型或是雪花型架构；可视化手段实现的页面配置会保存到job_table作业表、job_table_column作业表字段、job_table_relation作业表关系中。

进一步的，web页面中所有在第3栏中出现的表及字段都会出现在第3栏，同时第3栏还增加基于多个表的计算字段。

进一步的，根据web页面中第4栏的源、目标的字段映射，依次往前追溯，能够找到目标表以及作业的全部字段，并根据作业字段追溯出作业的表、作业表的字段、作业表间的关联关系，由作业表追溯出其所在的数据源，汇总出每个数据源的每张表的每个字段，即可编写ETL的ods脚本；

同时再结合表间关联关系以及目标表，转化为所有ods表的关联关系并匹配好每个关联的所需字段，即可编写对应到目标表的sql，并在最后一步写入目标表时调整好字段的对应顺序。

进一步的，对生成的ods脚本调用spark-ETL模块，实现源表到数仓ods层的数据传输，支持全量、增量；且整个工作的作业全部完成后，后台能够自动计算出所有脚本的依赖关系，并生成调度的作业依赖表job_relation；这些依赖关系被airflow调用后可视化展现。

本发明有益效果如下：

本发明全部基于内存实现，性能相比现有工具和方法更高效。

本发明通用性好，基本所有数据源通过Java接口都通用。

本发明针对公司内部需要针对性研发，适用于拥有标准的大数据产品及后台表结构的大数据服务公司。

附图说明

图1为本发明示意图；

图2为本发明web页面配置示意图；

图3为本发明流程实现示意图；

具体实施方式：

下面结合附图和实施例对本发明作进一步说明。

本发明的大数据ETL系统，基于以下技术方法实现：

基于内存计算、web可视化配置的多数据源ETL工具，包括数据源层、作业层及目标数仓层，数据源层用于连接客户的数据关系数据库，且在权限模块做了精简优化。常用的数据库连接工具都是默认展示数据库的所有用户对象，比如库、表等，但当双击展开库某张表时却提示用户权限。本工具会根据业务库的系统表，查询出只属于该用户有权限访问的表对象，将客户几千上万张的表对象缩减到仅需的几十张。同时会将该几十表结构信息缓存到本地，方便后续做映射操作。

可支持常用的关系数据库。作业层包括数据源层与目标数仓层的映射配置以及基于spark的数据传输模块。

映射配置主要体现在目标数仓层中数仓目标表与数据源层中数据来源业务表之间的关系，如：

例如：数仓中的商品维表Dim_sku,有款号Product_code、颜色 Color_code、尺码Cize_code、尺码顺序号Cize_order等字段，分别来自于业务表:

通过本发明工具，能够在web页面上直接拖拽配置出上述映射关系。

数据传输层包含一个基于spark的数据传输模块。经典的开源解决方案，hive一般使用sqoop抽数，但sqoop需要提前建表，对接各个不同的客户业务系统，手动建表工作量大，还要考虑业务数据库和hive表的字段类型的差异。使用spark数据模块，可以使用spark的dataframe写表的自动建表功能，同时还能大幅提高数据传输效率，sqoop底层是mapreduce实现，而spark使用全内存方案，可以达到2-10倍的性能提升。对于业务库中特殊字段类型和字段的特殊字符值，数据抽取出现问题时，使用自开发的spark数据模块更可控，更易排查问题。

进一步地，目标数仓为公司研发的标准数仓，该标准数仓使用经典的4层架构，分别为ods、edw、dm和rst，其中edw为按照标准 Kimball模型创建的维度表和事实表，无论客户的业务系统什么结构，全部统一到标准edw表，edw表结构可提前初始化，同时也支持自定义目标表。初始化的目标表target_table最后会体现在各个作业的目标映射中，即图1中最右边的字段列，这些就是该作业所需的目标字段。

实现过程如图2所示，web页面配置是从左到右依次将相关信息存储到对应表中。反过来从右到左则可以推导出其来源表、来源字段，以及来源表的关联关系，后续的生成sql脚本步骤遵循此流程。

进一步地，连接生成目标表所需要的源数据库，并在源数据库中选择所需的源表。

数据源信息会保存在datasoure和datasource_tree表， datasoure存储了各个数据源的连接信息，如IP地址、端口、数据库名、用户名、密码等。datasource_tree则存储了全部数据源的表及字段，涵盖库、schema、表、字段4级结构，当被引用时，在图中的最左边显示为数据源树，可点击展开。

进一步地，在web页面即可读取源表及其字段，并通过拖拽等可视化手段即可形成源表的关联关系以及与目标表的字段映射。

将最左侧即第1栏中的表拖至第2栏，并选择所需字段，即形成图中的每个图形块，同时将有关联关系的表进行连线，形成由一张主表为核心的星型或是雪花型架构。此部分的页面配置会保存到 job_table作业表、job_table_column作业表字段、 job_table_relation作业表关系中。

进一步地，所有在第3栏中出现的表及字段都会出现在第3栏，同时第3栏还可以增加基于多个表的计算字段，如金额由a表的价格和b表的数量而得。此部分数据会保存在job_columns中。

进一步地，在完成字段映射后，自动化生成ETL抽数的ods脚本，以及加工dim和fct的数据脚本。

由图中第4栏的源、目标的字段映射，依次往前追溯，可以找到目标表(含字段)以及作业的全部字段，并根据作业字段追溯出作业的表、作业表的字段、作业表间的关联关系，由作业表可追溯出其所在的数据源，汇总出每个数据源的每张表的每个字段(不同作业可能用到相同源表的不同字段)，即可编写ETL的ods脚本。

同时，再结合表间关联关系以及目标表，转化为所有ods表的关联关系并匹配好每个关联的所需字段，即可编写对应到目标表的sql，并在最后一步写入目标表时调整好字段的对应顺序。

进一步地，对生成的ods等脚本调用spark-ETL模块，即可实现源表到数仓ods层的数据传输，支持全量、增量。

进一步地，整个工作的作业全部完成后，后台可自动计算出所有脚本的依赖关系，并生成调度的作业依赖表job_relation。这些依赖关系被airflow调用后可视化展现：

所有过程除了配置数据源和字段映射需要人工操作外，其他全部由工具自动化生成。

如图3所示，基于内存计算、web可视化配置的多数据源ETL工具的实现方法如下：

步骤1、预配置目标表

市面上的自动化ETL工具都没有目标表，只能自动化生成即席查询的sql。本发明工具更进一步，直接将客户源与产品目标表对应起来。图2所示为本工具的最核心页面，目标表体现在图2中的第四栏 (最右边)具体的：目标表主要包括商品维表、店仓维表、主码表、零售表、库存表、出入库表、库存移动表等。

步骤2、配置数据源

数据源配置在另一个独立的页面，配置好以后会体现在图2中的第1栏，可树形展开直至表字段。

步骤3、源表选择与关联

图2中的第2栏中选择目标表所需要的各个源表及其对应关系，表现为星型或雪花模型。

步骤4、字段与计算字段

第2栏选择了各个表的所需字段，以及在现有字段基础上衍生出的计算字段。这些字段都会体现在第3栏。第3栏不需人的操作。

步骤5、字段映射

第4栏体现源与目标的字段映射关系，将第3栏中的备选字段拖拽到第4栏。

步骤6、自动化作业脚本

根据第4栏的映射及第2栏中的表及关联关系，自动计算出所需要的所有源表和所有字段，并生成自动抽取这些表到数仓的ods脚本，以及加工这些ods表到edw目标表的数据转换计算脚本，通称作业脚本。

步骤7、自动化调度依赖

整个数仓作业配置完成后，会产生大量的ods和edw作业脚本，人工整理这些脚本的依赖关系，不仅费时而且易出错，但是本工具可自动生成调度依赖关系。

步骤8、自动化运行

自动化产生作业脚本及调度依赖后，部署到对应服务器即可实现自动化运行。

总之，通过本发明工具可实现后台大量的脚本自动化编写，大大缩短了项目实施周期，降低了大数据开发的工作难度及人员素质要求，而且避免了当前人工开发时个体的代码差异。

Claims

1.基于内存计算、web可视化配置的多数据源ETL工具，其特征在于包括数据源层、作业层及目标数仓层，数据源层用于连接客户的数据关系数据库，可支持常用的关系数据库；作业层包括数据源层与目标数仓层的映射配置以及基于spark的数据传输模块；

映射配置主要体现在目标数仓层中数仓目标表与数据源层中数据来源业务表之间的关系；基于spark的数据传输模块能够使用spark的dataframe写表的自动建表功能，提高数据传输效率；

2.根据权利要求1所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于目标数仓中的商品维表的字段包括：款号Product_code、颜色Color_code、尺码Size_code、尺码顺序号Size_order，均分别来自于业务表。

3.根据权利要求1所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于通过该工具，能够在web页面上直接拖拽配置出上述映射关系。

4.根据权利要求1所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于web页面配置是从左到右依次将相关信息存储到对应表中；反过来从右到左则可以推导出其来源表、来源字段，以及来源表的关联关系，后续的自动生成sql脚本步骤遵循此流程。

5.根据权利要求4所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于数据源信息会保存在datasoure和datasource_tree表，datasoure存储各个数据源的连接信息，datasource_tree则存储全部数据源的表及字段，涵盖库、schema、表、字段4级结构，当被引用时，在web页面中的最左边显示为数据源树，可点击展开；且在web页面即可读取源表及其字段，并通过拖拽的可视化手段能够形成源表的关联关系以及与目标表的字段映射；连接生成目标表所需要的源数据库，并在源数据库中选择所需的源表；完成字段映射后，自动化生成ETL抽数的ods脚本以及加工dim和fct的数据脚本。

6.根据权利要求5所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于可视化手段的实现如下：

7.根据权利要求6所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于web页面中所有在第3栏中出现的表及字段都会出现在第3栏，同时第3栏还增加基于多个表的计算字段。

8.根据权利要求7所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于根据web页面中第4栏的源、目标的字段映射，依次往前追溯，能够找到目标表以及作业的全部字段，并根据作业字段追溯出作业的表、作业表的字段、作业表间的关联关系，由作业表追溯出其所在的数据源，汇总出每个数据源的每张表的每个字段，即可编写ETL的ods脚本；

9.根据权利要求8所述的基于内存计算、web可视化配置的多数据源ETL工具，其特征在于对生成的ods脚本调用spark-ETL模块，实现源表到数仓ods层的数据传输，支持全量、增量；且整个工作的作业全部完成后，后台能够自动计算出所有脚本的依赖关系，并生成调度的作业依赖表job_relation；这些依赖关系被airflow调用后可视化展现。