多个count(distinct)导致data skew的优化策略

hive中对于count(distinct)的执行逻辑大体是，在mapper端用HashSet将key去重后，全部发送给1个reducer再做去重，这样的问题在于会有单点问题。如果只有一个count(distinct)，则通过设置SET hive.groupby.skewindata = true;可以使执行逻辑自动优化避免data skew. 但如果有两个及以上则上述参数不会起作用。

具体case:

如下sql每天会执行11h，切换成优化后的select count(1) from (select * from ... group by ...) tmp之后，只需要28min即可。

--before optimization




select

  COUNT(DISTINCT (CASE WHEN field_a = 1 THEN field_b ELSE NULL END)) / COUNT(DISTINCT field_b)

from

  table_name

where

  label_spam_user = 0







--after optimization




with tmp1 as(

    select 

        field_b

    from table_name

    where field_a = 1

    group by field_b

)

, tmp2 as(

    select

        count(1) as cnt_1

    from tmp1

)

, tmp3 as(

    select 

        field_b

    from table_name

    group by field_b

)

, tmp4 as(

    select

        count(1) as cnt_2

    from tmp3

)

select 

    cnt_1/cnt_2

from tmp2

join tmp4 on 1=1;