数仓中全量数据和增量数据的合并方案

在hive数仓中我们经常会涉及到需要将ods层的历史全量数据同每天的增量数据进行合并的需求。
由于每天的增量数据中包含了新增数据和历史数据的修改，
所以合并的时候
对于新增的数据可以直接插入到全量表，
对于修改的数据需要更新全量表的对应历史数据。

两张表的字段都一样
base：全量表
log：增量表（包括新增和修改的数据）

1. sql

select * from base a where 0=(select count(1) from log b where a.id=b.id)  
union all
select * from log

先查出base表有但log表没有的数据,再查出增量log的数据，两份数据合并即可

2.开窗函数

select b.* from 
(select a.*,row_number() over(partition by id order by lastmodificationtime desc) as no from 
(select * from base union all select * from log) a) b
where b.no = 1		

先把base表和log表合并，然后再整体加开窗函数对相同id的数据按最后修改时候进行排序，取no排第一的数据即可去重

3.merge into

hive2.2之后可以使用 merge into进行数据合并

3.1 先要设置参数

set hive.support.concurrency = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

3.2 对要update的表要求：

• 带有分桶属性

• 数据格式为orc

• 指定参数(‘transactional’ = true)

    CREATE TABLE base(
     id int,
     name string,
     address string,
     lastmodificationtime timestamp)
    CLUSTERED BY (id) into 5 buckets 
    STORED AS ORC TBLPROPERTIES ('transactional'='true');
  

3.3 merge into语法：

MERGE INTO <target table> AS T USING <source expression/table> AS S
ON <boolean expression1>
WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list>
WHEN MATCHED [AND <boolean expression3>] THEN DELETE
WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>

合并

MERGE INTO base AS T USING log AS S
ON T.id = S.id 
WHEN MATCHED THEN UPDATE SET name=S.name,address=S.address,lastmodificationtime=S.lastmodificationtime
WHEN NOT MATCHED THEN INSERT VALUES(S.id,S.name,S.address,S.lastmodificationtime)

4.总结

如果是2.2版本之前的推荐使用第1种方法，如果是2.2版本之后的就推荐使用第3种方法。

别问为什么，问就一个字，快