浅谈离线数据倾斜

hive.map.aggr=true（是否在Map端进行聚合，默认为true），这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻清洗阶段数据传输和Reduce阶段的执行时间，提升总体性能Set hive.groupby.skewindata=true（hive自动进行负载均衡）

set hive.auto.convert.join=true;hive.mapjoin.smalltable.filesize=25000000（ 即25M）

select /*+mapjoin(b)*/a.field1 as field1,b.field2 as field2,b.field3 as field3from a left join bon a.field1 = b.field1; 

select field1,field2,field3…from log a left join user b on a.userid is not null and a.userid=b.useridunion select field1,field2,field3 from log where userid is null;

select a,count(distinct b) from t group by a 可以写成 select a,sum(1) from (select a,b from t group by a,b) group by a;  

select count (distinct key) from  a 可以写成 Select  sum(1) from (Select  key  from  a   group by  key)  t

select * from log ajoin users bon a.user_id is not nulland a.user_id = b.user_idunion allselect * from log awhere a.user_id is null;

select * from users aleft outer join logs bon a.usr_id = cast(b.user_id as string)

select * from log aleft outer join users bon a.user_id = b.user_id;

select /*+mapjoin(x)*/* from log aleft outer join (select /*+mapjoin(c)*/d.*from ( select distinct user_id from log ) cjoin users don c.user_id = d.user_id) xon a.user_id = b.user_id;

spark.shuffle.statistics.verbose=true 

spark.sql.autoBroadcastJoinThreshold=524288000

dataframe.groupBy(col("key"), pmod(hash(col("some_col")), 100)).agg(max("value").as("partial_max")).groupBy(col("key")).agg(max("partial_max").as("max"))

select (... row_number() over(partition by ... order by ...) as rn)    where rn [==|<=|<] k and other conditionsspark.sql.rankLimit.enabled=true （目前支持基于row_number的topK计算逻辑）