ETL中的數(shù)據(jù)清洗設(shè)計(jì)

1、ETL中的數(shù)據(jù)清洗設(shè)計(jì)1前言ETL即數(shù)據(jù)抽?。‥xtract）、轉(zhuǎn)換（Transform）、裝載（Load）的過(guò)程。它是構(gòu)建數(shù)據(jù)倉(cāng)庫(kù)的重要環(huán)節(jié)。數(shù)據(jù)倉(cāng)庫(kù)是面向主題的、集成的、穩(wěn)定的且隨時(shí)間不斷變化的數(shù)據(jù)集合，用以支持經(jīng)營(yíng)管理中的決策制定過(guò)程1。數(shù)據(jù)倉(cāng)庫(kù)主要用于決策分析，為領(lǐng)導(dǎo)者提供決策支持信息。數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中有可能存在著大量的臟數(shù)據(jù),引起的主要原因有：濫用縮寫詞、慣用語(yǔ)、數(shù)據(jù)輸入錯(cuò)誤、重復(fù)記錄、丟失值、拼寫變化、不同的計(jì)量單位和過(guò)時(shí)的編碼等。即便是一個(gè)設(shè)計(jì)和規(guī)劃良好的數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)，如果其中存在著大量的臟數(shù)據(jù)，那么這個(gè)系統(tǒng)也是沒(méi)有任何意義的，因?yàn)椤袄M(jìn)，垃圾出”（garbagein,garba

2、geout）,系統(tǒng)根本就不可能為決策分析系統(tǒng)提供任何支持。為了清除臟數(shù)據(jù)，必須在數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中進(jìn)行數(shù)據(jù)清洗。數(shù)據(jù)清洗（datacleansing/datacleaning/datascrubing）是一個(gè)減少錯(cuò)誤和不一致性、解決對(duì)象識(shí)別的過(guò)程2。目前有很多數(shù)據(jù)清洗研究2346和ETL研究8910，但是如何在ETL過(guò)程中進(jìn)行有效的數(shù)據(jù)清洗，此方面研究不多。筆者認(rèn)為這包括三方面的內(nèi)容：（1）ETL處理方式的選擇（2）數(shù)據(jù)清洗的原理及在ETL中的應(yīng)用模型（3）數(shù)據(jù)清洗的具體實(shí)現(xiàn)過(guò)程。2ETL處理方式數(shù)據(jù)抽取負(fù)責(zé)完成從數(shù)據(jù)源找到并取出當(dāng)前主題所需的那部分?jǐn)?shù)據(jù)，由于數(shù)據(jù)倉(cāng)庫(kù)中各個(gè)主題中的數(shù)據(jù)是按照前端應(yīng)

3、用的需求存放的，因此需要對(duì)抽取出的數(shù)據(jù)進(jìn)行轉(zhuǎn)換以適應(yīng)前端應(yīng)用的需要。轉(zhuǎn)換后的數(shù)據(jù)就可以裝入數(shù)據(jù)倉(cāng)庫(kù)了，數(shù)據(jù)加載過(guò)程定時(shí)進(jìn)行，并且不同主題的數(shù)據(jù)加載任務(wù)有各自不同的執(zhí)行時(shí)間表。常見的ETL處理方式可分為以下三種：（1）數(shù)據(jù)庫(kù)外部的ETL處理數(shù)據(jù)庫(kù)外部的ETL處理方式指的是大多數(shù)轉(zhuǎn)換工作都在數(shù)據(jù)庫(kù)之外、在獨(dú)立的ETL過(guò)程中進(jìn)行。這些獨(dú)立的ETL過(guò)程與多種數(shù)據(jù)源協(xié)同工作，并將這些數(shù)據(jù)源集成。數(shù)據(jù)庫(kù)外部ETL處理的優(yōu)點(diǎn)是執(zhí)行速度比較快。但缺點(diǎn)是大多數(shù)ETL步驟中的可擴(kuò)展性必須由數(shù)據(jù)庫(kù)的外部機(jī)制提供，如果外部機(jī)制不具備擴(kuò)展性，那么此ETL處理就不能擴(kuò)展。（2）數(shù)據(jù)庫(kù)段區(qū)域中的ETL處理數(shù)據(jù)庫(kù)段區(qū)域中的E

4、TL處理方式不使用外部引擎而是使用數(shù)據(jù)庫(kù)作為唯一的控制點(diǎn)。多種數(shù)據(jù)源的所有原始數(shù)據(jù)大部分未作修改就被載入中立的段結(jié)構(gòu)中。如果源系統(tǒng)是關(guān)系數(shù)據(jù)庫(kù)，段表將是典型的關(guān)系型表。如果源系統(tǒng)是非關(guān)系型的，數(shù)據(jù)將被分段置于包含列VARCHAR2（4000）的表中，以便于在數(shù)據(jù)庫(kù)內(nèi)作進(jìn)一步轉(zhuǎn)換。成功地將外部未修改數(shù)據(jù)載入數(shù)據(jù)庫(kù)后，再在數(shù)據(jù)庫(kù)內(nèi)部進(jìn)行轉(zhuǎn)換。這就是系列方法載入然后轉(zhuǎn)換。數(shù)據(jù)庫(kù)段區(qū)域中的ETL處理方式執(zhí)行的步驟是提取、裝載、轉(zhuǎn)換，即通常所說(shuō)的ELT。在實(shí)際數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中經(jīng)常使用這種方式。這種方式的優(yōu)點(diǎn)是為抽取出的數(shù)據(jù)首先提供一個(gè)緩沖以便于進(jìn)行復(fù)雜的轉(zhuǎn)換，減輕了ETL進(jìn)程的復(fù)雜度。但是這種ETL處理的

5、缺點(diǎn)有：（a）在段表中存貯中間結(jié)果和來(lái)自數(shù)據(jù)庫(kù)中源系統(tǒng)的原始數(shù)據(jù)時(shí)，轉(zhuǎn)換過(guò)程將被中斷。（b）大多數(shù)轉(zhuǎn)換可以使用類SQL的數(shù)據(jù)庫(kù)功能來(lái)解決，但它們可能不是處理所有的ETL問(wèn)題的最優(yōu)語(yǔ)言。（3）數(shù)據(jù)庫(kù)中的ETL處理數(shù)據(jù)庫(kù)中的ETL處理方式使用數(shù)據(jù)庫(kù)作為完整的數(shù)據(jù)轉(zhuǎn)換引擎，在轉(zhuǎn)換過(guò)程中也不使用段。數(shù)據(jù)庫(kù)中的ETL處理具有數(shù)據(jù)庫(kù)段區(qū)域中的ETL處理的優(yōu)點(diǎn)，同時(shí)又充分利用了數(shù)據(jù)庫(kù)的數(shù)據(jù)轉(zhuǎn)換引擎功能，但是這要求數(shù)據(jù)庫(kù)必須完全具有這種轉(zhuǎn)換引擎功能。目前的主流數(shù)據(jù)庫(kù)產(chǎn)品Oracle9i5等可以提供這種功能。綜上分析三種ETL處理方式，數(shù)據(jù)庫(kù)外部的ETL處理可擴(kuò)展性差，不適合復(fù)雜的數(shù)據(jù)清洗處理，數(shù)據(jù)庫(kù)段區(qū)域中的

6、ETL處理可以進(jìn)行復(fù)雜的數(shù)據(jù)清洗，而數(shù)據(jù)庫(kù)中的ETL處理具有數(shù)據(jù)庫(kù)段區(qū)域ETL處理的優(yōu)點(diǎn)，又利用了數(shù)據(jù)庫(kù)的轉(zhuǎn)換引擎功能。所以為了進(jìn)行有效的數(shù)據(jù)清洗，應(yīng)該使用數(shù)據(jù)庫(kù)中的ETL處理。3數(shù)據(jù)清洗的原理及在ETL中的應(yīng)用模型數(shù)據(jù)清洗的目的是保證數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)質(zhì)量。3.1數(shù)據(jù)質(zhì)量對(duì)于什么是數(shù)據(jù)質(zhì)量，文獻(xiàn)3將其定義為數(shù)據(jù)的一致性（consistency）、正確性（correctness）、完整性（completeness）和最小性（minimality）這4個(gè)指標(biāo)在信息系統(tǒng)中得到滿足的程度。根據(jù)處理的是單數(shù)據(jù)源還是多數(shù)據(jù)源以及問(wèn)題是模式層的還是實(shí)例層的，文獻(xiàn)4將數(shù)據(jù)質(zhì)量問(wèn)題分為4類：?jiǎn)螖?shù)據(jù)源模式層問(wèn)題（如缺

7、少完整性約束、糟糕的模式設(shè)計(jì)等）、單數(shù)據(jù)源實(shí)例層問(wèn)題（如數(shù)據(jù)輸入錯(cuò)誤）、多數(shù)據(jù)源模式層問(wèn)題（如異構(gòu)數(shù)據(jù)模型和模式設(shè)計(jì)等）、多數(shù)據(jù)源實(shí)例層問(wèn)題（如冗余、沖突、不一致的數(shù)據(jù)等）。單數(shù)據(jù)源中出現(xiàn)的問(wèn)題在多數(shù)據(jù)源中也有可能出現(xiàn)，并且這種現(xiàn)象也是普遍發(fā)生的。模式層次上的問(wèn)題也會(huì)體現(xiàn)在實(shí)例層次上。模式層次的問(wèn)題可以通過(guò)改進(jìn)模式設(shè)計(jì)、模式轉(zhuǎn)化和模式集成來(lái)解決。但實(shí)例層次的問(wèn)題在模式層次上是不可見的。所以數(shù)據(jù)清洗主要針對(duì)實(shí)例層次的數(shù)據(jù)質(zhì)量問(wèn)題。3.2數(shù)據(jù)清洗原理文獻(xiàn)11指出，數(shù)據(jù)清洗實(shí)際就是利用有關(guān)技術(shù)如數(shù)理統(tǒng)計(jì)、數(shù)據(jù)挖掘或預(yù)定義的數(shù)據(jù)清洗規(guī)則將臟數(shù)據(jù)轉(zhuǎn)化成滿足數(shù)據(jù)質(zhì)量要求的數(shù)據(jù)。按數(shù)據(jù)清洗的實(shí)現(xiàn)方式與范圍，

8、可將數(shù)據(jù)清洗分為四種：（1）手工實(shí)現(xiàn)方式：用人工來(lái)檢測(cè)所有的錯(cuò)誤并改正。這只能針對(duì)小數(shù)據(jù)量的數(shù)據(jù)源。（2）通過(guò)專門編寫的應(yīng)用程序：通過(guò)編寫程序檢測(cè)/改正錯(cuò)誤。但通常數(shù)據(jù)清洗是一個(gè)反復(fù)進(jìn)行的過(guò)程，這就導(dǎo)致清理程序復(fù)雜、系統(tǒng)工作量大。（3）某類特定應(yīng)用領(lǐng)域的問(wèn)題，如根據(jù)概率統(tǒng)計(jì)學(xué)原理查找數(shù)值異常的記錄。（4）與特定應(yīng)用領(lǐng)域無(wú)關(guān)的數(shù)據(jù)清洗，這一部分的研究主要集中于重復(fù)記錄的檢測(cè)/刪除。3.3數(shù)據(jù)清洗在ETL中的應(yīng)用模型在數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中，數(shù)據(jù)清洗是ETL過(guò)程中的一個(gè)重要環(huán)節(jié),主要任務(wù)是檢測(cè)并刪除/改正將裝入數(shù)據(jù)倉(cāng)庫(kù)的臟數(shù)據(jù)。由于數(shù)據(jù)倉(cāng)庫(kù)的多種異構(gòu)數(shù)據(jù)源和海量數(shù)據(jù)，數(shù)據(jù)清洗應(yīng)是與領(lǐng)域無(wú)關(guān)的。而且數(shù)據(jù)清洗

9、不是ETL中一個(gè)單獨(dú)的步驟，需要與數(shù)據(jù)抽取、數(shù)據(jù)轉(zhuǎn)換集成,與數(shù)據(jù)載入統(tǒng)一使用，需要進(jìn)行循環(huán)處理。如果數(shù)據(jù)源是一個(gè)能力比較強(qiáng)的DBMS（如圖1中的數(shù)據(jù)源1和數(shù)據(jù)源2），則可以在數(shù)據(jù)抽取過(guò)程中使用SQL來(lái)完成一部分的數(shù)據(jù)清洗工作。但是有一些數(shù)據(jù)源不提供這種能力（如數(shù)據(jù)源3），則只能直接將數(shù)據(jù)從數(shù)據(jù)源抽取出來(lái)，然后在數(shù)據(jù)轉(zhuǎn)換的時(shí)候進(jìn)行清洗。數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)清洗主要還是在數(shù)據(jù)轉(zhuǎn)換的時(shí)候進(jìn)行的。使用數(shù)據(jù)庫(kù)ETL處理方式中的DBMS的轉(zhuǎn)換清洗能力完成大部分的工作，這樣數(shù)據(jù)清洗就充分利用了DBMS提供的功能。4數(shù)據(jù)清洗的具體實(shí)現(xiàn)過(guò)程不同的系統(tǒng)可以采用不同的數(shù)據(jù)清洗方法4。我們用Northwind數(shù)據(jù)源為例來(lái)

10、進(jìn)行數(shù)據(jù)清洗。Northwind是SQLSERVER2000自帶的一個(gè)關(guān)于貿(mào)易公司的數(shù)據(jù)庫(kù)。表Customer記錄了公司的客戶信息，共有11個(gè)字段：CustomerIDCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax。我們以這個(gè)表為基礎(chǔ)，編寫程序來(lái)加入一些臟數(shù)據(jù)形成新表：Customernew。數(shù)據(jù)清洗分為以下幾步：（1）元素化將非標(biāo)準(zhǔn)的數(shù)據(jù)格式化成結(jié)構(gòu)數(shù)據(jù)。以數(shù)據(jù)庫(kù)表Customernew的任意一條記錄為例。Address、City和Phone三個(gè)字段值分別為：Address:ObereS

11、tre.57City:BerlinPhone素化為：Address(1):ObereAddress(2):StreAddress(3):57City:BerlinPhone(1):031Phone(2):0074321(2)標(biāo)準(zhǔn)化將元素標(biāo)準(zhǔn)化，根據(jù)字典消除不一致的縮寫。這里的字典是用戶通過(guò)分析樣本表預(yù)先制定的一些規(guī)則的集合。樣本表是根據(jù)概率統(tǒng)計(jì)從表Customernew選擇一部分記錄所形成的一個(gè)表。首先分析Address(2)的值Stre，此值應(yīng)是Street的縮寫。而在字典中Street的縮寫是Str,所以在這里，Stre被認(rèn)為是臟數(shù)據(jù)，正確的數(shù)據(jù)應(yīng)為：Str。(3

12、)校驗(yàn)對(duì)標(biāo)準(zhǔn)化的元素進(jìn)行一致性校驗(yàn),即在內(nèi)容上修改錯(cuò)誤。Berlin的電話區(qū)號(hào)應(yīng)為：030，所以Phone(1)的值應(yīng)為：030。(4)匹配在其它記錄中尋找相似的記錄,發(fā)現(xiàn)重復(fù)記錄。對(duì)整個(gè)表Customernew排序,然后在表中查找Address(1)值為Obere的所有記錄，如果有這樣的記錄再去查找Address(2)，依次類推，如果所有字段的數(shù)據(jù)全都相同，那么這兩條記錄就是重復(fù)記錄。(5)消除重復(fù)記錄根據(jù)匹配結(jié)果進(jìn)行處理，刪除部分記錄或者合并多個(gè)記錄為一個(gè)完整信息的記錄。(6)歸檔將前5個(gè)步驟的結(jié)果寫入元數(shù)據(jù)存儲(chǔ)中心。這樣可以更好地進(jìn)行后續(xù)的清洗過(guò)程，而且可以更加容易理解數(shù)據(jù)源以便在數(shù)據(jù)倉(cāng)

13、庫(kù)中進(jìn)行切片、切塊等操作。在上面的數(shù)據(jù)清洗方法中，我們找出的只是完全相同的記錄。對(duì)于一些相似的記錄，它們也有可能代表的是同一條記錄，找出這些相似重復(fù)記錄對(duì)于這個(gè)貿(mào)易公司有很重要的意義，例如，貿(mào)易公司的一個(gè)客戶在表Customernew中有可能有多條記錄。當(dāng)貿(mào)易公司郵寄廣告時(shí)，他就有可能將多個(gè)廣告郵寄給同一個(gè)客戶。這樣就浪費(fèi)了大量的資金。這只是一個(gè)很簡(jiǎn)單的廣告例子，但是由此我們也可以看出，檢測(cè)/刪除相似重復(fù)記錄可以提高貿(mào)易公司的經(jīng)濟(jì)效益。我們可以采用下面的一種方法或者結(jié)合使用這三種方法來(lái)檢測(cè)相似重復(fù)記錄:基于契比雪夫定理的統(tǒng)計(jì)學(xué)方法：這種方法可以隨機(jī)選取樣本數(shù)據(jù)進(jìn)行分析，加快了檢測(cè)速度，但是這是以犧牲準(zhǔn)確性為代價(jià)的。模式識(shí)別的方法：基于數(shù)據(jù)挖掘和機(jī)器學(xué)習(xí)算法來(lái)查找數(shù)據(jù)，主要牽涉關(guān)聯(lián)規(guī)則算法?；诰嚯x的聚類方法：這也是數(shù)據(jù)挖掘中的算法，重點(diǎn)在于它的類的評(píng)測(cè)標(biāo)準(zhǔn)為歐幾里德距離或者Edit距離，通過(guò)這種距離來(lái)發(fā)現(xiàn)數(shù)