ETL中的数据清洗设计

ETL中的数据清洗设计1前言ETL即数据抽取（Extract）、转换（Transform）、装载（Load）的过程。它是构建数据仓库的重要环节。数据仓库是面向主题的、集成的、稳定的且随时间不断变化的数据集合，用以支持经营管理中的决策制定过程[1]。数据仓库主要用于决策分析，为领导者提供决策支持信息。数据仓库系统中有可能存在着大量的脏数据,引起的主要原因有：滥用缩写词、惯用语、数据输入错误、重复记录、丢失值、拼写变化、不同的计量单位和过时的编码等。即便是一个设计和规划良好的数据仓库系统，如果其中存在着大量的脏数据，那么这个系统也是没有任何意义的，因为“垃圾进，垃圾出”（garbagein,garbageout），系统根本就不可能为决策分析系统提供任何支持。为了清除脏数据，必须在数据仓库系统中进行数据清洗。数据清洗（datacleansing/datacleaning/datascrubing）是一个减少错误和不一致性、解决对象识别的过程[2]。目前有很多数据清洗研究[2][3][4][6]和ETL研究[8][9][10]，但是如何在ETL过程中进行有效的数据清洗，此方面研究不多。笔者认为这包括三方面的内容：（1）ETL处理方式的选择（2）数据清洗的原理及在ETL中的应用模型（3）数据清洗的具体实现过程。2ETL处理方式数据抽取负责完成从数据源找到并取出当前主题所需的那部分数据，由于数据仓库中各个主题中的数据是按照前端应用的需求存放的，因此需要对抽取出的数据进行转换以适应前端应用的需要。转换后的数据就可以装入数据仓库了，数据加载过程定时进行，并且不同主题的数据加载任务有各自不同的执行时间表。常见的ETL处理方式可分为以下三种：（1）数据库外部的ETL处理数据库外部的ETL处理方式指的是大多数转换工作都在数据库之外、在独立的ETL过程中进行。这些独立的ETL过程与多种数据源协同工作，并将这些数据源集成。数据库外部ETL处理的优点是执行速度比较快。但缺点是大多数ETL步骤中的可扩展性必须由数据库的外部机制提供，如果外部机制不具备扩展性，那么此ETL处理就不能扩展。（2）数据库段区域中的ETL处理数据库段区域中的ETL处理方式不使用外部引擎而是使用数据库作为唯一的控制点。多种数据源的所有原始数据大部分未作修改就被载入中立的段结构中。如果源系统是关系数据库，段表将是典型的关系型表。如果源系统是非关系型的，数据将被分段置于包含列VARCHAR2(4000)的表中，以便于在数据库内作进一步转换。成功地将外部未修改数据载入数据库后，再在数据库内部进行转换。这就是系列方法载入然后转换。数据库段区域中的ETL处理方式执行的步骤是提取、装载、转换，即通常所说的ELT。在实际数据仓库系统中经常使用这种方式。这种方式的优点是为抽取出的数据首先提供一个缓冲以便于进行复杂的转换，减轻了ETL进程的复杂度。但是这种ETL处理的缺点有：(a)在段表中存贮中间结果和来自数据库中源系统的原始数据时，转换过程将被中断。(b)大多数转换可以使用类SQL的数据库功能来解决，但它们可能不是处理所有的ETL问题的最优语言。（3）数据库中的ETL处理数据库中的ETL处理方式使用数据库作为完整的数据转换引擎，在转换过程中也不使用段。数据库中的ETL处理具有数据库段区域中的ETL处理的优点，同时又充分利用了数据库的数据转换引擎功能，但是这要求数据库必须完全具有这种转换引擎功能。目前的主流数据库产品Oracle9i[5]等可以提供这种功能。综上分析三种ETL处理方式，数据库外部的ETL处理可扩展性差，不适合复杂的数据清洗处理，数据库段区域中的ETL处理可以进行复杂的数据清洗，而数据库中的ETL处理具有数据库段区域ETL处理的优点，又利用了数据库的转换引擎功能。所以为了进行有效的数据清洗，应该使用数据库中的ETL处理。3数据清洗的原理及在ETL中的应用模型数据清洗的目的是保证数据仓库数据质量。3.1数据质量对于什么是数据质量，文献[3]将其定义为数据的一致性（consistency）、正确性(correctness)、完整性(completeness)和最小性(minimality)这4个指标在信息系统中得到满足的程度。根据处理的是单数据源还是多数据源以及问题是模式层的还是实例层的，文献[4]将数据质量问题分为4类：单数据源模式层问题（如缺少完整性约束、糟糕的模式设计等）、单数据源实例层问题（如数据输入错误）、多数据源模式层问题（如异构数据模型和模式设计等）、多数据源实例层问题（如冗余、冲突、不一致的数据等）。单数据源中出现的问题在多数据源中也有可能出现，并且这种现象也是普遍发生的。模式层次上的问题也会体现在实例层次上。模式层次的问题可以通过改进模式设计、模式转化和模式集成来解决。但实例层次的问题在模式层次上是不可见的。所以数据清洗主要针对实例层次的数据质量问题。3.2数据清洗原理文献[11]指出，数据清洗实际就是利用有关技术如数理统计、数据挖掘或预定义的数据清洗规则将脏数据转化成满足数据质量要求的数据。按数据清洗的实现方式与范围，可将数据清洗分为四种：（1）手工实现方式：用人工来检测所有的错误并改正。这只能针对小数据量的数据源。（2）通过专门编写的应用程序：通过编写程序检测/改正错误。但通常数据清洗是一个反复进行的过程，这就导致清理程序复杂、系统工作量大。（3）某类特定应用领域的问题，如根据概率统计学原理查找数值异常的记录。（4）与特定应用领域无关的数据清洗，这一部分的研究主要集中于重复记录的检测/删除。3.3数据清洗在ETL中的应用模型在数据仓库系统中，数据清洗是ETL过程中的一个重要环节,主要任务是检测并删除/改正将装入数据仓库的脏数据。由于数据仓库的多种异构数据源和海量数据，数据清洗应是与领域无关的。而且数据清洗不是ETL中一个单独的步骤，需要与数据抽取、数据转换集成，与数据载入统一使用，需要进行循环处理。如果数据源是一个能力比较强的DBMS（如图1中的数据源1和数据源2），则可以在数据抽取过程中使用SQL来完成一部分的数据清洗工作。但是有一些数据源不提供这种能力（如数据源3），则只能直接将数据从数据源抽取出来，然后在数据转换的时候进行清洗。数据仓库中的数据清洗主要还是在数据转换的时候进行的。使用数据库ETL处理方式中的DBMS的转换清洗能力完成大部分的工作，这样数据清洗就充分利用了DBMS提供的功能。4数据清洗的具体实现过程不同的系统可以采用不同的数据清洗方法[4]。我们用Northwind数据源为例来进行数据清洗。Northwind是SQLSERVER2000自带的一个关于贸易公司的数据库。表Customer记录了公司的客户信息，共有11个字段：CustomerIDCompanyNameContactNameContactTitleAddressCityRegionPostalCodeCountryPhoneFax。我们以这个表为基础，编写程序来加入一些脏数据形成新表：Customernew。数据清洗分为以下几步：（1）元素化将非标准的数据格式化成结构数据。以数据库表Customernew的任意一条记录为例。Address、City和Phone三个字段值分别为：Address:ObereStre.57City:BerlinPhone:030-0074321元素化为：Address(1):ObereAddress(2):StreAddress(3):57City:BerlinPhone(1):031Phone(2):0074321（2）标准化将元素标准化，根据字典消除不一致的缩写。这里的字典是用户通过分析样本表预先制定的一些规则的集合。样本表是根据概率统计从表Customernew选择一部分记录所形成的一个表。首先分析Address(2)的值Stre，此值应是Street的缩写。而在字典中Street的缩写是Str，所以在这里，Stre被认为是脏数据，正确的数据应为：Str。（3）校验对标准化的元素进行一致性校验，即在内容上修改错误。Berlin的电话区号应为：030，所以Phone(1)的值应为：030。（4）匹配在其它记录中寻找相似的记录，发现重复记录。对整个表Customernew排序，然后在表中查找Address(1)值为Obere的所有记录，如果有这样的记录再去查找Address(2)，依次类推，如果所有字段的数据全都相同，那么这两条记录就是重复记录。（5）消除重复记录根据匹配结果进行处理，删除部分记录或者合并多个记录为一个完整信息的记录。（6）归档将前5个步骤的结果写入元数据存储中心。这样可以更好地进行后续的清洗过程，而且可以更加容易理解数据源以便在数据仓库中进行切片、切块等操作。在上面的数据清洗方法中，我们找出的只是完全相同的记录。对于一些相似的记录，它们也有可能代表的是同一条记录，找出这些相似重复记录对于这个贸易公司有很重要的意义，例如，贸易公司的一个客户在表Customernew中有可能有多条记录。当贸易公司邮寄广告时，他就有可能将多个广告邮寄给同一个客户。这样就浪费了大量的资金。这只是一个很简单的广告例子，但是由此我们也可以看出，检测/删除相似重复记录可以提高贸易公司的经济效益。我们可以采用下面的一种方法或者结合使用这三种方法来检测相似重复记录：基于契比雪夫定理的统计学方法：这种方法可以随机选取样本数据进行分析，加快了检测速度，但是这是以牺牲准确性为代价的。模式识别的方法：基于数据挖掘和机器学习算法来查找数据，主要牵涉关联规则算法。基于距离的聚类方法：这也是数据挖掘中的算法，重点在于它的类的评测标准为欧几里德距离或者Edit距离，通过这种距离来发现数据集中的重复记录。5结论数据清洗属于一个较新的研究领域，如何将数据清洗有效地运用到ETL以提高数据仓库数据质量，本文给出了待解决问题的设计方案。但是目前很多人认为数据清洗是针对特定应用领域的问题，是一个单纯浪费大量劳动的问题。其实在脏数据的检测/删除的精度方面以及人机交互设计等方面都有大量的可研究问题。本文给出了一个数据清洗在ETL中的应用模型，并通过例子说明了数据清洗的具体实现过程。此方法的完善还有待于进一步的研究。