hive中合理使用union all与multi insert

对同一张表的union all 要比多重insert快的多，
原因是hive本身对这种union all做过优化，即只扫描一次源表；

http://www.apacheserver.net/How-is-Union-All-optimized-in-Hive-at229466.htm

而多重insert也只扫描一次，但应为要insert到多个分区，所以做了很多其他的事情，导致消耗的时间非常长；
希望大家在开发的时候多测，多试！

 

lxw_test3 12亿左右记录数

Union all : 耗时7分钟左右

 

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'

更多大数据Hadoop、Spark、Hive的文章，请关注 我的博客

 

 

评论

4 楼 元博之 2013-03-28  

还不知道啊为啥%

3 楼 元博之 2013-03-28  

，刚好有用到，实验结果跟你的一样，union all 耗时比multi-insert少很多，唯一的不同的是创建表我用的动态分区。

2 楼 superlxw1234 2012-08-02  

wsbxd 写道

多重insert的时候，是不是有多少个select就会产生多少个JOB？

不是的，一个insert overwrite ... select就会产生2个job了，另外还得看select语句的逻辑

1 楼 wsbxd 2012-07-31  

多重insert的时候，是不是有多少个select就会产生多少个JOB？