数据清洗黑马程序员第六章数据转换——阅读笔记

6.1 多数据源的合并

随着信息技术的发展和科技的进步，人类步入了大数据时代，大数据作为当前高科技时代的产物，它的种类多而繁杂。如果想要得到需要的数据，则这些需要的数据有可能来源于多个不同的数据源中，此时，我们可以将多个数据源进行合并操作，从而获取到所需要的数据。

案例实现

数据准备

假设，某公司旗下有两个子公司，分别为A公司和B公司，且这两个子公司均在销售手机，其中A公司的手机日销售情况存储在CSV文件中，即文件company_a.csv；B公司的手机日销售情况存储在数据库的数据表中，即数据表company_b，具体内容如下图1、图2所示：

图1 company_a.csv的数据内容

 图2  数据表company_b的数据内容

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

使用Kettle工具，创建一个转换company_merge，并添加CSV文件输入控件、表输入控件、字段选择控件、排序合并控件、增加序列控件、表输出控件以及Hop跳连接线，具体如图3所示：

图3 创建转换company_merge

2.配置“CSV文件输入”控件

双击“CSV文件输入”控件，进入“CSV文件输入”配置界面，单击“浏览”按钮，选择要抽取的CSV文件company_a.csv；单击“获取字段”按钮，Kettle自动检索CSV文件获取字段名称，并对文件中字段的类型、格式、长度、精度等属性进行解析，具体效果如图4所示；单击“浏览”按钮，查看文件company_a.csv的数据是否抽取到CSV文件输入流中，具体效果如图5所示：

图4 配置“CSV文件输入”控件

图5 预览数据

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

双击“字段选择”控件，进入“选择/改名值”界面，在“选择和修改”选项卡的“字段”处手动添加CSV文件输入控件输出的所有数据字段，也可以单击“获取选择的字段”按钮，Kettle工具自动检索并添加CSV文件输入控件输出的所有数据字段，具体如图6所示；选择“元数据”选项卡，切换到“元数据”选项卡界面。添加需要改变元数据的字段，将“字段选择”流中的字段进行一致性处理，即单击“获取改变的字段”按钮，获取要改变的字段，并在“Binary to Normal”一列的下拉框中，选择为“是”，使得CSV文件company_a.csv中数据的字段类型与数据表company_b中数据的字段类型一致，具体如图7所示：

 图6 Kettle检索字段

图7 “元数据”选项卡配置

4.配置“表输入”控件

双击“表输入”控件，进入“表输入”界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。MySQL数据库连接的配置，如下图8所示；在图中的SQL框中编写查询数据表company_b的SQL语句，然后单击“预览”按钮，查看数据表company_b的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图9所示：

 图8 MySQL数据库连接的配置

图9 编写SQL语句和预览数据

5.配置“排序合并”控件

双击“排序合并”控件，进入“排序合并”配置界面，并在字段框中，指定按字段id进行升序排序，具体如图10所示：

图10 “排序合并”控件的配置

6.配置“增加序列”控件

双击“增加序列”控件，进入“增加序列”配置界面，在“值的名称”处，指定要增加列的列名，这里使用默认的名称，即valuename，其它配置项不作任何改变；单击“确定”按钮，完成“增加序列”控件的配置，如图11所示：

图11 “增加序列”控件的配置

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

双击“字段选择2”控件，进入“选择/改名值”界面。在“选择和修改”处选择和修改要输出的字段，具体如图12所示；

图12 “字段选择2”控件的配置

 8.配置“表输出”控件

双击“表输出”控件，进入“表输出”控件的配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮；单击目标表右侧的“浏览”按钮，指定输出目标表，即数据表company；勾选“指定数据库字段”复选框，用于将数据表company的字段与“字段选择2”控件输出流中的字段进行匹配，单击“数据库字段”选项卡；再单击“输入字段映射”按钮，弹出“映射匹配”对话框；依次选中“源字段”选项框的字段和“目标字段”选项框对应的字段，再单击“Add”按钮，将一对映射字段添加至“映射”选项框中，如图13所示；单击“确定”按钮，完成“源字段”与“目标字段”的映射匹配。“表输出”界面最终的配置，如图14所示：

 图13 设置“映射匹配”

图14 “表输出”控件配置的效果图 

9.运行转换company_merge

单击转换工作区顶部的按钮，运行创建的转换company_merge，实现将A公司和B公司的手机日销售数据合并到一个数据源（即数据表company）中，具体如图15所示：

图15  运行company_merge

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

通过SQLyog工具，查看数据表company是否已成功插入40条数据，查看结果如图16所示（只展示部分数据）：

 图16 数据表company

6.2 不一致数据转换

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

案例实现

数据准备

A公司和B公司销售的手机均从同一个供货商手里采购，因此，同一品牌型号的手机，售价也应相同。但是，数据表company中存在同一品牌型号的手机，售价却不同。如标记的4条数据中字段brand和mode1均指向同一品牌和型号，而id为19这条数据价格字段（unitPrice）与其它3条数据的价格字段不同，如图17所示：

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

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

使用Kettle工具，创建一个转换inconsistent，并添加表输入控件、字段选择控件、记录集连接控件、插入/更新控件以及Hop跳连接线，具体如图18所示：

图18  创建转换inconsistent

2.配置“表输入”控件

双击“表输入”控件，进入“表输入”配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。MySQL数据库连接的配置，具体如图19所示；在SQL框中编写查询数据表company中品牌型号不一致数据的 SQL语句，然后单击“预览”按钮，查看数据表company中品牌型号不一致数据是否成功从MySQL数据库中抽取到表输入流中，具体如图20所示：

 图19 MySQL数据库连接的配置

图20 编写SQL语句和预览数据

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

双击“表输入2”控件，进入“表输入”配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。MySQL数据库连接的配置；在SQL框中编写SQL语句，查询出不一致数据在数据表suppliers中的全部信息；在“从步骤插入数据”的下拉框选择“表输入”步骤；勾选“执行每一行”处的复选框，用于将表输入控件流中的数据放入到SQL语句对应的参数位置，通过条件查询与数据表suppliers中的数据进行匹配，具体配置如图21所示：

 图21 编写SQL语句

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

双击“字段选择”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处添加表输入2控件流中的所有数据字段，并将字段unitPrice改名成unitPrice1，具体如图22所示；选择“移除”选项卡，切换到“移除”选项卡界面，添加需要移除字段，这里添加的是字段id，由于后续操作不需要字段id，因此在此进行移除，具体如图23所示：

图22 Kettle检索字段

 图23 移除id字段

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

双击“表输入3”控件，进入“表输入”配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。在SQL框中编写查询与标准价格表（供货商提供的价格表）中品牌型号不一致数据的SQL语句，然后单击“预览”按钮，查看数据表company中品牌型号不一致数据是否成功从MySQL数据库中抽取到表输入流中，具体如图24所示：

 图24 编写SQL语句和预览数据

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

双击“记录集连接”控件，进入“合并排序”界面，在“第一个步骤”处的下拉框中选择“字段选择”控件，“第二个步骤”处的下拉框中选择“表输入3”控件，用于将“字段选择”控件流中的数据与“表输入3”控件流中的数据进行合并连接；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处添加连接字段，这里添加的连接字段是brand、model，用于将“字段选择”控件流中的字段brand、model与“表输入3”控件流中的字段brand、model进行连接；在“连接类型”处的下拉框选择连接类型，这里选择的RIGHT OUTER，即右外连接，具体如图25所示：

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

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

双击“字段选择2”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的字段名称处填写id和unitPrice1，用于在插入/更新控件中，通过唯一字段id修改对应的价格unitPrice1字段内容，具体如图26所示：

图26  “字段选择2”控件的配置

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

双击“插入/更新”控件，进入“插入/更新”配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。单击目标表右侧的“浏览”按钮，弹出“数据库浏览器”窗口，选择目标表company；单击“获取字段”按钮，用来指定查询数据所需要的关键字，也可通过手动输入，指定查询数据所需要的关键字，这里选择的是数据表company中的id字段和输入流里面的id字段；单击“获取和更新字段”按钮，用来指定需要更新的字，具体如图27所示：

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

9.运行inconsistent转换

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

图28  运行inconsistent转换

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

通过SQLyog工具，查看数据表company是否已成功将数据表company中品牌型号相同、价格不同的数据修改成品牌型号价格均相同的数据，查看结果如图29所示（只展示部分数据）：

 图29 数据表company

6.3 数据粒度的转换

业务系统一般存储非常明细的数据，而数据仓库中数据是用来分析的，不需要非常明细的数据。一般情况下，会将业务系统数据按照数据仓库粒度进行聚合，这个过程就被称为数据粒度的转换。

案例实现

数据准备

A公司的日手机销售情况中的销售区域是市级，而B公司的日手机销售情况中的销售区域是省级，A公司和B公司的日手机销售情况合并后存储在数据表company中，通过仔细观察数据表company中字段为salesArea的一列，发现该列既包含市级也包含省级。数据表company中的部分数据内容，如图30所示：

图30  数据表company

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

使用Kettle工具，创建一个转换granularity，并添加表输入控件、字段选择控件、排序记录控件、记录集连接控件、过滤控件、空操作控件、表输出控件以及Hop跳连接线，具体如图31所示：

 图31 创建转换granularity

2.配置“表输入”控件

单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。MySQL数据库连接的配置，具体如图32所示；在SQL框中编写获取数据表company中所有数据的SQL语句，然后单击“预览”按钮，查看数据表company中数据是否成功从MySQL数据库中抽取到表输入流中，具体如图33所示：

图32 MySQL数据库连接的配置 

 图33 编写SQL语句和预览数据

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

双击“字段选择”控件，进入“选择/改名值”界面，在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id和salesArea，具体如图34所示：

图34 添加所需字段 

4.配置“排序记录”控件

双击“排序记录”控件，进入“排序记录”配置界面，在“字段”框中添加字段salesArea，通过以该字段为基础对整体数据进行升序排序，具体如图35所示：

 图35 配置“排序记录”控件

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

双击图31,中的“表输入2”控件，进入“表输入”配置界面，单击“新建”按钮，配置数据库连接，配置完成后单击“确认”按钮。在SQL框中编写SQL语句，用于查询city数据表中字段city和pid的数据，用于与数据表company中字段salesArea进行合并连接；单击“预览”按钮，查看city数据表中的字段city和pid的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图36所示：

 图36 编写SQL语句和预览数据

6.配置“排序记录2”控件

双击图31中的“排序记录2”控件，进入“排序记录”配置界面，并在“字段”框中添加字段city，以该字段为基础对整体数据进行升序排序，具体如图37所示：

 图37 配置“排序记录2”控件

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

双击“记录集连接”控件，进入“合并排序”配置界面，并在“第一个步骤”处的下拉框中选择“排序记录”控件，“第二个步骤”处的下拉框中选择“排序记录2”控件；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处分别添加连接字段，这里添加的连接字段是salesArea和city，将“排序记录”控件流中的数据与“排序记录2”控件流中的数据进行合并连接；在“连接类型”处的下拉框选择连接类型，这里选择的LEFT OUTER，即左外连接，具体如图38所示：

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

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

双击“字段选择2”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id、pid和salesArea&#x