一、原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
优化后: