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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

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)

);

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
相关文章
|
4天前
|
机器学习/深度学习 算法 数据挖掘
利用机器学习优化数据中心能效的策略与实践
【5月更文挑战第13天】 在数据中心管理和运营的众多挑战中,能源效率优化是降低运营成本和减少环境影响的关键因素。本文旨在探讨如何应用机器学习技术来提高数据中心的能效,通过智能化的数据分析和资源管理达到节能的目的。与传统的摘要不同,本文将直接深入探讨所采用的技术手段、实施步骤以及预期效果,为读者提供一种新颖的视角。
12 4
|
2天前
|
机器学习/深度学习 数据采集 监控
构建高效机器学习模型的策略与实践
【5月更文挑战第17天】 在当今数据驱动的时代,机器学习(ML)模型的效能成为衡量技术创新和解决实际问题能力的重要指标。本文旨在探讨构建高效机器学习模型的先进策略,并通过具体实践案例来揭示这些方法的有效性。我们将从数据处理、特征工程、模型选择、调参技巧以及模型部署等方面详细论述,旨在为读者提供一个全面而深入的视角,帮助其优化现有模型或开发新模型,以应对复杂多变的业务挑战。
11 2
|
3天前
|
机器学习/深度学习 运维 算法
利用机器学习进行异常检测的技术实践
【5月更文挑战第16天】本文探讨了利用机器学习进行异常检测的技术实践,强调了在大数据时代异常检测的重要性。机器学习通过无监督、有监督和半监督学习方法自动识别异常,常见算法包括KNN、LOF、K-means和GMM等。异常检测流程包括数据准备、特征工程、选择算法、训练模型、评估优化及部署。机器学习为异常检测提供了灵活性和准确性,但需结合具体问题选择合适方法。
|
4天前
|
分布式计算 数据可视化 Hadoop
大数据实战——基于Hadoop的Mapreduce编程实践案例的设计与实现
大数据实战——基于Hadoop的Mapreduce编程实践案例的设计与实现
40 0
|
4天前
|
分布式计算 Java Hadoop
大数据实战——WordCount案例实践
大数据实战——WordCount案例实践
7 0
|
4天前
|
机器学习/深度学习 自然语言处理 监控
利用机器学习进行情感分析:技术详解与实践
【5月更文挑战第13天】本文探讨了利用机器学习进行情感分析的方法,包括技术原理、常用算法和实践应用。情感分析涉及文本预处理(如清洗、分词和去除停用词)、特征提取(如词袋模型、TF-IDF和Word2Vec)及分类器训练(如朴素贝叶斯、SVM和RNN/LSTM)。常见情感分析算法有朴素贝叶斯、支持向量机和深度学习模型。实践中,情感分析应用于社交媒体监控、产品评论分析等领域。通过本文,读者可了解情感分析的基础知识及其应用价值。
|
4天前
|
数据采集 供应链 安全
利用大数据优化业务流程:策略与实践
【5月更文挑战第11天】本文探讨了利用大数据优化业务流程的策略与实践,包括明确业务目标、构建大数据平台、数据采集整合、分析挖掘及流程优化。通过实例展示了电商和制造企业如何利用大数据改进库存管理和生产流程,提高效率与客户满意度。随着大数据技术进步,其在业务流程优化中的应用将更加广泛和深入,企业需积极采纳以适应市场和客户需求。
|
4天前
|
机器学习/深度学习 算法 异构计算
构建高效机器学习模型的策略与实践
【5月更文挑战第8天】 随着数据科学领域的不断进步,机器学习(ML)已成为解决复杂问题的重要工具。然而,构建一个既高效又准确的ML模型并非易事。本文将详细探讨在设计和训练机器学习模型时可以采用的一系列策略,以优化其性能和效率。我们将讨论特征工程的重要性、选择合适的算法、调整参数以及评估模型的有效性。通过这些策略,读者将能够更好地理解如何提升模型的预测能力并避免常见的陷阱。
|
4天前
|
数据可视化
R语言机器学习方法分析二手车价格影响因素
R语言机器学习方法分析二手车价格影响因素
|
4天前
|
机器学习/深度学习 人工智能 算法
【Python 机器学习专栏】强化学习在游戏 AI 中的实践
【4月更文挑战第30天】强化学习在游戏AI中展现巨大潜力,通过与环境交互和奖励信号学习最优策略。适应性强,能自主探索,挖掘出惊人策略。应用包括策略、动作和竞速游戏,如AlphaGo。Python是实现强化学习的常用工具。尽管面临训练时间长和环境复杂性等挑战,但未来强化学习将与其他技术融合,推动游戏AI发展,创造更智能的游戏体验。

热门文章

最新文章