实验目的

数据清洗，是整个数据分析过程中不可缺少的一个环节，其结果质量直接关系到模型效果和最终结论。在实际操作中，数据清洗通常会占据分析过程的 50%—80%的时间。 数据清洗的目的从两个角度来讲： 一、是为了解决数据质量问题 二、是让数据更适合做挖掘、展示、分析 ** 本案例所有例子都是基于 sakila 数据库的 customer 和 address 表，只是把数据弄乱了一些，这样数据清洗步骤就可以做一些工作了，即在原始数据上面增加一些重复数据，再拼写错一些名字，再增加一些额外数据行。可以使用 sakilamods.sql 脚本来做这些事情，这个脚本可以从附件下载，当然也可以自己把数据改成自己想要的样子。我们后面也描述了清洗转换例子所要求的数据格式，可以按照要求自己构造数据。

本案例很大一部分内容是数据验证，因为在清洗数据之前，必须明确哪些数据应该被清洗掉以及验证数据的条件。数据剖析是理解数据质量和组成的第一步。数据剖析的结果可以被用来构建数据清洗和验证步骤的条件。案例讲述的主题非常重要。因为据 2003 年统计，数据仓库学院（DataWarehouse Institute，TDWI）估计数据质量问题每年会消耗 6000 亿美元或更多。

附件脚本：sakilamods.sql

实验原理

对于大多数用户来说，ETL 的核心价值在 T 部分，T 代表转换（Transform）。但实际上，这个阶段发生的很多工作，并不是一个 T 就能代替的。Ralph Kimball 把 ETL 称为 ECCD，就是 Extract、Cleanse、Conform 和 Deliver，考虑到了这个单词有两个 C，这两个 C 分别代表了清洗（Cleanse）和校验（Conform）。 ETL 数据清洗过程通常希望修正脏数据，且提供清洗数据、获取数据质量事件，以及度量并最终控制数据质量的全面结构。要实现这些工作，需要合并且集成从多个源输入的数据，因此需要其数据源具有相同的结构、重复数据删除，过滤掉无效数据、标准化等特点。**

Sakila 介绍

Sakila 是 MySQL 中的一个示例数据库（sample database），提供了一个标准的方案，可用于自学，写书，教程，文章以及示例等。详细介绍请参考 Sakila 数据集介绍 。关于 Sakila 数据库读者亦可参考相关的官方文档。

数据清洗

数据清洗是使用 ETL 工具最重要的原因之一。另一方面关于数据清洗工作也有很多争论，如应该在 ETL 过程里做清洗工作，还是应该在数据仓库里做清洗工作。在我们在介绍什么是数据清洗之前，先看看关于数据清洗的几个问题。 数据清洗是数据质量这一更大主题的一部分，而数据质量又是数据管理这一主题的一部分。具有争议的是，数据质量问题应该在根源处解决，也就是事物型的业务系统里解决。如果这些数据在源系统里不能被清洗掉，那只能在它上一级，加载到数据仓库之前清洗掉，这样带来的问题就是数据仓库里的数据和源系统里的数据不一样。DataVault 模型的倡导者（2.8Data Vault 管理案例）在这个问题上有不同观点：他们认为，数据应该按照原样保存在数据仓库里，只有在移动到数据集市里，按照要求再做清洗。这样在加载时刻的数据都原封不动保存下来，便于以后追踪。ETL 开发人员的工作量没有减少，只是放到了数据处理环节的下游；因为每个数据集市是单一的实体，所以 ETL 开发人员的工作量可能还会增加。所以设计可复用的数据清洗转换是 ETL 开发过程中的一个重要部分。

UDI 提供了很多步骤帮助你完成数据清洗工作，无论从源系统中抽取数据清洗，还是从数据仓库中抽取数据清洗。

注意：Arkady Maydanchik 提出了如下的数据质量的五个规则。

• 属性值域约束：每个属性的取值范围域的约束。
• 关系完整性规则：检查数据的唯一性和参照性约束，这种约束可以从关系数据模型得到。
• 历史数据规则：对时间相关的数据，要进行时间轴的约束，值的模式匹配。
• 对象的状态依赖规则：检验数据是否满足所谓的状态机模型的约束。
• 一般依赖规则：描述复杂的属性的关系，包括冗余、派生、部分依赖和相关性的属性。

数据校验

数据校验是在数据抽取、清洗转换流程完成之后，通过一系列的校验规则，定位到错误数据，并将异常数据推送给下游处理，最终提升数据质量的环节。 数据校验是数据质量的重要保障，目前业内认可的数据质量的标准有：

• 准确性: 描述数据是否与其对应的客观实体的特征相一致；
• 完整性: 描述数据是否存在缺失记录或缺失字段；
• 一致性: 描述同一实体的同一属性的值在不同的系统是否一致；
• 有效性: 描述数据是否满足用户定义的条件或在一定的域值范围内；
• 唯一性: 描述数据是否存在重复记录；
• 及时性: 描述数据的产生和供应是否及时；
• 稳定性: 描述数据的波动是否是稳定的，是否在其有效范围内。

以上列出的数据质量标准只是一些通用的规则，这些标准是可以根据数据的实际情况和业务要求进行扩展。 由数据质量的标准，对应通用的校验规则有：

• 单字段校验：通过单一字段的约束条件进行校验，包含不为空、比较运算、包含、不包含、取值范围（区间）、取值范围（枚举）、字段长度、字段类型、正则表达式等规则，可用于校验数据的准确性、完整性等；
• 勾稽关系校验：校验字段通常和其他字段做勾稽对比，利用字段平衡关系（衍生计算）来进行校验；
• 唯一性校验：针对单一字段或者多个字段组合后做唯一性约束校验，通过重复记录行或其他违反唯一性约束属性值进行校验；
• 关联性校验：针对字段的关联关系校验，通过引入其他关联字段验证字段的存在和缺失进行校验；
• 记录行统计型校验：针对某个字段的记录行总数做校验，通过统计记录行数量与合理阈值范围比较来进行校验；
• 多源对比校验：针对多个数据源的数据进行对比校验，通过关联字段和对比字段的对比，以校验通过率高或匹配率高的数据知晓哪个数据源的数据质量高。

实验步骤

本章主要讲解关于数据清洗和验证的三个子系统，这三个子系统分别如下。

• 数据清洗子系统
• 错误处理子系统
• 数据排重子系统

数据清洗

数据清洗的步骤大体可以分为这么几步： 预处理阶段：将数据导入处理工具。也就是我们数据的抽取过程，将数据导入到处理工具中。 看数据。这里包含两个部分：一个是看元数据，包括字段解释、数据来源、代码表等等一切描述数据的信息；再有是抽取一部分数据，使用人工查看方式，对数据本身有一个直观的了解，并且初步发现一些问题，为之后的处理做准备 分析处理阶段： 缺失值清洗：去除不需要的字段：对某些字段内容，数据分析过程可能用不上，就需要将其删除，直接 delete，建议清洗的每一步都做备份，防止错删。 填充缺失内容:该字段需要使用，但是某些数据上该字段缺失，这时候需要我们给上默认值，或者计算结果（均值、中位数、众数等）填充上去 重新取数：如果某些字段数据非常重要，但是数据缺失又很严重，这时候就需要我们通过其他渠道重新获取数据将数据填充进去。 格式内容清洗：时间日期、数值、全半角显示不一致等：这种问题通常与输入端有关，在整合多来源数据时也有可能遇到，将其处理成一致的某种格式即可。 内容中有不该存在的字符：某些内容可能只包括一部分字符，比如身份证号是数字+字母。最典型的就是头、尾、中间的空格，也可能出现姓名中存在数字符号、身份证号中出现汉字等问题。这种情况下，需要以半自动校验半人工方式来找出可能存在的问题，并去除不需要的字符。 内容与该字段应有内容不符：一般数据获取时会有前端校验，但是也不乏这些问题会出现，此时需要我们再作处理，非重要字段可以去除掉，重要字段需要我们在做缺失值处理，处理方式就是上面提到的缺失值清洗。 逻辑错误清洗: 去重:建议把去重放在格式内容清洗之后，原因举例：多个空格导致工具认为“吴亦凡”和“吴 亦凡”不是一个人，去重失败。而且，并不是所有的重复都能这么简单的去掉。 去除不合理值：有人填表时候瞎填，年龄 200 岁，年收入 100000 万（估计是没看见”万“字），这种的就要么删掉，要么按缺失值处理。 修正属性依赖冲突：某些字段之间是可以相互验证的，比如北京市和北京市的邮政编码，你的身份证号和你的年龄，籍贯等这时候需要我们指定一个可靠的字段，然后去除或者重构不可靠的字段。比如：身份证号字段上显示你是 20 岁，但是年龄字段上 28 岁，这是我们就以身份证上的信息为可靠字段，将年龄字段改为 20 岁。 非需求数据清洗：很简单就是把不需要的字段删除（勤备份） 校验： 数据格式校验：验证数据格式是不是都符合标准化格式。 关联性校验：有时我们的数据来源可能有多个渠道，如果多个渠道获取的同一信息出现不一致的情况，需要我们重新判断调整数据值，或者去除数据。 数据清洗过程中出现了很多判断问题，一条数据是不是合乎标准，是否存在错误，我们可以通过相应算法去实现。关于数据清洗的大致介绍到这，数据清洗完成过后，就进入了数据转换过程。

参照表清洗

在很多场合下，如更正地址信息等，需要访问外部的主数据或参照数据（或字典表）。基础数据类的应用一般都使用这些数据，如显示出标准的国家名和州名。但很多其他类的应用，数据并不规范。例如，你有一个客服系统，只能通过文本输入的方式输入产品信息。为了清理这些数据，就需要把文本方式输入的产品名称和主数据里的产品名称进行匹配，主数据里的产品名称是最完整和标准的。最常用的使用主数据的场景就是做地址更正。如果地址主数据是免费的，那你很幸运；通常这些地址主数据都是使用订阅方式才能得到的，有一些公司维护和销售这些地址主数据。

参照表有很多用途，一个最常见的用途就是做数据的查询和检验。提供一个输入字段，如果输入字段里的值没有匹配上，就给对应的数据行做一个错误标志。因为只需要找出错误的数据，所以返回的数据都是有限的错误数据。为了提高成功查询的比例，在做查询之前，最好先使用其他步骤清洗数据。我们使用城市和邮政编码查询做个例子。几乎所有国家都有邮政编码体系，邮政编码连接到区域、城市或特定的街道（如荷兰）。德国的邮政编码体系是世界上最精准的，通过邮政编码和门牌号就能定位到一个唯一的地址。但人们输入邮政编码时，有时会犯错误，所以要检验邮政编码和地址是否匹配。下面的例子演示了如何使用“Lookup Step”步骤来判断地址和邮政编码是否匹配，具体操作如下： (1)、新建转换，首先需要一些输入数据，将**“自定义常量数据”**拖至画布，并添加一些数据。双击组件，步骤名称填入“SourceData”，在“元数据”中先定义【ID】、【Name】、【Address】、【PostalCode】和【City】五列并选择好数据类型，如下图所示：

(2)、选择“数据”标签，输入如下图所示的三条数据：

(3)、现在第一个清洗步骤就是从邮政编码里提取数字，要使用计算器。将**“计算器”**组件拖至画布，双击组件，步骤名称填入“getPC4”，在计算里选择“Return only digits from string A”，把【PostaCode】作为字段 A，新增加一个字段保存这些数字，字段名使用像【PC4】这样有业务含义的字段名，具体配置如下图：

(4)、再使用自定义常量数据步骤来模拟一个查询的参照表，将**“自定义常量数据”**拖至画布，双击组件，步骤名称填入“PC4”，插入如下图所示字段：

(5)、选择“数据”标签，输入如下图所示数据：

(6)、根据【PC4】字段里的四位数字，再使用流查询步骤从参照表中查询城市名称。将**“流查询”拖至画布，并且把“PC4”和“getPC4”步骤都连接到“流查询”**组件，双击组件，按下图配置：

“流查询”选择从参照表也就是PC4步骤中查询数据，**“流查询”**步骤会根据设置的关键字段在参照表中查询对应的值（【City】字段）并将结果返回到新增的字段【RefCity】中去，在现实场景中，可能要查询的数据在参照表中没有。为了后面再处理这些没有查询到的数据，建议设置一个容易识别的默认值。（这里设置的默认值的前缀和后缀都是***，这样设置有两个目的：首先，检查数据的时候比较容易找到这些异常数据；其次，查询后在模糊匹配原始输入的城市名时，这个默认值不会和原来的任何城市名有相似度。）

(7)、接下来进行的模糊匹配的目的主要是为了检查一些拼写或完整性错误，需要再使用另一个计算器步骤。将**“计算器”**组件拖至画布，双击组件，步骤名称为“Compare Cities”，把【City】和【RefCity】作为字段 A 和字段 B，使用 Jaro-Winkler 匹配算法，计算选择“JaroWinkler similitude between String A and String B”，新生成的字段命名为【cityscore】，具体配置如下：

(8)、将空操作（什么也不做）组件拖至画布，并按顺序连接。若提示选择步骤，依然选择“主输出步骤”。完整转换如下图所示：

(9)、运行转换，结果如下图所示：

(10)、选中**空操作（什么也不做）组件，右击并选中“预览”**查看数据。结果如下图所示：

可以观察到第一条记录原始数据和参照数据的相似度非常高，所以这里可能有一些拼写错误。第二条记录是完全正确的。第三条记录看上去有一些问题：城市名称完全不同，另外相似度非常低（一般低于 0.8 就要怀疑了）。但从数据里并不能判断出错误在哪里：是邮政编码对了城市名错了？还是城市名对了邮政编码错了？为了得到结论，还需要做一次相反的校验，“相反”校验是指根据城市名称再去参照表里找邮政编码，然后再和原始数据的邮政编码比较，如果邮政编码非常近似（如返回的邮政编码是 5556），就可以得出结论，是邮政编码拼写错误。

当然很多应用中，在用户输入地址、邮政编码和城市名时，都会自动检查它们之间的组合是否正确。但大部分企业都是在它们内部的应用里做这些检查，而面向客户的网站一般允许很多不一致的数据，这样把不同来源的数据整合到一起就比较困难了。

有一种参照表叫数据确认主表，性别编码就是这种参照表的例子。有的系统使用字母 M、F 和 U，分别代码男、女、未知，有的系统使用 NULL 来代表未知的性别，有的系统使用 Male 和 Female 代表男， 女，而有的系统则使用完全不同的编码，如 0（男）、1（女）或 0（未知），1（男）、2（女），等等。还有更复杂的情况，有的系统使用 C 代表儿童，使用 F 代表父亲，M 代表母亲，各种变化和组合都有可能。要把从这些来源的数据整合到一起，要有一套统一的编码规范，然后把已有的编码映射到规范的编码上。使用单一的查询表比每个系统都有一个查询表要更好，便于维护。 这里要满足两个基本的需求：

• 源系统中的每个可能的值都需要映射。
• 要映射到唯一的一组值。

基于上述性别的例子，接下来将介绍把数据整合到一起成统一的编码规范，具体操作如下： (1)、新建转换，将**“CSV 文件输入”**组件拖至画布，双击组件，点击“浏览文件”上传，更改“列分隔符”，并在底下空白处右击后选择“获取字段”以此获取到所有字段信息，具体配置如下图：

(2)、将**“字段选择”**组件拖至画布，双击组件，步骤名称为“字段选择”，并点击“选择和修改”标签，在空白处右击后选择“获取选择的字段”，此时将显示所有的字段信息，删除不需要的字段：

(3)、将**“表输入”**组件拖至画布，输入如下图所示的 Sql 语句，提取参照表中【area_code】和【l_area_name】数据，【area_code】字段是要获取的标准数据：

(4)、根据【src_code】字段里的数据，再使用流查询步骤从“表输入”提取的数据中查询信息。将流查询控件拖至画布，并把“表输入”和“字段选择”步骤都连接到**“流查询”**步骤，双击组件，按下图配置：

(5)、将**“过滤记录”**拖至画布，过滤掉没有查询到的area_code，配置如下图所示：

(6)、将空操作（什么也不做）组件拖至画布，并按顺序连接。若提示选择步骤，依然选择“True输出”。完整转换如下图所示：

(7)、运行转换，结果如下图所示：

数据校验

数据一般都要遵守一定的规则，在前面参照表的例子里，数据必须是参照表里定义的格式。基于参照表的数据验证比较简单，因为符合规则的数据都已经在参照表中事先定义好了。但是大多数数据校验比基于参数表的这种方式更复杂，例如下面的几种情况：

• 电子邮箱的地址必须是有效的格式。
• 输入的数据都必须是大写/小写。
• 日期必须是 dd-mm-yyyy 的格式。
• 电话号码必须是 xxxx-xxxx-xxxx 的格式。
• 数值不能超过最大值 X。
• 订阅用户必须大于 18 岁。
• IBAN（国际银行账户号）必须是有效的。

其实还能列出上百个和上面类似的例子，但这些例子都有一个共同点：检查数据是否遵照预定义的业务规则，要找出不符合业务规则的数据。注意，这些都属于“属性值域约束”的范畴。在 UDI 里能完成这些校验功能的就是**“数据校验”**步骤，具体操作如下：

(1)、新建转换，首先需要一些输入数据，将**“自定义常量数据”**拖至画布，双击组件，步骤名称填入“Data Grid”，插入如下图所示字段：

(2)、选择“数据”标签，输入如下图所示数据：

(3)、将计算器组件拖至画布来计算平均值，与上一步骤自定义常量数据连接，并双击组件，步骤名称填入“PricePerItem”，在计算里选择“A/B”，把【amount】和【items】作为字段 A 和字段 B，新增加字段名为【ItemPrice】，具体配置如下图：

(4)、再将自定义常量数据拖至画布，双击组件，步骤名称填入“ProductList”，插入如下图所示字段：

(5)、选择“数据”标签，输入如下图所示数据：

(6)、有了数据后进行数据校验，可以使用过滤组件实现该功能，将过滤组件组件拖至画布，并把上一步骤的自定义常量数据和计算器步骤都连接到**“数据校验”**，双击组件，步骤名称为“Data Validator”，增加数据校验，按下图配置：

校验描述为“date_val”的配置信息：

当后面有判断字段是否是空值的校验描述时，判断值的校验需要勾选“允许空？”，以免空值情况将出现两处错误；

校验描述为“name_val”的配置信息：

校验描述为“items_val”、“amount_val”和“itemprice_val”的配置信息都与“date_val”类似，“要校验的字段名”选择与校验描述一致的名称，“错误代码”与“错误描述”用户可以自行定义，还要注意设置字段对应的“字段类型”，并在“数据”标签中设置最大值与最小值；

校验描述为“items_null”的配置信息：

校验描述为“itemprice_null”、“date_null”、“prod_null”、“items_null”、“amount_null”和“itemprice_null”的配置信息都与“items_null”类似，“要校验的字段名”选择与校验描述一致的名称，“错误代码”与“错误描述”用户可以自行定义，注意设置字段对应的“字段类型”；

(7)、将空操作（什么也不做）组件拖至画布，“数据校验”与其连接，当提示选择步骤时，选择随机分发和**“错误输出步骤”**，并双击组件，步骤名称填入“Errors”；

(8)、再将空操作（什么也不做）组件拖至画布，“数据校验”与其连接，当提示选择步骤时，选择随机分发和**“主输出步骤”**，并双击组件，步骤名称填入“Valid rows”。完整转换如下图所示：

(9)、运行转换，结果如下图所示：

(10)、选中名为Error的组件，右击并选中**“预览”**查看数据。可以观察到【ItemPrice】字段中有 NULL 值，【amount】字段中有大于设置的最大值数据，【adate】字段中有无效的日期点，结果如下图所示：

(11)、选中名为VAlid rowsr的组件，右击并选中**“预览”**查看数据。通过校验的数据结果如下图所示：

和过滤记录步骤相比，数据校验步骤更像一个高度可配置化的过滤器。满足各种校验条件的数据被发送到主数据流里，不满足条件的数据被发送到错误数据流中。和过滤记录步骤不同的是，**“数据校验”**步骤不用必须指定不满足条件的数据的流向。但强烈建议要设置不满足条件的数据的流向，因为一般都要统计为什么会验证失败，验证失败的记录有多少等等。

错误处理

错误处理的目的很明确：你希望你的 ETL 作业或转换可以捕获并处理运行过程中发生的任何错误。但错误的种类是不同的，如下所示。

• 处理过程错误：因为技术原因导致处理过程不能继续的错误。可能是文件没找到，可能是服务器关了（或宕机），或服务器密码变化了而没有通知 ETL 团队，等等。
• 数据校验错误：一些数据不能通过数据校验步骤。根据错误的影响，转换可以继续执行，这种错误不是处理过程错误。
• 过滤器错误：实际这不是错误，只不过“过滤行”步骤需要两个输出步骤，一个接收通过过滤器的数据，一个接收没有通过过滤器的数据。经常使用“空操作”步骤接收后面这一类数据。
• 一般步骤错误：UDI 里的很多步骤都可以定义错误处理步骤，错误处理步骤用来接收当前步骤处理错误的数据。

处理过程错误

缺少作业-abort 组件 无法实现，截图如下

转换错误

缺少作业-abort 组件 无法实现，截图如下

数据（校验）错误

前面介绍的“数据校验”步骤类似一个功能强大的过滤器，但它可以做更多的事情。这个步骤可以告诉你拒绝某一行的准确原因，这样你可以采取相应的解决办法。现在来看一下“数据校验”步骤的两个复选框：

• 报告所有错误，不止第一个错误：一条记录里可能会不满足多个校验条件。如果选择了这个复选框，即使已经发现一条记录不满足某个验证条件，还是要用其他验证条件来验证这条记录，这样可以获取这条记录的所有错误。
• 只输出一行：当选中这个选项，一行记录的所有错误都放在一行的一个字段里，类似于 MySQL 的 group_concat 函数。如果选中了“报告所有错误”选项，而没有选中这个选项，所有的错误就会放到多行里。

下面还有两个要注意的选项是“错误代码”和“错误描述”，这两个选项关系到对每个校验规则如何报告错误。如果要在校验失败后，继续处理失败的数据，若使用一组规范的错误编码，会便于后面的错误处理转换。

在开始错误处理工作之前，需要先启用错误处理。定义了错误处理可以把错误的数据行传到另一个步骤里。错误代码和错误描述字段也被增加到错误行里。要启用错误处理，右键单击“数据校验”，编辑步骤并点击“增加校验”，添加校验名称，然后设置校验规则，如图所示：

接下来将进行数据校验的错误处理，创建数据清洗流程来处理没有通过校验的数据，具体操作如下： (1)、新建转换，将“数据校验”转换中的所有步骤复制到新建的转换中，并取消数据校验组件与Error步骤之间的连接；接着将Switch / Case组件拖至画布，“数据校验”与其连接，并选择随机分发和错误输出步骤，再连接Error步骤，选择随机分发和主输出步骤。**“Switch/Case”**步骤可以根据错误编码，把错误行发送到不同数据流中双击组件，配置信息如下图所示：

(2)、在数据校验转换中可以观察到错误数据，有日期错误、存在空值和数据超过设定最大值，接下来的操作将处理这些错误数据，先将获取系统信息拖至画布，并将**“Switch/Case”**与其连接，再双击组件，步骤名称为“CorrectDate”，插入如下图所示字段：

(3)、再将增加常量拖至画布，并让**“Switch/Case”**与其连接，再双击组件，步骤名称填入“MaxItems”，插入如下图所示字段：

(4)、将字段选择拖至画布，并让**“获取系统信息”**与其连接，再双击组件，步骤名称填入“CorrectedDatefields”，选择获取所有字段，并改变【newdate】字段名称为“adate”，如下图所示：

(5)、再将“字段选择”拖至画布，并让**“增加常量”**与其连接，再双击组件，步骤名称填入“CorrectedItemfields”，选择获取所有字段，并改变【newitems】字段名称为“items”，如下图所示：

(6)、将去除重复记录拖至画布，两个字段选择组件与其连接，再将其与**“VAlid rows”**步骤连接；双击组件，步骤名称填入“Unique rows”，在“用来比较的字段”中获取所有字段，如下图所示：

(7)、按顺序连接，完整转换如下图所示：

(8)、运行转换，结果如下图所示：

(9)、选中名为Error的组件，右击并选中预览查看数据。可以观察到错误数据，当数据错误不止一处时将报告多次，应查看【error_desc】错误描述来进行分析，结果如下图所示：

(10)、选中名为VAlid rowsr的组件，右击并选中**“预览”**查看数据，可以观察到通过校验的数据和通过错误处理后的正确数据，结果如下图所示：

数据排重

完全重复问题

数据排重是一项具有挑战性的工作，很多客户表里都有重复数据，例如 CRM（Customer Relationship Management）系统里，这个系统里的很多数据是呼叫中心的客服代表录入的。客服代表没有足够的时间检查一个打电话过来的客户是否在系统中已经存在，或者错误拼写了名字或地址，并保存到了数据库中。以后在使用这些数据时，如给客户发促销邮件，就会发生问题。所以，很多 CRM 系统初始化时，都要清洗客户数据，保证数据库里一个客户只有一条记录。当然，问题是如何检测出重复的数据？更具有挑战性的是：如何在没有主键的情况下或主键可能拼写错误的情况下，检测到重复数据。遗憾的是，对这类问题，不会有任何一个软件或方法可以百分之百解决。但通过模糊匹配，可以逐渐把数据整理好。

UDI 有去除重复记录的步骤，“去除重复记录”步骤。这个步骤只能识别完全相同的数据行，而且检查可以只限制在某几个字段。例如，一个公司想给每个客户地址寄送直邮广告，输入数据需要有客户号和地址信息，而且地址信息需要排序，前提是例子里至少要有两个相同地址的客户信息。接下来将介绍如何用 Uniplore 完成这个例子，具体操作如下： (1)、新建转换，将表输入组件拖至画布，双击组件，在弹出的对话框中选择在  Uniplore  中保存好的数据库连接，单击获取 SQL 查询语句，在对应的文本框中即可获得对应表的查询。当然，也可以在文本框中编写自定义 SQL 查询语句。获取需要的客户号和地址信息并按照地址号排序，配置如下图所示：

(2)、接着将**“去除重复记录”**拖至画布，去除重复的地址信息，并记录地址号出现的次数；

(3)、再将空操作（什么也不做）组件拖至画布，双击组件，步骤名称改为“UniqueRows”，并将去除重复记录连接这个步骤，若提示选择步骤，依然选择**“主输出步骤”**。完整转换如下图所示：

(4)、运行转换，结果如下图所示，可以观察到**“去除重复记录”**中读取是 600 行数据，但输出是 599 行数据，说明确实去除了一行重复及录：

(5)、选中UniqueRows组件，右击并选中**“预览”**查看数据。【address_sum】字段显示【address_id】字段出现的次数，此处数据的重复率并不高，但当数据拥有很多重复信息时，计数器将起到重要的作用。结果如下图所示：

不完全重复问题

正如在本案例前面介绍的那样，大多数数据质量问题是由客户和产品数据引起的。下面的例子都聚焦在客户数据上，因为客户数据易于理解，先来看一些典型的客户名称重复的例子。

假设 CRM 系统里存储了客户的姓氏、名字、电子邮件地址、城市和国家，如下图所示：

图中可以很容易地看出这两条记录指向同一个人，但如果有 3 条、30 条甚至上亿条该怎么办？不能靠眼睛在大量的数据里查找重复的数据。最后，还需要算法来匹配可能重复的数据。一些字段的数据是完全一样的，如上图所示，一些字段可能拼写错误或拼写方式不同。使用 SQL 是很难解决这种可能重复数据的问题的：不能使用相等检测，而“like”检查也不可行，因为要事先指定搜索字符串。唯一能找到可能重复记录的方法就是使用模糊匹配的逻辑，它可以计算字符串的相似度，接下来就设计一下方法：

首先要检查的是一些数据录入点，例如 City 、Postal cod e 和 Country 这类字段，这类字段一般是通过选择的方式录入的，系统一般不会让用户自己拼写这些数据，而且系统往往也会检查这些数据的逻辑规则是否正确。无论做何种检查，都需要有一些数据正确的列，否则没有信息能把重复的数据关联起来。在上图里，城市和国家是正确的数据（至少不会拼写错误）。如果没有这种把可能重复的数据联系到一起的准确数据，排重是根本不可能的。排重工作下面要做的事情就是要保证有充足的计算能力。检查可能的重复记录意味着要搜索表里的全部记录。如果表里有一百万条记录，检查一条记录就要检查一百万次（1 百万 ×1 百万），如果要比较多个字段，情况会更糟。这就是为什么市场上这类工具的价格这么高的其中一个原因。价格高的另一个原因是这类工具都有内置的“知识库”，可以自动地匹配正确的地址和人名信息。

这里设计的方法包含三个步骤，当然也可以再增加步骤或做一些修改。这里主要的步骤就是上面提到的**“模糊匹配”**步骤。这个步骤完成下面的工作：

• 从数据流里读取输入字段。
• 使用某一种模糊匹配算法查询另一个数据流里的一个字段。
• 返回匹配结果。

接下来将详细介绍如何用 Uniplore 进行对数据的“模糊匹配”，具体操作如下： (1)、新建转换，将表输入组件拖至画布，双击组件，步骤名称填入“ReadSource”，并在弹出的对话框中选择在 Uniplore 中保存好的数据库连接，单击获取 SQL 查询语句，在对应的文本框中即可获得对应表的查询。当然，也可以在文本框中编写自定义 SQL 查询语句。获取需要的客户号、客户姓氏和邮件并将字段名更改以便后期区分，将使用【last_name】字段作为模糊匹配字段，使用【e-mail】字段作为参照字段。 配置如下图所示：

(2)、再将表输入组件拖至画布，双击组件，步骤名称填入“Lkp_LastName”，在弹出的对话框中选择在 Uniplore 中保存好的数据库连接，单击获取 SQL 查询语句，在对应的文本框中即可获得对应表的查询。当然，也可以在文本框中编写自定义 SQL 查询语句。获取需要的客户号和客户姓氏并将字段名更改以便后期区分，配置如下图所示：

(3)、将模糊匹配组件拖至画布，两个**“表输入”**组件与其连接，双击组件，步骤名称填入“MatchLastName”，填入之前准备好的匹配字段和主要流字段，并使用“Jaro Winkler”算法，设置的最小相似度为  0.8。“设置”栏里的选项，主要取决于选择的算法。如果你选择的是“Soundex”或“Metaphone”算法等，是没有设置项，“区分大小写”选项也只适用于“Levenshtein”算法。 具体配置如下图：

设置中其他的一些选项都是用来控制匹配结果的。这里的“获取近似值”选项很重要，这个选项可以只返回一个最相似的数值。如果不选中这个选项，就会返回相似度在最小值和最大值之间的多个数值，多个值使用指定的分隔符分开。

(4)、选择“字段”标签，设置查询流里需要的其他字段，填入匹配字段与值字段，如下图所示：

在“字段”标签下可以设置匹配列的列名。但因为有多个匹配的数值，以及这些数值也没有可以参照的主键或引用，所以这个结果对后续处理没有太大作用。对于排重工作来说，不但要找到相似的数值，还要知道这些数值属于哪条记录。所以最好选中“获取近似值”选项。这个选项只返回相似度最高的数值，另外还返回这个数值所在记录的其他字段。

在这个例子里，模糊匹配是第一个步骤。在现实情况中，这个步骤的前面可能还有一些使用正则表达式匹配等技术做数据清洗的步骤。例如，一个数据源文件只包含了一个单一的 name 字段，这是人的全名。如果是美国人名，这时就需要使用正则表达式步骤把这个字段拆分成“first name”、“middle initial”和“last name”字段，如果是其他国家的人名，可能有其他的拆分方法。

(5)、将过滤记录组件拖至画布，让模糊匹配步骤与其连接，再将空操作（什么也不做）组件拖至画布，并将步骤名改为“DeletePerfect”，将数据库查询组件拖至画布，步骤名改为“Lkp_Email”；然后将**“过滤记录”**连接这两个步骤以便接下来的配置，双击组件，步骤名称改为“SelectSuspecs”，选择数据发送的步骤和条件，具体如下图所示：

(6)、双击**“Lkp_Email”**步骤，在弹出的对话框中选择在  Uniplore  中保存好的数据库连接，选择好要查询的表名、所需的关键字条件和要返回的值，具体如下图所示：

(7)、再进行一次过滤，将过滤记录组件拖至画布，让数据库查询步骤与其连接，再将**空操作（什么也不做）组件两次拖至画布，并分布将步骤名改为“View”和“DeleteNonMatch”；然后将“过滤记录”**连接这两个步骤以便接下来的配置，双击组件，步骤名称改为“RemoveWaste”，选择数据发送的步骤和条件，具体如下图所示：

记录是“可能的”重复记录，尽管在这个例子里，可以很肯定说这两个记录是一个人。但实际情况中也有可能是一对夫妻共享了同一个电子邮箱地址。还有更糟的情况，例如，两个记录指向同一个人，但两个地址都是有效的，一个是街道地址，一个是邮局邮箱地址，或者一个人有两个电子邮箱地址等等情况。

(8)、按顺序连接，若提示选择步骤，依然选择**“主输出步骤”**。完整转换如下图所示：

(9)、运行转换，结果如下图所示：

(10)、选中DeletePerfect步骤，右击并选中**“预览”**查看没有匹配到姓氏相近的数据信息。结果如下图所示：

(11)、选中DeleteNonMatch步骤，右击并选中**“预览”**查看通过匹配后姓氏相近，但邮件不相等的数据信息。结果如下图所示：

(12)、选中View步骤，右击并选中**“预览”**查看通过匹配后姓氏相近、邮件相等的数据信息。结果如下图所示：

排重的另一个问题是正确性的问题：即使找到了不同地址或不同电话号码的重复记录，那么哪一个地址或电话号码是正确的？不是所有的业务系统对字段的变化都有详细的变更日志，即使有，还有人为错误输入的可能。把多个记录合并成一个记录要非常小心。首先，要确定以哪条记录里的数据为准。其次，地址应该作为一个整体来处理，如果把一条记录的城市名称，合并到另一条城市名称为空的记录里通常没有太大意义，以上是一个基本的排重转换，实际生活中还要再结合很多注意的问题。

正则表达式

使用正则表达式来匹配字符串，可以使用简单或非常复杂的正则表达式，能把通过分组（capture groups）捕获到的字符串放到新的字段里。 正则表达式错误

实验小结

本章讲述了 UDI 里用于数据清洗、校验、更正的几个不同的工具和技术。我们讨论了数据应该遵守的不同类型的规则和约束。在本章里我们学到了：

• 用于数据清洗的不同步骤和这些步骤的用例。
• 如何使用 Kettle 里的字符串匹配算法。
• 用于清洗和更正数据的参照表。
• 如何使用数据校验步骤，校验数据的选项。
• 如何使用 Kettle 的错误处理机制处理错误。
• 如何使用模糊匹配步骤排除重复数据。