hive中实现merge into

低版本的hive中有很多函数或者语句不支持使用，比如merge into就是一个。
比如有两表

表一：testtable1             
包含以下字段：               
            name
            project
			projectid
			grademath
			yearq
			monthq
			dayq
表二：testtable2
包含以下字段：
			name 
			grade
			gradeid
			math
			chinese
			english

如果在oracle中出现以下语句：

merge into dw.testtable1 t
using (
  select 
      t1.name name,
      t1.grade grade,
      t1.gradeid gradeid,
      t1.math math
      from testtable2 t1
)s 
on (t.projectid=s.gradeid)
when matched then
  update set t.grademath=s.math  

在hive中可以改成

with q1 as (
  select 
        t1.math,
        t1.gradeid
        from testtable2 t1
)

insert overwrite TABLE testtable1
select 
    t1.name,
    t1.project,
    t1.projectid,
    nvl(t.math,t1.grademath),
    t1.year,
    t1.month,
    t1.day
    from testtable1 t1
    left join q1 t on t1.projectid=t.gradeid
  

简化的话还可以
insert overwrite TABLE testtable1
select 
    t1.name,
    t1.project,
    t1.projectid,
    nvl(t.math,t1.grademath),
    t1.year,
    t1.month,
    t1.day
    from testtable1 t1
    left join (select 
        tt.math,
        tt.gradeid
        from testtable2 tt) t on t1.projectid=t.gradeid