–全表字段更新,根据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;