count(distinct ),在数据量大的情况下,效率较低,如果是多count(distinct )效率更低,因为count(distinct)是按group by 字段分组,按distinct字段排序,一般这种分布方式是很倾斜的。举个例子:比如男uv,女uv,像淘宝一天30亿的pv,如果按性别分组,分配2个reduce,每个reduce处理15亿数据。
SELECT*FROM (SELECTT a1,COUNT(1) FROM T GROUPBY a1) subq WHERE subq.prtn=100; #(多余分区)
SELECT*FROM T1 JOIN (SELECT*FROM T2) subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;
INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age FROM page_view p
JOINuser u ON (pv.userid = u.userid)
JOIN newuser x ON (u.userid = x.userid);
如果 Join 的 key 相同,不管有多少个表,都会则会合并为一个 Map-Reduce
一个 Map-Reduce 任务,而不是 ‘n’ 个
在做 OUTER JOIN 的时候也是一样
如果 Join 的条件不相同,比如:
INSERT OVERWRITE TABLE pv_users
SELECT pv.pageid, u.age FROM page_view p
JOINuser u ON (pv.userid = u.userid)
JOIN newuser x on (u.age = x.age);
Map-Reduce 的任务数目和 Join 操作的数目是对应的,上述查询和以下查询是等价的:
INSERT OVERWRITE TABLE tmptable
SELECT*FROM page_view p JOINuser u
ON (pv.userid = u.userid);
INSERT OVERWRITE TABLE pv_users
SELECT x.pageid, x.age FROM tmptable x
JOIN newuser y ON (x.age = y.age);
场景:有一张 user 表,为卖家每天收到表,user_id,ds(日期)为 key,属性有主营类目,指标有交易金额,交易笔数。每天要取前10天的总收入,总笔数,和最近一天的主营类目。 解决方法 1
如下所示:常用方法
INSERT OVERWRITE TABLE t1
SELECTuser_id,substr(MAX(CONCAT(ds,cat),9) AS main_cat) FROM users
WHERE ds=20120329//20120329 为日期列的值,实际代码中可以用函数表示出当天日期 GROUPBYuser_id;
INSERT OVERWRITE TABLE t2 SELECTuser_id,sum(qty) AS qty,SUM(amt) AS amt FROM users WHERE ds BETWEEN20120301AND20120329 GROUPBYuser_id
SELECT t1.user_id,t1.main_cat,t2.qty,t2.amt FROM t1 JOIN t2 ON t1.user_id=t2.user_id
下面给出方法1的思路,实现步骤如下:
第一步:利用分析函数,取每个 user_id 最近一天的主营类目,存入临时表 t1。
第二步:汇总 10 天的总交易金额,交易笔数,存入临时表 t2。
第三步:关联 t1,t2,得到最终的结果。
解决方法 2
如下所示:优化方法
SELECTuser_id,substr(MAX(CONCAT(ds,cat)),9) AS main_cat,SUM(qty),SUM(amt) FROM users
WHERE ds BETWEEN20120301AND20120329GROUPBYuser_id
SELECT*FROM effect a
JOIN
(SELECT auction_id AS auction_id FROM auctions
UNIONAllSELECT auction_string_id AS auction_id FROM auctions) b
ON a.auction_id=b.auction_id
SELECT*FROM
(SELECT*FROM t1
UNIONALLSELECT c1,c2,c3 COUNT(DISTINCT c4) FROM t2 GROUPBY c1,c2,c3) t3
GROUPBY c1,c2,c3;
由于子查询里头有 COUNT(DISTINCT)操作,直接去 GROUP BY 将达不到业务目标。这时采用 临时表消灭 COUNT(DISTINCT)作业不但能解决倾斜问题,还能有效减少 jobs。
INSERT t4 SELECT c1,c2,c3,c4 FROM t2 GROUPBY c1,c2,c3;
SELECT c1,c2,c3,SUM(income),SUM(uv) FROM
(SELECT c1,c2,c3,income,0AS uv FROM t1
UNIONALLSELECT c1,c2,c3,0AS income,1AS uv FROM t2) t3
GROUPBY c1,c2,c3;
计算 uv 的时候,经常会用到 COUNT(DISTINCT),但在数据比较倾斜的时候 COUNT(DISTINCT) 会比较慢。这时可以尝试用 GROUP BY 改写代码计算 uv。
原有代码
INSERT OVERWRITE TABLE s_dw_tanx_adzone_uv PARTITION (ds=20120329)
SELECT20120329AS thedate,adzoneid,COUNT(DISTINCT acookie) AS uv FROM s_ods_log_tanx_pv t WHERE t.ds=20120329GROUPBY adzoneid
#统计每日IP
CREATETABLE ip_2014_12_29 ASSELECTCOUNT(DISTINCT ip) AS IP FROM logdfs WHERE logdate='2014_12_29';
耗时:24.805 seconds
#统计每日IP(改造)
CREATETABLE ip_2014_12_29 ASSELECTCOUNT(1) AS IP FROM (SELECTDISTINCT ip from logdfs WHERE logdate='2014_12_29') tmp;
耗时:46.833 seconds