样本数据处理 课件 第3、4章 数据的导入与导出、数据清洗

数据的导入与导出新工科建设之路·数据科学与大数据系列样本数据处理第三章01基于文件的数据导入与导出文本文件可能是使用ETL工具处理的最简单的一种数据。文本文件易于交换，压缩比高，任何文本编辑器都可打开。总体来说，文本文件可分为两类。（1）分隔符文件；（2）固定宽度文件。基于文件的数据导入与导出最基本的文本文件输入步骤就是“CSV文件输入”步骤。CSV文件是一种用分隔符分隔的文本文件。在处理这种文件之前，需要通过文本编辑器打开查看，以确定此文件的分隔符和字段。“CSV文件输入”步骤和与之类似的“固定宽度文件输入”步骤其实都是“文本文件输入”步骤的简化版，都不适合一次处理多个文件。这3个步骤是处理文本文件的首选步骤，“文本文件输入”步骤具备的功能如下。基于文件的数据导入与导出①从前一个步骤读取文件名。②一次运行读取多个文件名。③从.zip或.gzip压缩文件中读取文件。④不用指定文件结构就可以显示文件内容。⑤指定逃逸字符。⑥错误处理。⑦过滤。⑧指定本地化的日期格式。文本文件的导入与导出案例1.（1）转换的输入/输出需求读取student.csv文件，输出固定宽度为12字节的student.txt文件。student.csv文件以逗号为分隔符，内容如图3-2所示。文本文件的导入与导出案例期望输出的student.txt文件的内容如图3-3所示。文本文件的导入与导出案例（2）转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-4所示。文本文件的导入与导出案例（3）步骤的配置“CS∨文件输入”的配置如图3-5所示。文本文件的导入与导出案例（4）运行转换如图3-9所示，单击卜按钮开始运行程序，在打开的对话框中单击“启动”按钮运行此转换。文本文件的导入与导出案例2.（1）转换的输入/输出需求读取通过“固定宽度文件输入”的student.txt文件，输出分隔符为分号（；）的stu.txt文件。student.txt为本节案例1的输出文件，内容如图3-10所示。文本文件的导入与导出案例期望输出的stu.txt文件的内容如图3-11所示。文本文件的导入与导出案例（2）转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-12所示。文本文件的导入与导出案例（3）步骤的配置“固定宽度文件输入”的配置如图3-13所示。文本文件的导入与导出案例4）运行转换如图3-17所示，单击按钮开始运行程序，在打开的对话框中，单击“启动”按钮运行此转换。Excel文件的导入与导出Excel的数据可分为结构化的表格数据和非结构化的表格数据。对于非结构化的表格数据，有可能表里包含多个字段值的列或者重复的一组字段等。使用Kettle读取后还需要转化为结构化的表格数据，才能进一步处理，而且，Excel作为常用的办公软件，很难规范所有的人员按数据的格式要求，规范地输入数据。因此，在数据导入时，应尽量避免把Excel文件作为输入数据源。Excel文件的导入与导出案例1.转换的输入/输出需求读取student.xlsx文件，输出分隔符为逗号的student.csv文件和名为stuout.xlsx的文件。student.xlsx文件的内容如图3-18所示。Excel文件的导入与导出案例输出的student.csv文件的内容如图3-19所示。Excel文件的导入与导出案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-20所示。Excel文件的导入与导出案例3.步骤的配置“文件”选项卡的配置如图3-21所示。Excel文件的导入与导出案例“其他输出字段”选项卡的配置如图3-29所示，采用默认配置即可。Excel文件的导入与导出案例4.运行转换如图3-35所示，单击按钮开始运行程序，在打开的对话框中单击“启动”按钮运行此转换。XML是扩展标识语言（eXtensibleMarkupLanguage）的缩写，是一种在平面文本文件中定义数据结构和内容的开放标准。在互联网上，很多软件和系统都使用XML格式来交换数据，这使XML格式的文件非常流行。XML实际上是文本文件，它可以使用NotePad、vi等文本编辑器打开。XML文件的导入与导出XML文件不是普通的文本文件，而是一种遵循规范的半结构化的文本文件。关于XML的语法规则及相关的知识内容，本书不进行介绍，请不熟悉的读者自找资料完成XML相关知识的学习。XML文件的导入与导出案例1.转换的输入/输出需求读取XML文件.XML文件，输出XmltoExcel.xlsx文件和xmlout.xml文件。XML文件.XML文件的内容如图3-36所示。XML文件的导入与导出案例输出的XmltoExcel.xlsx文件的内容如图3-37所示。XML文件的导入与导出案例输出的xmlout.xml文件的内容如图3-38所示。XML文件的导入与导出案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-39所示。XML文件的导入与导出案例3.步骤的配置“文件”选项卡的配置如图3-40所示。XML文件的导入与导出案例4.运行转换如图3-48所示，单击按钮开始运行程序，在打开的对话框中单击“启动”按钮运行此转换。JSON（JavaScriptObjectNotation）是目前Web应用中使用越来越多的一种数据交换标准，甚至超过XML格式。JSON格式最初由DouglasCrockford提出，关于JSON的更多描述请参考JSON的官方网站。对JSON文件，Kettle可以用“JSONInput”和“JSONOutput”步骤完成文件的读取与输出。JSON文件的导入与导出JSON文件的导入与导出案例1.转换的输入/输出需求JsonToExcel.xlsx文件的内容如图3-50所示。JSON文件的导入与导出案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-51所示。JSON文件的导入与导出案例3.步骤的配置“文件”选项卡的配置如图3-52所示。JSON文件的导入与导出案例4.运行转换如图3-58所示，单击按钮开始运行程序，在弹出的对话框中单击“启动”按钮运行此转换。02基于数据库的数据导入与导出连接名称：设定一个在作业或转换范围内唯一的名称。连接类型：从数据库列表中选择要连接的数据库类型。连接方式：在列表里可以选择可用的连接方式。关系数据库的数据导入与导出关系数据库的数据导入与导出（1）主机名称：数据库服务器的主机名称或IP地址。（2）数据库名称：要访问的数据库的名称。（3）端口号：默认是选中的数据库服务器的默认端口号。（4）用户名和密码：数据库服务器的用户名和密码。“一般”选项卡常用的连接参数如下MySQL数据库的数据导入与导出案例1.转换的输入/输出需求期望输出的StuOut表的内容如图3-63所示。MySQL数据库的数据导入与导出案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-64所示。MySQL数据库的数据导入与导出案例3.步骤的配置“元数据”选项卡的配置如图3-65所示。MySQL数据库的数据导入与导出案例4.运行转换如图3-78所示，单击按钮开始运行程序，在打开的对话框中单击“启动”按钮运行此转换。03基于Web的数据导入与导出Kettle提供了“HTTPClient”和“HTTPPost”步骤从Web获取数据。“HTTPClient”步骤发出一个HTTPGET请求或者直接访问HTML页面，调用此步骤里的URL从Web获取数据。HTTPGET请求主要从服务器获取数据，不会对服务器数据进行更改。最常见的HTTPGET请求就是页面里的各种查询操作。基于Web的数据导入与导出“HTTPPost”步骤发出一个HTTPPOST请求，调用此步骤里的URL从Web获取数据。HTTPPOST请求主要向服务器提交数据，对服务器端的数据有影响。最常见的HTTPPOST请求就是用户登录操作，以及各种修改操作。该步骤可以通过HTTPPOST请求把参数作为消息体进行提交，也可以把整个文件作为消息体进行提交。HTML数据的导入与导出1.转换的输入/输出需求读取数据，输出HTML源码并保存在E：教材案例\第3章\webout.html文件中。其中，需要注意网页的字符编码（见图3-79）。HTML数据的导入与导出2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-81所示。HTML数据的导入与导出3.步骤的配置在“数据”选项卡中，WebURL的值按案例的需求填入目标分析地址，如图3-82所示。HTML数据的导入与导出4.运行转换如图3-87所示，单击按钮开始运行程序，在打开的对话框中单击“启动”按钮运行此转换。HTTPGET请求返回的结构化数据主要有XML和JSON，本例使用的API返回的是JSON格式的数据。通过对返回数据的后续处理，我们可以获取需要的数据。基于HTTPGET请求的导入与导出基于HTTPGET请求的导入与导出案例1.转换的输入/输出需求期望输出的httpGetJson.xls文件的内容如图3-88所示。基于HTTPGET请求的导入与导出案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-89所示。基于HTTPGET请求的导入与导出案例3.步骤的配置配置完的效果如图3-90所示。基于HTTPGET请求的导入与导出案例4.运行转换单击按钮开始运行程序，在打开的对话框（见图3-94）中单击“启动”按钮运行此转换。04基于CDC变更数据的导入与导出时间戳这种属性列最少需要一个更新时间，最好有两个时间，即一个插入时间和一个更新时间。自增序列大多数据库都有自增序列，如果数据库表中使用了这种序列，就可以通过序列识别出新插入的数据。基于源数据的CDC基于源数据的CDC案例1.（1）转换的输入/输出需求其中，输入数据studentcdc表的内容如图3-95所示。基于源数据的CDC案例（2）转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-101所示。基于源数据的CDC案例（3）步骤的配置在当前转换画布上空白的地方单击鼠标右键，在弹出的快捷菜单中选择“转换设置CTL-L”命令，打开“转换属性”对话框，在“命名参数”选项卡中配置命名参数的名字（curtime）和默认值（2018-08-04），如图3-102所示。基于源数据的CDC案例（4）运行转换单击按钮开始运行程序，在打开的对话框中设置命名参数“curtime”的“值”为“2018-08-06”，如图3-107所示。基于源数据的CDC案例（2）转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-114所示。基于源数据的CDC案例（3）步骤的配置在当前转换画布上空白的地方单击鼠标右键，在弹出的快捷菜单中选择“转换设置CTL-L”命令，系统打开“转换属性”对话框。在“命名参数”选项卡中配置命名参数的名字（cur_no）和默认值（1），如图3-115所示。基于源数据的CDC案例（4）运行转换单击按钮开始运行程序，系统打开“执行转换”对话框，单击“命令参数”选项卡，设置命名参数“cur_no”的“值”为“2”，如图3-120所示。当INSERT、UPDATE、DELETE等SQL执行时，系统会触发数据库自有的触发器，并执行某些动作。一般的设计思路是源表进行INSERT、UPDATE、DELETE操作时，利用数据库自有的触发器，将操作的数据记录到日志表中，根据日志表的记录对目标表进行相应的处理。因为要变动源数据库，服务协议或者数据库管理员不允许，所以在大多数情况下，系统不允许向数据添加触发器，而且变动源数据库还会降低系统的性能，因此人们较少使用这种方法。基于触发器的CDC基于触发器的CDC案例1.（1）转换的输入/输出需求期望输出的student_cdcsync表的内容如图3-123所示。基于触发器的CDC案例（2）转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-124所示。基于触发器的CDC案例（3）步骤的配置“CDC日志表输入”的配置如图3-125所示。基于触发器的CDC案例（4）运行转换单击按钮开始运行程序，系统打开“执行转换”对话框（见图3-129），单击“启动”按钮运行此转换。如果源数据中没有时间戳，又不能使用触发器，就可以使用不同版本的快照表进行对比，来获得数据的增量变化。我们将源数据中的所有数据加载到数据仓库的缓冲区形成源数据的第一个快照版本，下一次需要同步时，再将源数据的所有数据加载到数据仓库的缓冲区形成源数据的第二个快照版本，然后比较这两个版本的数据，就可以找到增量变化。基于快照的CDCKettle里的“合并记录”就可以用来比较两个表的差异。这个步骤读取两个使用关键字排序的输入流数据，并根据输入流里的关键字比较其他字段。基于快照的CDC案例1.转换的输入/输出需求期望输出的studentcdcsync表的内容如图3-143所示。基于快照的CDC案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-144所示。基于快照的CDC案例3.步骤的配置“快照1输入”的配置如图3-145所示。基于快照的CDC案例4.运行转换如图3-143所示。基于日志的方式是最高级的、最没有侵入性的CDC方法。数据库会把插入、更新、删除操作都记入日志中。MySQL提供了一个日志读取工具一MySQLbinlog。这个工具可以把二进制的日志格式转换为可以理解的格式，然后就可以把这种格式保存到文本文件中。我们可以在MySQLbinlog中设置开始/截止时间戳，这样就可以从日志中读取一段时间的日志。另外，日志中的每一项都有相应的序列号，也可以作为偏移的依据。MySQL提供了以上两种方式，来防止CDC过程发生数据重复和丢失。基于日志的CDC把MySQLbinlog的输出写到文本文件后，我们就可以使用Kettle步骤来读取文件的内容。其他的数据库也有相应的方法。使用基于日志的CDC也有其缺点，那就是只能用来处理一种特定的数据库。如果要在异构的数据库环境使用基于日志的CDC，就需要使用类似OracleGoldenGate的商业软件，而这些软件的价格都十分高昂。基于日志的CDC基于日志的CDC案例1.转换的输入/输出需求期望输出的student_cdc_sync表的内容如图3-149所示。基于日志的CDC案例2.转换的设计图参考2.2.2节的操作，新建转换文件，并开始可视化编程。该转换所需要的步骤及步骤之间的连接流程如图3-150所示。基于日志的CDC案例3.步骤的配置“读入SQL文件”的配置如图3-151所示。基于日志的CDC案例4.运行转换单击按钮开始运行程序，在打开的对话框（见图3-129）中单击“启动”按钮运行此转换，系统将执行替换后的SQL，studentcdcsync表的内容如图3-149所示。数据清洗新工科建设之路·数据科学与大数据系列样本数据处理第四章01数据清洗概述Kettle里没有单一的数据清洗步骤，很多数据清洗工作都需要结合多个步骤来组合完成。数据清洗工作从抽取数据时就开始，在很多输入步骤中都可以设置特定的数据格式，按照特定的数据格式来读取数据。示例代码是从一张studentinfo表中抽取studentid和score两个字段，并按score字段降序排列得到的。Kettle常用的数据清洗步骤注意：首先，如果SQL语句太过复杂，就会导致以后的维护工作非常困难；其次，如果数据在进入Kettle时已经做过清理，那么ETL便不能提供数据审计的功能。因此，是需要原样读入数据，还是利用SQL在数据抽取时清理，需要用户在效率和可维护性之间进行平衡。这里主要介绍Kettle在转换（Transform）目录下提供的数据清洗步骤。这是一个功能丰富的步骤，它提供了很多预定义的函数来处理输入字段，并且随着版本的更新还在不断增加。计算器从字面上看很简单，只有了解到这个步骤可以支持正则表达式时，才会真正感受到它的强大之处。字符串替换Kettle常用的数据清洗步骤该步骤提供了很多常规的字符操作，如大小写转换，字符填充、移除空白字符等。字段选择剪切字符串。字符串剪切这3个步骤通过使用分隔符来拆分、合并字段。拆分字段、合并字段和拆分字段成多行Kettle常用的数据清洗步骤该步骤使用一个标准的值来替换字段里的其他值。值映射该步骤可以对字段进行选择、删除、重命名等操作，还可以更改字段的数据类型、长度和精度等元数据。字段选择这两个步骤主要通过指定字段来清除重复记录。去除重复记录和去除重复记录Kettle常用的数据清洗步骤字符串清理“DataGrid”的元数据如图4-1所示。字符串清理另外输入一组示例数据，内容如图4-2所示。字符串清理“Stringoperations”的具体设置如图4-3所示。字符串清理清理步骤设置完毕。为了展示结果，我们使用一个“MicrosoftExcelOutput”步骤输出清理后的结果。"MicrosoftExcelOutput”步骤的设置方法前面已经讲过，这里不再赘述。这时整个转换流程如图4-4所示。字符串清理执行string_op转换，打开输出文件，可以看到如图4-5所示的结果。字符串清理这时将这个步骤添加到“Stringoperations”步骤和“MicrosoftExcelOutput”步骤之间，string_op转换的流程变成如图4-7所示的流程。字符串清理执行转换并打开输出文件，可以看到如图4-8所示的结果。字符串清理“Stringscut”的具体设置如图4-9所示。字符串清理组合几个步骤后，string_op转换的最终流程如图4-10所示。字符串清理执行上面的转换，系统将得出最终结果，如图4-11所示。关于字段清理，这里简单介绍4个常用步骤：“拆分字段成多行（Splitfiledtorows）"、“拆分字段（SplitFields）”、“合并字段（ConcatFields）”和“字段选择（Selectvalues）"。字段清理字段清理“Splitfiledtorows”步骤新建一个转换，命名为field_op，同样使用“DataGrid”步骤作为输入，设计一个表格，记录编号、省份、城市，如图4-12所示。字段清理表格设计完成后，输入一组示例数据，如图4-13所示。字段清理这里我们就用这个正则表达式来作为分隔符“Splitfieldtorows"的具体设置如图4-14所示。字段清理同样选择输出一个Excel文件，整个转换的流程如图4-15所示。字段清理这时查看结果，可以发现数据增加了一个字段“城市New”，具体结果如图4-16所示。字段清理“SplitFields”步骤和“ConcatFields”步骤具体设置如图4-17所示。字段清理对该步骤的结果同样用Excel输出。field_op_1转换的流程如图4-18所示。字段清理执行转换，“SplitFields”的输出结果如图4-19所示。字段清理具体设置如图4-20所示。字段清理同样选择Excel作为输出步骤，field_op_1转换的流程如图4-21所示。字段清理执行转换，“ConcatFields”的具体结果如图4-22所示。字段清理“Selectvalues”步骤元数据设置成如图4-23所示的结构。字段清理对“DataGrid”步骤输入一组示例数据，如图4-24所示。字段清理我们可以在该页面上添加“Age”字段将其删除，具体设置如图4-25所示。字段清理单击“Quick

launch”按钮，这里会看到如图4-26所示的结果。字段清理具体设置如图4-27所示。字段清理预览该步骤，可以查看到如图4-28所示的结果。字段清理具体设置如图4-29所示。字段清理预览该步骤，可以查看到如图4-30所示的最终清理结果。使用参照表清理数据在某些场合，我们无法直接从当前表中识别出数据的错误。在某些场合，我们无法直接从当前表中识别出数据的错误。例如，客户信息表包含客户的邮编、城市及其他信息，但录入时可能出现城市与邮编不一致的情况，这时就需要访问外部的一些参照数据来检查和修正这些错误。外部的参照数据里的信息是完整的、标准的，也称这些参照数据为主数据。外部的这些主数据从何而来？主数据一般都有相应的公司对其进行销售和维护，当然也有极少的主数据是可以免费下载的。使用参照表校验数据的准确性参照表的用途很多，其中最常见的用法就是用参照表来做查询和校验。使用参照表使数据一致不同的系统对性别的记录可能都不一样，有的系统用M表示男，用F表示女；有的系统用数字0表示男，用1表示女，或者用1表示男，用2表示女；而有的系统则用Male和Female分别表示男和女。对未知性别的表示也不尽相同，用Unknown、0、NULL、U等都可以表示。使用参照表清理数据在Kettle7.1中，校验目录下有以下4个步骤。（1）数据校验（DataValidator）。（2）信用卡校验（CreditCardValidator）。（3）电子邮箱校验（MailValidator）。（4）XML文件校验（XMLValidator）。后3个步骤都是功能相对“单一”的步骤。所谓单一，并不是指功能简单，而是指针对性很强。数据校验可以给一列设置多个约束：有的列可能需要多个约束，该步骤不会限制在同一列上设置几个约束。错误合并：将一行数据里的所有错误通过分隔符连接，合并成一个字符串，保存到错误描述字段中。校验数据类型：当输入的是日期类型的数据或者字符串格式的数值时非常有用，可以设置掩码，找到非法数据。约束条件参数化：几乎所有的约束条件都可以参数化。正则表达式：支持功能强大且灵活的正则匹配。数据校验02数据排重现实世界中的一个实体，理论上在数据库或者数据仓库中应该只有一条与之对应的记录。很多原因（如数据录入出错、数据不完整、数据缩写，以及多个数据集成过程中不同系统对数据的表示不尽相同）会导致集成后同一实体对应多条记录。在数据清洗的过程中，重复记录的检测与清除是一项非常重要的工作。如何识别重复数据重复数据分为两类，一类是完全重复数据，另一类是不完全重复数据。完全重复数据很好理解，就是两个数据行的数据完全一致，这类重复数据很好辨别，也很容易清除。对于完全重复数据，一个最简单的方式就是对数据集排序，然后通过比较相邻记录进行合并，Kettle的“Sortrows（记录排序）”步骤可以用于排序。Kettle有两个去除重复记录的步骤“Uniquerows（去除重复记录）”和“Uniquerows（HashSet）（去除重复记录（哈希值））"。前一个步骤只能针对有序记录去重，后一个步骤不需要。如何识别重复数据对于不完全重复数据，检查可能的重复记录需要保证有充足的计算能力，因为检查一条记录就需要遍历整个数据集，也就是说对整个数据集的检查需要在所有记录之间进行两两匹配，其计算复杂度为O（n²）。对可能重复记录的检测需要使用模糊匹配的逻辑，它可以计算字符串的相似度。首先通过模糊匹配找出疑似重复的数据，然后结合其他参考字段做数据排重。相同点“Redirectduplicaterow"：选中该选项后，需要对该步骤进行错误处理，否则一旦有重复记录，系统将会以出错处理。相同点“Fieldstocompare"：设置哪些字段参与比较。若留空，则表示整条记录参与比较。去除完全重复数据选项只在“Uniquerows（HashSet）”步骤中有设置，选中该选项后