ETL第五章

5.1数据去重-完全去重

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中完全重复的数据。

5.1数据去重-不完全去重

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

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

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

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

 2.2 单击【浏览】按钮，选择要去重的文件people.txt；单击【增加】按钮，将要去重的文件people.txt添加到转换5-1-不完全去重中。

 2.3单击“内容”选项卡；清除分隔符处的默认分隔符“；”，并单击【Insert TAB】按钮，在分隔符处插入一个制表符；取消勾选“头部”复选框。

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

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

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

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

 3.2在“用来比较的字段”处，添加要比较去重的字段，即Name、UserLevel、Phone字段。

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

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

5. 查看文件people.txt是否消除不完全重复的数据

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

5.2  缺失值处理-去除缺失值

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

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

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

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

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

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

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

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

3. 配置字段选择控件

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

3.2 在“选择和修改”选项卡的“字段”处手动添加文本文件输入控件输出的所有数据字段，也可以单击【获取选择的字段】按钮，Kettle工具自动检索并添加文本文件输入控件输出的所有数据字段。

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

4.配置过滤记录控件

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

4.2 在“条件”处设置过滤的条件，过滤掉有缺失值的数据字段（这里是过滤Name、Pay和Area字段中的缺失值）；单击左边“<field>”框，弹出字段对话框，选择要过滤的字段Name；单击 “=”框，弹出函数对话框，选择过滤条件（这里选择IS NULL）。

 4.3 字段Name的过滤设置如图所示。

4.4 单击符号“+”增加过滤条件；单击“AND”，弹出操作符对话框，选择操作符（这里选择的是OR）。

4.5 单击“增加条件”图中的“null = [ ]”，添加过滤字段；单击左边“<field>”框，弹出字段对话框，选择要过滤的字段Pay；单击“=”框，弹出函数对话框，选择过滤条件（这里选择IS NULL）。字段Pay的过滤设置，如图所示。

4.6 单击“字段Pay的过滤设置”图中的符号“+”增加过滤条件；单击“AND”，弹出操作符对话框，选择操作符（这里选择OR）。单击“增加条件”图中的“null = [ ]”，添加过滤字段；单击左边“<field>”框，弹出字段对话框，选择要过滤的字段Area；单击“=”框，弹出函数对话框，选择过滤条件（这里选择IS NULL）；字段Area的过滤设置，具体如图所示。连续单击两次【确定】按钮，查看整体设置的过滤条件。

4.7 在“发送true数据给步骤：”处的下拉框中选择“空操作”，将包含缺失值的行数据放在空操作控件中；在“发送false数据给步骤：”处的下拉框中选择“Excel输出”，将没有缺失值的行数据输出到Excel文件中。

5. 配置Excel输出控件

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

5.2 单击【浏览】按钮，选择要输出的文件路径

6. 运行转换5-2-缺失值

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

7. 查看文件file.xls

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

5.2缺失值处理-填充缺失值

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中的缺失值。

5.3  异常值处理-删除包含异常值的记录

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

1.1 通过使用Kettle工具，创建一个转换delete_anomalous _value，并添加“文本文件输入”控件、“过滤记录”控件、“空操作（什么也不做）”控件以及Hop跳连接线。

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

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

2.2 先单击【浏览】按钮，选择要去除异常值的文件temperature.txt，然后单击【增加】按钮，将要去除异常值的文件temperature.txt添加到“文本文件输入”控件中。

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

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

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

3. 配置过滤记录控件

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

3.2 在“条件”处设置过滤的条件，由于文件temperature.txt中time为6：30的温度是137摄氏度，不在非异常值的范围内，因此属于异常值，我们应该将过滤字段设置为temperature、过滤值为137。

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

4.运行转换5-2-删除异常值

4.1 单击转换工作区顶部的按钮，运行创建的delete_anomalous_value转换。

5. 查看文件temperature.txt是否去除了异常值

5.1 单击“空操作”控件，再单击执行结果的“Preview data”选项卡，查看是否去除了异常值。

5.3  异常值处理-修补异常值

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

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

2. 配置表输入控件

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

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

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

3. 配置过滤记录控件

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

3.2 在“条件”处设置过滤的条件，即设置Height字段的取值范围（[114-226]），从而判断数据表中的每个数据是否为异常值。若是在非异常值的取值范围内，则是非异常值，否则是异常值。

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

4. 预览“空操作（什么也不做）”控件中的数据

4.1 选中“空操作（什么也不做）”控件，然后单击转换工作区顶部的 按钮，预览“空操作（什么也不做）”控件中的数据，id为15的这条数据，Height字段为260，260不在非异常值范围[114，226]内，因此该条数据为异常数据。

5. 配置过滤记录控件

5.1 双击“设置值为NULL”控件，进入“设置值为NULL”界面；在“字段”处添加要设为NULL值的字段名称和值。

6. 配置合并记录控件

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

6.2 在“旧数据源：”处的下拉框选择“设置为NULL值”，“新数据源：”处的下拉框选择“空操作（什么也不做）2”；在“匹配的关键字：”处，添加关键字段，即id。

7. 配置替换NULL值控件

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

7.2 勾选“选择字段”处的复选框，并在“字段”框添加字段为Hight，值替换为170（通过计算得到499人的平均身高值近似为170，因此用170替换字段Hight中的NULL值）。

8. 配置字段选择控件

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

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

9. 运行转换5-2-修补异常值

9.1单击转换工作区顶部的按钮，运行创建的5-2-修补异常值转换。

10. 查看数据表interpolation_data中的异常值是否修改并替换

10.1 单击“字段选择”控件，再单击执行结果窗口的“Preview data”选项卡，查看是否修改并替换数据表interpolation_data中的异常值。

5.4  数据检验-数据一致性处理

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

1.1 使用Kettle工具，创建一个转换5-4-数据一致性处理，并添加“表输入”控件、“值映射”控件、“插入/更新”控件以及Hop跳连接线。

 2. 配置“表输入”控件

2.1 双击“表输入”控件，进入“表输入”配置界面；单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。

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

3. 配置“值映射”控件

3.1 双击“值映射”控件，进入“值映射”配置界面；在“使用的字段名”处的下拉框选择字段GENDER；在“字段值”框中，添加源值和目标值，这里是将Male替换成数字0，将Female替换成数字1。

4. 配置”插入/更新”控件

4.1 双击“插入/更新”控件，进入“插入/更新”界面；单击【新建】按钮，配置数据库连接，配置完成后单击【确认】按钮。

4.2 单击目标表右侧的【浏览】按钮，选择目标表Personnel_ Information_New。单击【获取字段】按钮，用来指定查询数据所需要的关键字，这里选择的是Personnel_ Information_New数据表中的USERID字段和输入流里面的USERID字段；单击【获取和更新字段】按钮，用来指定需要更新的字段。

5. 运行转换5-4-数据一致性处理

5.1 单击转换工作区顶部的按钮，运行创建的转换5-4-数据一致性处理

6. 查看数据表Personnel_ Information_New中的数据

6.1  通过MYSQL工具，查看数据表Personnel_ Information_New是否已成功插入501条数据，查看结果如图所示。

 6.3 点击插入/更新，再点击Preview_data查看，也能查看到相应的数据。