Hive 优化之 union all 与 multi insert

对同一张表的union all 要比多重insert快的多，
原因是hive本身对这种union all做过优化，即只扫描一次源表；而多重insert也只扫描一次，但应为要insert到多个分区，所以做了很多其他的事情，导致消耗的时间非常长；
希望大家在开发的时候多测，多试！

lxw_test3 12亿左右记录数

Union all : 耗时7分钟左右

USE dmz_tmp;
set hive.exec.parallel = true;
CREATE TABLE  lxw_test5 AS   
SELECT type,popt_id,login_date   
FROM (  
        select 'm3_login' as type,popt_id,login_date    
        from lxw_test3   
        where login_date>='2012-02-01' and login_date<'2012-05-01'   
        union all   
        select 'mn_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-05-01' and login_date<='2012-05-09'   
        union all   
        select 'm3_g_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='1'   
        union all   
        select 'm3_l_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='2'   
        union all   
        select 'm3_s_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='3'   
        union all   
        select 'm3_o_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='4'   
        union all   
        select 'mn_g_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='1'   
        union all   
        select 'mn_l_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='2'   
        union all   
        select 'mn_s_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='3'   
        union all   
        select 'mn_o_login' as type,popt_id,login_date   
        from lxw_test3   
        where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='4'   
) x  

多重insert耗时25分钟左右：

from lxw_test3   
insert overwrite table lxw_test6 partition (flag = '1')   
select 'm3_login' as type,popt_id,login_date    
where login_date>='2012-02-01' and login_date<'2012-05-01'   
insert overwrite table lxw_test6 partition (flag = '2')   
select 'mn_login' as type,popt_id,login_date   
where login_date>='2012-05-01' and login_date<='2012-05-09'   
insert overwrite table lxw_test6 partition (flag = '3')   
select 'm3_g_login' as type,popt_id,login_date   
where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='1'   
insert overwrite table lxw_test6 partition (flag = '4')   
select 'm3_l_login' as type,popt_id,login_date   
where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='2'   
insert overwrite table lxw_test6 partition (flag = '5')   
select 'm3_s_login' as type,popt_id,login_date   
where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='3'   
insert overwrite table lxw_test6 partition (flag = '6')   
select 'm3_o_login' as type,popt_id,login_date   
where login_date>='2012-02-01' and login_date<'2012-05-01' and apptypeid='4'   
insert overwrite table lxw_test6 partition (flag = '7')   
select 'mn_g_login' as type,popt_id,login_date   
where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='1'   
insert overwrite table lxw_test6 partition (flag = '8')   
select 'mn_l_login' as type,popt_id,login_date   
where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='2'   
insert overwrite table lxw_test6 partition (flag = '9')   
select 'mn_s_login' as type,popt_id,login_date   
where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='3'   
insert overwrite table lxw_test6 partition (flag = '10')   
select 'mn_o_login' as type,popt_id,login_date   
where login_date>='2012-05-01' and login_date<='2012-05-09' and apptypeid='4'