《ETL数据预处理技术》课件-任务三 利用kettle抽取数据

ETL数据预处理技术任务三利用kettle抽取数据数据的采集与获取是数据预处理及数据分析中的第一步，其来源可以是数据采集器、外部文件、数据库等。ETL数据抽取数据存在的形式多种多样，文本文件、web网页、数据库数据等，实际应用中往往需要将数据按照用户要求的形式呈现，ETL中的数据抽取技术则应用与此。子任务3.1利用kettle抽取文本文件01.抽取csv文件02.03.目录抽取tsv文件抽取excel文件CONTENTS文本文件格式是一种由若干行字符构成的计算机文件，其种类繁多。文本文件存在于计算机文件系统中。通常，通过在文本文件最后一行后放置文件结束标志来指明文件的结束。文本文件是指一种容器，而纯文本是指一种内容。文本文件可以包含纯文本。TSV特别关注1.1CSV文件抽取文件概念CSV是Comma-SeparatedValues的缩写，即逗号分隔值。CSV文件是用逗号分隔数据字段的文件01文件打开方式CSV文件是以纯文本形式存储表格数据（数字和文本），纯文本意味着该文件是一个字符序列。CSV文件可通过Excel、txt、Notepad++等文本编辑器操作02文件构成CSV文件是由任意数目的记录组成，记录之间以某种换行符分隔；每条记录由字段组成，字段之间的分隔符常见的有逗号或制表符。通常，整个文件中的所有记录都有完全相同的字段序列。031.1CSV文件抽取序号

规

则1文件开头不能留空，以“行”为单位2文件可含或不含列名，若含有列名，则位于文件第一行3文件中的一行数据不能跨行，行与行间不可存在空行4文件中以英文半角逗号作为分隔符，若列为空，也要表达空列的存在5文件中的列内容，若存在英文半角单引号，则替换成半角双引号转义6在文件读写时，引号和逗号操作规则可以互逆7文件中的编码格式不作限制，可以是ASCII，也可以是Unicode或者UTF8等编码格式8文件中不支持数字或特殊字符。CSV文件的实现规则1.1CSV文件抽取任务提出：通过Kettle工具抽取CSV文件csv_extract.csv中的数据，并保存至数据库zhao的数据表csv_zmz中。1.1CSV文件抽取效果如下图：csv_extract.csv文件的部分数据1.1CSV文件抽取数据准备CSV文件csv_extract.csv如下图1.1CSV文件抽取Step1：创建转换启动Kettle，创建转换，命名为“csv转换”

，并添加“CSV文件输入”控件、“表输出”控件以及Hop跳连接线如下图：1.1CSV文件抽取Step2：配置“CSV文件输入”双击“CSV文件输入”控件，进入“CSV文件输入”界面。单击【浏览】按钮，选择要抽取的文件csv_extract.csv（实操中，文件路径自定）。如下图：1.1CSV文件抽取Step2：配置“CSV文件输入”参数名称说明

基础参数步骤名称表示CSV文件输入组件名称，在单个转换工程中，名称必须唯一。默认值为【CSV文件输入】的组件名称文件名表示读取源数据的CSV文件名称，单击【浏览(B)】按钮，寻找存储计算机上的目录和文件，默认值为空列分隔符表示每个数据之间的分隔符，大多数情况用英文的逗号分隔，也有使用制表符TAB分隔的，单击【插入制表符(TAB)】按钮，可以输入制表符。默认值为“,”英文逗号封闭符表示封闭起一个数据、保持其完整性的一对符号，大多数情况用英文的双引号封闭，默认值为“”””英文双引号NIO缓存大小表示定义Java读取文件缓冲区的大小，默认值为50000简易转换表示是否启用简易转换。如果选择了简易转换，那么可以尽可能避免不必要的数据类型转换，从而显著提高性能。默认值为√包含列头行表示源文件是否包含有列名（字段名称）的标题行，大多数文件均包含。包含标题行时，在获取字段时，读取文件头部作为字段名；不包含时，在获取字段时，则不读取文件头部作为字段名。默认值为√将文件添加到结果文件中表示是否将文件名添加到结果文件中。默认值为空【CSV文件输入】组件参数包含组件的基础参数和字段参数，有关参数的说明如表所示。1.1CSV文件抽取Step2：配置“CSV文件输入”单击上图中【获取字段】按钮，kettle自动检索CSV文件，并对文件中的字段类型、格式、长度、精度等属性分析，如左图.1.1CSV文件抽取Step2：配置“CSV文件输入”单击【预览】按钮，查看文件csv_extract.csv的数据是否抽取到CSV文件输入流中。如左图，关闭预览数据后单击【确定】，完成配置。1.1CSV文件抽取Step3：配置“表输出”双击“表输出”控件，进入“表输出”控件的配置界面。如左图。1.1CSV文件抽取Step3：配置“表输出”单击【新建】按钮，配置数据库连接，如左图，配置完成后单击【确认】按钮。提示：配置数据库连接，务必测试是否成功1.1CSV文件抽取Step3：配置“表输出”单击目标表右侧的【浏览】按钮，获取目标表，即数据表csv_zmz；勾选“指定数据库字段”的复选框。如左图：1.1CSV文件抽取Step3：配置“表输出”切换到“数据库字段”选项卡，单击其中的“输入字段映射”按钮，弹出“映射匹配对话框。单击【猜一猜】按钮，kettle进行自动匹配，如左图：1.1CSV文件抽取Step3：配置“表输出”单击“确定”按钮，“表输出”控件配置的最终效果，如右图所示。1.1CSV文件抽取Step4：运行转换单击转换工作区顶部的按钮，运行创建的csv_extract转换。1.2TSV文件抽取文件概念TSV是Tab-separatedvalues的缩写，即制表符分隔值。使用制表符分隔数据字段的文件被称为制表符文件01文件打开方式TSV文件可通过Excel、txt、Notepad++等文本编辑器操作02文件构成制表符文件中的数据以表格结构储存，每一行储存一条记录，每条记录的各个字段间使用制表符分隔031.2TSV文件抽取左对齐式制表符居中式制表符右对齐式制表符小数点对齐式制表符竖线对齐式制表符制表符的种类说明：通过制表符分隔的文本数据与未使用制表符分隔的文本数据相比，前者较直观，同时也便于对数据进行抽取操作1.2TSV文件抽取任务提出：通过Kettle工具抽取TSV文件tsv_extract.tsv中的数据，并保存至数据库zhao的数据表tsv中。1.2TSV文件抽取效果如下图：TSV文件tsv_extract.tsv数据表tsv1.2TSV文件抽取Step1：创建转换1.2TSV文件抽取Step2：配置“文本文件输入”在“文本文件输入“对话框中，单击【浏览】按钮，选择要抽取的文件tsv_extract.tsv，然后点击【增加】，将文件加载到”选中的文件”中1.2TSV文件抽取Step2：配置“文本文件输入”切换“内容”选项卡，清除分隔符处默认分隔符“；”并单击【InsertTAB】按钮，在分隔符处插入一个制表符；取消勾选“头部”复选框。说明：若不取消勾选“头部”复选框，在进行操作数据抽取时会排除文件第一行的数据1.2TSV文件抽取Step2：配置“文本文件输入”单击“字段”选项卡，切换到“字段”选项卡界面，根据TSV文件的内容添加对应的字段名称，并指定数据类型1.2TSV文件抽取Step2：配置“文本文件输入”单击【预览记录】按钮，查看文件tsv_extract.tsv的数据是否成功抽取到文本文件输入流中。关闭预览数据，单击【确定】，完成配置。1.2TSV文件抽取Step3：配置“表输出”数据库连接设置目标表确定1.2TSV文件抽取Step3：配置“表输出”勾选对话框图中的“指定数据库字段”复选框，用于将数据表tsv的字段与文件tsv_extract.tsv的字段进行匹配，如右图。1.2TSV文件抽取Step3：配置“表输出”单击图中的“数据库字段”选项卡，在“映射匹配”中【猜一猜】，完成字段映射。1.2TSV文件抽取Step3：配置“表输出”单击映射匹配对话框的【确定】按钮，映射结果如右图，单击【确定】按钮，完成“表输出”控件设置。1.2TSV文件抽取Step4：运行转换运行转换，查看执行结果的【步骤度量】，可以看到“表输出”控件中写入了5条记录，如左图。在SQLyog中查看，数据表tsv内容如表右图。1.3Excel文件抽取提出任务：通过Kettle工具抽取Excel文件student.xlsx中的数据,并保存至数据库zhao的数据表stu中。1.3Excel文件抽取提出任务：效果如下图：1.3Excel文件抽取数据准备stu.xlsx的excel文件MySQL中创建数据表stu1.3Excel文件抽取Step1：创建转换1.3Excel文件抽取Step2：配置“Excel输入”打开“Excel输入”对话框，将文件student.xlsx导入提醒：表格类型默认是XLS格式，实际操作中需正确选择，否则抽取错误1.3Excel文件抽取Step2：配置“Excel输入”单击【工作表】选项卡，再单击下方的单击【获取工作表名称】按钮，出现如下左图的对话框，选择表名“sheet1”，再单击“>”添加到“你的选择”中，单击【确定】按钮，在“要读取的工作表列表”的右侧“起始行”和“起始列”中均输入0。因为Excel文件中有效数据是从第1行第1列开始的。如右图所示。1.3Excel文件抽取Step2：配置“Excel输入”切换到“字段”选项卡下，单击【获取来自头部数据的字段】，效果如下图。单击【确定】完成配置1.3Excel文件抽取Step3：配置“表输出”数据库连接目标表选择1.3Excel文件抽取Step3：配置“表输出”设置硬着字段，如左图。单击【确定】完成配置1.3Excel文件抽取Step4：运行转换、查看结果保存转换，运行，通过【预览数据】可以看到导出的数据小结逗号间隔符文件，配置其控件时，需设置间隔符CSV文件抽取制表符文件，一行一条记录，表格形式，配置控件时，设置间隔符为TABTSV文件抽取配置控件时，务必注意原文件内容中是否包含字段名，其次要确定数据所在工作表的名称及其起始范围Excel文件抽取子任务3.2利用kettle抽取web文件网页上数据形式多样，是我们日常生活、工作不可或缺的资源，这些数据资源若需“为我所有”，则需要下载或通过ETL技术进行获取。Web数据通常有JSON、XML、HTML三种形式，三种不同格式的数据利用kettle导出时使用不同的控件完成。01.HTML数据抽取02.03.目录XML数据抽取JSON数据抽取CONTENTS2.1HTML数据抽取1HTML，即超文本标记语言，它包含了一套标记标签，通过这些标签可以将网络上的文档格式统一，使分散的Internet资源连接为一个逻辑整体2HTML文本是由HTML命令组成的描述性文本，HTML命令可以说明文字，图形、动画、声音、表格、链接等3HTML可以以文档的形式展示，HTML文档中包含HTML标签和纯文本4超文本是一种组织信息的方式，它通过超链接方法将文本中的文字、图表与其他信息媒体相关联。将分布在不同位置的信息资源用随机方式进行连接，方便我们检索信息2.1HTML数据抽取123自动化方法中，网页模板的分析部分也交给了计算机进行，仅仅需要很少的人工参与或完全不需要人工参与，因而更加适合大规模、系统化、持续性的Web数据抽取基于数据库技术的HTML网页抽取技术经历三个阶段半自动化方法，应用网页模板抽取数据，从而生成具体包装器的部分被计算机接管，而网页模板的分析仍然需要人工参与人工方法，通过程序员人工分析出网页的模板，借助一定的编程语言，针对具体的问题生成具体的包装器。2.1HTML数据抽取提出任务：通过Kettle工具抽取HTML网页的数据，并保存至数据库extract中的数据表html中。2.1HTML数据抽取以抽取“豆瓣电影排行榜”网页的超链接数据为例，进行抽取数据，豆瓣电影排行榜页面的部分内容如图所示。同时需要创建一个数据表ex_html数据准备2.1HTML数据抽取Step1：创建转换通过使用Kettle工具，创建一个转换“html测试”

，并添加“自定义常量数据”输入控件、“HTTPclient”查询控件和“Java代码”脚本控件，如左图所示。2.1HTML数据抽取Step2：配置“自定义常量”在“元数据”选项卡中，定义一个字段常量filename（可以自行定义名称）并指定数据类型String，如下左图；然后切换到“数据”选项卡，添加html形式数据所在的URL，即/chart。如下右图：2.1HTML数据抽取Step2：配置“自定义常量”单击【预览】按钮，可以看到一行数据抽取到输入流中2.1HTML数据抽取Step3：配置“HTTPclient”双击“HTTPclient”控件，打开对话框，勾选图中的“从字段中获取URL？”的复选框；在“URL字段名”处的下拉框中选择URL字段名，即filename；在“结果字段名”处指定结果字段名称，这里选择默认的结果字段result。2.1HTML数据抽取Step4：配置“Java代码”双击“Java”控件，进入“Java代码”对话框。2.1HTML数据抽取Step4：配置“Java代码”双击“CodeSnippits”→“Commonuse”→“Main”，添加Java脚本代码的主方法，即程序入口。在“Java代码”控件中的代码框编写抽取HTML网页数据的Java脚本代码。说明：截图中为部分代码。其中root密码、数据库名、数据表名均需自己定义2.1HTML数据抽取Step4：配置“Java代码”单击“Java代码”控件中的“字段”选项卡，用于添加新生成的字段；入下图左所示；切换“参数”选项卡，用于传入参数。如下图右所示。单击【确定】完成配置。2.1HTML数据抽取Step5：运行转换，查看结果提示：现在大多数网站均有反爬机制，可选择公开的门户网站进行数据抽取练习2.2XML数据抽取XML文件2.2XML数据抽取XML的主要用途：XML可以将数据从HTML中分离XML可以简化数据传输XML可以简化数据共享XML简化平台变更XML可以使数据充分利用XML可用于存储数据XML可以用于创建新的互联网语言2.2XML数据抽取了解XPath：XPath即为XML路径语言（XMLPathLanguage），它是一种用来确定XML文档中某部分位置的语言。XPath基于XML的树状结构，提供在数据结构树中找寻节点的能力。起初XPath的提出的初衷是将其作为一个通用的、介于XPointer与XSL间的语法模型。但是XPath很快地被开发者采用来当作小型查询语言。2.2XML数据抽取XPath使用路径表达式在XML文档中选取节点，下面列出了最有用的路径表达式：2.2XML数据抽取下表列出了一些路径表达式以及表达式的结果：2.2XML数据抽取现有XML文件的内容如下：准备数据2.2XML数据抽取Step1：创建转换启动Kettle，创建一个转换“抽取XML数据”，并添加输入控件“GetdatafromXML”控件、输出控件“表输出”以及Hop跳连接线，具体如左图所示。2.2XML数据抽取Step2：配置“Getdatafromxml”控件双击“GetdatafromXML”控件，进入“XML文件输入”对话框，单击【浏览】按钮，选择要抽取的XML文件“xml示例.xml”,再点击【增加】，将文件导入。2.2XML数据抽取Step2：配置“Getdatafromxml”控件“内容”选项卡设置“字段”选项卡设置2.2XML数据抽取Step2：配置“Getdatafromxml”控件单击【预览】按钮，查看是否有数据。如左图，关闭预览，点击【确定】按钮，完成配置。2.2XML数据抽取Step3：配置“表输出”控件数据库连接目标表设置2.2XML数据抽取Step3：配置“表输出”控件勾选“指定数据库字段”，然后切换“数据库字段”选项卡,再单击【输入字段映射】按钮，进行字段映射2.2XML数据抽取Step3：配置“表输出”控件“表输出”控件配置的最终效果，如图所示。2.2XML数据抽取Step4：运行转换运行转换，查看步骤度量结果，如左图。2.2XML数据抽取Step5：查看结果通过SQLyog工具，查看数据表xml是否已成功插入6条数据。2.3JSONL数据抽取概念JSON（JavaScriptObjectNotation，即JS对象标记）是一种轻量级的客户端和服务器之间的数据交换格式，它是基于ECMAScript（欧洲计算机协会制定的js规范）的一个子集，从JavaScript脚本语言中演变而来的，采用完全独立于编程语言的文本格式来存储和表示数据。01特点JSON有简洁、清晰的层次结构，因此使得JSON成为理想的数据交换语言。02优势JSON易于程序开发者阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输的效率032.3JSONL数据抽取JSON的语法格式为：2.3JSONL数据抽取JSON的对象格式：用不同的语言描述一个简单的对象，书名+内容，体现JSON的轻量级ETL“ETL很有用”ETL“ETL很有用”2.3JSONL数据抽取JSON的对象格式：用不同的语言描述一个简单的对象，书名+内容，体现JSON的轻量级ETLETL很有用ETLETL很有用2.3JSONL数据抽取通过Kettle工具抽取json_extract.json文件中的数据保存至MySQL数据库的数据表json_zmz中。任务提出:2.3JSONL数据抽取json_extract.json文件中的部分内容如右图：明确要抽取的数据为id、及data中的field、value三部分。数据准备:2.3JSONL数据抽取结合抽取目的，需要在MySQL数据库zhao中创建数据表json_zmz，如下图（通过SQLyog创建）数据准备:2.3JSONL数据抽取Step1：创建转换并添加两个“JSONinput”控件、“表输出”控件以及Hop跳连接线，如左图：2.3JSONL数据抽取Step2：配置JSONinput输入双击“JSONinput”图标，进入“JSON输入”界面，单击【浏览】，选择要抽取的JSON文件（实操中，文件路径自定），2.3JSONL数据抽取Step2：配置JSONinput输入单击【增加】按钮，将所选文件添加到”选中的文件“处，如左图：2.3JSONL数据抽取Step2：配置JSONinput输入单击”字段“选项卡，添加要抽取的数据字段（这里采用分层抽取数据字段，即先抽取id和data，再从data中抽取filed和value字段，注意：路径的写法为$..字段名2.3JSONL数据抽取Step3：配置JSONinput2输入2.3JSONL数据抽取Step3：配置JSONinput2输入切换到“字段”选项卡中做如下设置，单击“确定”完成。2.3JSONL数据抽取Step4：配置“表输出”2.3JSONL数据抽取Step4：配置“表输出”单击“输入字段映射”，弹出映射匹配对话框，逐个字段进行映射。由于此时的源字段与目标字段不完全相同，所以不可用【猜一猜】按钮2.3JSONL数据抽取Step4：配置“表输出”单击“映射匹配”中的【确定】，字段匹配如左图。单击“表输出”中的【确定】完成“表输出”配置。2.3JSONL数据抽取Step5：运行转换，查看结果单击转换工作区的运行按钮，启动转换。执行结果中的“步骤度量”如下图。2.3JSONL数据抽取Step5：运行转换，查看结果小结超文本标记语言，采用超链接的方法将网络中的资源逻辑连接在一起HTML数据抽取可扩展的数据标记语言，用于数据传输和存储，其标签没有预定义格式XML数据抽取轻量级的客户端与服务器交换数据的文件形式，数据以键值对形式存在JSON数据抽取子任务3.3数据库文件抽取是按照数据结构来组织、存储和管理数据的仓库。在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提数据库Database数据库共有两种