Hive学习小记-（10）hive增量下发的变化流水表如何做update操作

场景

有一张明细事务级别的流水表，主键是事件流水号srl_id, 该表每天采集当天新增及变化的事件下发，上游下发文件分区日期prt_dt.

存在这样的情况，某个流水号srl_id在20210101发生，会在prt_dt=20200101的分区首次下发，若之后在20200105发生改变，在prt_dt=20200105会再次下发。

每个流水号都有一个estb_dt，即首次发生日期，同一srl_id，该日期值不变。

需求是：下游每天接收处理数据，对在20200105发生改变的srl_id，要在下游应用保留分区20200101中更新，即让这个发生改变的流水号srl_id信息在下游第一次落地的分区日期始终保持最新的状态，这个下游分区其实等价于estb_dt首次发生日期。

分析

一般的写法往往有全表扫描：

insert overwrite table 原表

select a.* from 原表 a left join 增量表 b on a.业务不会变化的字段如srl_id=b.业务不会变化的字段 where b.业务不会变化的字段 is null

union all select * from 增量表;

insert overwrite table full_data_table -- 全量
select
    a.pk_col
    a.data_col 
from full_data_table a    
left join inc_data_table b            --数据量很大，会造成全局扫描
on a.pk_col = b.pk_col and b.date=execution_date
where a.date<execution_date
  and b.pk_col is null       --没受影响的数据

union all 
   select 
        cc.data_col 
    from inc_data_table cc
    where date=execution_date；

对历史分区的有更改数据做UPDATE操作；难点在于如何避免全表扫描找有更改的srl_id

优化方法

1. tb_a下游累数分区全量表，选择相对业务srl_id不变的时间字段estb_dt做分区字段，每个分区存放当天estb_dt的srl_id数据；

2. 当天下发的新数据，包含新增业务数据+新增更改数据,建临时增量表 tb_b，也以estb_dt做分区(这里是否有必要？没必要，这里左表大数据量的关联键一定要是分区键，不能全表扫描，但b表只要数据量足够小默认放进内存就行，如果b表没法小怎么办？)

3.通过left semi join 定位出增量表影响到的分区，关联字段取分区字段，避免全表扫描(bug: 1.这里增量临时分区表是否必要，下发增量数据不以estb_dt分区，而是以prt_dt分区会避免全表扫描吗？可的，其实主要是避免a表全表扫描，b表起过滤作用，足够小可以放进内存就可以)

4.通过left join去除受影响分区中要发生变化的数据，这部分即无变化的历史数据

5.再union all当天增量下发数据：即新增业务数据+变更业务数据，就是目标数据

6.这里脚本都未加时间限制，加了时间限制后支持历史执行日期重新调度

一些小疑问：这里若A是增量表，B是全量表也可以操作，只是left semi join会变成左表小，右表大，这样性能会更好吗？

-- 创建增量数据临时表
create table if not exist tb_b ...partition by(esdb_dt int '首次业务日期')
insert overwrite tb_b partition(esdb_dt) select ...
 
--当天数据加工
insert overwrite tb_a partition(estb_dt)
select
  a.srl_id
  a.estb_dt
from tb_a a  -- 全量
left semi join tb_b b1  -- 增量，用left join on key防止全表扫描
  on a.estb_dt=b1.estb_dt     -- 获取历史表受影响的数据分区
left join tb_b b   -- 增量
  on a.srl_id = b.srl_id
where b.srl_id is null        -- 过滤掉b表出现变更的记录，得到未变更历史

union all
select                             --加上增量表(新增srl_id+变更srl_id)
  b.srl_id
  b.estb_dt
from tb_b b  -- 增量

一些其他帖子

full join ：https://blog.csdn.net/magicharvey/article/details/20692829

https://my.oschina.net/sniperLi/blog/755273

业务不会变化的字段：https://blog.csdn.net/iteye_3893/article/details/82651247?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/wujiandao/article/details/80413661

rownumber：http://www.fengxiaokai.cn/archives/27