《数据清洗》课件 第5章 数据抽取.pdf

数据清洗大数据应用人才培养系列教材第五章数据抽取5.1 文本文件抽取52 Web数据抽取5.3 数据库数据抽取5.4 增量数据抽取 习题，5.1文本文件抽取第五章数据抽取1.文本文件抽取文本文件抽取的基本方式是通过文本结构分析器或者人工分析，找出文本文件中所用到 的分隔符，把分隔符左右两边的内容作为两个字段值进行抽取。文本文件抽取实例如下：需要被抽取的文本文件TxtExtract_test.txt,文件内容如图5-1所示。量 TxtExtra ct testtxt-记事本文件(F)编辑(E)格式(0)查看(V)帮助(H)April|001|2016-4-7图5-1待抽取的文本文件 5.1文本文件抽取第五章数据抽取1.文本文件抽取步骤圜 TxtExtract_test.txt-记事本文件(F)编辑(E)格式(0)查看(V)帮助(H)April|001|2016-4-7人工分析文本文件中的分隔符，TxtExtract_test.txt文件的分隔符为|；2打开Kettl e,在左侧导航栏中，从主对象树中选择转换，右键选择新建，创建一个新的转换trans_txtExtract_test,双击DB连接,创建新的数据连接,本例创建一个MySQL的数据连接。5.1文本文件抽取第五章数据抽取1.文本文件抽取步骤2.1步骤2,MySQL的数据连接,入图5-2所示E1另回国后主对象的 Q横心对象0 5532!trans_test 黑 trans_txtExtract_test 汉a 瑞忌日岛 富|ioo%_v 口精换v tran$_txtExtrdct_test口 DB0Perspective:Data Integration图5-2创建MySQL数据连接浏黑S1实现图4-2连接成功的前 提条件还需要在本机的 MySQL数据库服务器上创 建test数据库，否则点击“测试”，会提示“UnKno wn Database test，提示test数据库未 知的异常。5.1文本文件抽取第五章数据抽取1.文本文件抽取步骤3在核心对象中，选择输入，双击文本文件输入，在创建的转换 tra n s_txt Extra ct_test工作区中添加文本文件输入的控件对象。双击打开“文本文件输入”控件,进入文本文件输入属性设置。H 在文件选单中点击浏览，在弹出的文件浏览器中选中需要被抽取的文本文 件TxtExtract_test.tx;5.1文本文件抽取第五章数据抽取1.文本文件抽取步骤点击增加，把文件添加到选中的文件列表中，如图5-3所示从以JI箝涉1覆受文件名口防文kU 展示文件内.砺WTUM行的内毒脸 9)血8 WSMQ图5-3添加需要抽取的文本文件 5.1文本文件抽取第五章数据抽取1.文本文件抽取步骤选择内容选单页，不改变默认的文件类型，修改分隔符为第1）步中分析 的T,把头部后的复选框取消选中，其余保持默认值，如图5-4所示;图5-4分隔符设定5.1文本文件抽取第五章数据抽取1.文本文件抽取先骤_选择字段，根据文本文件内容，键入3个新字段名称，name、id、date,再分别指定字段类型，这里均指定为String型；点击页面下方的预览记录 则把从文本文件中的内容根据设定的字段进行抽取并预览显示，如图4-5所示;文本文件端入 一 口 X4.4霹称:$：谢入文件I内容 错课处理 过港 字段 其他端出字段金名称 类型 格式 位置 长度 箱度 货币类型 4强 分组 Null if 默认 去除空字符宝方式 重复1 name String 芸拽左右两诙空梢2 id String 去掉左右两送交格3 date String 去捶左右两质空格-X步要文本文件瑜入的数据(1 rows)#name id date1 April 001 2016-4-7关闭(C)显示日志(L)Help|Wg)图5-5预览文本文件抽取数据)5.1文本文件抽取第五章数据抽取制表符文件在文本文件的编辑中，如果把一系列的数据做类似于表格形式的分隔，让所有的数 据信息看起来更容易识别，这就是制表位所需要实现的功效了。制表位，从本意上 说，指的是文字或符号在水平标尺上的位置，它指定了文字缩进的距离或一栏文字 开始的位置，使用户能够向左、向右或居中对齐文本行；或者将文本与小数字符或 竖线字符对齐。在众多操作系统和常用的文本编辑器中，键盘上的TAB键点击一次 作为插入一个制表符的默认按键。)5.2文本文件抽取第五章数据抽取制表位左对齐，居中对齐，右对齐，小数点对齐和竖线对齐等在输入一项数据之后，按一下TAB键，光标就会根据制表位的设置，在数据后面插入 一个制表符优势通过制表符分隔得文本数据，在识别上比不是用制表符的文本提高了几个档次，同时 对于文本数据的抽取也大有裨益大数据应用人才培养系列教材第五章数据抽取5.1 文本文件抽取5.2 Web数据抽取5.3 数据库数据抽取5-4 量数超抽习题)5.2 Web数据抽取第五章数据抽取WEB 数据抽取分类HTML 文 件抽取人工方法进行HTML的数据抽取，主要的任务就是通过人工对网页源码结果的分析，借助 编程语言，使用正则表达式，匹配HTML中的标签和标签属性，把有用的需要的数据过滤 出来，实现HTML文件的数据抽取工作。JSON 数 据抽取JSON的数据表现直截了当，通过花括号。包裹，冒号：前面是数据的键，后面是数据的 值，多个数据之间用逗号，分隔，若存在Jso nArray,则用口把数组的内容包裹起来。完 全免除了对HTML源码标签和属性的分析，解决了人力负担XML数 据抽取在kettl e中可以使用两种方式读取和解析XML文件,分别是Get data fro m xm和XML Input Stream(StAX)5.2 Web数据抽取第五章数据抽取JSON数据抽取实例第一步选择JSON文件，文件名为chinacitylist.js。如果使用kettle读取JSON文件，则文 件的后缀名需要改为js,ilkettle把该文件作为一个JavaScript文件来读取。第二步 口输入在kettle的核心对象树中诜择Input,而不是输入从Input中，诜中 JSON Input对象，双击或者拖动到转化的编辑区域，根据读取的JSON文件的内 容修改该对象名称为JSONInputChinaCity。第三步双击该对象，进行属性设置。在文件选项页中先浏览需要读取的文件，再选择增 加，注意JSON文件的后缀名已经改为了js,如图5-6所示 5.2 Web数据抽取第五章数据抽取先浏览找到需要抽取的JSON文件;再增加把选中的文件添加到选中的文件列表中Jso n输入步骤名称|ISONInputChinaCity文件内容I字段用峥出字阕从字段获取源源定义在一个字段里？源切总？口Help确定(O)预览(P)取消(9图5-6添加需要读取的JSON文件)5.2数据库数据清洗步第五章数据抽取在字段选项页中需要抽取的JSON文件的字段进行指派，这也是JSON文 件抽取和之前提到的所有文件类型抽取的最大不同，JSON Input缺少自动获取JSON文件的字段r=t=1 jtta/Tfu人工手动的进行输入，而且路径必须输入准确，并且对于JSON文件中需要抽取的内容JSON Input也有一些特殊要求。如 图5-7所示图5-7设置JSON字段)5.2 Web数据抽取第五章数据抽取JSON 抽取字 段设置每个JSON文件的字段内容均不相同，所以不同的JSON文件在抽取的时候都 必须重新设置字段；字段的名字是在抽取后显示的列名，可以使用中文；字 段的路径必须根据JSONPath规定的符号来设置，如图5-8所示JSONPathDescription、$the ro o t o bject/el ementthe current o bject/el ement-o r chil d o perato rn/aparent o perato rrecursive descent.JSONPath bo rro ws this syntax fro m E4X.*wil dcard.Al l o bjects/el ements regardl ess their names.图5-8 JSONPath的符号规范)5.2 Web数据抽取第五章数据抽取第五步点击预览，查看抽取效果，如图5-9所示第六步从左侧核心对象选择输出，Exce输出，拖动到工作区，把JSONInput的 主输出指向该Exce输出，在Exce输出中指定需要保存的xls路径和文件名，点击执行，就可以通过kettle抽取JSON文件中指定了路径的数据键，把这些内容抽取并保存到 Excel文件中。苗 预览数据步骤 JSONInputChinaCity 的数据(1000 ro ws)#citycntyidl atIo npro v1南子岛中国CN10131023011.26114.20海南2原中国CN10101010039.904000116.391000直辖市3海淀中国CN10101020039.590000116.170000直辖市4朝阳中国CN10101030039.570000116.290000直辖市5蹴中国CN10101040040.147000116.716000直辖市6怀柔中国CN10101050040.643000116.581000直辖市7通州中国CN10101060039.550000116.380000直辖市8昌平中国CN10101070040.206000116.165000直辖市9场中国CN10101080040.525000116.148000直辖市1丰台中国CN10101090039.859000116.286000直辖市1.石器山中国CN10101100039.560000116.110000直辖市1.大兴中国CN10101110039.639000116.475000直辖市图5-9 chinacityl istjs文件抽取预览大数据应用人才培养系列教材第五章数据抽取5；文本文件抽取5.2 Web数据抽取5.3 数据库文件抽取5.4 增量数据抽取 习题，5.3数据库文件抽取第五章数据抽取数据库数据抽取数据导入导 出ETL工具抽取数据导入导出一般涉及的问题就是相同类型数据库的备份和还原。因为数 据库所包含的除了数据表、视图、触发器等结构性工具性的成分，最多的 内容则是其中的数据信息。为了防止数据库服务器异常，恶意攻击，数据 库管理员的误操作的情况发生，数据的导入导出作为最基本的一项数据库 抽取操作变得尤为重要 不同DBMS之间也是存在较大区别的。由于每种DBMS之间的SQL语法和 变量类型也存在差异，所以每一种DBMS的脚本都是专用的，无法直接通 过DBMS进行直接迁移，把数据从一个数据库管理系统DBMS中把数据库 迁移到另一个DBMS中，使用ETL工具明显是一个高效的选择。SQL到NOSQL抽取把关系型数据库数据迁移到非关系型数据库中，5.3数据库文件抽取第五章数据抽取ELT工具抽取从MySQL中把数据库迁移到MS SQLServer第一步使用Kettle创建2种数据连接，一种针对于MySQL,一种针对MS SQLServer,假定两种DBMS都安装在本地。MySQL的数据库名为world，假设抽取其中 那个的country表，同理在MS SQLServer上需要创建一个同名数据库（数据 库名字可以修改，为了保证数据抽取时的一致性，建议同名），并根据源数 据库的country表字段创建新库的数据表。如图5-105-11所示 自主对象树，核心对象浏览器 _/叵转换/X mysql2mssql/口 DB连接0 mysql_world 1 world_mssql图5-10创建2个不同DBMS的数据源连接，5.3数据库文件抽取第五章数据抽取数据源连接设置数据库连接连接名称：.一wo rl d_mssql连接类型：KingbaseES 人主机名称：LucidDBl o cal ho st6MS Access数据库名堂I ms SOL ServerMS SQL Server(Native)wo rl d-rMaxDB(SAP DB)Mo netDB MySQLNative Mo ndrian Neo viewNete77a示例名称：端口号：14338连接方式：用户名：II Native(JDBC)|ODBCJNDI密码：测试 特征列表 浏览图5-10设置MS SQLServer数据连接，5.3数据库文件抽取第五章数据抽取ELT工具抽取从MySQL中把数据库迁移到MS SQLServer第二步从核心对象树中选择输入，表输入，并拖动到工作区中，选择 源数据库连接，并写好相关的SQL语句。由于country表中数据较多，本例中 使用where执行Continent为Asia。如图5-12所示表输入Xn涵简易转换口 替换sol语句里的变量n图5-11设置表输入，5.3数据库文件抽取第五章数据抽取ELT工具抽取从MySQL中把数据库迁移到MS SQLServer第三步从核心对象树中选择转化，字段选择，并拖动到工作区中,按 住Shift键和鼠标左键从表输入拖动连接线到字段选择，然后双击字段选择，设置需要显示在目标数据表中的字段。从核心对象树中选择输出”，表输出，并拖动到工作区中，按住Shift和鼠标左键从字段选择拉一根连接线到表输出。双击表输出，在表输出 属性页中选择数据库连接为之前设置的SQLServer连接world_mssql,目标表选中country，指定数据库字段复选框为选中状态。第四步在数据库字段选项页中点击获取字段，由于输入表结构和输出表结 构基本一致（部分数据类型不同），Kettle会自动匹配输入流中的字段名和 输出表中的字段。第五章数据抽取ELT工具抽取从MySQL中把数据库迁移到MS SQLServer五步点击运行这个转换“，查看日志是 否完成，提示表输出完成处理，Spoon转换完成则表示从MySQL到 MS SQLServer的数据抽取已经成功 完成。通过Visual.Net查看MS SQLServer 的 world 数据库 country 表，查询到所有亚洲国家的数据。如 图5-12所示。dbo.co untry 数周*XC%，“屹SWD）：1000 T T|Name|Co ntinent|Regio n|SurfaceAr.|IndepYear|Po pul atio n|LifeExpec.|GNPAfghanistan.So uthern.65209019192272000045.95976United Ara.Middl e Ea.836001971244100074.137966Armenia.Middl e Ea.298001991352000066.41813Azerbaijan.Middl e Ea.866001991773400062.94127Bangl ades.So uthern.143998197112915500060.232852Bahrain.Middl e Ea.6941971617000736366Brunei.So utheast.5765198432800073.611705Bhutan.So uthern.470001910212400052.4372China.Eastern As.9572900-1523127755800071.4982268Cyprus.Middl e Ea.9251196075470076.79333Geo rgia.Middl e Ea.697001991496800064.56064Ho ng Ko n.Eastern As.1075NULL678200079.5166448Indo nesia.So utheast.190456919452121070006884982India.So uthern.32872631947101366200062.5447114Iran.So uthern.164819519066770200069.7195746Iraq.Middl e Ea.43831719322311500066.511500Israel.Middl e Ea.210561948621700078.697477Jo rdan.Middl e Ea.889461946508300077.47526Japan.Eastern As.377829-66012671400080.73787042GNPOId36846 16274100 319666097 12460383 9177198246 5924173610 215002430572 160151NULL 985777051 4192638I Lo caINc Afganist Al-Imarf Hajastar Az?口 rba Bangl ad Al-Bahrc Brunei C Druk-Yu Zho ngq K?pro s.Sakartve Xiangga Indo nes Bharat/L Iran Al-Mraq Yisra?,el.Al-Urdu Niho n/hKazakstan.Kyrgyzsta.Cambo dia.So uth Ko r.Kuwait.Lao s.Lebano n.Sri Lanka.Macao.Mal dives.Myanmar.Mo ngo l ia.Mal aysia.So uthern.2724900 So uthern.199900 So utheast.181035 Eastern As.99434 Middl e Ea.17818 So utheast.236800 Middl e Ea.10400 So uthern.65610 Eastern As 18 So uthern.298 So utheast.676578Eastern As.1566500 So utheast.3297581991 19911953 19481961 19531941 1948 NULL 19651948 1921195716223000 63.24699000 63.411168000 56.546844000 74.41972000 76.15433000 53.13282000 71.318827000 71.8473000 81 6286000 62.245611000 54.92662000 67.322244000 70.824375162651213207492703712921712115706574919918037510436921323383 17675670 442544 303731746 1512915091 5940NULL 171028 933 97884Qazaqst Kyrgyzs K?d mpiTaehan Al-KuwaLao LubnanSri Lank.Macau/，Dhivehi Myanmi Mo ngo lMal avsi-生成随机数 获取系统信息 表输出图5-12增量抽取源表生成核心对象口/口TransformValidation输入Q钠随腿以生成随机的信用卡号 输出口图5-13通过关键字搜索核心对象，5.4增量数据抽取第五章数据抽取第四步生成随机数对象主要为了生成数据表的主键uuid,在生成随机数属性中，字段项中的类型属性值就有一个UUID类型可以选择，直接随机生成uuid 字段值第五步获取系统信息对象获取系统日期，为timejob表中createme和 update_time字段提供数据，和生成随机值对象类似，字段项的名称属性 需要手动输入，类型属性在下拉菜单中选取。第六步 表输出指定目标数据库和数据表，指定数据表字段，并设置流和表字段的映射 关系，设置方式和前面的示例一致，5.4增量数据抽取第五章数据抽取第七步点击左上角执行这个转化，如果执行结果无异常表示执行成 功。进入MySQL/01$MAXTIME把替换SQL语句里的变量选中，如图5-21所示。表输入行5歹U30允许简易转换口替换SQL语句里的变量国/L从步骤插入数据执行每一行？口记录数量限制 令0 Hel p 确定(O)预览(P)取消(C)图5-21根据时间戳从源表增量抽取数据，5.4增量数据抽取第五章数据抽取第二 十步表输出中指定数据库连接,目标表，并在主选项中选 择使用批量插入，如图5-22所示。图5-22表输出属性设置第五章数据抽取第二十创建作业，保存命名为增量抽取测试作业，添加 START和两个转化。START设置为不需要定时，转化1的转化设置属性选择设置增量抽取最大时间戳转化,并改名；转化2的转化设置属性选择设置增量抽取目标表 转化，并改名，设置3个对象之间的连接，如图5-22所示。START设置增量抽取最大时间戳设置增量抽取目标表图5-22根据时间戳从源表增量抽取数据，5.4增量数据抽取第五章数据抽取3 SELECT timejo b.uuid,4 time_jo bcreate_time,5 time_jo bupdate_time6 FROM tes t.time_jo b cc2cb6e3-8364-l l e7-8671-6bl c68.c9260692-8364-l l e7-8671-6bl c68.c61eel l l-8364-l l e7-8671-6bl c68.c31830c0-8364-l l e7-8671-6bl c68.c010960f-8364-l l e7-8671-6bl c684.bd09979e-8364-l l e7-8671-6bl c68.ba0223fd-8364-l l e7-8671-6bl c684.b6fab05c-8364-l l e7-8671-6bl c684.b3ee81cb-8364-l l e7-8671-6bl c68.b0e70e2a-8364-l l e7-8671-6bl c68.addf9a89-8364-l l e7-8671-6bl c684.aad7ffd8-8364-l l e7-8671-6bl c684.a7d0b347-8364-l l e7-8671-6bl c68.a4c83366-8364-l l e7-8671-6bl c68.al bf5b05-8364-l l e7-8671-6bl c684.9eb83584-8364-l l e7-8671-6bl c68.9bbl d353-8364-l l e7-8671-6bl c68.6ee2e2a2-8329-l l e7-8671-6bl c68.create time update time2017-08-17 23:57:402017-08-17 23:57:352017-08-17 23:57:302017-08-17 23:57:252017-08-17 23:57:202017-08-17 23:57:402017-08-17 23:57:352017-08-17 2357:302017-08-17 23:57:252017-08-17 23:57:202017-08-17 23:57:152017-08-17 23:57:102017-08-17 23:57:052017-08-17 23:57:002017-08-17 23:56:552017-08-17 23:56:492017-08-17 23:56:442017-08-17 23:56:392017-08-17 23:56:342017-08-17 23:56:292017-08-17 23:56:242017-08-17 23:56:192017-08-17 16:52:432017-08-17 23:57:152017-08-17 23:57:102017-08-17 23:57:052017-08-17 23:57:002017-08-17 23:56:552017-08-17 23:56:502017-08-17 23:56:442017-08-17 23:56:392017-08-17 23:56:342017-08-17 23:56:292017-08-17 23:56:242017-08-17 23:56:192017-08-17 16:52:44图5-23在源表中新增17条记录，5.4增量数据抽取第五章数据抽取第二十三步执行增量抽取测试作业，在执行结果中查看作业 量度选项页，显示当前作业的执行情况，如图5-24所示。执行结果 ex询史自日志信作业度量。Metrics酹目渊结果原因/增曷抽取测试任务:增量抽取测试开始执行任务开始START开始执行任务开始START喻所完毕设置增曷抽取最大时间i开始执行任务Fo l l o wed 无条件.设置增量抽取最大时间i任务执行完毕O设置增量抽取目标表开始执行任务Fo l l o wed l ink aft.设置增量抽取目标表潞楹亍完毕O任务:增量抽取测试酹加亍完毕文彳修喉2017/08/12017/08/10 2017/08/1F:iLearnsbigdata设置增量抽取最大时间戳.2017/08/18 00:01:0F:iL eamsbigdata设置增量抽取最大时间截 F:iLeansbigdatai翱取到目标表.ktr F:iL eamsbigdata增量抽取到目标表.ktr1 2017/08/12017/08/12 2017/08/12 2017/08/1图5-23作业成功完成，5.4增量数据抽取第五章数据抽取第二十 四步查看执行结果的日志选项页，其中可以查看Kettl e 当前作业表输入和表输出处理的记录条数，如图5-24所示。其中，I表示输入记录数，。表示输出记录数，R表示读取记 录数，W表示写入记录数。由此可以得出结论，Kettl e通过作业从timejo b表中抽取17 条记录，写入到timeo b_bak表中，实现了基于时间戳方式 的增量数据抽取。执行结果0历史日志 5作业言 匡Metrics0 W 02017/08/18 00:01:06-设置增量抽取目标表-L o ading transfo rmatio n fro m XML fil e F:iL earnsbigdata增量抽取到目标表.ktr2017/08/18 00:01:06-增量抽取到目标表-为了转换解除补丁开始增量抽取到目标表2017/08/18 00:01:06-表输出.0-Co nnected to database l o cal MySQL test(co mmit=1000)2017/08/18 00:01:06-获取变量.0-完成处理(1=0,0=0,R=1,W=1,U=0,E=0)2017/08/18 00:01:06-表输入.0-FinOhed reading query,cl o sing connectio n.2017/08/18 00:01:06-同入.0-完成处理(1=17,6=0,R1O,W=17,0=0,E=0)2017/08/18 00:01:06-就出.0-完成处理(1=0,0=17 R=17,W=17,U=0,E=0)2017/08/18 00:01:06-1量抽取测试-完成作业项设置增量抽取目标声侈舌果二true)2017/08/18 00:01:06-增量抽取测试-完成作业项设置增量抽取最大时间翻(结果=true)2017/08/18 00:01:06-增量抽取测试-任务执行完毕2017/08/18 00:01:06-Spo o n-任务已经结束.图5-24查看作业执行日志大数据应用人才培养系列教材第五章数据抽取5；文本文件抽取5幺Web数据抽取 5J攵据库文件抽取 5.4增量数据抽取习题习题：L制表符文件和普通文本文件的区别。2.解决CSV文件转换为Excel表格时乱码的问题。3.从和风天气https:/www.heweather.co m/do cu merit s/api/v5/weat her 中，使用免费用户链接https:free-api.heweather.co m/v5/weather?city=yo u rcity&key=yo urkey,把返回的天气JSON数据保存到MySQL数据库中。4.把MySQL一个数据库下的所有数据表全部迁移到MS SQL Server 的同名数据库下,建议只用Kettl e的一个转化来实现。5.XML数据解析的两种不同方式，使用速度快的那种方式解析一个 XML文件。感谢聆听