ETL课后题

第三章

1.将一张数据表的两个字段进行拼接，然后插入另一张数据表中

1.打开kettle工具，创建一个转换3-1

1.1通过使用Kettle工具，创建一个转换3-1并添加表输入控件和文本文件输出控件如图所示

2.配置表输入控件

2.1 双击“表输入”控件，进入“表输入”界面。

2.2 单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。MySQL数据库连接的配置，如下所示。

2.3 在图中的SQL框中编写查询数据表company中两个字段的SQL语句，然后单击【预览】按钮，查看数据表company的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图所示。

3.配置文本文件输出控件

3.1 双击文本文件输出进入页面

3.2 点击字段，在字段中获取字段然后确定

4.运行该转换结果如图所示

5.单击文本文件输出后，点击Preview data查看数据

2.发送邮件

1.创建作业3-2，添加START，发送邮件以及成功控件并把他们连接，如图所示

2.配置发送邮件控件

2.1 双击发送邮件控件，进入页面

2.2 在“地址”选项卡中，添加收件人和发件人的相关信息

3.运行该作业，完成发送邮件

第四章

1.抽取CSV文件 csv_extract.csv中的数据保存至数据库extract中的数据表csv中。

1.打开kettle工具，创建一个转换4-1-csv

1.1通过使用Kettle工具，创建一个转换3-1并添加表输入控件和文本文件输出控件如图所示

2.配置csv文件输入控件

2.1 双击“CSV文件输入”控件，进入“CSV文件输入”配置界面，具体如图所示。

2.2 单击【浏览】按钮，选择要进行完全去重处理的CSV文件csv_extract.csv；再单击【获取字段】按钮，Kettle会自动检索CSV文件，并对文件中的字段类型、格式、长度、精度等属性进行分析。

2.3.单击【预览】按钮，查看CSV文件csv_extract.csv的数据是否加载到CSV文件输入流中。

3. 配置表输出控件

3.1 双击“表输出”控件，进入“表输出”控件的配置界面。

3.2 单击【新建】按钮，配置数据库连接（所连接的数据库transform需提前创建，这里不作赘述），配置完成后单击【确认】按钮。数据库连接的配置具体如图所示。

3.3 单击目标表右侧的【浏览】按钮，指定输出目标表，即数据表etl_csv（该表需提前创建，这里不作演示）；勾选“指定数据库字段”复选框,如图所示。

3.4 单击“数据库字段”选项卡；再单击【输入字段映射】按钮，弹出“映射匹配”对话框；依次选中“源字段”选项框的字段和“目标字段”选项框对应的字段，再单击【Add】按钮，将一对映射字段添加至“映射”选项框中。

3.5 单击【确定】按钮，完成“源字段”与“目标字段”的映射匹配。“表输出”界面最终的配置如图所示。

4. 运行该4-1-csv转换，结果如图所示

5.利用mysql查询etl_csv表中数据

2.抽取JSON文件json_extract.js中的数据保存至数据库extract中的数据表json中

1.通过使用Kettle工具，创建一个转换4-2-web-json，并添加“JSON input”控件、“表输出”控件以及Hop跳连接线，具体如图所示。

2.配置JSON Input控件

2.1 双击“JSON input”控件，进入“JSON输入”界面。

2.2 单击【浏览】按钮，选择要抽取的JSON文件json_extract.json；单击【增加】按钮，将所选择的文件添加到“选中的文件和目录”处。

2.3 单击“字段”选项卡，添加要抽取的数据字段（这里采用分层抽取数据字段，先抽取id和data字段，再从data字段中抽取field和value字段）。

2.4 双击“JSON input 2”控件，进入“JSON输入”界面。

2.5 勾选“源定义在一个字段里？”的复选框；在“从字段获取源”处的下拉框中选择字段名，即data。

2.6 单击“字段”选项卡；添加从字段data中抽取的field和value字段。

3. 配置表输出控件

3.1 双击“表输出”控件，进入“表输出”配置界面。

3.2 单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。

3.3 单击目标表右侧的【浏览】按钮，选择输出的目标表，即数据表etl_json；勾选“指定数据库字段”的复选框，用于将数据表json的字段与JSON文件json_extract.json中的字段进行匹配。

3.4 单击“数据库字段”选项卡，再单击【输入字段映射】按钮，弹出“映射匹配”对话框，将“源字段”选项框的字段和“目标字段”选项框对应的字段进行映射匹配。

3.5 “表输出”控件配置的最终效果，具体如图所示。

4. 运行转换4-2-web-json转换

4.1单击转换工作区顶部的按钮，运行创建的4-2-web-json转换

5. 查看数据表json中的数据

5.1 通过mysql工具，查看数据表etl_json是否已成功插入6条数据，具体如图所示。

第五章

1.对文件merge.csv进行完全去重

1.打开kettle工具，创建一个转换

1.1通过使用Kettle工具，创建一个转换5-1完全去重，并添加“CSV文件输入”控件、“唯一行（哈希值）”控件以及Hop跳连接线，具体如图所示。

2.配置CSV文件输入控件

2.1.双击“CSV文件输入”控件，进入“CSV文件输入”配置界面，具体如图所示。

2.2.单击【浏览】按钮，选择要进行完全去重处理的CSV文件merge.csv；再单击【获取字段】按钮，Kettle会自动检索CSV文件，并对文件中的字段类型、格式、长度、精度等属性进行分析。

 2.3.单击【预览】按钮，查看CSV文件merge.csv的数据是否加载到CSV文件输入流中。

3.配置”唯一行（哈希值）”控件

3.1.双击“唯一行（哈希值）”控件，进入“唯一行（哈希值）”配置界面。

 3.2.在“用来比较的字段”处，添加要去重的字段，这里可以单击【获取】按钮，获取要去重的字段。       

4.运行转换5-1-完全去重

4.1单击转换工作区顶部的 按钮，运行创建的5-1-完全去重转换。      

5.查看merge.csv是否消除完全重复的数据

5.1选中“唯一行（哈希值）”控件，单击执行结果窗口的“Preview data”选项卡，查看是否消除CSV文件merge.csv中完全重复的数据。

2.对文件people_survey.txt中的缺失值进行填充

1.打开Kettle工具，创建转换

1.1 通过使用Kettle工具，创建一个转换5-2-填充缺失值，并添加“文本文件输入”控件、“过滤记录”控件、“空操作（什么也不做）”控件、“替换NULL值”控件、“合并记录”控件、“字段选择”控件以及Hop跳连接线。

2. 配置文本文件输入控件

2.1 双击“文本文件输入”控件，进入“文本文件输入”配置界面。

2.2 单击【浏览】按钮，选择要去除缺失值的文件people_survey.txt；单击【增加】按钮，将要去除缺失值的文件people_survey.txt添加到“文本文件输入”控件中。

 2.3 单击“内容”选项卡；在清除分隔符处的默认分隔符“；”，单击【Insert TAB】按钮，在分隔符处插入一个制表符；取消勾选“头部”复选框，若不取消，在进行数据抽取操作时会排除文件第一行的数据。

 2.4 单击“字段”选项卡；根据文件people_survey.txt文件的内容添加对应的字段名称，并指定数据类型。

 2.5 单击【预览记录】按钮，查看文件people_survey.txt的数据是否成功抽取到文本文件输入流中。

3. 配置过滤记录控件

3.1 双击“过滤记录”控件，进入“过滤记录”配置界面。

3.2 在“条件”处设置过滤的条件，由于从“预览数据”图中可以看出字段userid为000016用户的hours_per_week（即每周工作时间字段）存在缺失值，而它的workclass字段值为Private，因此我们可以将过滤字段设置为workclass、过滤值设置为Private作为过滤条件。

3.3 在“发送true数据给步骤：”下拉框中选择“空操作”，将workclass字段值为Private的数据放在“空操作”控件中；在“发送false数据给步骤：”下拉框中选择“空操作（什么也不做）2”，将workclass字段值不为Private的数据放在“空操作（什么也不做）2”控件中。

4. 配置替换NULL值控件

4.1 双击“替换NULL值”控件，进入“替换NULL值”配置界面。

4.2  勾选“选择字段”处的复选框，并在“字段”框添加字段为hours_per_week，值替换为44（44是字段为hours_per_week中所有值相加求的均值，这里指用44替换字段hours_per_week中的NULL值）。

4.3  双击“合并记录”控件，进入“合并行（比较）”配置界面。

4.4 在”旧数据源：”下拉框选择“替换NULL值”，“新数据源：”下拉框选择“空操作（什么也不做）2”；在“匹配的关键字：”部分，添加关键字段，即userid。

5.配置替换NULL值2控件

5.1 双击“替换NULL值2”控件，进入“替换NULL值”配置界面。

5.2 勾选“选择字段”处的复选框，并在“字段”框添加字段为workclass，值替换为Private（这里用Private替换字段workclass中的NULL值）。

6. 配置字段选择控件

6.1 双击“字段选择”控件，进入“选择/改名值”配置界面。

6.2 在“移除”选项卡处添加要移除的字段名称，这里移除的是字段flagfield。

7.运行转换5-2-填充缺失值

7.1 单击转换工作区顶部的按钮，运行创建的5-2-填充缺失值转换。

8. 查看文件people_survey.txt中的缺失值是否已填充

8.1 单击图中的“字段选择”控件，再单击执行结果窗口的“Preview data”选项卡，查看是否填充了文件people_survey.txt中的缺失值。

第六章

1. 对文本文件 personnel_data.txt中的数据进行数据粒度的转换,即将文本文personnel_data.txt 中字段为 household_register 的数据统一成省份,并输出到文本文件 personnel_data_new.txt 中。

2. 对文本文件personnel_data_new.txt 中字段为salary的数据进行商务规则计算即计算每个人的月薪（以22天工作日计算），最终输出到文本文件personnel_data_monthly。

第七章

1.通过使用Kettle工具,实现将数据表full_source中的数全量加载到数据表full_target 中。

 1. 打开Kettle工具，创建转换

1.1 使用Kettle工具，创建一个转换7-1-数据的加载机制，并添加执行SQL脚本控件、表输入控件、表输出控件以及Hop跳连接线，具体如图所示。

2. 配置执行SQL脚本控件

2.1 双击“执行SQL脚本”控件，进入“执行SQL语句”界面，具体如图所示。

2.2 单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。MySQL数据库连接的配置；在SQL框中编写删除数据表full_target中数据的SQL语句，如图所示。

3. 配置表输入控件

3.1 双击“表输入”控件，进入“表输入”配置界面，在SQL框中编写查询full_source数据表的SQL语句，如图所示。

3.2 单击【预览】按钮，查看full_source数据表的数据是否成功从MySQL数据库中抽取到表输入流中，如图所示。

4. 配置表输出控件

4.1 双击“表输出”控件，进入“表输出”配置界面；单击目标表处的【浏览】按钮，选择输出的目标表，即数据表full_target，这里不需要新建数据库连接，只需要选择已创建的数据库连接即可，如图所示。

5. 运行转换7-1-数据的加载机制

5.1 单击转换工作区顶部的按钮，运行创建的转换7-1-数据的加载机制，实现将数据表full_source中的数据全量加载到数据表full_target中，如图所示。

6. 查看数据表full_target中的数据

6.1 通过mysql工具，查看数据表full_target是否已成功加载到数据，查看结果如图所示

2.通过使用Kettle工具，实现将效据表incremental_source中的枚据增量加载到数据表incremental_target 中，即将数据表incremental_source中新增、变化的数据加载到数据表incremental_target 中。

1.打开Kettle工具，创建转换

1.1 使用Kettle工具，创建一个转换incremental_load，并添加表输入控件、插入/更新控件以及Hop跳连接线，具体如图所示。

2. 配置表输入控件

2.1 双击“表输入”控件，进入“表输入”配置界面，单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。MySQL数据库连接的配置，具体如图所示。

2.2 在SQL框中编写查询数据表incremental_source的SQL语句，然后单击【预览】按钮，查看数据表incremental_source的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图所示。

3. 配置插入/更新控件

3.1 双击“插入/更新”控件，进入“插入/更新”配置界面，单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。MySQL数据库连接的配置，具体如图所示。

3.2 单击目标表处的【浏览】按钮，弹出“数据库浏览器”窗口，选择目标表incremental_target，具体如图所示。

3.3 单击【获取字段】按钮，用来指定查询数据所需要的关键字，这里通过比较数据表incremental_target的字段id与输入流里的字段id是否一致为关键条件，更新数据表中的其它字段数据；单击【获取和更新字段】按钮，用来指定需要更新的字段，具体如图所示。

4. 修改数据表incremental_source中的数据

4.1 在数据表incremental_source中新增一条id为6、name为John、age为25的数据；对数据表incremental_source中id为2的数据进行修改，即将这条数据的年龄（age）改为25。修改后的数据表incremental_source中的数据内容如图所示。

5. 运行转换7-1-批量处理

5.1 单击转换工作区顶部的按钮，运行创建的转换incremental_load，实现将数据表incremental_source中的数据增量加载到数据表incremental_target中，如图所示。

6. 查看数据表incremental_target中的数据

6.1 通过mysql工具，查看数据表incremental_target是否已成功加载到数据，查看结果如图所示。