Hive union all性能优化

一、原SQL

*****  运行时间58min  ********

select  `time`,srcroot,srctag,terminal ,tokentype as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and tokentype<>'null'  and os_type<>'null' and  method='UserIntf.login' group by `time`,  srcroot, srctag,terminal ,tokentype  
-- union all select  `time`,srcroot,srctag,terminal ,"onekeyLogin" as type,count(1) as cnt from 
-- hdp_quc.ucent_login_rec_hive_part
-- where  `time` ="2022-01-03" and errno='0' and tokentype<>'null' and method='UserIntf.login' group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"smscode" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and smscode<>'null' and method='UserIntf.login' group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"weixinLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='weixin' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"QQLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='qq' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"accLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part where  `time` ="2022-01-03" and errno='0' and smscode='null' and srcroot<>'null' and tokentype='null' and platform='null' and method='UserIntf.login' group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"autoLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and method='CommentAccount.getUserInfo' group by `time`,  srcroot, srctag,terminal 

union all select  `time`,srcroot,srctag,terminal ,"weiboLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='Sina' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal
union all select  `time`,srcroot,srctag,terminal ,"appleLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='apple' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal 
union all select  `time`,srcroot,srctag,terminal ,"googleLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='google' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal 
union all select  `time`,srcroot,srctag,terminal ,"facebookLogin" as type,count(1) as cnt from 
hdp_quc.ucent_login_rec_hive_part
where  `time` ="2022-01-03" and errno='0' and platform='facebook' and (method='CommonAccount.oauthLogin' or method='CommonAccount.oauthLoginNew') group by `time`,  srcroot, srctag,terminal ;

二、调优参数

1.set hive.exec.parallel=true;

设置为TRUE的时候，同一个SQL中可以并行执行的job会并发的执行。

2.set hive.exec.parallel.thread.number=32;

控制对于同一个SQL来说同时可以运行job的最大数，默认为8，此时可以同时运行8个job。

3.性能对比

优化前：58min
优化后：