Hive 优化之 union all 与 multi insert

素颜马尾好姑娘i 2024-04-18 11:02 37阅读 0赞

对同一张表的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'