使用hive增量更新

目录

参考文末文章,加上自己的理解。

1、增量更新

有一个 base_table 表存放的是 12 月 15 日及其之前的所有数据,当 12 月 16 日的数据产生后,存入 incremental_table 表的当日分区中。

现在需要,将 incremental_table 表的新增数据合并到 base_table 表中。

那么,就有两种情况:

(1)保留历史数据

通过将主表建成拉链表实现:

将 历史数据中修改了的数据 union 当日新增的数据,

再 insert overwrite 到 base_table 表。

这样的话,就会存在重复的数据,保留了历史数据。

(2)不保留了历史数据

方法1:

先将 base_table 表和 incremental_table 表 left join,将 base_table 表中没有修改的数据插入到 base_table 表,

再将 incremental_table 表中的增量数据(最新数据)插入到 base_table 表。

方法2:

将 base_table 表和 incremental_table 表 union all ,再取更新时间最新的记录。

这样,就不会存在重复的数据,但是没有了历史数据。

2、对第一种情况

通过将主表建成拉链表实现

2.1、准备工作

(1)建表

-- 存放产生的每日增量数据,按天分区
create table incremental_table (
    id string,
    name string,
    addr string
) comment '增量表'
partitioned by (dt string)
row format delimited fields terminated by ','
stored as textfile;

-- 存放更新后的数据
create table base_table (
    id string,
    name string,
    addr string,
    start_date string,
    end_date string
) comment '主表'
row format delimited fields terminated by ','
stored as textfile;

(2)数据

incre0.txt:导入主表的历史数据

(模拟主表已有数据)

1,lijie,chongqing,20191020,99991231
2,zhangshan,sz,20191020,99991231
3,lisi,shanghai,20191020,99991231
4,wangwu,usa,20191020,99991231

incre1.txt:导入增量表的 20191020 新增数据

1,lijie,chongqing
2,zhangshan,sz
3,lisi,shanghai
4,wangwu,usa

incre2.txt:导入增量表的 20191021 新增数据

1,lijie,chengdu      # 地址变了
2,zhangshan,huoxing  # 地址变了
4,wangwu,lalalala    # 地址变了
5,xinzeng,hehe       # 新增数据

(3)导入数据

-- 将 incre0.txt 导入主表中,表示主表已经有数据了,
-- 现在需要更新主表里的数据
load data local inpath '/root/data/incre0.txt' overwrite into table base_table;

hive> select * from base_table;
OK
1       lijie   chongqing       20191020        99991231
2       zhangshan       sz      20191020        99991231
3       lisi    shanghai        20191020        99991231
4       wangwu  usa     20191020        99991231

-- 将 incre1.txt 和 incre2.txt 分别导入增量表中的相应分区中
load data local inpath '/root/data/incre1.txt' overwrite into table incremental_table partition (dt='20191020');

load data local inpath '/root/data/incre2.txt' overwrite into table incremental_table partition (dt='20191021');

hive> select * from incremental_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021

2.2、更新数据

-- 将 历史数据中修改了的数据 union 当日新增的数据,
-- 再 insert overwrite 到 base_table 表。
-- 也可以使用 hive 的 merge into 语法,但从 Hive 2.2 版本才开始可用,且只能在支持 ACID 的表上执行。
insert overwrite table base_table
select * from
(
    select a.id,  -- 更新历史数据中修改了的数据
           a.name,
           a.addr,
           a.start_date,
           case 
                when a.end_date='99991231' and b.id is not null then '20191020'  -- 更新了end_date
                else a.end_date
           end as end_date
    from base_table as a
    left join (select * from incremental_table where dt='20191021') as b 
    on a.id=b.id
union 
    select c.id,   -- 添加当日新增的数据
           c.name,
           c.addr,
           '20191021' as start_date,
           '99991231' as end_date
    from incremental_table c
    where c.dt='20191021'
) as t;

hive> select * from base_table;
OK
1       lijie   chengdu 20191021        99991231
1       lijie   chongqing       20191020        20191020
2       zhangshan       huoxing 20191021        99991231
2       zhangshan       sz      20191020        20191020
3       lisi    shanghai        20191020        99991231
4       wangwu  lalalala        20191021        99991231
4       wangwu  usa     20191020        20191020
5       xinzeng hehe    20191021        99991231

3、对第二种情况

3.1、准备工作

(1)建表

create table incremental_table (
    id string,
    name string,
    addr string
) comment '增量表'
partitioned by (dt string)
row format delimited fields terminated by ','
stored as textfile;

create table base_table (
    id string,
    name string,
    addr string
) comment '主表'
partitioned by (dt string)
row format delimited fields terminated by ','
stored as textfile;

(2)数据

源数据incre0.txt

1,lijie,chongqing
2,zhangshan,sz
3,lisi,shanghai
4,wangwu,usa

增量数据incre1.txt

1,lijie,chengdu      # 地址变了
2,zhangshan,huoxing  # 地址变了
4,wangwu,lalalala    # 地址变了
5,xinzeng,hehe       # 新增数据

(3)导入数据

-- 将 incre0.txt 导入主表中
load data local inpath '/root/data/incre0.txt' overwrite into table base_table partition (dt='20191020');

hive> select * from base_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020

-- 将 incre0.txt 和 incre1.txt 导入增量表中
load data local inpath '/root/data/incre0.txt' overwrite into table incremental_table partition (dt='20191020');

load data local inpath '/root/data/incre1.txt' overwrite into table incremental_table partition (dt='20191021');

hive> select * from incremental_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021

3.2、方法1

先将 base_table 表和 incremental_table 表 left join,将 base_table 表中没有修改的数据插入到 base_table 表,

再将 incremental_table 表中的增量数据插入到 base_table 表。

hive> select * from base_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020

hive> select * from incremental_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021

insert overwrite table base_table
    select a.id,  -- 插入 base_table 表中没有修改的数据
           a.name,
           a.addr,
           a.dt
    from base_table a
    left join (select * from incremental_table where dt='20191021') b
    on a.id=b.id
    where b.id is null 
    union
    select c.id,   -- 插入 incremental_table 表中的增量数据,即最新数据
           c.name,
           c.addr,
           c.dt
    from (select * from incremental_table where dt='20191021') c;

hive> select * from base_table;
OK
3       lisi    shanghai        20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021  

3.3、方法2

将 base_table 表和 incremental_table 表 union all ,再取更新时间最新的记录。

【可以通过窗口函数编一个序号,也可以使用 hive 的预定义属性最近更新时间字段】

hive> select * from base_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020

hive> select * from incremental_table;
OK
1       lijie   chongqing       20191020
2       zhangshan       sz      20191020
3       lisi    shanghai        20191020
4       wangwu  usa     20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021

insert overwrite table base_table
select b.id,b.name,b.addr,b.dt 
from 
(
    select a.*,
           row_number() over(distribute by a.id sort by a.dt desc) as rn
    from 
    (
        select id,name,addr,dt from base_table
        union all  -- 这里是 union all
        select id,name,addr,dt from incremental_table where dt='20191021'
    ) a
) b
where b.rn=1;

hive> select * from base_table;
OK
3       lisi    shanghai        20191020
1       lijie   chengdu 20191021
2       zhangshan       huoxing 20191021
4       wangwu  lalalala        20191021
5       xinzeng hehe    20191021 

参考地址:

https://www.cnblogs.com/lxbmaomao/p/9821128.html

https://blog.csdn.net/qq_20641565/article/details/52763663

https://blog.csdn.net/qq_20641565/article/details/53164155?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control

https://blog.csdn.net/ZhouyuanLinli/article/details/86638454?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.control

相关推荐

发表评论

路人甲

网友评论(0)