Hive 处理count distinct 产生数据倾斜处理

问题描述

问题数据倾斜范畴，但是又不可以在Map端join，剔除特殊Key等方法进行处理。

set hive.groupby.skewindata=true;insert overwrite table ad_overall_day partition（part_time="99", part_date="2015-11-99"） select account_id, nvl（client_id,-1）, nvl（track_id,"total"）, sum（if（type=3,1,0）） as imp_cnt, sum（if（type=4,1,0）） as click_cnt, count（distinct if（type=3,zid,NULL）） as imp_uv,count（distinct if（type=4,zid,NULL）） as click_uv from derived_di_v3 where year="2015" and month="11" group by account_id, client_id, track_id grouping sets（（account_id, client_id, track_id）,（account_id, client_id）,（account_id））

But error find.
FAILED: SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in data

separate group-by and join the results.

set hive.groupby.skewindata=true;set hive.exec.parallel=true;insert overwrite table ad_overall_day partition（part_time="99", part_date="2015-11-99"） SELECT COALESCE（t1.account_id,t2.account_id）,COALESCE（t1.client_id,t2.client_id）,COALESCE（t1.track_id,t2.track_id）,t1.imp_cnt,t1.imp_uv,t2.click_cnt,t2.click_uvFROM（select account_id, nvl（client_id,-1） as client_id, nvl（track_id,"total"） as track_id, sum（if（type=3,1,0）） as imp_cnt, count（distinct if（type=3,zid,NULL）） as imp_uvFROM derived_di_v3 where year="2015" and month="11" group by account_id, client_id, track_id grouping sets（（account_id, client_id, track_id）,（account_id, client_id）,（account_id）） ） t1FULL OUTER JOIN（select account_id, nvl（client_id,-1） as client_id, nvl（track_id,"total"） as track_id, sum（if（type=4,1,0）） as click_cnt, count（distinct if（type=4,zid,NULL）） as click_uvFROM derived_di_v3 where year="2015" and month="11" group by account_id, client_id, track_id grouping sets（（account_id, client_id, track_id）,（account_id, client_id）,（account_id）） ） t2ON t1.account_id = t2.account_id and t1.client_id = t2.client_id and t1.track_id = t2.track_id;

Cann’t run two MapReduce Job

Unfortunately, hive doesn’t explain the hql to two mapreduce job.
The parameter hive.groupby.skewindata seems has no affect.Change the hql :

insert overwrite table ad_overall_day partition（part_time="99", part_date="2015-11-99"）select account_id, nvl（client_id,-1） as client_id, nvl（track_id,"total"） as track_id, sum（imp1） as imp_cnt,count（imp2） as imp_uv,sum（click1） as click_cnt,count（click2） as click_uvFROM （select account_id, client_id, track_id, if（type=3,1,0） as imp1,if（type=3,zid,NULL） as imp2, if（type=4,1,0） as click1,if（type=4,zid,NULL） as click2FROM dmp.derived_di_v3 where year="2015" and month="11" group by account_id, client_id, track_id,type,zid） tgroup by account_id, client_id, track_id grouping sets（（account_id, client_id, track_id）,（account_id, client_id）,（account_id））

Help Links

HIVE-474
HIVE-537Hive编程指南 PDF 中文高清版 http://www.linuxidc.com/Linux/2015-01/111837.htm基于Hadoop集群的Hive安装 http://www.linuxidc.com/Linux/2013-07/87952.htmHive内表和外表的区别 http://www.linuxidc.com/Linux/2013-07/87313.htmHadoop + Hive + Map +reduce 集群安装部署 http://www.linuxidc.com/Linux/2013-07/86959.htmHive本地独立模式安装 http://www.linuxidc.com/Linux/2013-06/86104.htmHive学习之WordCount单词统计 http://www.linuxidc.com/Linux/2013-04/82874.htmHive运行架构及配置部署 http://www.linuxidc.com/Linux/2014-08/105508.htmHive 的详细介绍：请点这里
Hive 的下载地址：请点这里本文永久更新链接地址