existed hive ods_Hive增量更新两种场景的实现

–全表字段更新，根据id更新全表字段

CREATE TABLE ods_table_tmp AS

SELECT id,

col1,

col2,

col3,

col4,

col5,

col6,

col7,

col8

FROM

(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY modified_flag DESC) rno

FROM

(SELECT *, ‘0’ modified_flag FROM u_ods_data.ods_table

UNION ALL

SELECT *, ‘1’ modified_flag FROM u_st_data.st_table)t1

) t2

WHERE rno = 1;

INSERT overwrite TABLE u_ods_data.ods_table

SELECT *

FROM ods_table_tmp;

DROP TABLE IF EXISTS ods_table_tmp;

–部分字段更新,根据id更新col1、col2、col3、col4,其他字段不更新

CREATE TABLE ods_table_tmp AS

SELECT id,

col1,

col2,

col3,

col4,

col5,

col6,

col7,

col8

FROM

(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY modified_flag DESC) rno

FROM

(SELECT *, ‘0’ modified_flag FROM u_ods_data.ods_table

UNION ALL

SELECT *, ‘1’ modified_flag FROM u_st_data.st_table

UNION ALL SELECT b.id,

b.col1,

b.col2,

b.col3,

b.col4,

a.col5,

a.col6,

a.col7,

a.col8

‘2’ modified_flag

FROM u_ods_data.ods_table a

JOIN u_st_data.st_table b on(a.id=b.id))t1) t2

WHERE rno = 1;

INSERT overwrite TABLE u_ods_data.ods_table

SELECT *

FROM ods_table_tmp;

DROP TABLE IF EXISTS ods_table_tmp;