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

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

背景技术：

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

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

技术实现要素：

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

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

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

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

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

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

本申请具有以下优点：

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

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

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

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

附图说明

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

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

图3为文件处理类etlstage模板。

图4为数据处理类etlstage模板。

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

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

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

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

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

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

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

具体实施方式

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

(一)、etljob定制

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

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

1、参数设置

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

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

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

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

2、etlstage定制

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

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

1)、etlstage属性

etlstage属性包含如下关键属性：

id：用于唯一标识etljob中的每一个etlstage。

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

表1：etlstage类别说明表

from：源etlstageid，支持多个，作为本etlstage的输入。

to：目标etlstageid，支持多个，作为本etlstage的输出。

description：对本etlstage的文字说明。

2)、处理逻辑

文件处理类模板和数据处理类模板的处理逻辑设置界面有较大的差异，而数据处理类模板较为统一，然后不同类型的etlstage，组合的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、数据加工流程定制

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

图10是一张etljob内部数据加工处理流程示意图。etlstageid1和etlstageid2作为etljob数据的最初来源；etlstageid1数据输出目标是etlstageid3；etlstageid2数据输出目标是etlstageid3和etlstageid4；etlstageid3的数据来源是etlstageid1和etlstageid2，数据输出目标是etlstageid5；etlstageid4的数据来源是etlstageid2，数据输出目标是etlstageid5；etlstageid5是etljob最后一个etlstage，也就是最终的数据输出目标，数据来源分别是etlstageid3、etlstageid4。

(二)、元数据管理

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

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

1、元数据检查

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

2、元数据导出

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

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