ETL数据清洗设计

1、ETL 中的数据清洗设计1 1前言ETL即数据抽取(Extract)、转换(Transform)、装 载(Load)的过程。它是构建数据仓库的重要环节。数 据仓库是面向主题的、集成的、稳定的且随时间不断变 化的数据集合，用以支持经营管理中的决策制定过程1。数据仓库主要用于决策分析，为领导者提供决策支持信 息。数据仓库系统中有可能存在着大量的脏数据，引起的 主要原因有：滥用缩写词、惯用语、数据输入错误、重 复记录、丢失值、拼写变化、不同的计量单位和过时的 编码等。即便是一个设计和规划良好的数据仓库系统， 如果其中存在着大量的脏数据，那么这个系统也是没有 任何意义的，因为垃圾进，垃圾出(garba

2、ge in, garbage out),系统根本就不可能为决策分析系统提供任何支持。为了清除脏数据，必须在数据仓库系统中进行数据清洗。数据清洗(data cleansing/data cleaning/data scrubing)是一个减少错误和不一致性、解决对象识别的过程2。目前有很多数据清洗研究2346和ETL研究8910,但 是如何在ETL过程中进行有效的数据清洗，此方面研究 不多。笔者认为这包括三方面的内容：(1) ETL处理方式的选择(2)数据清洗的原理及在ETL中的应用模型(3)数据清洗的具体实现过程。2 2 ETLETL处理方式数据抽取负责完成从数据源找到并取出当前主题 所需的那

3、部分数据， 由于数据仓库中各个主题中的数据 是按照前端应用的需求存放的， 因此需要对抽取出的数 据进行转换以适应前端应用的需要。转换后的数据就可 以装入数据仓库了，数据加载过程定时进行， 并且不同 主题的数据加载任务有各自不同的执行时间表。常见的ETL处理方式可分为以下三种：(1)数据库外部的ETL处理数据库外部的ETL处理方式指的是大多数转换工作 都在数据库之外、在独立的ETL过程中进行。这些独立 的ETL过程与多种数据源协同工作，并将这些数据源集 成。数据库外部ETL处理的优点是执行速度比较快。但 缺点是大多数ETL步骤中的可扩展性必须由数据库的外 部机制提供， 如果外部机制不具备扩展性，

4、那么此ETL处理就不能扩展。(2)数据库段区域中的ETL处理数据库段区域中的ETL处理方式不使用外部引擎 而是使用数据库作为唯一的控制点。 多种数据源的所有 原始数据大部分未作修改就被载入中立的段结构中。 如 果源系统是关系数据库，段表将是典型的关系型表。如 果源系统是非关系型的，数据将被分段置于包含列VARCHAR2(4000)的表中，以便于在数据库内作进一步 转换。成功地将外部未修改数据载入数据库后，再在数 据库内部进行转换。这就是系列方法载入然后转换。数据库段区域中的ETL处理方式执行的步骤是提取、装 载、转换，即通常所说的ELTo在实际数据仓库系统中经常使用这种方式。 这种方式的优点是

5、为抽取出的数据 首先提供一个缓冲以便于进行复杂的转换，减轻了ETL进程的复杂度。但是这种ETL处理的缺点有：(a)在段表 中存贮中间结果和来自数据库中源系统的原始数据时，转换过程将被中断。(b)大多数转换可以使用类SQL的数据库功能来解决，但它们可能不是处理所有的ETL问题的最优语言。(3)数据库中的ETL处理数据库中的ETL处理方式使用数据库作为完整的数 据转换引擎，在转换过程中也不使用段。数据库中的ETL处理具有数据库段区域中的ETL处理的优点，同时又充 分利用了数据库的数据转换引擎功能， 但是这要求数据 库必须完全具有这种转换引擎功能。目前的主流数据库 产品Oracle 9i5等可以提供

6、这种功能。综上分析三种ETL处理方式，数据库外部的ETL处 理可扩展性差，不适合复杂的数据清洗处理，数据库段 区域中的ETL处理可以进行复杂的数据清洗，而数据库 中的ETL处理具有数据库段区域ETL处理的优点，又利 用了数据库的转换引擎功能。所以为了进行有效的数据 清洗，应该使用数据库中的ETL处理。3 3数据清洗的原理及在ETLETL中的应用模型数据清洗的目的是保证数据仓库数据质量。3.1数据质量对于什么是数据质量， 文献3将其定义为数据的一 致性(consistency)、 正确性(correctness)、 完整性(completeness)和最小性(minimality)这4个指标在信

7、息系 统中得到满足的程度。 根据处理的是单数据源还是多数 据源以及问题是模式层的还是实例层的，文献4将数据质量问题分为4类：单数据源模式层问题(如缺少完整性 约束、糟糕的模式设计等)、 单数据源实例层问题(如数 据输入错误)、多数据源模式层问题(如异构数据模型和 模式设计等)、 多数据源实例层问题(如冗余、冲突、不 一致的数据等)。单数据源中出现的问题在多数据源中也有可能出 现，并且这种现象也是普遍发生的。 模式层次上的问题 也会体现在实例层次上。模式层次的问题可以通过改进 模式设计、模式转化和模式集成来解决。 但实例层次的 问题在模式层次上是不可见的。所以数据清洗主要针对 实例层次的数据质量

8、问题。3.2数据清洗原理文献11指出，数据清洗实际就是利用有关技术如数 理统计、 数据挖掘或预定义的数据清洗规则将脏数据转 化成满足数据质量要求的数据。按数据清洗的实现方式 与范围，可将数据清洗分为四种：(1)手工实现方式：用人工来检测所有的错误并改正。 这只能针对小数据量 的数据源。(2)通过专门编写的应用程序：通过编写程 序检测/改正错误。但通常数据清洗是一个反复进行的过 程，这就导致清理程序复杂、系统工作量大。(3)某类特定应用领域的问题， 如根据概率统计学原理查找数值 异常的记录。(4)与特定应用领域无关的数据清洗，这 一部分的研究主要集中于重复记录的检测/删除。3.3数据清洗在ETL

9、中的应用模型在数据仓库系统中，数据清洗是ETL过程中的一个重要环节，主要任务是检测并删除/改正将装入数据仓库 的脏数据。由于数据仓库的多种异构数据源和海量数据，数据清洗应是与领域无关的。而且数据清洗不是ETL中一个单独的步骤，需要与数据抽取、数据转换集成，与 数据载入统一使用，需要进行循环处理。如果数据源是一个能力比较强的DBMS(如图1中的数据源1和数据源2),则可以在数据抽取过程中使用SQL来完成一部分的 数据清洗工作。但是有一些数据源不提供这种能力(如 数据源3),则只能直接将数据从数据源抽取出来，然后在数据转换的时候进行清洗。 数据仓库中的数据清洗主 要还是在数据转换的时候进行的。使用

10、数据库ETL处理方式中的DBMS的转换清洗能力完成大部分的工作，这 样数据清洗就充分利用了DBMS提供的功能。4 4数据清洗的具体实现过程不同的系统可以采用不同的数据清洗方法用Northwind数据源为例来进行数据清洗。SQL SERVER 2000自带的一个关于贸易公司的数据库。 表Customer记录了公司的客户信息，共有11个字段：CustomerID CompanyName ContactNameContactTitle Address City RegionPostalCode Country Phone Fax。我们以这个表为基础，来加入一些脏数据形成新表：Customernew。

11、数据清洗 分为以下几步：(1)元素化将非标准的数据格式化成结构数据。以数据库表Customernew的任意一条记录为例。Address City和Phone三个字段值分别为：Address:Obere Stre. 57City:BerlinPhone素化为：Address(1):ObereAddress(2):StreAddress(3):57City:BerlinPhone(1):031Phone (2):0074321(2)标准化将元素标准化，根据字典消除不一致的缩写。这里的字典是用户通过分析样本表预先制定的一些规则的集合。样本表是根据概率统计从表Customer

12、new选择一部分记录所形成的一个表。首先分析Address(2)的值Stre， 此值应是Street的缩写。 而在字典中Street的 缩写是Str,所以在这里，Stre被认为是脏数据，正 确的数据应为：Str。(3)校验对标准化的元素进行一致性校验，即在内容上修改 错误。Berlin的电话区号应为：030,所以Phone(1)的值应(6)归档将前5个步骤的结果写入元数据存储中心。这样可以 更好地进行后续的清洗过程，而且可以更加容易理解数 据源以便在数据仓库中进行切片、切块等操作。在上面的数据清洗方法中，我们找出的只是完全相 同的记录。对于一些相似的记录，它们也有可能代表的 是同一条记录，找出

13、这些相似重复记录对于这个贸易公 司有很重要的意义，例如，贸易公司的一个客户在表Customernew中有可能有多条记录。当贸易公司邮寄广 告时，他就有可能将多个广告邮寄给同一个客户。这样 就浪费了大量的资金。这只是一个很简单的广告例子， 但是由此我们也可以看出， 检测/删除相似重复记录可以 提高贸易公司的经济效益。我们可以采用下面的一种方 法或者结合使用这三种方法来检测相似重复记录：基于契比雪夫定理的统计学方法：这种方法 可以随机选取样本数据进行分析，加快了检 测速度，但是这是以牺牲准确性为代价的。模式识别的方法：基于数据挖掘和机器学习 算法来查找数据，主要牵涉关联规则算法。基于距离的聚类方法： 这也是数据挖掘中的 算法，重点在于它的类的评测标准为欧几里 德距离或者Edit距离，通过这种距离来发现数 据集中的重复记录。5 5结论数据清洗属于一个较新的研究领域，如何将数据清 洗有效地运用到ETL以提高数据仓库数据质量， 本文给 出了待解图图 1 款振清洗在款振清洗在 ETL 中的应用模型中的应用模型为：030o(4)匹配在其它记录中寻找相似的记录，发现重复记录。对整个表Customernew排序，然后在表中查找Address(1)值为Obere勺所有记录，如果有这样的记 录再去查找Address(2),依次类推，如果所有字段 的数