基于正則表達式的海量數據清洗系統

常征 呂勇

摘 要：針對目前主流的數據提取、變形、加載（ETL）工具和受限環境下一些應用的不足之處，結合受限應用場景下的特殊要求，提出一種基于正則表達式的海量數據清洗系統（REMCS）。REMCS首先針對超長錯誤數據問題、批量數據源文件融合問題、數據源文件自動分揀問題等典型的6個問題找到數據的特點，其次根據數據的特點設置合適的正則表達式和預處理算法，然后使用算法模型去除數據中的錯誤完成數據預處理工作。同時詳細闡述了REMCS的系統邏輯結構、常見問題、對應的解決算法和代碼實現方案。最后通過對兼容的數據源文件格式、能夠處理的問題種類、問題處理時間、處理數據極限值等4個方面進行對比，從幾組常見的數據處理問題的對比實驗可知，相較于傳統的ETL工具，REMCS支持csv格式、json格式、dump格式等典型的9種文件格式，能夠處理全部的6種常見問題，處理時間更短，能夠支持的數據極限值更大。實驗結果驗證了針對受限應用場景下常見的數據處理問題，REMCS具有很好的適用性和準確性。

關鍵詞： 正則表達式;數據清洗;大數據;提取、變形、加載工具

中圖分類號：TP391

文獻標志碼：A

Abstract：Based on the current mainstream Extract Transform Load （ETL） tools for data and the disadvantages of some applications in restricted environments， a Regular Expression Mass-data Cleaning System （REMCS） was proposed for the specific requirements in the restricted application scenarios. Firstly， the data features of six typical problems including ultra-long error data， batch fusion of data source files， automatic sorting of data source files， were discovered. And the appropriate regular expressions and pre-processing algorithms were put forward according to the data features. Then， data pre-processing was completed by using the algorithm model to remove the errors in data. At the same time， the system logical structure， common problems， and corresponding solutions， and code implementation scheme of REMCS were described in detail. Finally， the comparison experiments of several common data processing problems were carried out with the following four aspects： the compatible data source file formats， the soveble problem types， the problem processing time and the data processing limit value. Compared with the traditional ETL tools， REMCS can support nine typical file formats such ascsv format， json format， dump format，? and can address all six common problems with shorter processing time and larger supportable data limit value. Experimental results show that REMCS has better applicability and high accuracy for common data processing problems in restricted application scenarios.Key words： regular expression; data cleaning; mass data; Extract Transform Load （ETL） tool

0 引言

筆者參與某集團公司的“核心電子器件在電子信息裝備中的驗證與應用”課題，本課題組通過構建的聯試驗證環境掌握了大量的實驗數據的原始文件，亟須一種自主可控可靠的數據處理技術方法。

截止2017年10月底，該集團公司聯試驗證環境掌握的實驗數據原始文件總大小為21.1TB，實驗數據原始文件總數目為4970405。因為數據涉及商業秘密，該集團公司聯試驗證環境要求所有工作及工具必須經過嚴格審查。本項目團隊面臨的具體業務問題就是把如此海量的數據在有限的工具和環境下進行清洗、導入、遷移和發布[1]。

目前國內外針對海量數據的預處理過程已經有很多成熟的數據預處理——提取、變形、加載（Extract Transform Load， ETL）工具，包括Kettle、Spark stream、Navicat等[2]。關于不同應用場景下的ETL的學術研究也有很多，比較新的研究有基于MySQL環境下處理大數據量的輕量級SETL系統[3]，也有針對Hadoop環境下的視頻流數據的ETL工具的研究[4]，還有面向銀行支付清算業務的大數據分析平臺的ETL流程設計的研究[5]。

本項目團隊在數據處理過程中遇到了許多問題：總量成千上萬，來源多種多樣，格式千奇百怪，質量參差不齊，內容范圍廣泛。

而上面提及的主流工具和研究在面對海量實驗數據處理時暴露出一些不足，例如處理數據靈活性不夠，處理數據格式不夠多樣，針對數據處理過程中出現的問題沒給出定位信息等。

而且因為數據敏感性較高，該集團公司聯試驗證環境要求盡可能在現有基礎工具環境下進行處理，對于其他各種成熟工具都需要另外進行審批和檢查。所以這就要求本項目團隊必須在現有環境下進行數據處理方法上的創新，研發一些自動化工具模型來解決本項目團隊處理實驗數據時候面臨的一些問題。

針對本項目中發現的問題，通過這一年來的積累，本項目團隊逐漸累積下來一些常用的模型工具，整合得到基于正則表達式的海量數據清洗系統（Regular Expression Mass-data Cleaning System， REMCS）。

1 問題的提出

該集團公司聯試驗證環境要求盡可能在現有基礎工具環境下進行處理，對于其他各種成熟工具都需要另外進行審批和檢查。本項目團隊加入之前，該集團公司聯試驗證環境的處理方式是通過人工查找。這種人力方法既費時費力，又只能處理少量的小文件，對于大量的數據文件或者單個大文件都無能為力。

在實驗數據處理過程中本項目團隊發現一些常見的典型問題：

1）數據源文件中出現過長數據內容問題;

2）大量單一表結構數據文件處理問題[6];

3）大量多表數據源文件混雜問題;

4）結構化數據重排列問題;

5）數據內容代碼翻譯問題[7];

6）數據內出現錯行問題[8-9]。

接下來，本文就針對以上這6個典型問題給出基于正則表達式的海量數據清洗系統（REMCS）的處理方式。

2 方法

本文提出的基于正則表達式的海量數據清洗系統的總體結構如圖1所示。

該結構參考了最新的ETL研究。

例如：基于Kafka和Disruptor技術的優化ETL工具[10]，面向數據倉庫的ETL工具的研究與實現[11]，支持探索式研判分析的動態ETL流程服務研究[12]，以及基于Kettle軟件對氣象數據ETL流程的政務云氣象數據倉庫研究[13]，還有高性能正則表達式匹配算法研究[14]和正則表達式匹配算法研究[15]。這些最新的研究為本文提供了很好的研究思路，也為本項目團隊后續的工作提供了參考。

與目前主流的三種典型ETL工具（包括Kettle、Spark stream、Navicat）相比，本文提出的REMCS的不同點主要體現在：

1）工具集合。REMCS針對常見的預處理典型問題預制了對應的模型工具，通過對比實驗可以證明處理兼容性和時間效率上都更好。

2）輸入輸出接口。REMCS針對常見的數據源文件格式都提供了讀取解析工具和輸出工具，對于數據源文件格式和輸出文件格式的兼容性更好。

3）預處理工作臺。REMCS針對數據源文件中的常見錯誤都能自動糾正或者給出錯誤位置信息，便于后續糾正。

接下來本文將針對第1章提出的6個典型問題，重點闡述一下系統的工作原理。

本文會根據不同的問題給出不同的算法實現流程，以下算法模型都是使用Python腳本完成，運行調試環境都是sublime text3和Python2.7編譯器。本文針對實際數據處理過程中遇到的不同數據格式文件出現同一常見問題，提供了多種實現模型。

2.1 特定超長字段自動化檢測模型本模型主要針對數據原文件中出現超長問題字段的自動化檢測問題，這種問題大量存在于excel格式數據、csv格式數據、文本格式數據等。

本文設計了特定字段超長自動化檢測模型，并且提供了不同格式數據文件的處理功能。流程如圖2所示。

圖2 特定超長字段自動化檢測模型流程因為這個問題廣泛存在于多種數據格式的文件中，所以REMCS提供了多種數據文件的支持，包括：文本文件處理模型、excel文件處理模型、dmp文件處理模型。因為原始數據比較敏感而且數量很大，本文均以樣例數據代替。

這里以文本文件處理模型為例。樣例數據如下所示：

REMCS通過遍歷所有的數據，分析得到每一個數據項，然后比較數據項的長度，得到超過最大限制值的數據項內容，給出對應的位置信息和數據內容。

數據處理人員就可以根據位置信息和數據內容來排除數據錯誤，使得數據能夠成功導入。

2.2 批量文件合并自動化模型

本模型主要針對大量單一表結構數據文件的批量自動化處理問題。多個數據源文件中字段都是一樣的，該集團公司聯試驗證環境現在提供的數據導入工具每次只能對于單一數據文件進行導入操作，所以對于大量的小數據文件就需要通過人力不停地進行導入配置，既費力費時又效率不高。

批量文件合并自動化模型的流程如圖3所示。

REMCS處理這種問題的思路是通過Python腳本來遍歷指定文件夾下所有原始數據文件的數據，把所有數據合并到一個數據文件中。

數據處理人員可以直接處理合并之后的數據源文件。

2.3 字段名稱批量對比模型

本模型主要針對大量多表數據源文件混雜問題。根據不同的表頭信息，大量數據源文件可以分為若干類，這樣對于每一類數據源文件就可以歸結到上面第二個問題了。

這種問題大量存在于excel格式數據、csv格式數據、文本格式數據等。

本文設計了字段名稱批量對比模型，并且提供了不同格式數據文件的處理功能。

流程如圖4所示。

這里以多個csv文件挑選為例。樣例數據如下所示。

REMCS處理這種問題的思路是通過Python腳本來遍歷所有的數據源文件，分析得到每一個數據源文件的表頭信息，然后對比表頭信息，將數據源文件分到不同的文件夾中。數據處理人員可以在不同的文件夾中運行2.2節中的模型。

2.4 結構化數據重排模型

本模型主要針對結構化數據重排列問題。實驗數據中的人力資源數據的預處理過程需要從文本格式文件中將人力資源信息進行重新排列，使其能夠符合自動化導入工具要求的應用場景，并且將對應導出的人員圖像根據其員工號碼進行重命名。這種問題大量存在于txt格式數據、sql格式數據、 json格式數據等。本文設計了結構化數據重排列模型，并且提供了不同格式數據文件的處理功能。

REMCS通過Python腳本來遍歷所有的數據，借助正則表達式分析得到每一個數據項，然后根據數據自動導入工具對格式的要求對分析得到的數據項進行重新排列。數據處理人員可以使用重新排列之后的數據文件進行導入操作。

2.5 文本數據代碼批量翻譯模型

本模型主要針對數據內容代碼翻譯問題。當數據來源文件為文本文件，并且數據來源方提供了對應的數據字典，需要根據數據來源方提供的數據字典對數據源文件內部數據進行代碼翻譯。

文本數據代碼批量翻譯模型的流程如圖6所示。

REMCS處理這種問題的思路是通過Python腳本來遍歷指定文件夾下所有原始數據文件的數據，并結合數據來源提供的數據字典，對數據內容進行翻譯。數據處理人員可以直接導入翻譯之后的數據源文件。

2.6 錯行數據自動清洗模型

本模型主要針對數據內出現錯行問題。某些文本格式數據原始文件中數據內部因為出現回車符，導致同一字段內部數據出現跨行，該集團公司聯試驗證環境提供指定數據導入工具針對錯行數據導入出現錯誤。

REMCS處理這種問題的思路是通過Python腳本來遍歷指定文件夾下所有原始數據文件的數據，模型可以通過正則表達式找到出錯的數據行，并將跨行的數據記錄進行糾正。數據處理人員可以直接導入處理之后的數據源文件。

3 實驗與分析

為了驗證本文提出的REMCS在功能和性能上的優勢，從幾個不同的角度進行實驗對比。本實驗使用簡化過的測試數據，數據錯誤出現概率符合正態分布抽樣，以便于對算法模型進行檢驗。實際數據體量應該需要放大成千上萬倍，實際生產環境下的工作結果說明了上述算法模型在實際的海量數據環境下依然能夠正常高效工作。

3.1 不同ETL工具問題適用文件對比

這里本文使用3種常見的通用ETL工具（包括Kettle、Spark stream、Navicat）和本文提出的系統對常見的數據文件格式進行實驗處理對比，包括txt文件、sql文件、csv文件、dmp文件、 json文件、bson文件、xml文件、xls/xlsx文件、doc/docx文件等，以便驗證本文提出的REMCS數據預處理系統對以上常見數據的兼容性。

和本文提出的系統對第2章中提出的6種常見典型問題進行實驗對比，包括：特定超長字段自動化檢測問題（簡稱問題1）、批量文件自動化合并問題（簡稱問題2）、字段名稱批量對比問題（簡稱問題3）、結構化數據重排問題（簡稱問題4）、本文數據代碼批量翻譯問題（簡稱問題5）、錯行數據自動化清洗問題（簡稱問題6），以便驗證本文提出的數據預處理系統對以上6種典型問題的處理效果。

從表2可知，本文提出的REMCS對于6種常見的典型問題都具有很好的處理能力，而其他的3種常見通用工具都有或多或少的局限，不能很好地處理全部6種常見的典型問題。

3.3 處理時間對比

本實驗使用3種常見的通用ETL工具和本文提出的系統對于給定3組數據集合進行處理，對比各種工具的處理時間。本文按照樣本數量從UCI公共數據集合中選擇了3組數據集合進行對比實驗，如表3所示，

包括：

大樣本量的數據集合Census Income（下文稱集合1）、

中等樣本數量的數據集合Wine Quality（下文稱集合2）和

小樣本量的數據集合Student Performance（下文稱集合3），

并對這些標準化數據插入錯誤數據。錯誤數據種類會根據3組數據集合進行選擇，錯誤數據的插入位置隨機選擇。

從圖8的結果可知，本文提出的RENCS能夠兼容3類數據集合的同時，還具有相對較短的處理時間。

相比較于其他的3種工具，本文提出的REMCS系統對于全部3組數據中的錯誤都進行了自動處理或者給出錯誤定位;而Navicat雖然在數據集合2上耗時接近REMCS，但是在數據集合1上因為無法處理的錯誤數據而意外終止;Kettle在數據集合2上也因為無法處理的錯誤導致意外終止，從而缺少結果數據。

在數據集合1中，REMCS、Kettle和Spark stream檢測出全部的200處錯誤，Navicat意外終止是因為無法處理連續出現的數據內出現錯行問題，當錯行問題數量超過600行以上時會出現工具意外終止的現象。在數據集合2中，REMCS、Spark stream和Navicat檢測出全部的50處錯誤，Kettle意外終止是因為出現無法處理數據源中數據過長的問題，當數據長度超過1000B以上時會出現工具意外終止現象。在數據集合3中，REMCS、Kettle、Spark stream和Navicat均檢測出全部的10處錯誤。

3.4 處理極限值對比

為了驗證本文提出的REMCS的處理極限值，這里本文跟其他的3種常見通用預處理工具進行了對比實驗。

為了避免測試數據的數據量對實驗結果的干擾，本文在不同大小的三種數據集合上都進行了多次實驗，并統計了各種算法的處理時間。

本文按照樣本數量從UCI公共數據集合中選擇了3組分類問題數據集合進行對比實驗，如表4所示，包括：SIFT10M（大總量，多字段，下文稱集合4）、HEPMASS（大總量，較少字段，下文稱集合5）和Record Linkage Comparison Patterns（較少總量，較少字段，下文稱集合6），并對這些標準化數據隨機插入錯誤數據，插入的錯誤數據是4種工具都可以處理的簡單錯誤。

本文提出的REMCS對比其他工具在處理大數據量的數據時候也能滿足時間和功能性的要求，對于全部的三種大小的數據集合都能在可接受的時間內處理完畢，并自動處理其中的錯誤或給出定位信息，便于用戶進行手動調整。

4 結語

本項目團隊針對處理大量實驗數據過程中遇見的常見典型問題總結出REMCS。REMCS的應用場景為高度受限的敏感數據中心環境，該環境要求盡可能在現有基礎工具環境下進行敏感數據的處理，對于其他各種成熟工具都需要進行繁瑣的審批和檢查。REMCS提升了本項目團隊處理實驗數據的效率，減輕了數據預處理工作的工作量和工作壓力。本文通過多個對比實驗從不同方面說明了所提REMCS在受限環境下的優良特性。

致謝 非常感謝“核心電子器件在電子信息裝備中的驗證與應用課題”對本項目的大力支持。

參考文獻（References）

[1] KARAGIANNIS A， VASSILIADIS P， SIMITSIS A. Scheduling strategies for efficient ETL execution[J]. Information Systems， 2013， 38（6）： 927-945.

[2] WILKINSON K， SIMITSIS A， CASTELLANOS M， et al. Leveraging business process models for ETL design[C]// Proceedings of the 2010 International Conference on Conceptual Modeling， LNCS 6412. Berlin： Springer， 2010： 15-30.

[3] 馮運輝. 一種基于MySQL的可擴展ETL系統的研究與實現[J]. 電子技術與軟件工程， 2018（5）： 185-187. （FENG Y H. Research and implementation of an extensible ETL system based on MySQL[J]. Electronic Technology and Software Engineering， 2018（5）： 185-187.）

[4] 張敬鋒， 劉瓊， 李磊. Hadoop與ETL技術在視頻數據中的應用[J]. 警察技術， 2018， 170（5）： 29-31. （ZHANG J F， LIU Q， LI L. Application of Hadoop and ETL in video data[J]. Police Technology， 2018， 170（5）： 29-31.）

[5] 謝亞龍. 支付清算業務大數據分析平臺ETL流程設計與實踐[J]. 金融科技時代， 2018， 276（8）： 62-64. （XIE Y L. Design and practice of ETL process for large data analysis platform of payment and settlement business[J]. Financial Technology Time， 2018， 276（8）： 62-64.）

[6] EL AKKAOUI Z， ZIMNYI E， MAZN J， et al. A model-driven framework for ETL process development[C]// Proceedings of the 14th ACM International Workshop on Data Warehousing and OLAP. New York： ACM， 2011： 45-52.

[7] SIMITSIS A， VASSILIADIS P， DAYAl U， et al. Benchmarking ETL workflows[C]// Proceedings of the 2009 Technology Conference on Performance Evaluation and Benchmarking， LNCS 5895. Berlin： Springer， 2009： 199-220.

[8] 徐安令. 正則表達式的應用研究[J]. 數字技術與應用， 2016（5）： 68-68. （XU A L. Research on regular expression application[J]. Digital Technology and Application， 2016（5）： 68-68.）

[9] 陳增鑫， 歐陽林艷， 龔思思， 等. 正則表達式在數據抓取中的應用研究 [J]. 佳木斯職業學院學報， 2017（4）： 408-408. （CHEN Z X， OUYANG L Y， GONG S S， et al. Research on the application of regular expression in data grabbing[J]. Journal of Jiamjusi Vocational Institute， 2017（4）： 408-408.）

[10] 王梓， 梁正和， 吳瑩瑩. 基于Kafka、Disruptor技術對傳統ETL的改進[J]. 計算機技術與發展， 2018， 28（11）： 26-29. （WANG Z， LIANG Z H， WU Y Y. Improvement of traditional ETL based on Kafka and Disruptor technology[J]. Computer Technology and Development， 2018， 28（11）： 26-29.）

[11] 林昆. 面向數據倉庫的ETL工具的研究與實現[J]. 計算技術與自動化， 2018， 37（1）： 136-140. （LIN K. Research and implementation of ETL tool oriented data warehouse[J]. Computing Technology and Automation， 2018， 37（1）： 136-140.）

[12] 張碩， 趙卓峰， 王桂玲， 等. 支持探索式研判分析的動態ETL流程服務[J]. 小型微型計算機系統， 2019， 40（1）： 176-180. （ZHANG S， ZHAO Z F， WANG G L， et al. Dynamic ETL process service to exploratory judgment analysis[J]. Journal of Chinese Computer Systems， 2019， 40（1）： 176-180.）

[13] 許皓皓， 廉亮， 姚浩立. 基于ETL的政務云氣象數據倉庫構建[J]. 計算機系統應用， 2018，27（9）： 224-228. （XU H H， LIAN L， YAO H L. Establishment of meteorological data warehouse based on ETL tools[J]. Computer Systems & Applications， 2018，27（9）： 224-228.）

[14] 付哲， 李軍. 高性能正則表達式匹配算法綜述[J]. 計算機工程與應用， 2018， 54（20）： 6-18. （FU Z， LI J. Survey on high performance regular expression matching algorithms[J]. Computer Engineering and Applications， 2018， 54（20）： 6-18.）

[15] 邵翔宇， 劉勤讓， 孫淼. 基于模板有限自動機的正則表達式匹配算法[J]. 計算機應用研究， 2016， 33（7）： 2139-2142， 2147. （SHAO X Y， LIU Q R， SUN M. Regular expressions matching algorithm based on templates finite automata[J]. Application Research of Computers， 2016， 33（7）： 2139-2142， 2147.）