數(shù)據(jù)遷移與清洗的策略研究及其在政務(wù)基礎(chǔ)數(shù)據(jù)的應(yīng)用

楊光，吳明芬，李敬民

（五邑大學 智能制造學部，廣東 江門 529020）

數(shù)據(jù)的質(zhì)量是數(shù)據(jù)分析和數(shù)據(jù)挖掘的基礎(chǔ). 海量數(shù)據(jù)在收集后常常存在著數(shù)據(jù)分散和數(shù)據(jù)重復(fù)等情況，這些數(shù)據(jù)也包含了大量的錯誤值、缺失值、異常值以及可疑數(shù)據(jù)等，如何將這些“臟”數(shù)據(jù)，變成可以進行分析和挖掘的“干凈”數(shù)據(jù)，是大數(shù)據(jù)預(yù)處理不可缺少的環(huán)節(jié)[1].

大數(shù)據(jù)預(yù)處理主要包括數(shù)據(jù)遷移與數(shù)據(jù)清洗，數(shù)據(jù)遷移是指不同存儲格式、數(shù)據(jù)類型以及硬件設(shè)備之間的數(shù)據(jù)移動過程. 數(shù)據(jù)清洗是指對不準確、不完整或不合理數(shù)據(jù)進行修補或移除以提高數(shù)據(jù)質(zhì)量的過程[2]. 當前，數(shù)據(jù)的質(zhì)量越來越引起人們的重視[3]，許多學者針對這兩種技術(shù)也開展了一系列研究，郝爽等[4]指出數(shù)據(jù)清洗是對“臟”數(shù)據(jù)進行檢測和糾正的過程，是進行數(shù)據(jù)分析和管理的基礎(chǔ). 陸葉杉[5]在某系統(tǒng)建設(shè)中設(shè)計了一套基于ETL 工具的數(shù)據(jù)遷移流程，并通過組合和串聯(lián)得到了完整的數(shù)據(jù)遷移流程線，完成了新舊系統(tǒng)的數(shù)據(jù)對接. 目前，數(shù)據(jù)遷移與清洗技術(shù)取得了很多成果，但以下方面還有待進一步改進.

1）數(shù)據(jù)清洗技術(shù)主要處理重復(fù)、異常、邏輯錯誤和不一致的數(shù)據(jù)等. 目前大部分的研究都是針對某項或幾項清洗內(nèi)容獨立進行，如果按照每一類數(shù)據(jù)單獨清洗，那么其清洗效率會非常低，因為在數(shù)據(jù)庫里，有可能包含幾類需要清洗的數(shù)據(jù)，需要按列的內(nèi)容進行清洗.

2）在數(shù)據(jù)分析與挖掘的過程中，數(shù)據(jù)遷移與清洗往往要花費整個過程60%～80%的時間[6]，當數(shù)據(jù)量很大時，遷移與清洗的效率是需要重點研究的問題.

針對以上問題，本文設(shè)計了數(shù)據(jù)遷移與清洗策略及流程，并應(yīng)用在某市政務(wù)服務(wù)基礎(chǔ)數(shù)據(jù)上，該策略能在不改變原數(shù)據(jù)的情況下，提高數(shù)據(jù)的質(zhì)量，提升清洗速度，同時可以定時自動清洗，遷移和清洗完成后還能自動統(tǒng)計遷移與清洗結(jié)果并生成報告.

1 數(shù)據(jù)遷移策略及流程

1.1 數(shù)據(jù)遷移策略

數(shù)據(jù)在遷移之前，需要詳細調(diào)研并確定原數(shù)據(jù)庫的數(shù)量n、目標數(shù)據(jù)庫的數(shù)據(jù)m以及用何種方式遷移. 現(xiàn)在有兩種比較通用的遷移方式，一種是全量遷移，另一種是增量遷移，具體如圖1 所示.

1）當n=m= 1，就是從一個原數(shù)據(jù)庫遷移到一個目標數(shù)據(jù)庫，容災(zāi)備份就屬于這種情況.

圖1 數(shù)據(jù)遷移方式

2）當n＞ 1，m= 1，就是將多數(shù)據(jù)庫遷移到一個數(shù)據(jù)庫，方便數(shù)據(jù)的分析、挖掘和共享.

3）當n＞ 1，m＞ 1，當數(shù)據(jù)量很大時，將多原數(shù)據(jù)庫遷移到多個數(shù)據(jù)中心進行分類分級管理，既保障數(shù)據(jù)的安全，又達到數(shù)據(jù)共享的目標.

確定了遷移的數(shù)據(jù)后，遷移方式也是至關(guān)重要的一步. 全量遷移適用于時間戳沒有明顯界限的數(shù)據(jù)，需要做全量備份. 這種方式遷移的數(shù)據(jù)量較多，每次遷移都需要遷移全部數(shù)據(jù)，但全量遷移不需要更新數(shù)據(jù)庫，只需要將原來的數(shù)據(jù)清空，再插入數(shù)據(jù). 增量遷移則適用于時間戳有明顯界限的數(shù)據(jù)，每次遷移只需將時間戳變動的數(shù)據(jù)遷移到目標庫即可，但需要進行數(shù)據(jù)庫的更新. 與增量遷移相比，全量遷移的操作比較簡單，但當需要遷移的數(shù)據(jù)量比較大時，遷移的時間比較長. 如果數(shù)據(jù)每周變動比較少，而且時間戳界限比較明顯，那么采用增量遷移的方式會更高效.

1.2 數(shù)據(jù)遷移流程

數(shù)據(jù)在進行遷移時，可以按照串行和并行的方式進行遷移. 串行遷移需要執(zhí)行完一個流程再執(zhí)行下一個流程，并行遷移是一起并發(fā)執(zhí)行. 串行遷移作業(yè)和并行遷移作業(yè)如圖2、3 所示，圖中的作業(yè)方式都是針對n個原數(shù)據(jù)庫遷移到一個目標數(shù)據(jù)庫，遷移到多個目標數(shù)據(jù)庫時，原理相同.

圖2 串行遷移作業(yè)

圖3 并行遷移作業(yè)

串行作業(yè)和并行作業(yè)都是由多個轉(zhuǎn)換組成的，作業(yè)的主要作用是調(diào)用多個轉(zhuǎn)換或者作業(yè)形成一個完整的流程以方便執(zhí)行. 在數(shù)據(jù)遷移時，最影響遷移速度的是單個表的遷移轉(zhuǎn)換. 在編寫單個表的遷移轉(zhuǎn)換時，須進行多次調(diào)優(yōu)，才能使遷移速度達到比較理想的狀態(tài)，最常使用的是增加線程的方式.

數(shù)據(jù)遷移流程步驟如下：

1）清空目標表：用于清空目標表里面的數(shù)據(jù)，適用于全量遷移的過程，若是增量遷移則不需要清空目標表，只需插入更新目標表數(shù)據(jù)即可；

2）遷移表：連接需要遷移的數(shù)據(jù)源；

3）目標表：用于將需遷移表的數(shù)據(jù)導入到目標表中，為了加快導入的速度，會使用多個線程，在線程的選擇上，通過設(shè)置多個線程可以加快程序的運行速度，但過多的線程會造成阻塞和資源浪費，過少的線程達不到速度的優(yōu)化，通過計算最佳線程[7]可以使程序的運行速度達到最佳，同時也不會過多地占用計算機資源，公式如下：

其中，Nthreads指最佳線程數(shù)量，Ncpu指CPU 的數(shù)量，Twaiting指線程等待時間，Tcpu指線程CPU 執(zhí)行時間. 在平常的遷移中將線程數(shù)設(shè)置成與本機CPU 數(shù)量一樣，即可達到較優(yōu)的遷移速度.

本策略的遷移表和目標表能夠支持Oracle、MySQL、SQL server、MongoDB、Hbase 等常用的數(shù)據(jù)庫. 對關(guān)系型和非關(guān)系型數(shù)據(jù)也可以支持，這就較好地解決了異構(gòu)數(shù)據(jù)庫的問題，僅使用表輸入表輸出就可以將不同服務(wù)器、不同數(shù)據(jù)庫、不同表輸出到符合要求的目標表，且在輸入和輸出時，還可選擇Excel、CSV、TXT 等格式的數(shù)據(jù)進行數(shù)據(jù)輸入或輸出.

數(shù)據(jù)遷移流程要根據(jù)實際應(yīng)用情況，通過多次測試和調(diào)優(yōu)遷移速度才能達到較滿意的效率.

2 數(shù)據(jù)清洗策略及流程

2.1 數(shù)據(jù)清洗策略

數(shù)據(jù)清洗是確保數(shù)據(jù)質(zhì)量最重要的環(huán)節(jié)，它對利用基礎(chǔ)標準庫進行反向清洗、數(shù)據(jù)分析和建立數(shù)據(jù)模型起著至關(guān)重要的作用. 通過對大量不同行業(yè)的數(shù)據(jù)進行調(diào)研和分析，設(shè)計了數(shù)據(jù)清洗策略流程圖，如圖4 所示.

圖4 數(shù)據(jù)清洗策略流程

1）數(shù)據(jù)調(diào)研：在數(shù)據(jù)清洗之前需要對數(shù)據(jù)進行充分的調(diào)研，這一部分直接決定數(shù)據(jù)清洗的結(jié)果. 如對證件號碼調(diào)研，18 位身份證號碼編碼規(guī)則和15 位身份證號碼的編碼規(guī)則都是正確規(guī)則. 在證件字段中，除了居民身份證號碼，護照號碼等也應(yīng)包含在正確字段內(nèi).

2）分析字段：針對不同的字段有不同的清洗規(guī)則.

3）錯誤分類：目前將錯誤分成缺失、重復(fù)、內(nèi)容錯誤和格式錯誤等. 可對錯誤進行編碼，比如將“缺失”編碼為0100，“重復(fù)”編碼為0200. 對編碼進一步細分，重復(fù)編碼又可以分為身份證重復(fù)0201，統(tǒng)一社會信用代碼重復(fù)0202 等，具體的劃分可以根據(jù)需求來制定. 通過劃分可以方便統(tǒng)計錯誤，并有針對性地清洗.

4）判斷字段是否符合規(guī)范：符合規(guī)范的數(shù)據(jù)可以直接遷入標準庫. 如果不符合規(guī)范，則要對錯誤數(shù)據(jù)貼標簽，并對錯誤進行處理，錯誤處理主要有去重、去空格、全角半角轉(zhuǎn)換、去非法字符等. 錯誤處理完成后對數(shù)據(jù)再次進行驗證，如果滿足規(guī)范就遷入標準庫，否則導入錯誤庫.

2.2 數(shù)據(jù)清洗流程

設(shè)計清洗流程需要先對字段進行調(diào)研，根據(jù)字段的內(nèi)容格式以及錯誤情況制定相應(yīng)的清洗方法.圖5 是本文設(shè)計的數(shù)據(jù)清洗流程，該流程易于操作和拓展.

圖5 數(shù)據(jù)清洗流程

數(shù)據(jù)清洗流程如下：

1）用表輸入連接要清洗的表.

2）用數(shù)據(jù)校驗檢查字段存在的問題，在校驗時主要用的是正則表達式，把滿足校驗條件的數(shù)據(jù)直接插入到正確表，不滿足條件的執(zhí)行清洗路線.

3）清洗路線主要對全角半角、非法字符、空格等進行清洗.

4）清洗完畢后，再次執(zhí)行數(shù)據(jù)校驗，校驗將數(shù)據(jù)分為已更改和未更改，已更改數(shù)據(jù)直接寫入正確表，未更改數(shù)據(jù)輸出到錯誤表，以便進一步觀察數(shù)據(jù)錯誤原因，并反饋給相關(guān)負責部門.

在清洗流程中，最重要的是數(shù)據(jù)校驗這個功能. 數(shù)據(jù)校驗是判斷數(shù)據(jù)是否正確的重要手段. 通過編寫不同的校驗規(guī)則，可以很快地發(fā)現(xiàn)數(shù)據(jù)中存在的問題，并進行清洗.

在具體應(yīng)用中設(shè)計了4 種數(shù)據(jù)校驗方式.

1）數(shù)據(jù)字典校驗

數(shù)字字典是不同類型數(shù)據(jù)的集合，它可以對數(shù)據(jù)進行分類管理. 比如行業(yè)代碼，行政區(qū)域代碼，這些代碼都有專門的字典表. 當需要校驗的數(shù)據(jù)在此字典表中，則校驗通過，否則不通過.

2）正則表達式校驗

正則表達式[9]校驗被廣泛應(yīng)用于檢驗字段值是否正確、替換符合某個模式（規(guī)則）的文本. 在本文中，正則表達式主要用于檢查字段是否正確，以及去除一些非法字符. 但不是所有情況都適用正則表達式，比如校驗身份證號碼、統(tǒng)一社會信用代碼等，不能單從是否滿足正則表達式就判斷其是正確的，還要進行一些規(guī)則驗證.

3）JavaScript 代碼校驗

JavaScript 常用于字段清洗中對數(shù)據(jù)內(nèi)容格式的錯誤糾正，比如全角半角轉(zhuǎn)換、去除一些復(fù)雜字段的內(nèi)容. JavaScript 同樣可以應(yīng)用于校驗數(shù)據(jù)是否正確，比如驗證企業(yè)成立時間是否晚于企業(yè)注銷時間、驗證企業(yè)成立時間是否晚于當前日期、驗證企業(yè)人數(shù)是否合理等.

4）JavaScript 和正則表達式結(jié)合校驗

JavaScript 和正則表達式結(jié)合，可以解決很多復(fù)雜字段問題，比如校驗身份證、校驗統(tǒng)一社會信用代碼、校驗注冊號等等. 如身份證號碼由于歷史原因現(xiàn)在有15 位和18 位的，需要對這兩種格式進行校驗.

以上4 種數(shù)據(jù)校驗方式要結(jié)合具體的字段內(nèi)容進行選擇，通過大量的數(shù)據(jù)調(diào)研了解哪些字段格式是正確的，再選擇適合的數(shù)據(jù)校驗方法，對于太過于復(fù)雜的字段，可以通過Java 程序進行清洗.

3 數(shù)據(jù)遷移與清洗速度對比及結(jié)果分析

本文把相應(yīng)的數(shù)據(jù)遷移和清洗策略應(yīng)用于某市政務(wù)服務(wù)基礎(chǔ)數(shù)據(jù)庫. 以下分別從程序處理速度、數(shù)據(jù)遷移結(jié)果和數(shù)據(jù)清洗結(jié)果等3 個方面進行對比分析.

3.1 程序處理速度對比

實驗數(shù)據(jù)來自MySQL 數(shù)據(jù)庫，一張表里有1120012 條數(shù)據(jù)，60 個字段，數(shù)據(jù)表共534 Mb，Windows Server 2012 R2 操作系統(tǒng)，Intel(R) Xeon(R) CPU E7-4850 v2 @2.30 GHz(8 CPUs)處理器，8 Gb 運行內(nèi)存，Python 版本為3.7.0，Python 編譯器為PyCharm Community Edition 2018.2.4 x64，Kettle[7]版本為5.4，MySQL 版本為5.7，Navicat Premium 版本為15.0.14. 實驗將MySQL 數(shù)據(jù)庫的數(shù)據(jù)從一臺服務(wù)器遷移到另一臺服務(wù)器，也就是跨服務(wù)器、跨數(shù)據(jù)庫、跨數(shù)據(jù)表遷移.

因同一個字段里面有多種錯誤類型，需要對每種錯誤類型，比如重復(fù)、內(nèi)容錯誤、格式錯誤、邏輯錯誤等進行清洗. 而數(shù)據(jù)的讀取和寫入是數(shù)據(jù)遷移與清洗中必不可少的一步，也是影響整個程序運行快慢的最重要一步，故在速度對比上通過表輸入和表輸出進行對比. 分別以文獻[10]的Navicat 處理方式和文獻[11]的Python 處理方式與本策略進行對比，處理時間見表1.

表1 的用時是取5 次實驗平均值，從處理用時可以看出本文策略的用時約為Python 和Navicat的1/4，這僅僅是表輸入和表輸出的用時. 在清洗時需要單獨對每個字段進行清洗，清洗完后每個字段再單獨插入，在時間、操作性、簡易度等方面本文提出的遷移和清洗策略都會比Python 和Navicat有較明顯的優(yōu)勢.

表1 處理時間比較

3.2 數(shù)據(jù)遷移結(jié)果分析

實驗環(huán)境配置為Linux 操作系統(tǒng)，4 個物理4 核CPU，每個CPU 有4 個線程，邏輯CPU 有16個，1 Tb 硬盤，處理器是AMD Opteron(tm) Processor 6172，Kettle 版本為5.4，Python 版本為3.7.0.

在實際工作中，已經(jīng)完成數(shù)據(jù)遷移量約為1080 萬條，目標計劃遷移4000 多萬條的數(shù)據(jù)，每周定時全量遷移. 遷移1080 萬條數(shù)據(jù)，用時約16 min 40 s，數(shù)據(jù)來源的詳細用時如表2 所示.

表2 遷移用時比較

通過實驗發(fā)現(xiàn)，不一定數(shù)據(jù)量越多，遷移速度越慢，遷移的速度也和每條字段的數(shù)量、字段的長度以及字段的內(nèi)容有關(guān)系.

3.3 數(shù)據(jù)清洗結(jié)果分析

對表2 序號1 的210 多萬條數(shù)據(jù)進行清洗，清洗后的數(shù)據(jù)將作為某市政務(wù)服務(wù)基礎(chǔ)信息數(shù)據(jù)的標準數(shù)據(jù)，進而反向清洗其他部門的數(shù)據(jù). 表3 是清洗數(shù)據(jù)量的統(tǒng)計，由于在一個流程里每個表每個字段的清洗時間難以計算，因此用總的時間來作為結(jié)果分析. 表3 累計清洗約2861 萬次，用時1 h 8 min.

表4 是對清洗結(jié)果的統(tǒng)計，錯誤條數(shù)是指含有空格、非法字符、重復(fù)、全半角轉(zhuǎn)換等總的錯誤條數(shù)，已更改是指將字段中存在的錯誤已經(jīng)清洗成滿足條件的數(shù)據(jù)，也就是正確的數(shù)據(jù)，未更正條數(shù)是指通過程序無法更正的錯誤，如企業(yè)名稱錯誤、身份證錄入錯誤和空白錯誤.

表3 清洗數(shù)據(jù)量

表4 清洗結(jié)果

針對表4 序號3 做未更改分析，未更改主要分為字段為空和內(nèi)容錯誤，字段為空是指字段本身缺失無法通過其他字段進行填充，內(nèi)容錯誤是指去重、去空格、去非法字符后仍是錯誤，無法通過程序進行更改，需要人工去確定是否需要更改. 通過對清洗結(jié)果分析可知，涉及到格式錯誤的數(shù)據(jù)已全部修改，涉及到內(nèi)容錯誤的數(shù)據(jù)需結(jié)合人工確定是否需要更改，清洗結(jié)果總體達到要求.

遷移與清洗結(jié)果是在Linux 平臺利用Crontab 定時自動執(zhí)行遷移和自動執(zhí)行清洗，遷移與清洗形成了一個總的執(zhí)行作業(yè)，遷移作業(yè)完成時，立即進行清洗作業(yè).

4 總結(jié)與展望

為了加強對數(shù)據(jù)的管理和提高數(shù)據(jù)的質(zhì)量，本文構(gòu)建了一種通用的數(shù)據(jù)遷移與清洗策略，設(shè)計了數(shù)據(jù)遷移與清洗流程，并在某市政務(wù)服務(wù)基礎(chǔ)數(shù)據(jù)庫上實施應(yīng)用. 在清洗速度和清洗質(zhì)量上都有明顯提升，同時可以實現(xiàn)定時清洗、自動生成統(tǒng)計報告，全自動、可并行、速度快，可廣泛應(yīng)用于各種數(shù)據(jù)遷移與清洗領(lǐng)域.

用本文策略遷移表21080 萬條數(shù)據(jù)并清洗表3210 萬條數(shù)據(jù)，總用時僅為1 h 30 min，數(shù)據(jù)遷移完成的同時，直接進行數(shù)據(jù)清洗入庫.

上述數(shù)據(jù)只是實際遷移與清洗的一部分，到2020 年底需要處理的全部數(shù)據(jù)已經(jīng)達到幾千萬條，如果每個表有十幾甚至幾十個字段，就需要處理上億的數(shù)據(jù)，雖然已經(jīng)通過各種調(diào)優(yōu)技術(shù)和手段，加快處理的速度，但處理速度還不是特別理想. 接下來，計劃結(jié)合用Hadoop 平臺和Spark 技術(shù)，以追求更快的遷移速度以及更好的數(shù)據(jù)清洗結(jié)果.