《数据采集与处理技术》课件-第7章 ETL工具Kettle

第7章ETL工具Kettle目

录7.1Kettle的基本概念7.2Kettle的基本功能7.3安装Kettle7.4数据抽取7.5数据清洗与转换7.6数据加载7.1Kettle的基本概念7.1Kettle的基本概念一个数据抽取过程（如图7-1所示）主要包括创建一个作业（Job），每个作业由一个或多个作业项（JobEntry）和连接作业项的作业跳（JobHop）组成。每个作业项可以是一个转换（Transformation）或是另一个作业。一个转换由一个或多个步骤（Step）和连接步骤的跳（Hop）组成。图7-1一个数据抽取过程的构成要素7.1Kettle的基本概念转换主要用于数据的抽取（Extraction）、转换（Transformation）以及加载（Load），比如读取文件、过滤输出行、数据清洗或加载到数据库等步骤。一个转换包含一个或多个步骤，每个步骤都是单独的线程，当启动转换时，所有步骤的线程几乎并行执行。步骤之间的数据以数据流方式传递。所有的步骤都会从它们的输入跳中读取数据，并把处理过的数据写到输出跳，直到输入跳里不再有数据就终止步骤的运行；当所有步骤都终止了，整个转换就终止了。由于转换里的步骤依赖前一个步骤获取数据，因此转换里不能有循环。7.1Kettle的基本概念相较于转换，作业是更加高级的操作。作业由一个或多个作业项（作业或转换）组成。所有的作业项是以某种自定义的顺序串行执行的。作业项之间可以传递一个包含了数据行的结果对象。当一个作业项执行完成后，再传递结果对象给下一个作业项。作业里可以有循环。跳是步骤之间带箭头的连接线，它定义了一个单向通道，用于连接两个步骤，实现将数据从一个步骤（写入数据到行集）流向另一个步骤（从行集中读取数据）。跳是两个步骤之间的被称为“行集”（RowSet）的数据行缓存（可以在转换设置中定义行集大小）。若行集满了，则向行集写数据的步骤将停止写入，直到行集里又有空间。若行集空了，则从行集读取数据的步骤就会停止读取，直到行集里又有可读取的数据行。跳对于向行集写入数据的步骤来说是输出跳，一个步骤可以拥有多个输出跳；跳对于从行集中读取数据的步骤来说是输入跳。作业跳是作业项之间带箭头的连接线，它定义了作业的执行路径。7.2Kettle的基本功能7.2Kettle的基本功能Kettle的基本功能包括转换管理和作业管理。转换管理主要包括输入、输出、转换、应用、流程、脚本、查询、检验、作业、映射和批量加载等功能（表7-1给出了常用的转换控件及其相关说明）。作业管理主要包括通用、邮件、文件管理、条件、脚本、批量加载等功能（表7-2给出了常用的作业控件及其相关说明）。转换类别步骤/控件相关说明输入CSV文件输入从本地的CSV文件中输入数据

文本文件输入从本地的文本文件中输入数据

表输入从数据库的数据表中输入数据

获取系统信息读取系统信息输入数据输出文本文件输出将处理后的结果输出到文本文件中

表输出将处理后的结果输出到数据库的数据表中

插入/更新根据处理后的结果对数据库中的数据表进行插入更新。根据查询条件中的字段判断数据表中是否存在相关记录，若存在，则进行更新，否则进行插入表7-1常用的转换控件及其相关说明7.2Kettle的基本功能转换

值映射数据的映射列转行将数据表的列转换成数据表的行去除重复记录从输入流中去除重复的数据，需要注意的是输入流中的数据必须是已排序的唯一行（哈希值）从输入流中去除重复的数据，不需要对输入流中的数据进行排序字段选择选择需要的字段，过滤掉不要的字段，也可与数据库字段对应拆分字段将一个字段拆分成多个字段排序记录基于某个字段值将数据进行升序或降序处理行转列将数据表的行转成数据表的列增加常量增加需要的常量字段7.2Kettle的基本功能应用

替换NULL值若某个字符串的值为NULL，则指定某个字符串的值进行替换设置值为NULL若某个字符串的值等于指定的值，则将这个字符串的值设置为NULL流程空操作不做任何操作，一般充当一个占位符

过滤记录根据条件对数据进行过滤分类脚本Java代码转换的扩展功能，编写Java脚本，对数据进行相应的处理

JavaScript代码转换的扩展功能，编写JavaScript脚本，对数据进行相应的处理

执行SQL脚本执行SQL脚本，对数据进行相应的处理7.2Kettle的基本功能查询

HTTPClient通过一个可以动态设定参数的基本网址调用HTTPWeb服务流查询将目标表读取到内存，通过查询条件对内存中的数据集进行查询数据库查询根据设定的查询条件对目标表进行查询，返回需要的结果字段连接

合并记录合并两个数据流，并根据某个关键字排序排序合并合并多个数据流，并且数据的行要基于某个关键字进行排序作业

复制记录到结果将数据写入正在执行的任务中获取变量获取环境或Kettle变量设置变量设置环境变量7.2Kettle的基本功能表7-2常用的作业控件及其相关说明作业类别步骤/控件相关说明通用

Start作业执行的开始Dummy作业执行的结束作业使用新的作业执行之前已定义好的作业成功提示作业执行成功转换使用作业执行之前已定义好的转换流程邮件

POP收信通过设置好的POP服务器地址收取邮件发送邮件发送作业执行成功或者失败的邮件文件管理

创建文件创建一个新的文件，若文件名已经存在，则提示创建失败并退出删除一个文件删除指定文件名的文件，若不存在指定的文件名称，则提示删除失败复制文件将源文件的内容复制到新创建的文件中或替换已存在的文件比较文件比较两个文件中的内容移动文件将文件移动到另一个目录下解压缩文件将作业文件进行解压或压缩操作7.2Kettle的基本功能条件

检查表是否存在检查数据库中的数据表是否存在检查一个文件是否存在检查指定的文件是否存在脚本

JavaScript编写JavaScript脚本，进行相应的数据处理Shell编写Shell脚本，进行相应的数据处理SQL编写SQL脚本，进行相应的数据处理批量加载

MySQL批量加载将本地文件中的数据批量加载到MySQL数据库中SQLServer批量加载将本地文件中的数据批量加载到SQLServer数据库中从MySQL批量导出到文件将MySQL数据库中的数据批量导出到本地文件中7.3安装Kettle7.3安装Kettle在Windows系统中打开浏览器，访问Kettle官网（/projects/pentaho/），下载Kettle安装文件pdi-ce--324.zip。或者，也可以直接到教材官网的“下载专区”的“软件”目录中下载pdi-ce--324.zip文件。把pdi-ce--324.zip解压缩到“D:\”目录下（或者也可以选择一个其他目录，比如“C:\”），会生成一个“data-integration”目录，该目录下就包含了Kettle。在data-integration目录里包含了Spoon的启动文件，即spoon.bat，双击该文件就可以启动Spoon，启动界面如图7-2所示。7.3安装Kettle图7-2Spoon启动界面7.3安装Kettle启动成功以后的界面如图7-3所示。图7-3Spoon启动以后的欢迎界面7.4数据抽取7.4数据抽取7.4.1把文本文件导入到Excel文件中7.4.2把文本文件导入MySQL数据库中（请直接参考教材）7.4.3把Excel文件导入到MySQL数据库中（请直接参考教材）7.4.1把文本文件导入到Excel文件中这里给出一个实例，演示如何使用Kettle把文本文件导入到Excel文件中，具体包括如下步骤：创建文本文件；建立转换；设计转换；执行转换。7.4.1把文本文件导入到Excel文件中1.创建文本文件在“D:\”目录下新建一个文本文件studentinfo.txt，其内容如图7-4所示，文件的第1行是字段名称，包括sno、name、sex和age，字段之间用“|”隔开，其余行都是记录，字段之间也是用“|”隔开。图7-4studentinfo.txt文件内容7.4.1把文本文件导入到Excel文件中2.建立转换在Spoon主界面的“主对象树”栏目中，在“转换”上面（如图7-5所示）单击鼠标右键，在弹出的菜单中点击“新建”。点击Spoon主界面左上角的“保存”图标，把这个转换保存到某个路径下并且名称为“text_to_excel”。图7-5新建“转换”7.4.1把文本文件导入到Excel文件中3.设计转换在“核心对象”栏目中，在“输入”控件里把“文本文件输入”拖到右侧设计区域，然后在“输出”控件里把“Excel输出”拖到右侧设计区域，然后为这两个控件建立连线（如图7-6所示），这里的连线就是前文介绍过的“跳”。为这两个控件建立连线的方法是，按住键盘上的Shift键，然后用鼠标左键单击“文本文件输入”控件图标，再用鼠标左键单击“Excel输出”控件图标，最后在其他空白区域单击鼠标左键，这样就建立了一条从“文本文件输入”到“Excel输出”的连线。图7-6放置文本文件输入和Excel输出两个控件7.4.1把文本文件导入到Excel文件中双击设计区域的“文本文件输入”控件，打开设置界面，点击“文件”选项卡，点击“文件或目录”右侧的“浏览”按钮（如图7-7所示），把studentinfo.txt文件添加进来，然后点击“增加”按钮，studentinfo.txt文件就会被增加到“选中的文件”中，增加后的效果如图7-8所示。图7-7添加文件7.4.1把文本文件导入到Excel文件中图7-8添加文件以后的效果7.4.1把文本文件导入到Excel文件中图7-9设置“内容”选项卡7.4.1把文本文件导入到Excel文件中在“字段”选项卡中（如图7-10所示），点击“获取字段”按钮，会弹出如图7-11所示的样本数据行数设置界面，直接点击“确定”按钮，会得到如图7-12所示结果。这时，点击界面底部的“预览记录”，就可以看到如图7-13所示的数据。最后，点击界面底部的“确定”按钮，完成文本文件输入控件的设置。图7-10设置“字段”选项卡7.4.1把文本文件导入到Excel文件中图7-11设置样本数据行数7.4.1把文本文件导入到Excel文件中图7-12获取字段以后的效果7.4.1把文本文件导入到Excel文件中图7-13预览记录7.4.1把文本文件导入到Excel文件中双击设计区域的“Excel输出”控件图标，打开设置界面（如图7-14所示），在“文件”选项卡中，设置“文件名”为“D:\file”。图7-14设置文件名7.4.1把文本文件导入到Excel文件中在“字段”选项卡中（如图7-15所示），点击界面底部的“获取字段”按钮，成功获取字段以后的效果如图7-16所示，然后把“sno”和“age”字段的“格式”设置为“#”。最后，点击“确定”按钮完成“Excel输出”控件的设置。全部设置完成以后，需要保存设计文件。图7-15“字段”选项卡7.4.1把文本文件导入到Excel文件中图7-16获取字段后的效果7.4.1把文本文件导入到Excel文件中4.执行转换在转换设计界面中（如图7-17所示），点击三角形按钮开始执行转换，会弹出如图7-18所示界面，在界面中点击“启动”，如果转换执行成功，会显示如图7-19所示的效果，在两个控件图标上都会显示绿色的勾号。这时，到D盘根目录下就可以看到新生成的文件file.xls，可以使用Excel软件打开file.xls查看内容（如图7-20所示）。图7-17运行转换7.4.1把文本文件导入到Excel文件中图7-18转换启动界面7.4.1把文本文件导入到Excel文件中图7-19转换执行成功的效果7.4.1把文本文件导入到Excel文件中图7-20file.xls文件内容7.5数据清洗与转换7.5数据清洗与转换7.5.1使用Kettle实现数据排序7.5.2在Kettle中用正则表达式清洗数据（请直接参考教材）7.5.3使用Kettle去除缺失值（请直接参考教材）7.5.4使用Kettle转化MySQL数据库中的数据（请直接参考教材）7.5.1使用Kettle实现数据排序这里给出一个实例，演示如何使用Kettle实现数据排序，具体包括如下步骤：创建文本文件；建立转换；设计转换；执行转换。7.5.1使用Kettle实现数据排序1.创建文本文件在“D:\”目录下新建一个文本文件score.txt，其内容如图7-51所示，文件的第1行是字段名称，包括name和score，字段之间用分号隔开，其余行都是记录，字段之间也是用分号隔开。图7-51score.txt文件内容7.5.1使用Kettle实现数据排序2.建立转换在Spoon主界面的“主对象树”栏目中，在“转换”上面（如图7-52所示）单击鼠标右键，在弹出的菜单中点击“新建”。点击Spoon主界面左上角的“保存”图标，把这个转换保存到某个路径下并且名称为“sort_data”。图7-52新建“转换”7.5.1使用Kettle实现数据排序3.设计转换在“核心对象”栏目中，在“输入”控件里把“文本文件输入”拖到右侧设计区域，然后在“转换”控件里把“排序记录”拖到右侧设计区域，然后为这两个控件建立连线（如图7-53所示）。图7-53放置文本文件输入和排序记录两个控件7.5.1使用Kettle实现数据排序双击设计区域的“文本文件输入”控件图标，打开设置界面（如图7-54所示），点击“文件或目录”右侧的“浏览”按钮，添加文件“D:\score.txt”，然后，点击“增加”按钮，执行效果如图7-55所示。图7-54添加文件7.5.1使用Kettle实现数据排序图7-55添加文件以后的效果7.5.1使用Kettle实现数据排序在“内容”选项卡中，设置分隔符为分号“;”（如图7-56所示）。图7-56设置“内容”选项卡7.5.1使用Kettle实现数据排序在“字段”选项卡中（如图7-57所示），点击“获取字段”按钮，成功获取字段以后的效果如图7-58所示。图7-57获取字段图7-58获取字段成功以后的效果7.5.1使用Kettle实现数据排序这时，点击界面（如图7-58所示）底部的“预览记录”按钮，就可以预览数据（如图7-59所示）。最后，点击界面底部的“确定”按钮，完成“文本文件输入”控件的设置。图7-59预览数据7.5.1使用Kettle实现数据排序双击设计区域的“排序记录”控件图标，打开设置界面（如图7-60所示），在“字段名称”下拉列表中选择“score”，在“升序”下拉列表中选择“是”，然后点击“确定”按钮完成设置。全部设置完成以后，需要保存设计文件。图7-60排序记录设置界面7.5.1使用Kettle实现数据排序4.执行转换在转换设计界面中（如图7-61所示），点击三角形按钮开始执行转换，在弹出的界面中点击“启动”，如果转换执行成功，会显示如图7-62所示的效果，在两个控件图标上都会显示绿色的勾号。这时，在“执行结果”的“Previewdata”选项卡中就可以预览排序后的数据（如图7-63所示）。图7-61运行转换图7-62转换执行成功7.5.1使用Kettle实现数据排序图7-63排序后的数据7.6数据加载7.6数据加载7.6.1把本地文件加载到HDFS中（请直接参考教材）7.6.2把HDFS文件加载到MySQL数据库中7.6.2把HDFS文件加载到MySQL数据库中这里给出一个实例，演示如何使用Kettle把HDFS文件导入到MySQL数据库中，具体包括如下步骤：新建HDFS文件；创建数据库；建立转换；创建MySQL连接和Hadoop连接；设计转换；执行转换。7.6.2把HDFS文件加载到MySQL数据库中1.新建HDFS文件在Windows系统中打开一个cmd窗口，启动Hadoop。在“D:\”目录下新建一个文本文件student.txt，其内容如图7-140所示，文件的第1行是字段名称，包括no、name、sex和age，字段之间用“|”隔开，其余行都是记录，字段之间也是用“|”隔开。图7-140student.txt文件内容7.6.2把HDFS文件加载到MySQL数据库中在cmd窗口中执行如下命令，把本地文件student.txt上传到HDFS系统的根目录下：>cdc:\hadoop-3.1.3\bin>hadoopfs-putD:\book.txthdfs://localhost:9000/可以继续执行如下命令查看HDFS中student.txt的内容：>hadoopfs-cathdfs://localhost:9000/student.txt或者，也可以打开浏览器，访问“http://localhost:9870”，使用HDFS的WEB管理界面查看文件内容。7.6.2把HDFS文件加载到MySQL数据库中2.创建数据库在Windows系统中启动MySQL服务，打开MySQL命令行客户端，执行如下SQL语句创建数据库：CREATEDATABASEkettle;继续执行如下SQL语句创建student_table表：USEkettle;#------------创建表student_tableDROPTABLEIFEXISTSstudent_table;CREATETABLEstudent_table(noint,nameVARCHAR(10),sexVARCHAR(2),ageint);7.6.2把HDFS文件加载到MySQL数据库中3.建立转换在Spoon主界面的“主对象树”栏目中，在“转换”上面（如图7-141所示）单击鼠标右键，在弹出的菜单中点击“新建”。点击Spoon主界面左上角的“保存”图标，把这个转换保存到某个路径下并且名称为“hdfs_to_mysql”。图7-141新建“转换”7.6.2把HDFS文件加载到MySQL数据库中3.创建MySQL连接和Hadoop连接参照本章7.4.2节的内容，建立一个名称为“mysql”的数据库连接（如图7-142所示）。图7-142建立数据库连接7.6.2把HDFS文件加载到MySQL数据库中参照本章7.6.1节的内容，建立一个名称为“Hadoop3”的Hadoop连接（如图7-143所示）。图7-143建立Hadoop连接7.6.2把HDFS文件加载到MySQL数据库中4.设计转换在Spoon主界面的“核心对象”的“BigData”里面，找到“Hadoopfileinput”控件，放置到设计区域，在“核心对象”的“输出”里面，找到“表输出”控件，放置到设计区域，为两个控件建立连线（如图7-144所示）。图7-144放置“Hadoopfileinput”和“表输出”控件7.6.2把HDFS文件加载到MySQL数据库中在设计区域双击“Hadoopfileinput”控件图标，打开设置界面（如图7-145所示），用鼠标点击“Environment”下面的空白单元格，会出现如图7-146所示的下拉列表，选中“Hadoop3”。图7-145“Hadoopfileinput”设置界面7.6.2把HDFS文件加载到MySQL数据库中图7-146设置Environment7.6.2把HDFS文件加载到MySQL数据库中点击“File/Folder”下面的空白单元格，会出现如图7-147所示的效果，点击省略号按钮，会弹出如图7-148所示的界面，选中HDFS中的student.txt文件，点击“OK”按钮，返回到“Hadoopfileinput”设置界面。图7-147设置File/Folder7.6.2把HDFS文件加载到MySQL数据库中图7-148选中HDFS中的student.txt文件7.6.2把HDFS文件加载到MySQL数据库中点击“Hadoopfileinput”设置界面的“内容”选项卡，会出现如图7-149所示的界面，“文件类型”选择“CSV”，把“分隔符”设置为“|”，“头部”后面的勾号选中，设置“头部行数量”为1。图7-149设置分隔符7.6.2把HDFS文件加载到My