一种基于MySQL的可扩展ETL系统的研究与实现

摘 要 数据抽取、转换和装载（Extraction，Transformation and Loading，简称ETL）是构建数据仓库的重要步骤，对数据仓库数据质量有着至关重要的影响。但是目前大多数ETL系统都以图形化设计ETL作业为目标，从而导致ETL系统操作复杂、在数据量较大时处理效率偏低且功能难以扩展。针对这些问题，在PERL方法属性处理器和Mysql数据库基础之上，设计并实现了一个轻量级、可扩展、高效的ETL系统（SETL），应用SETL系统可以将大量数据高效地加载到数据仓库中。最后通过试验验证了该系统具有更高效率。

【关键词】ETL 数据库 数据加载 数据转换

随着互联网用户的骤增，系统的访问量越来越大，势必会产生大量数据，例如访问日志数据等。在电子商务、数字媒体、数字广告等领域，通常用这类数据分析用户的行为，从而预测市场前景。数据已成为现代企业的重要资源，是企业运用科学管理、决策分析的基础。当前，大多数企业花费大量的资金和时间构建联机事务处理（On-Line Transaction Processing）的业务系统和办公自动化系统，用来记录事务处理的各种相关数据。而ETL（Extract Transform Load） 能将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后按照定义好的数据仓库模型，加载到数据仓库中，是联机分析处理、数据挖掘的基础。因此，ETL成为了构建数据仓库过程中一个极其重要的环节。

1 问题描述

目前比较成熟的ETL商业工具有IBM Datastage、微软DTS和Oracle Warehouse Builder等，这些商业工具设计的初衷是帮助不具备计算机专业知识的用户设计ETL作业，从而都以元数据驱动的方式实现，这反而增加了这些商业工具的复杂性，用户需要掌握这些工具的复杂的规则和语言，其设计的复杂性直接或间接地导致了数据处理的效率低下和系统可扩展性差。由于商业工具一般都价格昂贵，很多小型企业都选择开发自己的ETL工具，用以构建数据仓库，然而自行开发的ETL工具维护成本相当高。一些开源工具的出现，在一定程度弥补了这些问题，如KETL、Kettle、CloverETL等。然而，这些开源工具也都具有前述的商业工具中存在的问题。

因此，本文设计并实现了SETL 系统，该系统以最终需要生成的数据表为目标，将不同数据处理划分为不同的ETL作业，一个ETL作业最终生成一个数据表，ETL作业在ETL管道（ETL pipe line）中执行，并将最终结果存储到数据库相应的表中。ETL作业和ETL管道实际上是同一含义，ETL作业是对用户而言，ETL管道是對SETL系统而言。SETL采用插件的方式设计ETL作业，一个ETL作业可以插入到SETL系统中，也可以从SETL中卸载掉。这种插件式的结构使得SETL系统结构简单；同时，不同的ETL管道能在不同的主机上分布式的执行，从而保证SETL的数据处理效率；最后，本文还利用了数据分区等技术对数据库做了相应优化，以保证加载过程及查询操作都较为高效。SETL以Mysql作为数据仓库；采用PERL语言实现，并利用PERL语言的方法属性特性来生成ETL管道，每一个ETL作业在一个管道中执行，不影响其它ETL作业，使得SETL具有高可扩展性。

2 ETL简介

ETL 是数据的抽取、转换和加载，它能将分散、凌乱、异构的数据整合到目标数据仓库中，以供不同的业务系统查询使用。它首先从多种数据源中收集数据并进行处理，然后把处理过的数据加载到数据库中，其过程是相互关联的任务的顺序执行（如图1如示），主要包括数据抽取，数据转换和数据加载。

3 SETL系统架构

ETL 过程是一个端到端的过程，是一种从源到目标系统转换数据的过程。SETL中的ETL作业就是一个ETL过程，SETL系统主要由任务分析器、任务分发器和ETL管道组成。

任务分析器用于分析用户定义的ETL作业，并对ETL作业进行语法、语义检查。任务分析器基于PERL语言的方法属性处理器实现。PERL语言的方法属性处理器能在编译过程中BEGIN、CHECK、INIT和END中的某一阶段触发，从而实现在系统编译期对用户定义的ETL作业进行分析。

任务分发器收集SETL系统中所有的ETL作业，根据用户定义配置将每一个任务分发到不同的ETL管道中去执行。ETL作业执行成功，相应的数据便能成功加载到目标数据库中。

ETL管道是一系统的方法调用，每个ETL管道可以在一个线程运行，也可以在另一个主机上的一个独立进程中运行。这样设计可以提高ETL执行效率，且不同任务之间互不干扰，从而去掉一个已有ETL作业或增加一个新的ETL作业都不会影响到其它正常ETL作业的运行，实现了SETL系统的可扩展性。其系统架构图如图2所示。

4 SETL系统实现

图2所示的任务分析器中，首先定义了五种PERL语言方法属性：

Sub Setup：ATTR（CODE） {}；

Sub Extract：ATTR（CODE）{}；

Sub Transfor：ATTR（CODE）{}；

Sub Load：ATTR（CODE）{}；

Sub Teardown：ATTR（CODE）{}；

当ETL管道中定义的方法具有上述这五种方法属性中的任何一种属性时，ETL管道中定义的该方法语法信息会被当作参数传入与其属性相对应的属性定义方法中，语法信息包括：该方法所在类名，包含该方法的符号表引用，该方法的引用，方法属性的名称，属性的数据，该方法触发属性处理器的阶段，属性处理器所在文件及在该文件的行号。

ETL作业由一系列方法组成，每个方法可以具有上述的五种属性中的一个。每一个ETL作业都可以定义Setup、Extract、Transform、Load和Teardown这五个步骤中的某几个步骤：

Sub method_name：Setup（qw（task_name1，…））

Sub method_name：Extract（qw（task_name1， …））

Sub method_name：Transfor（qw（task_name1， …））

Submethod_name：Load（qw（task_name1，…））

Sub method_name：Teardown（qw（task_name1， …））

其中，method_name是一個Perl方法的名称，只需要符合Perl语言方法命名规则即可；Setup是该方法的一个属性，表示该方法将在ETL过程第一步调用，其它的属性类似；qw（task_name1，…）是属性的值（ETL作业名称），表示该方法属于哪些ETL作业，这样做的目的是使某些ETL作业共享这些方法。SETL系统能在编译期间统计有哪些ETL作业需要运行，然后依次加载并运行。要实现一个ETL作业，只需要定义一个继承SETL的类即可，并且实现上述五种带有属性的方法。系统的主要类图如图3所示。

对于指定的某一个ETL作业，将其放入一个ETL管道中运行，其运行流程如图4所示。本文将一个ETL管道中的五个步骤称之为五个运行级别，这样可以使ETL管道运行到指定的步骤后停止运行，从而实现ETL作业细粒度运行。

在SETL系统中，采用Log4perl模块输出ETL管道的每一步运行日志，并在此基础上实现了基于日志分析的监控系统。从而能够实时监控线上SETL系统运行情况，例如SETL系统的每一个ETL作业所花费的时间等。

5 ETL作业示例

现有某系统十小时的日志数据，每个小时的数据存储在一个压缩包内，每个压缩包大约900M（共十个压缩包），每个压缩包有一千一百万条记录，以二进制文本存储。每个记录以object_id，object_type，interval_start为主键。现需要将object_type为特定值的数据加载到数据库中，且经常需要查询某一个小时的数据。现根据需求设计ETL作业如下：

5.1 Setup过程

该过程负责处理执行传统ETL步聚前需要做的初始化操作，如建立数据库表、对数据库表进行分区、准备源数据等。由于数据量较大，因此可以将数据按时间（interval_start）加以分区。数据的分区对应于数据库中表的分区，这样做既可以避免数据的重复处理，又可以加速数据的加载过程和查询操作，数据库表分区定义为：

CREATE TABLE test_table （

object_id int unsigned NOT NULL，

object_type int unsigned NOT NULL，

interval_start mediumint NOT NULL，

…

PRIMARY KEY（object_id， interval_start）

）ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

PARTITION BY RANGE（interval_start）

（

PARTITION zero VALUES LESS THAN （1），

PARTITION VALUES LESS THAN （1000），

PARTITION VALUES LESS THAN （1001）

…

PARTITION future VALUES LESS THAN MAXVALUE

）；

5.2 Extract过程

该过程负责将异构数据源如二进制文件、数据库表等抽取出来，统一文本方式存储，便于后继步聚的合并、清洗、分类、分拣和列检索等操作。抽取的方法通常依赖于源数据的具体结构，为上述二进制文件编写单独的抽取工具，将抽取出来的数据以CSV文本格式存储。

5.3 Transform过程

该过程负责将抽取出来的数据进行合并、清洗、分类、分拣和列检索等处理，形成最终能直接加载到目标数据库的文本文件。这一步中可以编写具体系统相关的高效转换工具，也可以用已有的文本处理工具sed、awk、grep（其实这些工具已相当高效）等来分析抽取出来的CSV文本文件。这里采用awk进行数据转换，如过滤掉object_type为”test”的记录并统计与上一个小时除主键外的其它域发生变化的object个数，其中第一、二、三个域为记录主键。

awk -F， ARGIND==1{＼

key=$1"，"$2"，"$3；f1[key]=$4；f2[key]=$5；f3[key]=$6；f4[key]=$7；f5[key]=$8；f6[key]=$9；} ＼

ARGIND>1&&（$2 ！= “test”） {

if（f1[key2]！=$4 || f2[key2]！=$5 || f3[key2]！=$6 ||f4[key2]！=$7||f5[key2]！=$8|| f6[key2]！=$9）{shifts++}}

END{print $0"，"shifts} file1 file2

5.4 Load过程

该过程直接将转换后的数据文件加载到相应数据库中。在加载过程中，用Mysql的内建函数load data来加载数据文件：

LOAD DATA ‘file_name

INTO TABLE tab_name

FIELDS TERMINATED BY '，' ENCLOSED BY '"'

LINES TERMINATED BY ‘＼n

5.5 Teardown过程

该过程负责处理后续事宜，如删除无用文本文件、生成一些统计信息表以方便用户查询等。

从以上ETL作业的示例可以看出，在SETL系統中定义一个ETL作业，只需定义上述几个步骤对应的方法即可，可以借助已有的sed、awk和grep等高效的文本处理工具，大大减少了设计ETL作业的代码量。

6 实验验证

为了测试SETL系统的性能，进行了如下实验：

（1）采用SELT运行第五章所述的ETL作业十次，每次加载一个小时的数据，每小时数据有一千一百万条记录，运行ETL任务过程中记录ETL每一步所花费的时间，实验结果如图5所示。

（2）分别用开源工具KETTLE和SETL运行相同的ETL作业十次，每次加载一个小时数据，每小时数据有一千一百万条记录，记录该ETL作业分别在Kettle和SETL中运行所需时间，实验结果如图6所示。

实验环境为：Inter（ n） Celeron（ r） 2.60GHz，内存 3.00GB。软件环境为： 32位Ubuntu11.10，Perl5.12，Mysql 5.1数据库。

从图5可以看出SETL系统具有较高效率，一千多万条数据加载在80秒内便能处理完毕，且大部分时间花费在数据转换这一步，这一步是由转换规则的复杂性决定的；从图6的对比可以看出，SETL比开源工具Kettle具有更高的执行效率。

7 结束语

本文阐述了可扩展 ETL 系统的设计情况以及数据的处理过程。系统是在合理运用现有 ETL 技术的基础上，利用Perl的方法属性处理器和Mysql数据库的优化方法设计并实现了具有较高扩展性和较高效率的SETL系统。本系统不足之处在于，设计ETL作业的自动化程度不高，部分工作需要手工编码完成。

参考文献

[1]赵俊，夏小玲.公共数据中心的ETL系统设计与实现[J].计算机应用与软件，2011，28（10）：168-169，190.

[2]郭树行，兰雨晴.基于EDI的异地协同工作流管理系统研究与实现[J].计算机系统应用，2004，13（08）：7-9.

[3]徐俊刚，裴莹.数据ETL研究综述[J].计算机科学，2011，38（04）：15-20.

[4]Simitsis A.Mapping Conceptual to Logical Models for ETL Processes[C].Proceedings of the 8th ACM International Workshop on Data Warehousing and OLAP.New York：ACM，2005：67-76.

[5]Simitsis A，Vassiliadis P. A Methodology for the Conceptual Modeling of ETL Prcoess[C].Process of the Decision Systems Engineering Workshop.Kla-genfurt：CAiSE，2004：501-505.

[6]Mysql [EB/OL].http：//dev.mysql.com/doc/refman/-5.6/en/insert-speed.html.

[7]Perl Attribute[EB/OL].http：//perldoc.perl.org/-Attribute/Handlers.html.

[8]Squire C.Data Extraction and Transformation for the Data Warehouse Solutions[C].Proceedings of the 1995 ACM SIGMOD International Conference on Management of Data.New York：ACM，1995：446-447.

作者简介

冯运辉（1979-），男，河北省廊坊市人。硕士学位。工程师。主要研究方向为计算机科学与技术。

作者单位

91550部队41分队 辽宁省大连市 116023