唐文笙，張亮，程登，溫麗梅

（上汽通用五菱汽車股份有限公司 廣西汽車新四化重點實驗室，廣西柳州，545007）

0 引言

近年來，大數(shù)據(jù)技術(shù)掀起了計算機領(lǐng)域的一個新的浪潮，無論是數(shù)據(jù)挖掘、數(shù)據(jù)分析、數(shù)據(jù)可視化，還是機器學(xué)習(xí)、人工智能，它們都繞不開“數(shù)據(jù)”這個主題，從新興產(chǎn)業(yè)到傳統(tǒng)行業(yè)，從統(tǒng)計學(xué)家到軟件開發(fā)人員，越來越多的人對數(shù)據(jù)科學(xué)產(chǎn)生了興趣，廉價的硬件、可靠的數(shù)據(jù)處理工具以及海量的數(shù)據(jù)這些資源使得我們可以輕松地、精確地發(fā)現(xiàn)趨勢、預(yù)測未來。

而同時，對于傳統(tǒng)汽車行業(yè)，近幾年由內(nèi)而外的智能化轉(zhuǎn)型，讓智能汽車逐步成為了新一代汽車消費者的首選，而智能汽車各個零部件所產(chǎn)生的海量數(shù)據(jù)，更加有助于汽車廠商們對用戶的需求進行定位，從而幫助汽車廠商們快速跟進用戶需求，拓寬汽車營銷市場。然而，海量數(shù)據(jù)來源于汽車的各個零部件，每個零部件所上傳的數(shù)據(jù)類型也是多且繁雜的，因此數(shù)據(jù)中會夾雜著不完整、重復(fù)以及錯誤的數(shù)據(jù)，如果直接使用這些原始數(shù)據(jù)，不僅沒辦法提高數(shù)據(jù)決策的準(zhǔn)確性，反而更容易帶偏決策，嚴(yán)重者可能錯失用戶市場。所以，我們需要對原始數(shù)據(jù)進行一個數(shù)據(jù)清洗的相關(guān)操作，進而得出可靠的、準(zhǔn)確的數(shù)據(jù)。

ETL就是用來描述將數(shù)據(jù)從源端經(jīng)過抽取、轉(zhuǎn)換、加載至目的端的過程，它能夠?qū)Ω鞣N分布的、異構(gòu)的源數(shù)據(jù)進行抽取，按照預(yù)先設(shè)計好的規(guī)則將不完整數(shù)據(jù)、重復(fù)數(shù)據(jù)以及錯誤數(shù)據(jù)等“臟”數(shù)據(jù)進行清洗，得到符合要求的“干凈”數(shù)據(jù)。

Kettle是一款完全開源的ETL工具，其主要作用是完成數(shù)據(jù)抽取、轉(zhuǎn)換和加載等數(shù)據(jù)處理方面的工作。Kettle誕生于2006年，經(jīng)過十五年的發(fā)展完善，kettle現(xiàn)在已經(jīng)可以全面支持多平臺運行，可對接包括傳統(tǒng)數(shù)據(jù)庫、文件、大數(shù)據(jù)平臺、接口、流數(shù)據(jù)等數(shù)據(jù)源，支持ETL數(shù)據(jù)管道加入機器學(xué)習(xí)算法。同時，kettle提供了簡單明了的圖形操作界面，它的流程式設(shè)計也十分地便捷。

1 Kettle的轉(zhuǎn)換和作業(yè)

Kettle的控件包含兩個部分，即轉(zhuǎn)換(Transform)和作業(yè)(Job)。

轉(zhuǎn)換是ETL中最主要的部分，它處理抽取、轉(zhuǎn)換、加載各個階段各種對數(shù)據(jù)行的操作。轉(zhuǎn)換包括一個或多個步驟，如讀取文件、過濾輸出行、數(shù)據(jù)清洗或者將數(shù)據(jù)加載到數(shù)據(jù)庫。

轉(zhuǎn)換里的步驟通過跳來連接，跳定義了一個單向通道，允許數(shù)據(jù)從一個步驟向另一個步驟流動。在Kettle中，數(shù)據(jù)的單位就是行，數(shù)據(jù)流就是數(shù)據(jù)行從一個步驟到另一個步驟的移動過程。

轉(zhuǎn)換的跳，就是步驟之間帶箭頭的連線，跳定義了步驟之間的數(shù)據(jù)通路。跳實際上是兩個步驟之間的行集緩存，當(dāng)行集滿了，則向行集寫入數(shù)據(jù)的步驟將停止寫入，直到行集又有了空間，當(dāng)行集空了，從行集讀取數(shù)據(jù)的步驟將停止讀取，直到行集又有了可讀取的數(shù)據(jù)行。跳基于這種行集緩存的規(guī)則允許每個步驟都由一個獨立的線程運行，這樣的并發(fā)程度最高；同時這一規(guī)則也允許數(shù)據(jù)以最小消耗內(nèi)存的數(shù)據(jù)流的方式來處理。當(dāng)轉(zhuǎn)換啟動后，所有步驟都同時啟動，從它們的輸入跳中讀取數(shù)據(jù)，并把處理過的數(shù)據(jù)寫入到輸出跳，直到輸出跳中不再有數(shù)據(jù)，就中止步驟的運行，當(dāng)所有的步驟都中止了，整個轉(zhuǎn)換就中止了。

Kettle中提供了許多單一組件用以實現(xiàn)轉(zhuǎn)換，每個轉(zhuǎn)換由一個或者多個步驟組成，每個步驟通常包含一個組件，每個組件都有其單一作用，一個完整的轉(zhuǎn)換可以作為作業(yè)的一個步驟而存在，轉(zhuǎn)換是kettle開發(fā)的基礎(chǔ)，而其中的組件是轉(zhuǎn)換的基礎(chǔ)，下文會詳細介紹常用組件的作用。

作業(yè)由一個或者多個作業(yè)項組成，這些作業(yè)項按照某種順序來執(zhí)行，形成一個工作流；作業(yè)執(zhí)行順序有作業(yè)項之間的跳和每個作業(yè)執(zhí)行結(jié)果來決定。作業(yè)中每一個作業(yè)項，都必須等到前面的步驟執(zhí)行完畢，后面的步驟才會執(zhí)行，如此等待全部步驟完成后就完成了此作業(yè)。

作業(yè)是按照順序執(zhí)行作業(yè)項，所以必須定義一個起點，從而有了一個“開始”的作業(yè)項，一個作業(yè)只能定義一個“開始”作業(yè)項。同時，Kettle使用了一種回溯算法來執(zhí)行作業(yè)里的所有作業(yè)項，而且作業(yè)項的云心結(jié)果（真或假）也決定執(zhí)行路徑。這種回溯算法就是：假設(shè)執(zhí)行到了圖里的一條路徑的某個節(jié)點時，要依次執(zhí)行這個節(jié)點的所有子路徑，直到?jīng)]有再可以執(zhí)行的子路徑，就返回該節(jié)點的上一個節(jié)點。這種回溯算法有兩個重要特征：1.當(dāng)在作業(yè)中創(chuàng)建了一個循環(huán)時，一個作業(yè)項就會被執(zhí)行多次，作業(yè)項的多次運行結(jié)果會保存在內(nèi)存中，后續(xù)作業(yè)項可以使用。2.如果不對作業(yè)項定義執(zhí)行順序的話，那么該作業(yè)會存在多次運行結(jié)果，這種情景對于作業(yè)的嵌套很不友好，不確定的因素會直接影響后續(xù)作業(yè)的執(zhí)行結(jié)果，進而影響到整個作業(yè)流程的結(jié)果。

2 Kettle關(guān)鍵組件

Kettle本身是一個可視化的ETL工具，下面從幾個點介紹Kettle的關(guān)鍵組件：

(1)數(shù)據(jù)準(zhǔn)備

Kettle提供了多種數(shù)據(jù)庫連接方式，其中包括JDBC、ODBC和JNDI連接，一般情況下，常用的連接方式是JDBC和JNDI，這兩種方式與Kettle本身開發(fā)源碼十分契合。值得注意的是，配置好數(shù)據(jù)源，做好數(shù)據(jù)準(zhǔn)備之后，可以將其配置共享為全局的數(shù)據(jù)源，這樣就可以在所有的轉(zhuǎn)換中共用一個數(shù)據(jù)，避免冗余接入的麻煩。

(2)數(shù)據(jù)抽取

Kettle提供多種數(shù)據(jù)抽取方式，其中包括XML輸入、EXCEL輸入、JSON輸入、CSV輸入、表輸入等，前三種輸入方式為文件輸入方式，可直接讀取指定文件內(nèi)數(shù)據(jù)，表輸入為數(shù)據(jù)庫輸入，通過查詢語句來提取到數(shù)據(jù)庫表中數(shù)據(jù)。

(3)數(shù)據(jù)轉(zhuǎn)換

Kettle中不提供單一的數(shù)據(jù)轉(zhuǎn)換步驟，有許多的組件組合成為一個完整的數(shù)據(jù)轉(zhuǎn)換功能。下面就一些常用的組件進行介紹：

排序記錄：顧名思義，就是對數(shù)據(jù)流進行排序，Kettle中許多組件的前置步驟就需要進行排序；

去除重復(fù)記錄：此組件的作用就是去除重復(fù)的數(shù)據(jù)記錄，如果存在兩條重復(fù)的記錄，則會刪除第二條數(shù)據(jù)；

數(shù)值范圍：此組件的作用對指定的字段進行限制，將其限制在指定的數(shù)值范圍內(nèi)；

拆分字段：此組件的作用是將一個字段的內(nèi)容按照指定規(guī)則拆分；

替換NULL值：此組件的作用是將NULL值替換成指定的內(nèi)容；

值映射：此組件的作用是將字段內(nèi)容映射成指定的內(nèi)容；

此外，Kettle還提供其他的組件功能，各個轉(zhuǎn)換功能都十分強大。

(4)數(shù)據(jù)裝載

完成數(shù)據(jù)準(zhǔn)備、抽取以及轉(zhuǎn)換后，就需要將數(shù)據(jù)裝載輸出，Kettle提供多種數(shù)據(jù)裝載組件，其中有表輸出、插入/更新、EXCEL輸出、文本文件輸出等。表輸出和插入/更新是輸出至數(shù)據(jù)庫表，而EXCEL輸出和文本文件輸出是輸出成文件形式，其中插入/更新是按照指定的字段進行插入/更新操作，而表輸出、EXCEL輸出、文本文件輸出則是全量輸出。

(5)查詢連接

實際Kettle應(yīng)用有許多需要連表查詢的操作，將兩個數(shù)據(jù)庫通過指定關(guān)鍵字連接，并查詢數(shù)據(jù)結(jié)果，此時就可以用到記錄集連接、記錄關(guān)聯(lián)以及流查詢了，其中：流查詢主要用來查詢數(shù)據(jù)源里的數(shù)據(jù)并合并到主數(shù)據(jù)流中，記錄集連接則是通過關(guān)鍵字進行連接，實現(xiàn)SQL語句中左右連接以及內(nèi)外連接的效果，記錄關(guān)聯(lián)則是實現(xiàn)笛卡爾積的一個步驟組件。

3 數(shù)據(jù)清洗方案

對于數(shù)據(jù)及時性有較高要求的業(yè)務(wù)場景中，比如智能網(wǎng)聯(lián)汽車車機產(chǎn)生的行駛數(shù)據(jù)，每時每刻都會有許許多多的車輛上傳數(shù)據(jù)報文，每天的數(shù)據(jù)內(nèi)容巨大，而且其內(nèi)容參差不齊，這就要求我們有一個完整的數(shù)據(jù)清洗規(guī)則，對于車機上傳的臟數(shù)據(jù)進行清洗，進而獲得高質(zhì)量的車機行駛數(shù)據(jù)。以下將會通過一個實際案例來講解數(shù)據(jù)清洗方案。

(1)數(shù)據(jù)準(zhǔn)備

首先準(zhǔn)備一個車機數(shù)據(jù)表t_car，該表由ID、VIN、駕駛里程、駕駛時長、平均速度、最高速度組成，該表為一天多臺車的駕駛數(shù)據(jù)統(tǒng)計結(jié)果，不應(yīng)當(dāng)存在一天多條記錄的現(xiàn)象，具體數(shù)據(jù)表如表1所示。

表1 車機數(shù)據(jù)表

(2)去除重復(fù)數(shù)據(jù)

Kettle中提供了去除重復(fù)記錄組件以實現(xiàn)去除重復(fù)記錄的效果，使用此組件前需要對數(shù)據(jù)進行排序，因此，完整的步驟應(yīng)該是：輸入→排序記錄→去除重復(fù)記錄→輸出；表輸入為讀取車機數(shù)據(jù)表t_car數(shù)據(jù)，排序記錄按照VIN進行排序，并按照VIN進行去除重復(fù)記錄,最終數(shù)據(jù)流流入下一步。

(3)數(shù)據(jù)校驗

數(shù)據(jù)經(jīng)過去重處理后，去除了重復(fù)的LK1545記錄，可以看見數(shù)據(jù)還是存在臟數(shù)據(jù)：最高車速為278km/h 以及 最高車速錯亂，故而還需要對每個數(shù)據(jù)項進行進行數(shù)據(jù)校驗。對于此類數(shù)值類數(shù)據(jù)，可以使用數(shù)值范圍來進行數(shù)據(jù)校驗，對每個字段進行數(shù)值約束：最高車速字段的最小值定義為0，最大值定義為150，同樣，對平均車速加以同樣的約束。

(4)數(shù)據(jù)輸出

數(shù)據(jù)輸出使用表輸出來實現(xiàn)數(shù)據(jù)輸出，將經(jīng)過了前面步驟的數(shù)據(jù)輸出到指定的數(shù)據(jù)表，即可完成數(shù)據(jù)清洗的過程。整體轉(zhuǎn)換流程如圖1所示。

圖1 Kettle實現(xiàn)流程圖

最終的數(shù)據(jù)清洗效果數(shù)據(jù)表如表2所示。

表2 清洗完成數(shù)據(jù)表

可以看到，重復(fù)VIN的數(shù)據(jù)已被清洗掉，同時車速異常的數(shù)據(jù)也被上文定義的規(guī)則清洗掉，得到了質(zhì)量更高的數(shù)據(jù)內(nèi)容。

(5)任務(wù)部署

此前的1、2、3、4步驟已完成了數(shù)據(jù)清洗轉(zhuǎn)換的過程，現(xiàn)在需要將此轉(zhuǎn)換部署成定時任務(wù)的形式。將此轉(zhuǎn)換封裝成作業(yè)，設(shè)置定時，每天的早上7點30分啟動作業(yè)，同時設(shè)置失敗郵件警告，選擇發(fā)送郵件組件，設(shè)置收件人、發(fā)件人及郵件服務(wù)器，當(dāng)作業(yè)失敗時發(fā)送郵件給接收人，可以達到監(jiān)控作業(yè)的目的，方便對應(yīng)人員及時處理作業(yè)過程中發(fā)生的異常；完成相關(guān)設(shè)置后，點擊啟動，Kettle會保留一個進程在后臺，每天7點30分啟動作業(yè)，作業(yè)失敗時會有郵件通知，作業(yè)成功時無其他操作，即完成了任務(wù)部署。

4 總結(jié)

在數(shù)據(jù)爆發(fā)式增長的時代，數(shù)據(jù)挖掘的價值日益可見，數(shù)據(jù)清洗已經(jīng)成為大數(shù)據(jù)挖掘項目中必不可少的準(zhǔn)備步驟。數(shù)據(jù)的質(zhì)量也是會直接影響數(shù)據(jù)分析的結(jié)果，數(shù)據(jù)清洗方案中包含的步驟有很多，例如缺值的處理、去除重復(fù)記錄和數(shù)據(jù)的校驗等等。本文通過Kettle工具提供的組件功能，構(gòu)建了一個車機大數(shù)據(jù)清洗流程，實現(xiàn)了對車機大數(shù)據(jù)的清洗目標(biāo)。

未來將不斷完善數(shù)據(jù)轉(zhuǎn)換清洗的過程,提高數(shù)據(jù)質(zhì)量,優(yōu)化ETL設(shè)計流程,提升數(shù)據(jù)更新效率,為企業(yè)進一步的商業(yè)智能分析、OLAP分析以及知識發(fā)現(xiàn)奠定數(shù)據(jù)基礎(chǔ)。