一種基于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