基于正则表达式的海量数据清洗系统

doi:10.11772/j.issn.1001-9081.2019030492

计算机应用 ›› 2019, Vol. 39 ›› Issue (10): 2942-2947.DOI: 10.11772/j.issn.1001-9081.2019030492

基于正则表达式的海量数据清洗系统

常征, 吕勇

中国电子科技集团公司电子科学研究院, 北京 100086

收稿日期:2019-03-25 修回日期:2019-06-14 发布日期:2019-07-15 出版日期:2019-10-10
通讯作者: 常征
作者简介:常征(1986-),男,重庆人,工程师,博士,主要研究方向:模式识别、软件工程、数据库;吕勇(1981-),男,辽宁沈阳人,工程师,硕士,主要研究方向:计算机运维、系统工程。
基金资助:
国家科技重大专项（2017ZX01013201）（2017ZX01013201）。

Mass data clean system based on regular expression

CHANG Zheng, LYU Yong

China Academy of Electronics and Information Technology, Beijing 100086, China

Received:2019-03-25 Revised:2019-06-14 Online:2019-07-15 Published:2019-10-10
Supported by:
This work is partially supported by the National Science and Technology Major Project (2017ZX01013201).

摘要/Abstract

摘要： 针对目前主流的数据提取、变形、加载（ETL）工具和受限环境下一些应用的不足之处，结合受限应用场景下的特殊要求，提出一种基于正则表达式的海量数据清洗系统（REMCS）。REMCS首先针对超长错误数据问题、批量数据源文件融合问题、数据源文件自动分拣问题等典型的6个问题找到数据的特点，其次根据数据的特点设置合适的正则表达式和预处理算法，然后使用算法模型去除数据中的错误完成数据预处理工作。同时详细阐述了REMCS的系统逻辑结构、常见问题、对应的解决算法和代码实现方案。最后通过对兼容的数据源文件格式、能够处理的问题种类、问题处理时间、处理数据极限值等4个方面进行对比，从几组常见的数据处理问题的对比实验可知，相较于传统的ETL工具，REMCS支持csv格式、json格式、dump格式等典型的9种文件格式，能够处理全部的6种常见问题，处理时间更短，能够支持的数据极限值更大。实验结果验证了针对受限应用场景下常见的数据处理问题，REMCS具有很好的适用性和准确性。

关键词: 正则表达式, 数据清洗, 大数据, 提取、变形、加载工具

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 as csv 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

中图分类号:

TP391

常征, 吕勇. 基于正则表达式的海量数据清洗系统[J]. 计算机应用, 2019, 39(10): 2942-2947.

CHANG Zheng, LYU Yong. Mass data clean system based on regular expression[J]. Journal of Computer Applications, 2019, 39(10): 2942-2947.

参考文献

[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, ZIMÀNYI E, MAZÍN 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.)

基于正则表达式的海量数据清洗系统

Mass data clean system based on regular expression

PDF

可视化

摘要/Abstract

引用本文

使用本文

参考文献

相关文章 15

编辑推荐

Metrics

[1]	周翔, 翟俊海, 黄雅婕, 申瑞彩, 侯璎真. 基于随机森林和投票机制的大数据样例选择算法[J]. 计算机应用, 2021, 41(1): 74-80.
[2]	曹策俊, 刘桔. 灾害运作管理中应急组织决策建模方法综述[J]. 计算机应用, 2020, 40(7): 2142-2149.
[3]	朱小杰, 赵子豪, 杜一. 模型驱动的大数据流水线框架PiFlow[J]. 计算机应用, 2020, 40(6): 1638-1647.
[4]	吴文莉, 刘国华, 张君宝. 大数据上函数查询解答的复杂度分析[J]. 《计算机应用》唯一官方网站, 2020, 40(2): 416-419.
[5]	李孜颖, 石振国. 面向大数据任务的调度方法[J]. 计算机应用, 2020, 40(10): 2923-2928.
[6]	章永来, 周耀鉴. 聚类算法综述[J]. 计算机应用, 2019, 39(7): 1869-1882.
[7]	马建刚, 马应龙. 语义驱动的司法文档学习分类方法[J]. 计算机应用, 2019, 39(6): 1696-1700.
[8]	纪丽娜, 陈凯, 于彦伟, 宋鹏, 王淑莹, 王成锐. 基于城市交通大数据的车辆类别挖掘及应用分析[J]. 计算机应用, 2019, 39(5): 1343-1350.
[9]	张译天, 于炯, 鲁亮, 李梓杨. 大数据流式计算框架Heron环境下的流分类任务调度策略[J]. 计算机应用, 2019, 39(4): 1106-1116.
[10]	徐垚, 李卓然, 孟金龙, 赵利坡, 温建新, 王桂玲. 基于大规模船舶轨迹数据的航道边界提取方法[J]. 计算机应用, 2019, 39(1): 105-112.
[11]	王雪菲, 丁维龙. 面向高速公路大数据的短时流量预测方法[J]. 计算机应用, 2019, 39(1): 87-92.
[12]	唐小川, 罗亮. 基于析因设计的大数据相关关系挖掘算法[J]. 计算机应用, 2018, 38(9): 2507-2510.
[13]	陈镜任, 吴业福, 吴冰. 基于车辆行驶数据的驾驶人行为谱分析方法[J]. 计算机应用, 2018, 38(7): 1916-1922.
[14]	潘鸣宇, 张禄, 龙国标, 李香龙, 马冬雪, 徐亮. 用于重复充电运营记录的基于块采样的高效聚集查询算法[J]. 计算机应用, 2018, 38(6): 1596-1600.
[15]	吴仁彪, 刘超, 屈景怡. 基于HBase和Hive的航班延误平台的存储方法[J]. 计算机应用, 2018, 38(5): 1339-1345.