ETL中的数据清洗设计

ETL中的数据清洗设计

1 前言

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

数据清洗（data cleansing/data cleaning/data scrubing）是一个减少错误和不一致性、解决对象识别的过程[2]。目前有很多数据清洗研究[2][3][4][6]和ETL研究[8][9][10]，但是如何在ETL过程中进行有效的数据清洗，此方面研究不多。笔者认为这包括三方面的内容：（1）ETL处理方式的选择（2）数据清洗的原理及在ETL中的应用模型（3）数据清洗的具体实现过程。 2 ETL处理方式

数据抽取负责完成从数据源找到并取出当前主题所需的那部分数据，由于数据仓库中各个主题中的数据是按照前端应用的需求存放的，因此需要对抽取出的数据进行转换以适应前端应用的需要。转换后的数据就可以装入数据仓库了，数据加载过程定时进行，并且不同主题的数据加载任务有各自不同的执行时间表。

常见的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处理的优点，同时又充分利用了数据库的数据转换引擎功能，但是这要求数据库必须完全具有这种转换引擎功能。目前的主流数据库产品Oracle 9i[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是SQL SERVER 2000自带的一个关于贸易公司的数据库。表Customer记录了公司的客户信息，共有11个字段：CustomerID CompanyName ContactName ContactTitle Address City Region PostalCode Country Phone Fax。我们以这个表为基础，编写程序来加入一些脏数据形成新表：Customernew。数据清洗分为以下几步：（1）元素化

将非标准的数据格式化成结构数据。

以数据库表Customernew的任意一条记录为例。Address、City和Phone三个字段值分别为：Address:Obere Stre. 57 City:Berlin

Phone:030-0074321 元素化为：

Address(1):Obere Address(2):Stre Address(3):57 City:Berlin