Kettle数据清洗与转换

一、实验目的

1、学习如何使用Kettle对不一致数据进行转换。

二、实验设备与器件

PC机、phpstudy（wamp环境）、sublime text或者php storm工具

三、实验内容

相关知识

在学习该课程之前，请先打开终端执行如下命令启动Mysql并初始化课程所需案例数据：

sudo service mysql start

mysql -uroot -pTempoTalents@2021 < /data/workspace/data_set/company_suppliers.sql2

具体操作如下图：

不一致数据转换主要是将不同业务系统中的相同类型的数据进行统一。例如，同一供应商在结算系统中的编码是XX0001，而在CRM（客户关系管理）系统中的编码是YY0001，这时就需要将这两个业务系统中的数据抽取过来进行统一转换，转换成同一个编码。

A公司和B公司销售的手机均从同一个供货商手里采购，因此，同一品牌型号的手机，售价也应相同。但是，数据表company中存在同一品牌型号的手机，售价却不同，具体如图1所示。

 

图1   数据表company中的不一致数据

从图1中可以看出，标记的4条数据中，字段brand和model均指向同一品牌和型号，而id为19的这条数据价格字段（unitPrice）与其他3条数据的价格字段不同。
下面通过Kettle工具对数据表company进行不一致数据的转换操作，即通过与供货商提供的标准价格表进行比较，得出不一致数据，从而进行修改，最终输出到数据表company中，具体实现步骤如下。

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

使用Kettle工具创建转换inconsistent，并添加“表输入”控件、“字段选择”控件、“记录集连接”控件、“插入更新”控件以及Hop跳连接线，具体效果如图3所示。

 

图2   创建转换inconsistent

2. 配置“表输入”控件

双击图2中的“表输入”控件，进入“表输入”界面，如图3所示。

 

图3   “表输入”界面

在图2中单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。MySQL数据库连接的配置如图4所示，MySQL数据库密码为TempoTalents@2021。

 

图4   MySQL数据库连接配置

在图3的SQL框中编写查询数据表company中品牌、型号不一致数据的SQL语句，SQL语句如下：

select

          c2.brand,

          c2.modelfrom

          company c2where

          c2.id not in (

          select

                    c1.id

          from

                    suppliers s1

          join company c1 on

                    s1.brand = c1.brand

                    and s1.model = c1.model

       and s1.unitPrice = c1.unitPrice);

注意：
数据表suppliers为供应商出售手机的价格清单表，如下图：

 

然后单击“预览”按钮，查看数据表company中品牌、型号不一致数据是否成功从MySQL数据库中抽取到表输入流中，具体如图5和图6所示。

 

图5   编写SQL语句

 

图6   预览数据

从图6中可以看出，数据表company中品牌、型号不一致数据已经成功从MySQL数据库中抽取到表输人流中，单击“关闭”按钮关闭预览数据窗口，再点击“确定”按钮，完成“表输入”控件的配置。

3. 配置“表输入2”控件

双击图2中的“表输入2”控件，进入“表输入”界面，如图7所示。

 

图7   “表输入”界面

在图7的SQL框中编写SQL语句，查询出不一致数据在数据表suppliers中的全部信息；在“从步骤插入数据”后的下拉列表中选择“表输人”控件；勾选“执行每一行”复选框，用于将“表输入”控件流中的数据放入SQL语句对应的参数位置，通过条件查询与数据表suppliers中的数据进行匹配，具体配置如图6-37所示。

 

图8   编写SQL语句

需要注意的是，“表输入”控件查询字段的顺序要与本控件内SQL语句的参数对应。在图8中单击“确定”按钮，完成“表输入2”控件的配置。

4. 配置“字段选择”控件

双击图2中的“字段选择”控件，进入“选择/改名值”界面，如图9所示。

 

图9   “选择/改名值”界面

在图9中“选择和修改”选项卡的“字段”处添加“表输入2”控件流中的所有数据字段，并将字段unitPrice改名成unitPrice1，具体如图10所示。

 

图10   Kettle检索字段

在图10中选择“移除”选项卡，切换到“移除”选项卡界面，如图11所示。

 

图11   “移除”选项卡界面

在图11中添加需要移除的字段，这里添加的是字段id，由于后续操作不需要字段id，因此在此进行移除，具体如图12所示。

 

图12   移除id字段

在图12中单击“确定”按钮，完成“字段选择”控件的配置。

5. 配置“表输入3”控件

双击图2中的“表输入3”控件，进入“表输入”界面，如图13所示。

 

图13   “表输入”界面

在图13的SQL框中编写查询与标准价格表（供货商提供的价格表）中品牌、型号不一致数据的SQL语句，SQL语句如下：

SELECT

          id,

          brand,

          model,

          unitPriceFROM

          company c2WHERE

          c2.id NOT IN (

          SELECT

                    c1.id

          FROM

                    suppliers s1

          JOIN company c1 ON

                    s1.brand = c1.brand

                    AND s1.model = c1.model

                    AND s1.unitPrice = c1.unitPrice)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

然后单击“预览”按钮，查看数据表company中品牌、型号相同，价格不同的不一致数据是否成功从MySQL数据库中抽取到表输入流中，具体如图14和图15所示。

 

图14   编写SQL语句

 

图15   预览数据

从图15中可以看出，数据表company中品牌、型号相同，价格不同的不一致数据已经成功从MySQL数据库中抽取到表输人流中，单击“关闭”按钮关闭预览数据窗口，再点击“确定”按钮，完成“表输入3”控件的配置。

6. 配置“记录集连接”控件

双击图2中的“记录集连接”控件，进入“合并排序”界面，如图16所示。

 

图16   “合并排序”界面

在图16中“第一个步骤”后的下拉列表中选择“字段选择”控件，在“第二个步骤”后的下拉列表中选择“表输人3”控件，用于将“字段选择”控件流中的数据与“表输入3”控件流中的数据进行合并连接；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处添加连接字段，这里添加的连接字段是brand、model，用于将“字段选择”控件流中的字段brand、 model与“表输人3”控件流中的字段brand、model进行连接；在“连接类型”后的下拉列表中选择连接类型，这里选择的是RIGHT OUTER，即右外连接，具体如图17所示。

 

图17   配置“记录集连接”控件

在图17中单击“确定”按钮，完成“记录集连接”控件的配置。

7. 配置“字段选择2”控件

双击图2中的“字段选择2”控件，进入“选择改名值”界面，如图18所示。

 

图18   “选择/改名值”界面

在图18中“选择和修改”选项卡的字段名称处填写id和unitPrice1，用于在“插入/更新”控件中通过唯一字段id修改对应的价格字段unitPricel内容，具体如图19示。

 

图19   “字段选择２”控件的配置

在图19中单击“确定”按钮，完成“字段选择2”控件的配置。

8. 配置“插入/更新”控件

双击图2中的“插入/更新”控件，进入“插入/更新”界面，如图20所示。

 

图20   “插入/更新”界面

单击图20中目标表右侧的“浏览”按钮，弹出“数据库浏览器”界面，选择目标表company；单击图20中的“获取字段”按钮，用来指定查询数据需要的关键字，也可通过手动输入，指定查询数据需要的关键字，这里选择的是数据表company中的id字段和输入流里的id字段；单击“获取和更新字段”按钮，用来指定需要更新的字段，具体如图21所示。

 

图21   “插入/更新”控件的配置

在图21中单击“确定”按钮，完成“插入/更新”控件的配置。

9. 运行转换inconsistent

单击转换工作区顶部的   按钮，运行创建的转换inconsistent，实现将数据表company中品牌、型号相同，价格不同的不一致数据修改成品牌、型号、价格均相同的一致数据，具体如图22所示。

 

图22   运行转换inconsistent

从图22中执行结果的“步骤度量”可以看出，“表输入”控件输入1条数据并写入该控件；“表输入2”控件读取“表输入1”控件的1条数据，作为“表输入2”控件的输入，并写入该控件；“表输入3”控件输入1条数据并写入该控件；“字段选择”控件从“表输入2”控件中读取1条数据并写人该控件；“记录集连接”控件从“字段选择”和“表输入3”控件中各读取1条数据，并将合并后的1条数据写入该控件；“字段选择2”控件从“记录集连接”控件中读取1条数据并写入该控件；“插入/更新”控件读取“字段选择2”控件的1条数据，作为“插入/更新”控件的输入并写入该控件完成对数据库1条数据的更新操作。

10. 查看数据表company中的数据

通过终端使用命令mysql -uroot -pTempoTalents@2021 -e "select * from transform.company;"查看数据表company是否已成功将数据表company中品牌、型号相同，价格不同的数据修改成品牌、型号、价格均相同的数据，查看结果如图23所示（只展示部分数据）。

 

图23   数据表company

从图23中可以看出，数据表company中字段brand为“苹果”、model为“苹果iPhone 11 Pro Max（6GB/64GB/全网通）”的unitPrice均为9599，说明成功实现了将数据表company中品牌、型号相同，价格不同的数据修改成品牌、型号、价格均相同的数据。

编程要求

请在右侧云桌面中进行操作，完成以下任务:

·                         根据以上操作步骤完成不一致数据转换inconsistent的创建并运行，将数据表company中同一品牌型号的手机，售价不同的数据进行不一致数据转换，并更新数据表company。

测试说明

在按编程要求完成操作后，请点击评测按钮，系统会自动对你的操作进行评测。
当你的结果与预期输出一致时，即为通过。