前言
使用sqoop+oozie, 可以实现大批量数据从mysql到hdfs的自动导入, 我们借助网页可以实时监控流程的运转情况, 但是如果业务逻辑发生变化, 想要调整sqoop+oozie的流程细节并不是件轻松的事.
kettle可以提供一个图形化的用户环境, 来描述我们要做的每一步工作, 以方便后期运维.
此次流程中, 数据从mysql增量导入到hdfs, 然后加载(load
)到hive中的ods层的对应分区, 最后使用sql语句将ods层数据简单清洗到dwb层
此次流程中的全部环节, 借助kettle实现了自动化
一. 下载和安装
- 搜索
kettle中文网
, 可以下载到汉化版 - 配置java环境
- 在环境变量中添加
PENTAHO_JAVA_HOME
, 指向jdk所在位置 - 考虑到测试环境(个人电脑)配置有限, 适当调低kettle默认的资源配置
REM REM开头的字段是bat批处理文件中的注释
REM window环境下请修改$KETTLE_HOME/spoon.bat文件
REM 找到并改变下述代码
REM if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m"
if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xms512m" "-Xmx512m" "-XX:MaxPermSize=256m"
- 双击spoon.bat文件尝试运行
二. jar包依赖
我们需要连接到hdfs, mysql和hive, 所以要准备好对应的jar包
1. 配置hdfs远程连接环境
- 主对象树 -> Hadoop clusters -> 右击 -> new cluster
- 补全相应信息. 实际上只需要填写HDFS和JobTracker
- 测试连接, 选择自己hdfs对应的版本. (以cdh版本为例)
- 在
KETTLE_HOME
下搜索cdh
, 将相关文件夹下的配置文件替换为自己集群的配置文件
2. 配置mysql远程连接环境
直接连接MySQL时可能遇到如下错误
错误连接数据库 [ods] : org.pentaho.di.core.exception.KettleDatabaseException:
Error occurred while trying to connect to the database
Driver class 'org.gjt.mm.mysql.Driver' could not be found, make sure the 'MySQL' driver (jar file) is installed.
org.gjt.mm.mysql.Driver
这是因为缺少mysql连接驱动包.
下载mysql连接驱动包, 移动到$KETTLE_HOME/lib
下
3. 配置hive远程连接环境
- 服务器端启动hive2服务.
hive --service hiveserver2
(注1) - 需要以下jar包(注2)
hadoop-core-1.2.1.jar
hive-common-0.13.0.jar
hive-jdbc-0.13.0.jar
hive-service-0.13.0.jar
libthrift-0.9.1.jar
slf4j-api-1.7.5.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
注1: 使用beeline连接hive也需要先启动该服务,所以可以使用beeline测试服务状态
注2: 未验证是否仅需要所列jar包. 我在linux中使用find -name "hive-*jar" /
全盘搜索了相关jar包, 然后找到对应目录, 将目录下的所有jar包都复制到了$KETTLE_HOME/lib
下
4. 重新启动kettle
三. 构建增量导入的逻辑
增量导入是数据抽取过程中最常见的一种导入逻辑, 该过程的核心就是使用where等条件控制语句, 在导入时指定数据范围.
sqoop中集成了该功能, 而且有Append和Lastmodified两种增量导入的模式. 简而言之,一般情况下, append模式会在每次更新后记录表中的最大主键, 下一次更新时, 只取出主键大于记录值的行; 而Lastmodified模式需要指定一个值为时间戳的列, 下一次更新时, 取时间戳大于记录值的行
虽然kettle没有直接集成这些功能, 但它提供了ETL过程中的大量组件, 我们可以根据需求自定义各种功能.
以增量导入user
表为例, 对应的主键是user_id
1. 建立拉链表
借鉴sqoop中Append的思路, 每次增量导入都要根据last_value筛选新增的值(实际上就是select * from user where user_id > last_value
). 要做到这点, 必然要记录每次增量的情况.
于是在mysql中新建表格update_info
, 表结构如下:
列名 | 类型 | 描述 |
update_id | bigint | 自增主键 |
table_name | varchar(31) | 被导出表的表名 |
last_value | bigint | 导出的控制条件 |
update_status | tinyint | 导出状态(0是未完成) |
create_time | timestamp | 生成该条记录的时间 |
end_time | timestamp | 完成该条记录的时间 |
2. 在拉链表中新增导入记录
- 获取当前时间
current_time
: 核心对象 -> 输入 -> 获取系统信息 - 获取上次更新的
last_value
: 核心对象 -> 输入 -> 表输入
SELECT
last_value
FROM update_info
where table_name='user'
order by end_time desc
limit 1
;
- 使用sql语句在
update_info
表中新增记录: 核心对象 -> 脚本 -> 执行sql脚本
insert into update_info
(table_name, last_value, create_time, update_status) values
('user', ?, '?', 0)
;
-- 变量替换: { 1 : current_time ; 2 : last_value }
3. 增量导入
- 得到
last_value
- 得到增量数据: 核心对象 -> 输入 -> 表输入
SELECT
*
FROM user
where user_id > ?
;
-- 变量替换: { 1: last_value }
- 输出为指定格式: 核心对象 -> 输出 -> Big Data -> Avro output
Location=HDFS
Folder/File namehdfs://root:***@cdh1:8020/data/temp/user.avro
# 该文件不能已存在, kettle会在该路径上生成文件(而不是文件夹)
4. 在拉链表中更新记录
1) 获取当前时间current_time
2) 获取此次更新的last_value
, 也就是最大的user_id
: 核心对象 -> 输入 -> 表输入
SELECT
user_id
, 1 flag
FROM user
order by user_id desc
limit 1
;
3) 更新数据和状态: 核心对象 -> 输出 -> 插入/更新
目标表: update_info
用来查询的关键字
表字段 | 比较符 |
|
|
更新字段
表字段 | 流字段 | 更新 |
end_time | now_time | Y |
last_value | user_id | Y |
update_status | flag | Y |
5. 导入到hive中对应的ods层
骚操作来了user
表在ods层对应的表是ods_user
, 该表以字符串形式的日期(date string
)作为分区依据, 例如"2019-5-19"
但是kettle中, 获取到的系统时间只有时间戳格式, 自动转换为字符串后会变成"2019/05/19 20:00:49.669"
.
为了转换系统时间, 我新建了一个只有一行的表, 表名为ods.ods_tool
. 然后步骤如下:
- 获取当前时间
current_time
- 利用hive函数转换时间戳: 核心对象 -> 输入 -> 表输入
SELECT
to_date(?) as str
FROM ods.ods_tool
-- 变量替换: { 1: current_time }
- 将hdfs上的文件导入hive: 核心对象 -> 脚本 -> sql脚本
load data inpath '/data/temp/user.avro' into table ods.ods_user partition (dt='?');
-- 变量替换: { 1 : str }
6. ods层清洗到dwb层
和步骤5类似, 改变sql语句即可