hive中实现merge into的功能（hive中低版本中无直接使用merge into的情况）

insert overwrite table dim_date_100w
-- 旧的改变了的数据
select t2.date_key,t2.day,t2.month,t2.month_name,t2.year,t2.year_month,t2.week_of_year,t2.week,t2.week_name,1001 as quarter
from dim_date_100w t1
join dim_date_1w t2 on t1.date_key=t2.date_key
-- 旧的不变的数据
union all
select t1.*
from dim_date_100w t1
left join dim_date_1w t2 on t1.date_key=t2.date_key
where t2.date_key is null
-- 新增的数据
union all
select t1.*
from dim_date_1w t1
left join dim_date_100w t2 on t1.date_key=t2.date_key
where t2.date_key is null
;

Hive2.3.5
MERGE INTO dim_date_100w AS T USING dim_date_1w AS S
ON t.date_key=s.date_key
WHEN MATCHED THEN UPDATE SET quarter=1001
WHEN NOT MATCHED THEN INSERT VALUES(S.date_key,S.day,S.month,S.month_name,S.year,S.year_month,S.week_of_year,S.week,S.week_name,S.quarter);