ch03数据的导入与导出

数据的导入与导出新工科建设之路·数据科学与大数据系列样本数据处理第三章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提供了一个日志