hive表数据更新merge into

​ 背景： 根据甲方要求，需要对大数据平台指定表(hive、impala表)的历史数据[2021-01-01至2023-03-29]指定字段进行批量更新，然后把表同步到Oracle。先更新大数据平台上的表，再把更新完成的表同步到Oracle。hive有8张表更新，其中4张大表【分区表】（数据量分别为：1038738976、260958144、25860509、2867005），另外4张小表(几万、二十几万的样子)。

一、小表更新，不用按月\按分区更新，直接全量更新。

insert overwrite table 表a (字段1,字段2,...,字段n) select 字段1,字段2,..., nvl(t2.projectbelong,t1.projectbelong) projectbelong, ..., 字段n from 表a t1 left join 表b t2 on t1.root_item_code=t2.desc1; 二、大表更新，拿其中一张表举例：按月\按分区更新

方法一：

insert overwrite table 表1 partition (date_month = '2021-01',date_day,org_code) select 字段1,字段2,..., nvl(t2.projectbelong,t1.projectbelong) projectbelong, ..., 字段n, --t1.date_month, t1.date_day, t1.org_code from (select * from 表1 where date_month = '2021-01') t1 left join 表2 t2 on t1.root_item_code=t2.desc1; 替换date_month日期即可。

方法二：

使用impala外部命令：impala-shell

1、创建impala.sql脚本，内容如下：

Linux上，使用vim：

vim impala.sql  写入以下内容：

insert overwrite table 表1 partition (date_month = '${var:CURR_TIME}',date_day,org_code) select 字段1,字段2,..., nvl(t2.projectbelong,t1.projectbelong) projectbelong, ..., 字段n, --t1.date_month, t1.date_day, t1.org_code from (select * from 表1 where date_month = '${var:CURR_TIME}') t1 left join 表2 t2 on t1.root_item_code=t2.desc1; 2、impala外部命令：

impala-shell -f impala.sql -d tianma_bi --var CURR_TIME='2021-01'; 3、多个月份，使用vim命令创建bash文件：impala.sh

impala-shell -f impala.sql -d tianma_bi --var CURR_TIME='2021-01'; impala-shell -f impala.sql -d tianma_bi --var CURR_TIME='2021-02'; impala-shell -f impala.sql -d tianma_bi --var CURR_TIME='2021-03'; 4、执行sh文件：

sh impala.sh 更新总结：m-分钟、s-秒 1、百万级表数据总量：500万，更新用时约20s 2、千万级表数据总量：2500万，更新用时约1m 3、亿级表数据总量：分区更新 50万，更新用时约17s 100万，更新用时约32s 500万，更新用时约2m10s 1000万，更新用时约5m 1500万，更新用时约6m 2000万，更新用时约6m30s 5000万，更新用时约16m 7500万，更新用时约22m 根据以上，估算1亿数据更新用时约35m

方法三：也可使用merge into

说明：Hive在2.2版本之后开始支持Merge操作，并且Merge只能在支持ACID的表上执行。低版本的hive中有很多函数或者语句不支持使用，比如merge into

举个例子：

MERGE INTO merge_data.transactions AS T USING merge_data.merge_source AS S ON T.ID = S.ID and T.tran_date = S.tran_date WHEN MATCHED AND (T.TranValue != S.TranValue AND S.TranValue IS NOT NULL) THEN UPDATE SET TranValue = S.TranValue ,last_update_user = 'merge_update' WHEN MATCHED AND S.TranValue IS NULL THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES ( S.ID , S.TranValue , 'merge_insert' , S.tran_date ); 建议使用merge into，效率更快一些。 

​