大数据与机器学习：实践方法与行业案例.2.3　ETL

2.3　ETL

ETL是Extract-Transform-Load的缩写，是数据的抽取、转换、加载过程，当需要将数据从一个环境转移到另一个环境时（例如从生产环境到分析环境），或者需要对数据进行进一步加工处理时（例如在分析环境中，在数据仓库基础上产出每日交易量指标），即需要借助ETL过程。

ETL是构建数据闭环自循环过程的重要途径，几乎每个环节均可以通过ETL来完成。通过专门的ETL工具，定制满足业务要求的ETL作业，并结合自动调度工具，即可以实现数据的自动循环。

2.3.1　ETL工具

目前国内商用ETL工具以IBM的Datastage为代表，该ETL工具在金融行业有广泛的应用，但Datastage价格昂贵，许多公司从成本上考虑，采用了Pentaho的开源ETL工具Kettle。

在本书成书时，尽管Kettle本身仍然存在一些Bug，但由于部署简单、使用方便并且完全免费的特点，使其成为越来越多用户的首选。

1. 开源ETL工具：Kettle

Kettle是国外的一款开源ETL工具，中文名称为水壶。在写作本书的时候，官网上最新的发布版本为Data Integration 5.4.0，下载后会得到一个pdi-ce-5.4.x.x-xxx.zip的压缩包，使用解压缩工具解压该压缩包，便可以开始使用Kettle了。

Kettle可以运行在Windows环境或者Linux环境，如果运行在Windows环境，则进入解压后的data-integration目录，可以看到Spoon.bat（Linux环境为Spoon.sh），双击Spoon.bat打开Kettle的图形界面，如图2-8所示。

图2-8　Kettle的图形界面

图2-8展示了Kettle的两个基本组件：转换（transformation）和作业（job）。转换用来定义数据处理的一个或多个步骤（step），如读取文件、过滤输出行、数据清洗、数据加载至目的数据库等。作业用来将多个定制完成的转换串接起来，使转换能够按照一定的顺序和规则执行。

定义完成的转换和作业可以使用程序或者脚本进行调用，首先将定义的转换或者作业存储在Kettle的资源库（Repository）中，然后通过Kettle提供的Pan和Kitchen组件分别进行调用（Pan用来调用transformation，Kitchen用来调用job），我们将在随后的内容介绍调用方法。

通常，首次接触“资源库”这个词汇会让人感觉难以理解，但撇开这个名词本身，它本质上就是创建关系数据库中的一些配置表，这些配置表用来存储转换或者作业的相关信息（如转换的名称、数据库连接的字符串等），Pan和Kitchen组件可以根据资源库里的这些信息来调用对应的转换或者作业。

在使用资源库之前，需要先创建一个资源库，在图形界面中进入工具→资源库→连接资源库，即可以出现相应的创建向导，通过向导可以轻松完成资源库的创建。

一旦成功创建并连接了资源库，随后进行的文件读取和保存操作就可以对应到该资源库。例如，作者在自己的测试环境中连接资源库后，单击菜单文件→打开，Kettle会自动读取资源库中保存的转换和作业信息，并将所有的转换和作业展示出来以供选择，如图2-9所示。

图2-9　Kettle资源库中的转换和作业

这些存放在资源库中的转换和作业，可以通过Pan和Kitchen组件进行调用，Pan是Kettle提供的用于批量调用转换的工具，Kitchen是用于批量调用作业的工具。在data-integration目录中可以找到Pan.bat和Kitchen.bat（Windows环境对应Pan.sh，Linux环境对应Kitchen.sh）。

在Linux环境中可以使用如代码清单2-37所示的脚本调用转换。

代码清单　2-37

pan.sh -rep=kettle_rep_test -trans="batch-into-table" -dir=/ -user=admin

-pass=admin

    -level=Basic

调用作业可以使用如代码清单2-38所示的命令。

代码清单　2-38

kitchen.sh -rep=kettle_rep_test -job="hive-oracle-test" -dir=/ -user=admin

-pass=admin

-level=Basic

如果有很多转换和作业需要运行，那么可以将这些命令写在一个shell脚本中，然后通过Linux系统自带的Crontab进行调度，或者通过专门的调度工具进行调度（请参阅2.4节）。

Kettle方面的推荐书籍：《Pentaho Kettle解决方案：使用PDI构建开源ETL解决方案》（作者：Matt Casters、Roland Bouman、Jos van Dongen著，初建军、曹雪梅译，电子工业出版社）。

2. 商用ETL工具：DataStage

DataStage是IBM InfoSphere开发的一款商用ETL工具，是IBM InfoSphere Information Server套件的简称。该套件包含三个组件：InfoSphere DataStage and QualityStage Designer、InfoSphere DataStage and QualityStage Director、InfoSphere DataStage and QualityStage Administrator。

InfoSphere DataStage and QualityStage Designer用于创建DataStage作业。

InfoSphere DataStage and QualityStage Director用于验证、调度、运行和监视DataStage作业。

InfoSphere DataStage and QualityStage Administrator用于系统管理（例如设置 IBM InfoSphere Information Server 用户，记录、创建和移动项目，设置清除记录的条件等）。

显然，相对于Kettle的轻量级部署，DataStage本身的架构已经非常复杂，相应的部署要求也比较高。DataStage作为IBM公司的一款产品，其目标客户群为大型企业，它甚至支持在大型机上运行ETL作业（能够生成可在大型机上运行的COBOL代码）。由于目前国内的银行仍然沿用IBM公司的大机系统，所以DataStage在国内的客户多存在于金融行业。

DataStage价格昂贵，一般需要支付年服务费、购买License等。同样，由于收取年服务费，所以能够提供很好的培训和技术支持。因此，需要根据企业自身特点选择商用ETL工具或者开源ETL工具，对于小型公司而言，开源工具仍是首选。

2.3.2　ETL作业

ETL作业是按照一定顺序组织的数据处理过程，它将数据处理的各个环节关联起来，并定义各个环节的触发规则，从而完成整个数据处理流程。

以Kettle为例，ETL作业由多个步骤（或称为作业项）组成，如图2-10所示。该作业除了开始的“START”与最后的“成功”步骤外，还包含以下三个实体作业项：

1）检测昨日交易明细文件是否存在。

2）SQL Server批量加载。

3）统计昨日交易。

作业项1）负责检测昨日的交易明细文件是否存在，如果该步骤返回“true”，则进行下一个作业项，否则退出作业。

作业项2）将昨日的交易明细文件批量加载至SQL Server数据库中，该作业项需要指定文件名称、格式文件等相关信息，使用的命令即是“SQL Server ：bcp in”章节中讲述的方式。如果该作业项执行成功，那么昨日交易明细数据将增量更新至SQL Server的数据库表中；如果该作业项执行失败，则退出作业。

作业项3）对作业项2）中加载的交易数据进行统计，该作业项执行一段SQL脚本，并将计算结果存储在对应的结果表中。

图2-10　ETL作业示例

图2-10中的ETL作业按照预定的顺序将多个作业项串联起来，完成一个完整的数据加载和统计过程，该过程的每个步骤作为一个作业项独立存在，仅当上游的作业项执行成功后，才开始下一个作业项的执行。

需要注意的是，ETL工具仅用于作业的创建和简单调度，如果需要周期性地执行ETL作业，则需要使用专门的调度工具。

为了使ETL作业便于调度和监控，为ETL作业制定规范是一项非常重要的工作，良好的ETL作业命名规范和日志规范可以极大地方便作业监控和错误排查。接下来深入介绍这两个实用性的操作规范：ETL作业命名规范和ETL作业日志规范。

1. ETL作业命名规范

ETL作业命名规范主要是为了通过作业名称来标识作业的归属、重要程度、主要用途等，以便于作业的自动调度和监控，它不是ETL工具的强制要求。

通常需要根据企业具体的管理要求为ETL作业制定命名规范，该规范要尽可能地反应作业的归属用途等，并且长度不能太长，下面给出一个范例：

[员工编号].[作业类型].[作业描述]

该命名规范包含三个部分，用“.”分割：

1）员工编号，用于描述作业的归属，一般使用作业创建人或者负责人的员工编号。

2）作业类型，用于描述作业的重要程度，比如将作业类型定义为analysis、report、product等，分别对应分析、报表、生产。不同的作业类型的作业出现错误时，可以根据重要程度进行不同等级的报警通知。

3）作业描述，用于描述作业的主要功能，比如图2-10中的作业可以描述为trx_load_and_static，或者使用中文描述（如果ETL工具支持中文名称）。

命名规范同样可以规定ETL作业中出现的字母统一使用大写或者小写，本书采用小写的方式。按照这个规范，图2-10中的ETL作业将命名为：z06837. analysis.trx_load_and_static，其中z06837是员工编号，analysis说明该作业属于分析型的作业。

按照规范进行作业命名后，作业监控进程便可以自动发现运行失败的作业，并且根据作业名称中的员工编号找到该员工的邮箱地址和手机号码（需预先在数据库中保存员工编号与邮箱地址和手机号码的对应关系），并发送邮件通知和短信提醒，还可以根据作业类型在邮件中标记紧急程度，这部分内容将在2.5节进一步展开。

2. ETL作业日志规范

ETL作业一般包含多个步骤（作业项），作业运行中某些步骤可能运行失败，记录下失败原因对于错误排查非常重要。

虽然ETL工具都自带日志记录功能，但系统自动记录的日志信息一般可读性很差且缺乏灵活性。ETL作业日志规范就是要自定义一个统一且灵活的日志记录方式，以便于作业的监控和错误排查。下面给出一个ETL作业日志规范的范例：

1）ETL作业中需包含记录作业开始和作业完成的作业项。

2）每个作业项均需增加作业项运行失败分支，并发送邮件通知。

3）日志记录统一记录在数据库表etl_job_log中。

4）日志记录中的状态在作业状态表etl_job_status中统一定义。

5）使用统一的存储过程进行日志记录。

根据规范1、2的要求，图2-10中的ETL作业将修改为图2-11所示的样子。

图2-11　满足规范1和规范2的ETL作业

图2-11中，作业开始后增加了一个作业项“记录日志：作业开始”，这个作业项往etl_job_log表中插入一条新记录，记录今日该作业的开始时间等相关信息。另外，在作业的最后加入了作业项“记录日志：作业完成”，用于更新作业的最终状态，图2-12是表etl_job_log中记录的部分ETL作业日志。

图2-12　作业日志表中的部分记录

在其余的三个作业项上面，分别增加了运行错误分支。这些错误分支分别记录对应的作业项出错信息，同时记录下作业项出错时的系统时间，并在错误日志记录完成后，发送失败通知邮件。

修改后的ETL作业会在运行过程中将作业状态自动记录到数据库中，随后BI工具可以根据数据库中的日志记录展示监控报表或者进行错误报警。

日志记录表etl_job_log创建表脚本如代码清单2-39所示。

代码清单　2-39

create table etl_job_log

(

id   bigint not null auto_increment comment '自增长id',

job_name varchar(100) not null comment '作业名称',

run_date varchar(20) comment '运行日期',

start_time datetime comment '作业开始时间',

end_time datetime comment '作业结束时间',

upt_time datetime default current_timestamp comment '更新时间',

job_status int not null comment '作业状态id',

remark varchar(1000) comment '作业状态补充说明',

primary key (id)

);

其中，remark字段的记录原则为：作业项名称+错误说明，例如，“SQL Server批量加载：出错。”可以方便追踪到作业出错的作业项。

job_status字段是表etl_job_status的外键，记录的是状态id，其对应的状态描述可以通过关联表etl_job_status得到。

作业状态表etl_job_status创建表脚本如代码清单2-40所示。

代码清单　2-40

create table etl_job_status

(

id   bigint not null auto_increment comment '自增长id',

status_desc varchar(1000) not null comment '状态描述',

primary key (id)

);