hiveSql 优化

表现：reduce卡到99%不动或者某几个reduce长时间的执行

1.mapper端优化

distribute by增加reducer个数和后续任务的mapper任务

由于字段数据的不均匀，按照某一字段分区reduce执行时会产生数据倾斜，此时在distribute by 后加一个随机数，按照随机数分区，每个分区的数量大致一致。同时可以修改reducer的个数和最终的hdfs文件个数，作为后续任务的mapper个数。

这个模型作为接下来计算的输入，有多少个文件就有多少个mapper进行处理。为了加大计算的mapper个数可以手动指定该模型的reducer个数来实现。默认情况下计算的mapper个数就是文件大小除以文件块（64M或者128M）大小。

use vetl;
set mapreduce.job.queuename=video;
set mapred.reduce.tasks=300;
insert overwrite table push_alltype_detail_tmp partition(p_day=$idate, push_type='general')
select 
    pro,
    mfo
from push_alltype_detail_tmp
where p_day=$idate
and push_type='general'
distribute by rand()      //300个里面随机分配

2、join数据倾斜优化

Join 执行阶段会将 Join Key 相同的数据分发到同一个执行 Instance 上处理 。如果某个Key 上的数据量比较大，则是发生数据倾斜，会导致该 Instance 执行时间较长。

map join

大小表join导致数据倾斜，可以使用mapjoin，将小表load到内存，广播到各个分区，mapjoin(小表)

set odps.sql.mapjoin.memory.max=2048;
SELECT  /*+ mapjoin (certain_id) */
-- SELECT 
        tls_coupon.buyer_id
        ,tls_coupon.activity_id
        ,tls_coupon.activity_name
        ,tls_coupon.coupon_type
FROM    (
        SELECT
                buyer_id
                ,template_code
                ,activity_id
                ,activity_name
                ,coupon_type
        FROM    tripcdm.dwd_tls_di
        WHERE   ds = '${bizdate}'
        ) tls_coupon
JOIN (
        SELECT  *
        FROM    trip_flight.dim_certain_sell_activity_id
        WHERE   xitem_type = '延误'
     ) certain_id
ON      tls_coupon.template_code = certain_id.template_code
;

空值join

空值join的情况导致数据倾斜，使用随机数均匀分区数  coalese(col,rand())

select col_a, col_b
from table_a  
left join table_b on coalesce(table_a.key, rand()*9999) = table_b.key

热点key

热点数据实现思路：

1.对于主表的处理：将这几个key对应的数据从原来的数据表中拆分出来，形成一个单独的表，并给每个key都打上n以内的随机数作为后缀。
2.对于从表的处理：也过滤出来那几个倾斜的key对应的数据并形成一个单独的表，将每条数据膨胀成n条数据，这n条数据都按顺序附加一个0～n的后缀。
3.再将附加了随机后缀的独立表与另外一个膨胀n倍的独立表进行join，此时就可以将原先相同的key打算成n份，分散到多个task中去进行join了。
4.而另外两个普通的rdd就照常join即可。
5.最后将两次join的结果使用union算子合并起来即可，就是最终join结果。

Skew Join Hint参数避免热值倾斜

开启功能：set odps.sql.skewjoin=true/false
设置倾斜的Key及对应的值：set odps.sql.skewinfo=skewed_src:(skewed_key) [("skewed_value")]，
其中skewed_key代表倾斜的列，skewed_value代表倾斜列上的倾斜值。


Skew Join Hint避免热值倾斜
使用方法
-- 方法1：hint表名(注意hint的是表的alias）
select /*+ skewjoin(a) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1;
-- 方法2：hint表名和认为可能产生倾斜的列，下面的case认为a的c0和c1列存在数据倾斜
select /*+ skewjoin(a(c0, c1)) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1 and a.c2 = b.c2;
-- 方法3：hint表名和列，并提供发生倾斜的key值（注意如果是String类型，需要加上引号）
-- 下面的例子是认为，(a.c0=1 and a.c1="2") 和 (a.c0=3 and a.c1="4")的值都存在数据倾斜
select /*+ skewjoin(a(c0, c1)((1, "2"), (3, "4"))) */ * from T0 a join T1 b on a.c0 = b.c0 and a.c1 = b.c1 and a.c2 = b.c2;

注意事项

  1.支持的Join类型：Inner Join可以对Join两侧表中的任意一侧进行Hint；Left Join、Semi Join和Anti Join只可以Hint左侧表；Right Join只可以Hint右侧表；Full Join不支持Skew Join Hint；

  2.建议只对一定会出现数据倾斜的Join添加Hint，因为Hint会运行一个Aggregate，存在一定代价；

  3.被Hint的Join的Left Side Join Key的类型需要与Right Side Join Key的类型一致，否则skew join hint不生效。

        4.可以在Logview的Json Summary中搜索是否出现topk_agg字段判断skew join hint是否生效

3、group by 数据倾斜优化

原因：group by的key分布不均匀，相同key的数据分发到同一个执行 Instance 上处理

解决方案：在SQL执行前设置防倾斜的参数： set odps.sql.groupby.skewindata=true;

原理：和手动添加前缀进行聚合再去掉前缀进行聚合的原理一样

注意：只能对单个字段聚合。
在第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中， 每个reduce 做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的reduce中，从而达到负载均衡的目的，第一次聚合后数据量会减少很多。
第二个 MapReduce 任务可以保证相同的 Group By Key 分布到同一个 reduce 中，最后完成最终的聚合操作。

4、multi-distinct优化

distinct原理：ODPS中的Distinct的执行原理是将需要Distinct的这个字段以及Group By 字段联合作为Key将数据分发到Reduce端的，而且只有一个Reduce Task。由于Distinct操作的存在，数据无法在Map端的Shuffle阶段根据Group By先做一次聚合操作,减少传输的数据量，而是将所有的数据都传输到Reduce端。特别当多个Distinct同时出现在一段SQL代码中时，数据会被分发多次，不仅会造成数据膨胀N倍，也会把长尾现象放大N倍。

当一个sql数据量大且出现多个distinct时，我们可以把sql进行改进成不用distinct的sql。例如先进行group by 然后再进行sum case when的优化方式。

例如

SELECT day,
COUNT(DISTINCT id) AS uv
FROM lxw1234
GROUP BY day

可以转换成：

SELECT day,
COUNT(id) AS uv
FROM (SELECT day,id FROM lxw1234 GROUP BY day,id) a
GROUP BY day;

5、什么情况下只有一个reducer

（1）、COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成。

（2）、用了Order by

6、hive参数设置

map参数设置

1.    mapper个数决定因素有： input的文件总个数，input的文件大小，集群设置的文件块大小为128M

2、如何合并小文件

我通过以下方法来在map执行前合并小文件，减少map数：
set mapred.max.split.size=100000000;100M
set mapred.min.split.size.per.node=100000000;100M
set mapred.min.split.size.per.rack=100000000;100M
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;表示执行前进行小文件合并
前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100M的合并

3、其他参数

set odps.sql.mapper.cpu=100
作用：设置处理Map Task每个Instance的CPU数目，默认为100，在[50,800]之间调整。
场景：某些任务如果特别耗计算资源的话，可以适当调整cpu数目。对于大多数sql任务来说，一般不需要调整cpu个数的。

set odps.sql.mapper.memory=1024
作用：设定Map Task每个Instance的Memory大小，单位M，默认1024M，在[256,12288]之间调整。
场景：当Map阶段的Instance有Writer Dumps时，可以适当的增加内存大小，减少Dumps所花的时间。

set odps.sql.mapper.split.size=256
作用：设定一个Map的最大数据输入量，可以通过设置这个变量达到对Map端输入的控制，单位M，默认256M，在[1,Integer.MAX_VALUE]之间调整。
场景：当instance个数过多，可以调大该值；当每个Map Instance处理的数据量比较大，时间比较长，并且没有发生长尾时，可以适当调小这个参数。

reducer和join参数设置

1.    Hive自己如何确定reduce数：
每个reduce任务处理的数据量，默认为1000^3=1G
每个任务最大的reduce数，默认为999
计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1)
即，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务；
如：select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′ group by pt;
pt=2012-07-04这个分区总大小为9G多，因此这句有10个reduce

2、其他参数

Reduce设置
set odps.sql.reducer.instances=-1
作用: 设定Reduce Task的Instance数量，手动设置区间在[1,99999]之间调整。不走HBO优化时,ODPS能够自动设定的最大值为1111，手动设定的最大值为99999，走HBO优化时可以超过99999。
场景：每个Join Instance处理的数据量比较大，耗时较长，没有发生长尾，可以考虑增大使用这个参数。

set odps.sql.reducer.cpu=100
作用：设定处理Reduce Task每个Instance的Cpu数目，默认为100，在[50,800]之间调整。
场景：某些任务如果特别耗计算资源的话，可以适当调整Cpu数目。对于大多数Sql任务来说，一般不需要调整Cpu。

set odps.sql.reducer.memory=1024
作用：设定Reduce Task每个Instance的Memory大小，单位M，默认1024M，在[256,12288]之间调整。
场景：当Reduce阶段的Instance有Writer Dumps时，可以适当的增加内存的大小，减少Dumps所花的时间。

Join设置
set odps.sql.joiner.instances=-1
作用: 设定Join Task的Instance数量，默认为-1，在[0,2000]之间调整。不走HBO优化时,ODPS能够自动设定的最大值为1111，手动设定的最大值为2000，走HBO时可以超过2000。
场景：每个Join Instance处理的数据量比较大，耗时较长，没有发生长尾，可以考虑增大使用这个参数。

set odps.sql.joiner.cpu=100
作用: 设定Join Task每个Instance的CPU数目，默认为100，在[50,800]之间调整。
场景：某些任务如果特别耗计算资源的话，可以适当调整CPU数目。对于大多数SQL任务来说，一般不需要调整CPU。

set odps.sql.joiner.memory=1024
作用：设定Join Task每个Instance的Memory大小，单位为M，默认为1024M,在[256,12288]之间调整。
场景：当Join阶段的Instance有Writer Dumps时，可以适当的增加内存大小，减少Dumps所花的时间。