CN107315726A

CN107315726A - 一种基于Excel的大数据ETL全过程定义的方法

Info

Publication number: CN107315726A
Application number: CN201710566324.5A
Authority: CN
Inventors: 陈涛; 黄卓凡; 张志聪; 李笋; 林志广
Original assignee: Guangdong Strong Wind Polytron Technologies Inc
Current assignee: Guangdong Strong Wind Polytron Technologies Inc
Priority date: 2017-07-12
Filing date: 2017-07-12
Publication date: 2017-11-03

Abstract

一种基于Excel的大数据ETL全过程定义的方法，是以Excel模板为基础，采用统一的界面格式和规范化的描述语言进行ETL的全过程定义。该方法操作简单，在易用性和可维护性方面较传统ETL工具产品和直接编程有了很大的提高，并可以结合机器人软件进行批量定制，在效率上有数倍的提升。本方法高度融合了元数据管理，从源数据的抽取、数据加工、到目标数据的最终落地，在Excel模板所涉及的全部过程定义和参数设置，都作为ETL系统的元数据输入。ETL系统根据本方法设置的元数据，自动流水线式运行数据抽取、数据处理、数据整合、数据输出等模块，无需人工干预。

Description

一种基于Excel的大数据ETL全过程定义的方法

技术领域

本申请涉及一种基于Excel的大数据ETL全过程定义的方法，特别地，涉及一种通过Excel模板进行ETL作业定制和元数据管理的方法。

背景技术

伴随着大数据的爆炸性增长，大数据应用获得了极大的发展，而大数据应用离不开海量数据的收集、清洗和加工。大数据的几大特点：数据巨量化(Volume)、数据形态多样化(Variety)、数据产生及处理快速化(Velocity)，给大数据的数据抽取、转换和加载(以下简称ETL)带来了新的机遇和挑战，也对ETL系统的易用性、高效性、可维护性提出了更高的要求。

通常情况下，企业采用传统的ETL产品或直接编码来构建ETL系统，对大数据进行加工和处理。传统的ETL产品注重操作界面的易用性，每个ETL作业(本申请中Job和作业互为通用,ETL作业即ETL Job)通过画图进行设计，先在界面通过拖拉来定义ETL阶段(本申请中Stage和阶段互为通用,ETL阶段即ETL Stage)，并在ETL Stage之间连线定义数据加工流程，然后再点击每个ETL Stage定义相关参数，从而完成一个完整的ETL Job设计。这样定义的环节多，效率低下。另外，元数据管理和传统的ETL产品分离，一旦数据结构有变动，无法自动去调整ETL工具产品中的ETL Job定义，需通过人工逐个点击ETL作业中的Stage来进行变更。当ETL作业很多的时候，开发和维护的工作量会非常大。直接编程的好处是灵活度高，但和传统的ETL产品面临同样的问题，开发低效，维护困难；因此有必要设计一种新的ETL产品。

发明内容

本申请解决了传统ETL产品和直接编程时的开发低效，维护困难问题。

一种基于Excel的大数据ETL全过程定义的方法，通过Excel模板进行ETL Job定制和元数据管理。该方法操作简单，在易用性和可维护性方面较传统ETL工具产品和直接编程有了很大的提高。本方法高度融合了元数据管理，针对数据结构、参数、加工流程的变更，只需修改相应的Excel模板元数据，无需再编程，易于管理和维护。

本方法是轻量级的独立于ETL系统的ETL全过程定义载体，无限覆盖所有ETL功能的定制，操作简单，灵活易用，借助Excel模板，将大大降低使用者的学习成本，无需学习新的ETL工具产品。

本方法充分满足各行业在ETL Job定制和元数据管理的需要。

一种基于Excel的大数据ETL全过程定义的方法是以Excel模板为基础，采用统一的界面格式和规范化的描述语言进行ETL的全过程定义。该方法操作简单，在易用性和可维护性方面较传统ETL工具产品和直接编程有了很大的提高，并可以结合机器人软件进行批量定制，在效率上有数倍的提升。

本方法高度融合了元数据管理，从源数据的抽取、数据加工、到目标数据的最终落地，在Excel模板所涉及的全部过程定义和参数设置，都作为ETL系统的元数据输入。ETL系统根据本方法设置的元数据，自动流水线式运行数据抽取、数据处理、数据整合、数据输出等模块，无需人工干预。针对数据结构、参数、加工流程等的变更，只需修改相应的Excel模板元数据，甚至可以通过机器人软件进行元数据的批量修改，无需再编程，易于管理和维护。

本申请具有以下优点：

1、以Excel模板为基础定制大数据ETL全过程，简单高效

本方法以Excel模板为基础，采用统一的界面格式和规范化的描述语言进行ETL的全过程定制。该方法操作简单，在易用性和可维护性方面较传统ETL工具产品和直接编程有了很大的提高，并可以结合机器人软件进行批量定制，在效率上有数倍的提升。

2、高度融合了元数据管理，简明易用，维护方便

采用简单易用的Excel模板管理和配置元数据；元数据变更直接在Excel模板里进行维护，一目了然。甚至可以通过机器人软件进行元数据的批量修改，无需再编程，易于管理和维护。

附图说明

图1为本申请的方法构成框图。

图2为参数设置模板示意图。

图3为文件处理类ETL Stage模板。

图4为数据处理类ETL Stage模板。

图5为文件处理逻辑界面。

图6为数据处理逻辑界面。

图7为文件处理类模板定制的读文件范例。

图8为数据处理类模板定制的数据集关联(Join)范例。

图9为数据处理类模板定制的数据清洗与格式化(Transformer)范例。

图10为ETL Job内部数据加工处理流程示意图。

图11为元数据管理示意图。

具体实施方式

一种基于Excel的大数据ETL全过程定义的方法，包含了ETL Job定制和元数据管理两大功能。其中，ETL Job定制包括了参数设置、ETL Stage定制和数据加工流程定制等；元数据管理包括了元数据定义、元数据检查和元数据导出等功能。

(一)、ETL Job定制

ETL Job是指一个独立完整的数据加工过程；ETL Stage则是ETL Job内部为实现某个特定功能的数据加工阶段。本方法采用Excel模板进行ETL定制，一个Excel工作簿(WorkBook)对应一个ETL Job，一个Excel工作簿含有多个Excel工作表(WorkSheet)，其中一个Excel工作表设置参数，其它Excel工作表对应ETL Stage。

每个Excel工作表模板分为左右两边，左边是注释，右边是具体设置内容。

1、参数设置

参数设置模板包含三种参数设置：

1)、输入参数。此处设置ETL Job运行时的外部输入参数，如标识源数据的日期参数，账务数据日期(ACDATE)、上一个账务数据日期(YEDATE)等。

2)、ETL Job参数。此处设置ETL Job内部参数，如数据记录处理出错是否继续(RECORD_ERROR_CONTINUE)，数据记录结束字符(RECORD_END_CHAR)等参数。

3)、ETL Stage参数。此处设置ETL Stage之间传递的参数。

2、ETL Stage定制

ETL Stage模板采用统一的界面格式和规范化的描述性语言进行ETL的全过程定义。ETL Stage模板分为两大类，一类是文件处理类模板，一类是数据处理类模板。

ETL Stage模板的界面要素主要包括了ETL Stage属性、处理逻辑、调试设置三部分内容。

1)、ETL Stage属性

ETL Stage属性包含如下关键属性：

ID：用于唯一标识ETL Job中的每一个ETL stage。

TYPE：选择本ETL Stage所属的类别，不同类型的ETL Stage，对应的加工处理逻辑不同。ETL Stage类别如下：

表1：ETL Stage类别说明表

FROM：源ETL Stage ID，支持多个，作为本ETL Stage的输入。

TO：目标ETL Stage ID，支持多个，作为本ETL Stage的输出。

DESCRIPTION：对本ETL Stage的文字说明。

2)、处理逻辑

文件处理类模板和数据处理类模板的处理逻辑设置界面有较大的差异，而数据处理类模板较为统一，然后不同类型的ETL Stage，组合的ETL算子有所不同，但界面基本上是一致的。

文件处理逻辑设置界面见图5：

文件处理逻辑设置说明：

表2：文件处理逻辑设置说明表

数据处理逻辑设置界面见图6。

数据处理逻辑设置说明：

表3：数据处理逻辑设置说明表

结果筛选：

设置数据加工结果集的筛选逻辑。

LIMIT：选取结果集头N条记录。

LOGIC：筛选逻辑，支持通过定义筛选条件对输出的(多个)结果集进行筛选，筛选条件支持函数与逻辑运算。

3)、调试参数

设置数据加工出错时记录导出逻辑。

METHOD：支持LOG/OVERWRITE/APPEND，出错信息导出支持LOG、CSV文件，其中LOG参数为日志输出，无须填写后续参数。不输出则填写"IGNORE"或者留白。

DELIMITER：分隔符，支持多字节。

QUOTE：引号符，CSV文件可在此处标识双引号。

LIMIT：选取错误数据集头N行记录。

FILE：文件路径与文件名。

根据文件处理类模板定制的读文件范例见图7。

根据数据处理类模板定制的范例见图8、图9。

3、数据加工流程定制

ETL Job是指一个独立完整的数据加工过程；ETL Stage则是ETL Job内部的为实现某个特定功能的数据加工阶段。在每个ETL Stage模板中定义FROM和TO，分别设置数据来源的ETL Stage ID和数据输出目标的ETL Stage ID。ETL Job根据FROM和TO的定义来连接ETL Stage，并组合成ETL Job的内部数据加工处理流程。FROM和TO都可以将前后连接的ETLStage设置为多对一或者一对多关系，这样就完全满足ETL Job内部各种数据加工处理流程的定制需求。

图10是一张ETL Job内部数据加工处理流程示意图。ETL Stage ID1和ETL StageID2作为ETL Job数据的最初来源；ETL Stage ID1数据输出目标是ETL Stage ID3；ETLStage ID2数据输出目标是ETL Stage ID3和ETL Stage ID4；ETL Stage ID3的数据来源是ETL Stage ID1和ETL Stage ID2，数据输出目标是ETL Stage ID5；ETL Stage ID4的数据来源是ETL Stage ID2，数据输出目标是ETL Stage ID5；ETL Stage ID5是ETL Job最后一个ETL Stage，也就是最终的数据输出目标，数据来源分别是ETL Stage ID3、ETL StageID4。

(二)、元数据管理

元数据管理功能，是将使用本方法和Excel模板设置的各种要素，如源数据结构、目标数据结构、数据加工处理逻辑、数据加工环节及数据加工处理流程等各种参数进行管理。元数据一旦设置完成，ETL系统自动流水线式运行数据抽取、数据处理、数据整合、数据输出等模块，无需人工干预。源数据变更，只需修改相应的元数据，无需编程。

元数据管理功能包括元数据检查和元数据导出功能，具体如下：

1、元数据检查

本方法根据一系列的元数据规范对元数据进行检查，元数据包括：数据来源、数据字段、字段数据类型、字段长度、字段数据格式，包含各种数据转换规则、表达式、ETL算子的数据处理逻辑，设置ETL Stage ID的FROM和TO串接而成的数据加工处理流程，以及ETLStage ID和Excel Sheet名称的对应关系等。并将检查结果输出元数据检查报告。

2、元数据导出

将Excel模板中的元数据导出为元数据控制文件，作为ETL系统的元数据输入。元数据控制文件采用XML文件保存元数据，使元数据管理变得简单和通用。

以上所述仅为本申请的较佳实施例，并不用以限制本申请，凡在本申请的精神和原则之内，所作的任何修改、等同替换、改进等，均应包含在本申请的保护范围之内。

Claims

1. 一种基于Excel的大数据ETL全过程定义的方法，其特征在于：该方法包括ETL作业定制和元数据管理，通过Excel模板进行ETL 作业定制和元数据管理； ETL 作业定制包括参数设置、ETL 阶段定制和数据加工流程定制, ETL作业是指一个独立完整的数据加工过程；ETL 阶段则是ETL 作业内部的数据加工阶段；元数据管理包括元数据定义、元数据检查和元数据导出。

2.根据权利要求1所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：ETL作业定制中的参数设置在参数设置模板中进行，其中参数包括输入参数、ETL作业参数、ETL阶段参数，其中输入参数是指ETL作业运行时的外部输入参数，ETL作业参数是指ETL 作业内部参数，ETL阶段参数是指ETL 阶段之间传递的参数。

3.根据权利要求1所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：ETL阶段定制采用的模板分为两大类, 一类是文件处理类模板，另一类是数据处理类模板。

4.根据权利要求3所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：ETL阶段定制模板的界面要素包括ETL阶段属性、处理逻辑、调试设置三部分内容。

5.根据权利要求4所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：ETL阶段属性包含如下属性：

ID：用于唯一标识ETL 作业中的每一个ETL 阶段；

TYPE：选择本ETL 阶段所属的类别，不同类型的ETL 阶段，对应的处理逻辑不同;

FROM：源ETL 阶段 ID，支持多个源ETL 阶段 ID作为本ETL 阶段的输入；

TO：目标ETL 阶段 ID，支持多个目标ETL 阶段 ID作为本ETL 阶段的输出；

DESCRIPTION：对本ETL 阶段的文字说明。

6.根据权利要求5所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：在每个ETL阶段模板中定义“FROM”和“TO”，分别设置数据来源的ETL 阶段 ID和数据输出目标的ETL 阶段 ID；ETL 作业根据“FROM”和“TO”的定义来连接ETL 阶段，并组合成ETL 作业的内部数据加工处理流程；“FROM”和“TO”能将前后连接的ETL 阶段设置为多对一或者一对多关系。

7.根据权利要求6所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：元数据检查是根据元数据规范对元数据进行检查，元数据包括：数据来源、数据字段、字段数据类型、字段长度、字段数据格式、数据转换规则、表达式、ETL算子的数据处理逻辑、设置ETL作业ID的“FROM”和“TO”串接而成的数据加工处理流程，以及ETL 作业 ID和Excel 工作表名称的对应关系；元数据检查过程中还将检查结果输出元数据检查报告。

8.根据权利要求7所述的基于Excel的大数据ETL全过程定义的方法，其特征在于：元数据导出是将Excel模板中的元数据导出为元数据控制文件，作为ETL系统的元数据输入；元数据控制文件采用XML文件保存元数据。

9.根据权利要求1所述的基于Excel的大数据ETL全过程定义的方法, 其特征在于：一个Excel工作簿模板对应一个ETL 作业模板，一个Excel工作簿含有多个Excel工作表，其中一个Excel工作表用于在ETL作业中设置参数，其它Excel工作表对应ETL 阶段模板。

10.根据权利要求9所述的基于Excel的大数据ETL全过程定义的方法, 其特征在于：每个Excel工作表模板分为左右两边，左边是注释，右边是具体设置内容。