大数据学习笔记9-ETL工具kettle的使用

知识点1：kettle的介绍

1. 数据库和数据仓库的认识:

数据库: mysql 对于数据进行增删改查操作,一般都是业务数据库

• 需要数据高速处理,及时响应

• 对数据的安全性有极高的要求

• 数据内容,数据形式比较单一.

• 数据库一般是app或者用户通过接口访问.

数据仓库: 一般是用于数据分析的数据集中平台

• 将多重数据源(业务数据库, 日志信息, 备份文件,爬虫文件,问卷调查等)的数据集中存放在数据仓库中,便于分析统计

• 数据仓库更加重视的是数据的吞吐量.对及时响应和高效读写没有太高的要求.

• 数据仓库一般是数据业务人员使用

注意: 目前我们还没有学习常用的数据仓库,暂时先用mysql代替,真正开发中我们会存储在hive数仓或者其他数据仓库类软件中.

1. 什么是ETL:

E: extract 抽取数据

• 从多种不同的数据源中抽取数据(从不同数据源中抽取数据的方式不一样)

T: transform 转换数据

• 数据处理 : 数据的拆分, 合并,单位统一.统一逻辑等

• 数据清洗 : 处理异常值,缺失值,重复值 (不破坏数据的客观规律)

L: load 数据加载

• 一般是将数据加载到数据仓库中,但是也可以加载到备份文件或者日志记录中.

• 在开发中,重要的数据信息,我们通常会保存不止一份(数据仓库存储+文件备份)

知识点2：Windows安装Kettle

安装kettle 之前必须先安装jdk

jdk : java 开发工具包 (java开发环境)

jre : java 运行时环境 (java运行环境)

jvm : java 虚拟机环境 (java跨平台基础)

为什么市面上使用jdk8的应用居多????

1. 我们在开发中使用的jdk8 和 jdk1.8 其实是一个版本,只不过编号规则不一样.

2. jdk的版本不是每一个都会长期维护, LTS版本(长期维护版本) jdk 6 jdk 8 jdk 11 jdk 17

3. jdk 17 22 年才推出, 没有经过市场验证不敢随便用

4. jdk 11 在2023年统计已经占有市场超过40%

5. jdk 8 还在持续使用 , 足够稳定, 而且老旧项目还在使用, 但是已经于2019年停止维护, 所以新项目在逐渐向jdk11迁移.

6. 已经使用jdk8开发的老旧项目一般不会更换新版本, 重构维护成本过高.

安装过程:

1. 安装jdk8, 存放到指定位置即可,但是务必要能够找到该软件

2. 找到java.exe文件的文件路径,并复制该文件路径

3. 添加环境变量,在搜索栏中搜索"环境变量", 点击下方查询结果打开系统环境变量

4. 点击环境变量进入编辑界面

5. 点击编辑系统环境变量 >> 新建 >> 输入变量信息后点击确定

6. 双击Path添加新的环境变量%JAVA_HOME%bin\,点击确定,保存环境变量修改内容.

7. 验证环境变量的添加

• 在搜索栏输入cmd ，点击命令提示符

• 在终端窗口输入java -version如果能够输出版本信息，则java环境变量配置成功 8.将kettle压缩包解压后 点击spoon.bat 即可开启kettle

目前最常用的ETL工具：

• flume

• sqoop

• datax

知识点3：将txt文件抽取到excel中

1. 双击转换, 创建转换标签

   也可以点击文件 >>> 新建 >>> 转换

   也可以使用ctrl +n快速创建

2. 从输入输出对象中拖拽 文本文件输入和excel输出到操作区域

3. 点击shift从文本文件输入拖转一个箭头到excel输出

4. 设置文本文件输入内容

   4.1 双击进入文本文件输入编辑, 点击浏览,选择我们要作为输入源的文本文件
   4.2 点击增加,将文件路径,加载到选中的文件中
   4.3 修改内容中的分隔符和编码格式
   4.4 选择字段, 点击获取字段,查看字段的类型和格式
   4.5 获取 字段信息后, 点击预览记录, 如果出现与预期不符的数据类型或格式,在上方字段处修改后,重新预览
   4.6 点击确定, 完成文本文件对象编辑

5. 设置excel输出内容

   5.1 双击进入excel输出编辑.点击浏览, 选择excel输出位置,编辑输出文件名,点击保存
   5.2 删掉扩展名信息
   5.3 选择格式, 设置输出excel的样式
   5.4 选择字段,点击获取字段,查看字段与预期格式和数据类型是否匹配,不匹配则在字段内容中修改,修改后点击确定

6. 保存本次转换脚本

7. 点击运行按钮, 然后启动脚本

8. 查看输出文件, 发现格式不正确

   例如: id 存在小数位, age也存在小数位, 生日存在时间点.

9. 回到excel输出位置,修改输出字段格式

   # 代表以字符串形式输出

   0 代表数字仅保留整数部分

   0.00 代表保留两位小数

   yyyy-MM-dd 年-月-日格式

   yyyy-MM-dd HH:mm:ss 年-月-日 时:分:秒 格式

10. 重新保存并执行脚本, 查看最终输出结果,发现结果已经正常显示

知识点4：将excel表数据抽取到表中

准备工作:

如果我们需要将数据从excel中使用kettle抽取到mysql中, 需要先在mysql中创建对应的数据库

因为 kettle不支持创建数据库, 但是可以创建数据表.

drop database if exists kettle_demo;
create database kettle_demo charset = 'utf8';

操作流程

1. 创建一个新的转化脚本, 在kettle中使用ctrl _ n 快速创建

2. 从核心对象中, 将excel输入对象, 和表输出对象拖拽到操作区

3. 按住shift从Excel将箭头拖拽到表输出位置, 指定数据的流转方向

4. 编辑excel输入内容

   • 4.1 双击打开excel输入编辑界面, 点击浏览找到要加载的excel数据,选择成功后点击增加

   • 4.2 选择工作表标签, 获取工作表名称, 选择需要的工作表移动到右侧,点击确定

   • 4.3 点击获取来自头部数据的字段, 点击预览记录, 查看输出内容时,发现数据格式不正确.

   • 4.4 修改数据格式后,点击确定

5. 编辑表输出内容

   • 5.1 双击打开表述出编辑界面, 点击新建, 编辑数据库连接信息,测试连接成功后点击确定.

   • 此时不一定会成功, 失败原因有两个

     • 服务器没有开启, 在连接数据库之前,确保node1是开启状态.

     • 没有放置mysql连接驱动

       • 上述驱动包,放置在 安装目录的lib文件夹下即可

   • 5.2 书写目标表名称, 点击下方sql按钮,修改建表语句中的字段类型, 点击执行成功后,确定,完成编辑.

     此时数据表已经被创建

6. 保存脚本数据

7. 执行脚本查看最终结果

8. 如果我们出现了中文问号问题,就要在mysql连接信息中增加一个键值对

   characterEncoding = utf8

   

9. 增加选项后,重新保存并执行脚本即可

知识点5：将mysql表中的数据插入到另一个表中

准备工作

数据连接共享

共享后,在任意kettle脚本中,都可以使用db连接对象.

操作流程:

1. 将表输入, 和表输出对象拖拽到操作区, 并且按住shift将箭头从表输入连接到表输出

2. 编辑表输入

   • 2.1 获取db连接, 点击下方的获取查询sql语句, 选择对应的表,点击确定.

   • 2.2 查询预览,如果没有问题直接点击确定, 表输入编辑完成

3. 编辑表输出内容

   • 3.1 选择数据库连接, 输入目标表名,点击下方的sql进行建表,之后选择确定.

4. 保存脚本文件

5. 执行脚本文件

6. 验证mysql中的数据信息

知识点6：执行mysql表的插入更新操作

准备工作

清空t_user1表

truncate table kettle_demo.t_user1;

操作流程:

1. 将表输入对象和插入/更新对象拖拽到kettle操作区, 按住shift将数据流转方向指定为从表输入 到插入/更新

2. 编辑表输入

3. 编辑插入/更新内容

   • 3.1 选择数据库连接, 在目标表处选择浏览,找到要插入的表,点击确定即可

   • 3.2 设置两张表中id值相同,即为一个数据记录, 如果数据记录相同,下方更新字段为Y的数据将会进行更新.

4. 保存并执行脚本文件

5. 将t_user表中的李四年龄修改为30岁

6. 再次执行脚本文件,对于修改过的数据进行了更新

知识点7：switch_case条件判断

1. 将表输入 和 excel输出拖拽到操作区, 在中间添加一个switch-case流程 ,使用shift 将数据流转方向指定好

   注意: 将switch-case指定给excel输出时要选择创建一个cse节点.

2. 编辑表输入内容

3. 编辑switch_case流程内容, 先指定条件判断的字段, 并说明不同的case值执行哪一个步骤

4. 编辑excel输出内容, 确定表的输出位置, 以及字段格式, 删除扩展名

5. 保存并执行脚本,验证执行结果是否正确

6. 验证发现数据格式有误,修改输出位置格式后,再次运行.

知识点8：设置转换参数

执行sql脚本文件

配置参数

1. 添加转换参数

2. 创建sql脚本文件, 在sql语句中,使用${参数名称}设置参数, 并勾选下方的替换参数

3. 保存脚本后, 执行脚本, 执行时传入转换参数

知识点9：windows下执行job任务

1. 创建一个作业

2. 将 start 转换 和 成功 三个标签,拖转到操作区,并使用shift拖转箭头,指明任务方向

3. 双击转换,添加之前保存的转换脚本

4. 在start处设置循环执行时间为间隔5秒

5. 保存并执行作业

6. 如果作业需要停止则点击方块即可

今天的学习笔记就到这里 欢迎大家点赞评论 互相交流
祝大家在程序员的道路上越走越远