Hive增量更新

/ Hive / 没有评论 / 99浏览

介绍一种Hive增量更新的方案。

场景

我们在Hive中存储数据,然后每天将变化的数据从MySQL等增量抽取到Hive中,进行离线分析。

表结构

以用户表user为例:

idnameageupdate_at
1zhangsan112018-08-16
2lisi222018-08-16
3wangwu442018-08-16

第二天数据更新了,下面是sqoop抽取到的变化的数据,以user_tmp为例:

idnameageupdate_at
1zhangsanfeng112018-08-17
4wangwu332018-08-17

模拟增量更新

Hive表结构

create table user(
id             int,
name           string,
age            tinyint,
update_at      string
)
partitioned by(year int)
row format delimited
fields terminated by '|'
stored as textfile;
create table user_tmp(
id             int,
name           string,
age            tinyint,
update_at      string
)
partitioned by(year int)
row format delimited
fields terminated by '|'
stored as textfile;

其中,year可能是该年加入的用户,用来分区。

插入数据

数据

1|zhangsan|11|2018-08-16
2|lisi|22|2018-08-16
3|wangwu|44|2018-08-16
1|zhangsanfeng|11|2018-08-17
4|wangwu|33|2018-08-17

导入Hive

在Hive shell中执行:

load data local inpath 'user.txt' into table user partition(year=2018);

load data local inpath 'user_tmp.txt' into table user_tmp partition(year=2018);

最新数据

-- 覆盖数据
insert overwrite table user partition(year)
-- 变化的数据
select id, name, age, update_at, year
from user_tmp
union all
-- 下面是未变化的数据
select u.id, u.name, u.age, u.update_at, u.year
from user u
left outer join user_tmp ut
on u.id = ut.id
where ut.id is null

上面就把所有数据更新了。

但是,这样做了全量更新,我们可以利用分区,只更新那些有数据变化的分区。

只更新分区有变化的

加一个表change_year_tmp,存储有变化的分区。

create table user_tmp(
year int
);

找出变化的数据中,变化的分区:

insert overwrite table change_year_tmp
select distinct(year) from user_tmp

最新的sql如下:

-- 仅仅覆盖分区有变化数据
insert overwrite table user partition(year)
-- 分区有变化的数据中变化的数据
select id, name, age, update_at, year
from user_tmp
union all
-- 下面是分区有变化的数据中未变化的数据
select u.id, u.name, u.age, u.update_at, u.year
from (
-- 这里查分区有变化的分区数据
select a.* from user a, change_year_tmp b where a.year = b.year
) u
left outer join user_tmp ut
on u.id = ut.id
where ut.id is null