### 实验名称

客户信息数据抽取

### 实验目的

本数据预处理综合案例主要包括客户信息数据、性别参照数据和城市区号参照数据三种数据源。

### 实验背景

在数据开发项目中，数据的预处理是一项基本的工作，也是一项重要的工作，需要从复杂多样化的数据源中抽取数据

### 实验原理

在本综合案例中，我们使用Kettle提供的输入步骤实现对数据的抽取，主要涉及"CSV文件输入"步骤、"Excel输入"步骤和"表输入"步骤。

CSV文件是一种较为常见的文本文件。在这种文件里，每个字段或列都使用英文逗号进行分割。通常，这类文件也被称为逗号分隔符文件。

Excel的数据可分为结构化的表格数据和非结构化的表格数据。

对非结构化的表格数据，有可能表里包含有多个字段值的列或者有重复的一组字段等。使用Kettle读取后还需要转化为结构化的表格数据，才能进一步处理，而且，Excel作为常用的办公软件，很难要求所有的人员按数据的格式要求规范地输入数据。因此，在数据导入时，我们应尽量避免把Excel文件作为输入数据源。

尽管如此，我们在数据预处理过程中，有可能要处理人们所常用的Excel文件，Kettle也提供了"Excel输入"、"Excel输出"和"Microsoft Excel 输出"步骤来处理Excel文件的导入与导出。对"Excel输出"步骤，Kettle仅能输出Excel 97版本的文件；而"Excel输入"和"Microsoft Excel 输出"步骤则可以设置文件类型，文件类型可以选择Excel 97版本或Excel 2007版本的文件。

### 实验环境

Ubuntu 16.04

Kettle 8.2

### 建议课时

1 课时

### 实验步骤

#### 一、从CSV文件中读取性别参照数据

在本综合案例中，性别参照数据存储在csv文件中，我们使用以下命令将其下载到 /home/ubuntu/ 目录

```markup
wget http://10.90.3.2/HUP/ETL/2023/04-1/gender_ref.csv


```

下载完成后，打开"gender\_ref.csv"文件，可以看到文件以逗号为分隔符，内容如图所示。

![chapter4-1-0.png](./pic/chapter4-1-0.png)

Kettle提供了"CSV文件输入"步骤支持该类型文件的处理。在ETL工作中，我们也常常面临着处理TXT、XML、JSON等文件类型的场景，Kettle也提供了处理这些类型文件的相关步骤，限于篇幅，不再进行介绍，感兴趣的读者可自行查阅相关资料。

##### "CSV文件输入"步骤的配置：

首先，我们在桌面上找到Spoon图标 ![chapter4-1-1.png](./pic/chapter4-1-1.png)打开后新建一个转换，将项目名命名为kettle-preprocess，并将其保存到/home/ubuntu/目录。

1、在"核心对象"的输入文件夹中找到"CSV文件输入"步骤，拖到右侧画布中，命名为"CSV文件输入-性别参照表"；

2、单击"浏览(B)…"按钮，选择gender\_ref.csv文件作为输入文件来处理。

"列分隔符"选择了逗号（,），因为用文本编辑器打开gender\_ref.csv文件，可以看到此文件的分隔符是逗号。

勾选"包含列头行"，表示此文件的第一行作为字段名，不在后续输出流中输出。

单击"获取字段"，在此步骤的字段列表中选择此文件的4个字段。

![chapter4-1-6.jpg](./pic/chapter4-1-6.jpg)

3、单击"预览"按钮，进行数据的预览，预览结果如下。

![chapter4-1-7.png](./pic/chapter4-1-7.png)

#### 二、从Excel中读入客户信息

在本综合案例中，客户信息是存储在user.xlsx文件中，主要包括Name（姓名）、Age（年龄）、Birth（生日）、Sex（性别）、Salary（工资）、City（城市）、AreaCode（区号）、Email（邮箱）、Hobby（爱好）共9列数据。

![chapter4-1-8.png](./pic/chapter4-1-8.png)

user.xlsx 下载地址如下，将其下载到/home/ubuntu/目录。

```markup
wget http://10.90.3.2/HUP/ETL/2023/04-1/users.xlsx


```

接下来，我们使用"Excel输入"步骤从user.xlsx文件中读入客户信息，并对数据进行预览。

##### "Excel输入"步骤的配置：

在上述转换文件中，创建一个"Excel输入"步骤，双击后，将步骤名称重命名为"Excel输入-用户信息表"，并通过可视化编程的方式进行步骤配置。

"Excel输入"的配置

1、"文件"选项卡的配置。

![chapter4-1-9.png](./pic/chapter4-1-9.png)

因为输入的文件扩展名为xlsx，属于Excel 2007以上版本的文件。可通过单击下拉符，将"表格类型（引擎）"配置为"Excel 2007 XLSX (Apache POI)"。

单击"浏览(B)…"按钮，选择路径"/home/ubuntu/users.xlsx"文件作为输入文件，然后单击"增加"按钮，把文件增加到"选中的文件"列表中。

2、"工作表"选项卡的配置

如下图单击"获取工作表名称…"按钮，将打开"输入列表"对话框。

![chapter4-1-10.png](./pic/chapter4-1-10.png)

输入列表对话框

![chapter4-1-11.png](./pic/chapter4-1-11.png)

在"输入列表"对话框中，单击左边"可用项目"栏目的"客户信息表"，选择客户信息表作为要处理的数据源。单击"输入列表"对话框中间的">"按钮，把"客户信息表"移动到"你的选择"栏目中。效果如图所示，单击"确定(O)"按钮，完成工作表选择的配置。

![chapter4-1-12.png](./pic/chapter4-1-12.png)

在"要读取的工作表列表"中，"起始行"和"起始列"都填"0"。因为表格的数据从第0行第0列开始的。

![chapter4-1-13.png](./pic/chapter4-1-13.png)

3、"内容"选项卡的配置

"内容"选项卡采用默认的配置即可。勾选"头部"，即意味着表格的第一行作为表头。表格文字的"编码"采用默认的编码即可。

![chapter4-1-14.png](./pic/chapter4-1-14.png)

4、"错误处理"选项卡采用默认的配置即可。

5、"字段"选项卡的配置

单击"获取来自头部数据的字段…"按钮，系统将获取客户信息表的所有字段信息。

![chapter4-1-15.png](./pic/chapter4-1-15.png)

6、"其他输出字段"选项卡采用默认配置即可。

7、数据预览

单击"预览记录"按钮，完成数据的预览，预览结果如图所示。

![chapter4-1-16.png](./pic/chapter4-1-16.png)

#### 三、从MySQL中读取城市区号参照数据

城市区号参照数据存储在"MySQL"中，首先在"Terminal"中输入以下命令登录到"MySQL"，密码为"123456" ；

```shell
mysql -u root -p


```

登录成功后，执行下述SQL语句创建数据库和表

```sql
set names utf8; 
drop database if exists test;
create database test default charset utf8 collate utf8_general_ci;
use test;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for 城市区号参照表
-- ----------------------------
DROP TABLE IF EXISTS `城市区号参照表`;
CREATE TABLE `城市区号参照表` (
  `区号` varchar(255) DEFAULT NULL,
  `城市` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 城市区号参照表
-- ----------------------------
INSERT INTO `城市区号参照表` VALUES ('010', 'BEIJING');
INSERT INTO `城市区号参照表` VALUES ('0023', 'CHONGQING');
INSERT INTO `城市区号参照表` VALUES ('0755', 'TIANJIN');
INSERT INTO `城市区号参照表` VALUES ('021', 'SHANGHAI');


```

##### "表输入"步骤的配置

1、双击"表输入"步骤进行配置，在打开的对话框中单击"新建…"按钮以配置数据库的连接信息。

![chapter4-1-17.png](./pic/chapter4-1-17.png)

我们给连接名称任意起个名字，在这里命名为"sql\_testlink"。

"连接类型"选择"MySQL"，因为我们需要连接到MySQL数据库。

"主机名称"为MySQL服务端的IP，由于Kettle和MySQL服务端都安装在同一PC中，此处配置为"localhost"。

"数据库名称"配置为"test"。

"端口号"配置为默认的端口。

输入MySQL登录的用户和密码。单击"测试"按钮，如果参数正确，系统将弹出包含"正确连接到数据库"提示字符串的对话框。表明数据库连接成功，点击"确定"按钮关闭对话框。

![chapter4-1-18.png](./pic/chapter4-1-18.png)

此时，界面将退回到"表输入"对话框的配置界面，"表输入"对话框的"数据库连接"下拉栏目中，会显示刚刚配置的数据库连接"sql\_testlink"。

![chapter4-1-19.png](./pic/chapter4-1-19.png)

如果用户需要修改数据库连接信息，可以单击"编辑…"按钮，在打开的对话框中进行修改。

2、在"表输入"对话框的"SQL"栏目中，输入如下SQL语句。

```sql
SELECT
 区号
, 城市
FROM test.城市区号参照表 


```

![chapter4-1-20.png](./pic/chapter4-1-20.png)

3、单击"预览"按钮，完成数据查询和预览，预览结果如图所示。

![chapter4-1-21.png](./pic/chapter4-1-21.png)

### 实验总结

数据抽取是数据预处理中的首要环节，在本实验中我们使用Kettle（也称为Pentaho Data Integration）这个ETL（Extract, Transform, Load）工具分别从CSV文件、Excel文件和MySQL数据库中提取了数据。为后续数据清理做好了准备工作。