CN113986951A - 一种通用etl代码的生成系统及方法 - Google Patents

Abstract

本发明公开一种通用ETL代码的生成系统及方法，属于数据分析技术领域，提出以下方案：一种通用ETL代码的生成系统，包括：数据轮询模块，定时对源系统数据库中的批量表进行扫描，以判断是否存在相应数据日期的批量完成记录；数据抽取模块，在数据轮询模块扫描到存在相应数据日期的批量完成记录时，抽取系统数据文件；数据加载模块，将抽取的系统数据文件加载到目标数据库；数据转换模块，根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；数据导出模块，导出相应时间段的目标数据文件及相对应的校验文件。本发明技术方案节约的ETL软件成本，同时解决高质量、高效的数据处理，节省开发时间和成本，提升效率。

Description

一种通用ETL代码的生成系统及方法

技术领域

本发明涉及数据分析技术领域，具体涉及一种通用ETL代码的生成系统及方法。

背景技术

数据仓库技术(ETL，Extract-Transform-Load)，用来描述数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。目前ETL实现方式有两类：一类是通过专业工具实现，二类是通过编写程序实现。专业的ETL工具有Datastage、Informatica、Kettle等，但专业的ETL工具存在价格昂贵，学习成本高，配置作业流繁琐等问题。编写程序对程序编码能力要求高，存在代码不规范，质量参差不齐，维护成本高等问题。此外，大量的已有技术是基于特定语言开发的数据处理脚本，缺乏通用的脚本语言设计思路，去集成标准的SQL代码封装算法模块。

发明内容

本发明的主要目的在于提供一种通用ETL代码的生成系统及方法，旨在节约高昂的ETL软件成本，同时解决高质量、高效的数据处理，节省开发时间和成本，提升效率。

本发明提供的基础方案：

一种通用ETL代码的生成系统，包括：

数据轮询模块，用于定时对源系统数据库中的批量表进行扫描，以判断所述批量表中是否存在相应数据日期的批量完成记录；

数据抽取模块，用于在所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录时，从所述源系统数据库抽取系统数据文件；

数据加载模块，用于将抽取的所述系统数据文件通过外部表方式加载到目标数据库；

数据转换模块，用于根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；

数据导出模块，用于导出相应时间段的目标数据文件及相对应的校验文件。

本发明基础方案的原理为：

本方案中，通用ETL代码的生成系统包括数据轮询模块、数据抽取模块、数据加载模块、数据转换模块和数据导出模块；具体地，通过数据轮询模块定时对源系统数据库中的批量表进行扫描，以判断批量表中是否存在相应数据日期的批量完成记录；在数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录时，数据抽取模块从源系统数据库抽取系统数据文件；数据加载模块就将抽取的系统数据文件通过外部表方式加载到目标数据库；通过数据转换模块根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；最后，通过数据导出模块导出相应时间段的目标数据文件及相对应的校验文件。也即，本方案通过数据轮询模块、数据抽取模块、数据加载模块、数据转换模块、数据导出模块的结合，每个模块实现相对应的功能，通过导入配置数据文件，生成参数化的SQL代码，通过脚本语言封装成不同算法的程序模块，再通过脚本或者调度工具，传入参数，批量生成ETL数据处理作业流，实现业务系统源数据自动化同步到目标数据库或文件系统。从而节约了ETL软件成本，同时解决高质量、高效的数据处理，节省了开发时间和成本，提升效率。

进一步，当所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录，则触发所述数据抽取模块进行数据抽取作业；当所述数据轮询模块扫描到所述批量表中不存在相应数据日期的批量完成记录，则返回失败，等待所述数据轮询模块下一次扫描源系统数据库，直到扫描到所述批量表中存在相应数据日期的批量完成记录或者超时。

本方案中，通过对数据轮询模块定时扫描并判断具体的说明，利于对源系统数据库中批量表的相应数据日期的批量完成记录进行扫描判断。

进一步，所述数据抽取模块基于松耦合方式，通过文件接口方式进行数据交换。

本方案中，数据抽取模块的松耦合方式，有助于降低系统客户端和远程服务之间的依赖性。

进一步，当所述源系统数据库具有增量接口时，所述数据抽取模块以增量方式按照预设周期进行系统数据文件抽取；当所述源系统数据库不具有增量接口时，所述数据抽取模块以全量方式按照预设周期进行系统数据文件抽取。

本方案中，通过源系统能够明确提供增量的接口，以增量的方式进行每天的文件抽取，不能提供增量的接口，以全量的方式进行文件抽取，源系统提供主键，以便于通过比对的方式产生每天的增量。

进一步，所述数据加载模块还用于将抽取的所述系统数据文件数据进行数据记录比对，以判断进入目标数据库的系统数据文件和加载完成的系统数据文件在记录条数上是否相同。

本方案中，通过数据记录比对，确保了进入目标数据库的数据和加载的数据在记录条数上相同。

进一步，所述数据加载模块还用于对源数据文件进行类型变换、长度变换和去除空格变换。

本方案中，通过在数据加载的同时，对源数据文件按相应规则进行数据变化，便于数据加载模块加载后的数据文件输出。

进一步，所述数据导出模块，用于将相应时间段的目标数据文件及相对应的校验文件按照整表导出或者按照SQL导出。

本方案中，将相应时间段的目标数据文件及相对应的校验文件导出，且按照整表导出或者按照SQL导出，可以批量生成ETL数据处理作业流，实现系统源数据自动化同步到目标数据库或文件系统。

为实现上述目的，本发明还提出一种通用ETL代码的生成方法，所述通用ETL代码的生成方法包括：

定时对源系统数据库中的批量表进行扫描，以判断所述批量表中是否存在相应数据日期的批量完成记录；

在所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录时，从所述源系统数据库抽取系统数据文件；

将抽取的所述系统数据文件通过外部表方式加载到目标数据库；

根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；

导出相应时间段的目标数据文件及相对应的校验文件。

附图说明

图1为本发明实施例方案涉及的终端设备内部结构示意图；

图2为本发明通用ETL代码的生成系统一实施例的模块结构示意图；

图3为本发明通用ETL代码的生成方法一实施例的流程示意图；

图4为本发明通用ETL代码的生成方法另一实施例的流程示意图。

具体实施方式

应当理解，此处所描述的具体实施例仅仅用以解释本发明，并不用于限定本发明。

下面通过具体实施方式进一步详细说明：

说明书附图中的附图标记包括：处理器1001、通信总线1002、用户接口1003、网络接口1004、存储器1005、数据轮询模块10、数据抽取模块20、数据加载模块30、数据转换模块40和数据导出模块50。

如图1所示，其为本发明实施例方案涉及的终端设备内部结构示意图。

需要说明的是，图1也即是终端设备的硬件运行环境的结构示意图。本发明实施例终端设备可以是PC、便携计算机等终端设备。

如图1所示，该终端设备可以包括：处理器1001，通信总线1002，用户接口1003，网络接口1004，存储器1005。其中，通信总线1002用于实现处理器1001、用户接口1003、网络接口1004、存储器1005之间的连接通信。用户接口1003可以包括显示屏(Display)、输入单元比如键盘(Keyboard)、手写板、触控笔等，可选用户接口1003还可以包括标准的有线接口、无线接口。网络接口1004可选的可以包括标准的有线接口(如RJ45接口)、无线接口(如WIFI接口)。存储器1005可以是高速RAM存储器，也可以是稳定的存储器(non-volatilememory)，例如磁盘存储器。存储器1005可选的还可以是独立于前述处理器1001的存储装置。

本领域技术人员可以理解，图1中的终端设备结构并不构成对终端设备的限定，可以包括比图示更多或更少的部件，或者组合某些部件，或者不同的部件布置。

如图1所示，作为一种计算机存储介质的存储器1005中可以包括操作系统、网络通信模块、用户接口模块以及分布式任务的处理程序。其中，操作系统是管理和控制样本终端设备硬件和软件资源的程序，支持分布式任务的处理程序以及其它软件或程序的运行。

在图1所示的终端设备中，用户接口1003主要用于与各个终端进行数据通信；网络接口1004主要用于连接后台服务器，与后台服务器进行数据通信；而处理器1001可以用于调用存储器1005中通用ETL代码的生成程序，并执行如图3所示的以下操作：

步骤S100，定时对源系统数据库中的批量表进行扫描，以判断所述批量表中是否存在相应数据日期的批量完成记录；

本实施例中，如图2所示，该步骤对应通用ETL代码的生成系统的数据轮询模块10；需要说明的是，抽取业务系统数据，首先是要判断是否满足抽取条件，通过轮询作业来实现。轮询作业扫描到批量表中存在相应数据日期的批量完成记录，则返回成功退出接口扫描作业，表示数据准备完成，以触发后续的数据抽取作业；如果相应记录不存在，则返回失败，等待下一次扫描，直到扫描到相应记录或者超时。对于可以直接从源系统数据库抽取数据的，轮询作业会定时对数据库中的批量表进行监测，一旦发现批量完成则开始数据抽取。对于不能直连抽取的源系统，要求源系统按需求提供每天的全量数据。

进一步地，所有系统轮询任务都放在同一个脚本，本方案采用perl脚本，采用参数控制的方式。调度系统在设定的时间会开始执行轮询perl脚本，同时根据轮询目标系统及轮询日期传入不同的参数，perl脚本获取到这些变量参数，并从配置文件获取固定参数，通过解密算法取得数据库连接信息，根据轮询目标系统生成对应的轮询SQL；然后调用SQLplus执行这些SQL，最后判断返回的记录数，返回给调度系统成功或失败的信息；如果接收到成功信息，调度系统会继续执行该轮询后续的抽数任务，如果接收到失败信息，调度系统会判断是否超过轮询任务设定的最长时间，未超过则重新执行轮询任务，超过则退出显示轮询失败。

本实施例中，在数据轮询模块10之前具有配置文件的读取，具体地，数据库/文件系统连接配置信息：需要明确系统源数据存储环境信息，包含服务器、数据库等信息。如：系统中文名称、系统英文名称、数据库类型、数据库字符集、数据获取方式、服务器IP地址、服务器端口、实例名称和ETL数据库用户。

MAP配置信息：需要明确源业务表和目标表，表类型(状态表ST/事件表EV)，接口类型(增量I/全量F)，文件存储目录，表字段，字段类型等信息。map_table配置信息：数据平台表名、源系统标识、源系统表名、表类型(状态表ST/事件表EV)、接口类型(增量I/全量F)、增量获取条件、算法类别、数据文件名称、校验文件名称和文件目录。map_column配置信息：数据平台表名、源系统标识、源系统表名、数据平台字段名、数据平台字段类、字段长度、字段精度、源系统字段、源系统字段类型和是否主键。

步骤S200，在所述数据轮询模块10扫描到所述批量表中存在相应数据日期的批量完成记录时，从所述源系统数据库抽取系统数据文件；

本实施例中，如图2所示，该步骤对应通用ETL代码的生成系统的数据抽取模块20；需要说明的是，采用松耦合的方式，通过文件接口的方式进行数据交换。对于源系统能够明确提供增量的接口，以增量的方式进行每天的文件抽取，不能提供增量的接口，以全量的方式进行文件抽取，源系统提供主键，以方便后面通过比对的方式产生每天的增量。

进一步地，当前面的数据轮询完成并满足条件，触发数据抽取脚本，读取MAP配置信息，根据表名和抽取日期传入不同的参数，perl脚本获取到这些变量参数，并从配置文件获取固定参数，自动化生成数据查询SQL语句，并选择不同的算法模块自动化装配不同的数据抽取作业流。通过解密算法取得数据库连接信息，然后用后台命令的方式执行相应的作业，把执行的返回值反馈给调度系统。如果调度系统接收到的返回值是0，则继续执行后续的加载作业，如果是其他值，则说明作业执行失败。

步骤S300，将抽取的所述系统数据文件通过外部表方式加载到目标数据库；

本实施例中，如图2所示，该步骤也即通用ETL代码的生成系统的数据加载模块30；需要说明的是，数据加载就是将数据文件通过外部表的方式加载到数据库，数据加载要完成数据记录的比对，确保进入目标库的数据和加载的数据在记录条数上是相同的。在数据加载的同时，还对源数据按一定的规则进行数据变化：

类型变换：如将源系统的char、long、clog、blob转为varchar2类型，decimal、numeric、int、float、money转为number类型；

长度变换：如将源系统中定义的varchar2(1char)转为varchar2(2char)；

去除空格：去除字符型数据中的前后空格，如果字段内容全是空格，trim后为null。

进一步地，在数据加载部分，每个源系统的表都有一个相对应的加载作业，当该作业的前置作业(抽数作业)成功完成，调度系统会自动开始执行该作业。根据表名和加载日期传入不同的参数，perl脚本获取到这些变量参数，并从配置文件获取固定参数，通过解密算法取得数据库连接信息，然后通过SQLplus完成数据的加载；加载过程中如果出错则直接返回错误信息至调度系统。

步骤S400，根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；

本实施例中，如图2所示，该步骤也即通用ETL代码的生成系统的数据转换模块40；需要说明的是，数据转换是按照目标表的数据结构，对一个或多个源数据的字段进操作得到目标数据的字段。主要包括格式和字段合并与拆分、数据翻译、数据匹配、数据聚合以及其他复杂计算等。

进一步地，在数据转换部分，每个源系统的表都有一个相对应的转换作业，当该作业的前置作业(加载作业)成功完成，调度系统会自动开始执行该作业。根据表名和转换日期传入不同的参数，perl脚本获取到这些变量参数，并从配置文件获取固定参数，通过解密算法取得数据库连接信息，然后根据各算法不同采用不同的转换模板，通过SQLplus完成数据的转换，最后把执行的结果反馈给调度系统。

步骤S500，如图2所示，导出相应时间段的目标数据文件及相对应的校验文件。

本实施例中，该步骤也即通用ETL代码的生成系统的数据导出模块50；需要说明的是，数据在导出时，先判断当天的文件是否存在，如果存在则为重复执行程序，需要将上次产生的文件导出，然后进行当天文件的导出。如果不存在文件直接导出当天的文件即可。生成文件后，生成该文件对应的校验文件，提供给下游使用。

进一步地，数据导出脚本提供两种数据导出的方式：整表导出，可以根据传入的表名，把整表数据导出到指定目录。按照SQL导出，这是数据导出的主要方式，在传入的SQL语句中可以自定义要导出增量还是全量数据，可以对某个字段进行脱敏处理，可以加上任意的where条件对数据进行过滤。

基于上述实施例，本方案提出一种通用ETL代码的生成系统包括数据轮询模块10、数据抽取模块20、数据加载模块30、数据转换模块40、数据导出模块50；具体的流程参照如图4所示，具有调度执行加载脚本、传入参数、读取配置文件、获取数据库连接信息、生成加载相关的SQL语句用SQLplus执行、判断算法类型、判断SQLplus返回结果。每个步骤中的各个模块实现相对应的功能，通过导入配置数据文件，生成参数化的SQL代码，通过脚本语言封装成不同算法的程序模块，再通过脚本或者调度工具，传入参数，批量生成ETL数据处理作业流，实现业务系统源数据自动化同步到目标数据库或文件系统。从而节约了ETL软件成本和学习成本，避免繁琐的作业流配置，节省时间。提供通用的脚本语言处理数据，提供多个算法模块，支持可参数化配置，自动化生成ETL代码，生成的通用程序支持常用脚本语言和专业的ETL工具调度执行，同时可以灵活、快捷的扩展到不同程序语言开发的数据处理和集成。

以上的仅是本发明的实施例，方案中公知的具体结构及特性等常识在此未作过多描述，所属领域普通技术人员知晓申请日或者优先权日之前发明所属技术领域所有的普通技术知识，能够获知该领域中所有的现有技术，并且具有应用该日期之前常规实验手段的能力，所属领域普通技术人员可以在本申请给出的启示下，结合自身能力完善并实施本方案，一些典型的公知结构或者公知系统不应当成为所属领域普通技术人员实施本申请的障碍。应当指出，对于本领域的技术人员来说，在不脱离本发明结构的前提下，还可以作出若干变形和改进，这些也应该视为本发明的保护范围，这些都不会影响本发明实施的效果和专利的实用性。本申请要求的保护范围应当以其权利要求的内容为准，说明书中的具体实施方式等记载可以用于解释权利要求的内容。

Claims (8)

1.一种通用ETL代码的生成系统，其特征在于，包括：

数据轮询模块，用于定时对源系统数据库中的批量表进行扫描，以判断所述批量表中是否存在相应数据日期的批量完成记录；

数据抽取模块，用于在所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录时，从所述源系统数据库抽取系统数据文件；

数据加载模块，用于将抽取的所述系统数据文件通过外部表方式加载到目标数据库；

数据转换模块，用于根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；

数据导出模块，用于导出相应时间段的目标数据文件及相对应的校验文件。

2.根据权利要求1所述的通用ETL代码的生成系统，其特征在于，当所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录，则触发所述数据抽取模块进行数据抽取作业；当所述数据轮询模块扫描到所述批量表中不存在相应数据日期的批量完成记录，则返回失败，等待所述数据轮询模块下一次扫描源系统数据库，直到扫描到所述批量表中存在相应数据日期的批量完成记录或者超时。

3.根据权利要求1所述的通用ETL代码的生成系统，其特征在于，所述数据抽取模块基于松耦合方式，通过文件接口方式进行数据交换。

4.根据权利要求3所述的通用ETL代码的生成系统，其特征在于，当所述源系统数据库具有增量接口时，所述数据抽取模块以增量方式按照预设周期进行系统数据文件抽取；当所述源系统数据库不具有增量接口时，所述数据抽取模块以全量方式按照预设周期进行系统数据文件抽取。

5.根据权利要求4所述的通用ETL代码的生成系统，其特征在于，所述数据加载模块还用于将抽取的所述系统数据文件数据进行数据记录比对，以判断进入目标数据库的系统数据文件和加载完成的系统数据文件在记录条数上是否相同。

6.根据权利要求1所述的通用ETL代码的生成系统，其特征在于，所述数据加载模块还用于对源数据文件进行类型变换、长度变换和去除空格变换。

7.根据权利要求1所述的通用ETL代码的生成系统，其特征在于，所述数据导出模块，用于将相应时间段的目标数据文件及相对应的校验文件按照整表导出或者按照SQL导出。

8.一种通用ETL代码的生成方法，其特征在于，所述通用ETL代码的生成方法包括：

定时对源系统数据库中的批量表进行扫描，以判断所述批量表中是否存在相应数据日期的批量完成记录；

在所述数据轮询模块扫描到所述批量表中存在相应数据日期的批量完成记录时，从所述源系统数据库抽取系统数据文件；

将抽取的所述系统数据文件通过外部表方式加载到目标数据库；

根据目标表的数据结构对至少一个源数据文件的字段进行转换操作，以获取目标数据文件的字段；

导出相应时间段的目标数据文件及相对应的校验文件。

Images