使用kettle自动增量导入数据

前言

使用sqoop+oozie, 可以实现大批量数据从mysql到hdfs的自动导入, 我们借助网页可以实时监控流程的运转情况, 但是如果业务逻辑发生变化, 想要调整sqoop+oozie的流程细节并不是件轻松的事.
kettle可以提供一个图形化的用户环境, 来描述我们要做的每一步工作, 以方便后期运维.

此次流程中, 数据从mysql增量导入到hdfs, 然后加载(load)到hive中的ods层的对应分区, 最后使用sql语句将ods层数据简单清洗到dwb层
此次流程中的全部环节, 借助kettle实现了自动化

一. 下载和安装

1. 搜索kettle中文网, 可以下载到汉化版
2. 配置java环境
3. 在环境变量中添加PENTAHO_JAVA_HOME, 指向jdk所在位置
4. 考虑到测试环境(个人电脑)配置有限, 适当调低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"

5. 双击spoon.bat文件尝试运行

二. jar包依赖

我们需要连接到hdfs, mysql和hive, 所以要准备好对应的jar包

1. 配置hdfs远程连接环境

1. 主对象树 -> Hadoop clusters -> 右击 -> new cluster
2. 补全相应信息. 实际上只需要填写HDFS和JobTracker
3. 测试连接, 选择自己hdfs对应的版本. (以cdh版本为例)
4. 在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远程连接环境

1. 服务器端启动hive2服务. hive --service hiveserver2(注1)
2. 需要以下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. 在拉链表中新增导入记录

1. 获取当前时间current_time: 核心对象 -> 输入 -> 获取系统信息
2. 获取上次更新的last_value: 核心对象 -> 输入 -> 表输入

SELECT
  last_value
FROM update_info
where table_name='user'
order by end_time desc
limit 1
;

3. 使用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. 增量导入

1. 得到last_value
2. 得到增量数据: 核心对象 -> 输入 -> 表输入

SELECT
*
FROM user
where user_id > ?
;
-- 变量替换: { 1: last_value }

3. 输出为指定格式: 核心对象 -> 输出 -> 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	is null

更新字段

表字段	流字段	更新
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. 然后步骤如下:

1. 获取当前时间current_time
2. 利用hive函数转换时间戳: 核心对象 -> 输入 -> 表输入

SELECT
to_date(?) as str
FROM ods.ods_tool
-- 变量替换: { 1: current_time }

3. 将hdfs上的文件导入hive: 核心对象 -> 脚本 -> sql脚本

load data inpath '/data/temp/user.avro' into table ods.ods_user partition (dt='?');
-- 变量替换: { 1 : str }

6. ods层清洗到dwb层

和步骤5类似, 改变sql语句即可