最近经常有优化sql的任务，但是自己能力有限，只能凭经验去优化，现整理加学习一波，也欢迎各位学习和讨论。
我们经常用hivesql 的模型就是 join.如下。
insert overwrite table a select * from b left join c
这里面发生了什么，执行流程是什么，为什么有的insert要几十分钟有的只要几分钟。
CREATE TABLE `cc_test`.`multi_join`
(
`id` int,
`name` string,
`desc` string
)
stored as orc;--造1000w条数据 文件大小为300M
insert overwrite table cc_test.multi_join
select if(row_number() over ()<1000,1,row_number() over ()),
'cclovezbf'||(row_number() over ()),
repeat('desc',1000)||(row_number() over ())
from (select explode(split(space(10000000),'')) t)t
已知上面的数据为1000w条
其中 id=1的有1000条 其余id=1000到1000w的依次分布
1.explain 和 set
所有的优化和学习离不开explain和set
set分 set 和set -v 两者差别不清楚，但是有些设置参数需要自己查。
explain就是解析sql的执行顺序和逻辑。
2.
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
执行过程
这里为啥有2个map？为啥reduce又只有1个呢？
3.distribute by
explain
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30)
而且大家注意到没有 上面的31个文件为什么有2个5.6M，其他的是11.1M，为什么这么特殊？不是平均分配吗？有人告诉过你这个点吗？我来为你揭秘，这个就是纯属数学概率问题。
已知 rand=0-0.99 例如我们rand()*4 本意是想 数值从1到4或者 0到4平均分配。rand*4=0-3.99
round(rand*4）最终(0-0.49)=0 (0.5-1.49)=1 (1.5-2.49)=2 (2.5-3.49)=3 (3.5-3.99)=4 看到没
最终=0和4的概率会明显小于 =123的概率
其实正确的做法是int(rand*4) 这样分布的概率就会平均
4.reduce的个数
set mapred.reduce.tasks=-1;
set hive.exec.reducers.bytes.per.reducer=67108864 ##默认64M
set mapred.reduce.tasks=40;
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30);
此时大家猜一猜文件数是40还是30呢？答案是22个。
通过文件的序号可以看到这里应该是有类似merge操作的。此处暂且留下疑问
那么么我们将reudce个数改小呢？
set mapred.reduce.tasks=5;
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join
distribute by round(rand()*30);
可以看到文件数为5
说明该参数set mapred.reduce.tasks与生成的文件数有关，注意不是最终文件数。
再说说为什么reduce=40 distribute30 生成22个文件，根据我测试
reduce=100 distribute30 生成25个文件
reduce=300 distribute30 生成27个文件
reduce=301 distribute30 生成25个文件
reduce=500 distribute30 生成27个文件
文件数和reduce个数好像又成正比，但是reduce:300增加到301的时候文件数为什么变少了？其实聪明的小伙伴此时应该都想到了，可能就是取模。
简单的来说 map阶段获取的数据 在distrbue分为30份为data1/data2/....data30
reduce=300,data1/2/3/4对hash(300)取模后=1，data5到30分布在分区2-27，最终27个文件
reduce=300,data1/2/3/4/5/6对hash(300)取模后=1，data7到30分布到分区2-25，最终25个文件
验证下
reduce=15 distribute30 生成12个文件
reduce=14 distribute30 生成14个文件
reduce=13 distribute30 生成9个文件
这说明reduce=13时运气不好 取模后的值大多都在一个分区 如上，都在1/2/6/12号分区，没有取模后=3/5号分区的，所以文件数<reudce个数
至于reduce=5 30个文件大概率是会均分到5个reduce。
那么我们 怎么合理的使用这两个参数呢？
比如我们的目的是最终生成10个文件，数据尽量分散或者平均。
首先reduce=10毫无疑问，distributeby 如何设置呢？ 设置小了文件数可能<10，设置大了就会越分散，并且经过测试 distribute 不会产生额外的mr，只会在map阶段产出一个分区
由此可以得到结论：该参数会影响生成的最后文件，但是不是最终决定文件个数的决定性因数。
比如distribute by 和merge都会影响最后生成的文件个数。
题外话知道为什么该参数默认=-1吗？就是因为你有时候设置了具体的参数，但是结果数不一致，会导致你怀疑人生，所以reduce默认时按照大小生成reduce个数的。这里又涉及到一个知识点，reduce个数设置过大，会导致空转白白浪费资源。
5.map的个数
mapreduce.input.fileinputformat.split.minsize=1
mapreduce.input.fileinputformat.split.maxsize=256000000
细心的小伙伴在实验的时候肯定会发现 map数量少的时候跑的很慢，数量多的时候跑的很快。
那么map数量和什么有关呢？
1.文件数量， 一般来说文件数量=map数，注意这里说的一般来说。
2.文件的总大小。
我上面提到的两个参数又是什么意思呢？是说map阶段读取的文件大小最小是1最大时256M。
例如我们现在的数据为2个180M
1k<180M <256M 所以刚好使用2个Map
如果我们修改参数
set mapreduce.input.fileinputformat.split.minsize=64000000;
set mapreduce.input.fileinputformat.split.maxsize=64000000;
设置map读取的范围时64M 那么180M如何拆分呢？
注意这里很有意思用了4个map，最后输出文件为4，但是仔细看文件大小 两大121M两小59M
121+59M=180M 刚好等于之前没拆分的大小。
继续实验
5.多出来的reduce(统计信息)
set hive.stats.autogather=true;
set hive.stats.column.autogather=true;
细心的小伙伴可能会发现之前的执行过程有点奇怪。
map1：读取from 表数据 map1input=1000w output=1000w 没问题
reduce2：插入到insert表的过程 reduce读取map的数据，input=1000w也没问题，output=10?这个10是什么呢？暂时猜测是最终的文件数
reduce3：这个是什么呢？而且reduce3只有一个task，我们可是设置reduce=10了的呀？而且这个的output=0这又是什么呢 ？
这里就用到我们的explain了。
explain
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join_bak
distribute by int (rand()*3000);
看到这个大部分人就知道了，这个是hive默认的统计信息，意思就是当你覆盖表信息的时候，hive就要统计下，这个一般不会耗时很久，还是做个实验
可以看到reduce3没有了，这样就能更好的分析执行过程了。。
6.文件合并的优化
#小于该大小的就会被合并
set hive.merge.smallfiles.avgsize=16777216
# 文件合并的最后大小
set hive.merge.size.per.task=268435456
set hive.merge.sparkfiles=false
set hive.merge.tezfiles=false//reudce阶段合并
set hive.merge.mapredfiles=false//map阶段合并
set hive.merge.mapfiles=true
有时候我们的目录下面都是小文件，这样会可能会开启很多map任务
insert overwrite table cc_test.multi_join_bak
select * from cc_test.multi_join_bak distribute by int (rand()*3000);
##为什么只有956个呢？ 因为3000 hash后也没有均匀分布6000之后reduce的个数正好就是1009了。
set hive.exec.reducers.max=1009
已知我们读取的表数据有360M 文件数有1009个 那我们读取的时候会有多少个map呢？
insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;
答案是25个？ 为什么是25个呢？ 这个14.4又是什么呢？所以我们考虑到的事情，比我们优秀的人更早就就考虑到了。下面的是默认参数
set hive.merge.mapfiles=true;
//小文件合并的阈值16M是不是和14.4很像呢？
set hive.merge.smallfiles.avgsize=16777216;
如何证实呢？set hive.merge.smallfiles.avgsize=33554432 这样是不会map会变为12或13呢？
发现还是25个，此时有点想不通了，是参数没起作用还是我理解错了？ 这个暂且不说。这问题留到下面讲。
先说ditribute by 这个参数可以让reduce个数更多，但是我们有时候真的需要这么多文件吗？这个对于namenode来说负担很大，还有reduce个数多了，map数也多了，真的好吗？
所以我们需要处理这些小文件，有人又说了 distributeby(rand*6) 这样就只有6个文件了，或者set reduce.task=6也可以。但是我们现在既想任务跑的快，又想文件少。
这个时候就要说
set hive.merge.tezfiles=true;
set hive.merge.mapredfiles=true; #默认关闭 我们此时打开
set hive.merge.smallfiles.avgsize=16777216; #文件小于这个参数的开始被merge
set hive.merge.size.per.task=268435456;
已知文件个数为1009 每个文件差不多在300K左右。
insert overwrite table cc_test.multi_join_bak select * from cc_test.multi_join_bak
可以看到mapreduce阶段完了之后 又多了一个file merge阶段，说明参数确实起作用了。
最后形成的文件大小也是接近于256M。
所以这个参数适用于小文件比较多的表，注意最后的merge。
再说说最开始的map数据为什么不变呢？
set mapreduce.input.fileinputformat.split.minsize=1;
set mapreduce.input.fileinputformat.split.maxsize=256000000;set tez.grouping.min-size=16777216;
set tez.grouping.max-size=134217728;hive.merge.smallfiles.avgsize=16777216;
hive.merge.mapfiles=true;
7.资源控制(TEZ)
set tez.grouping.min-size=16777216 ##16M
set tez.grouping.max-size=134217728 ## 128这个是我这边集群的
set tez.grouping.split-waves=1.7 #调整一个比例的，
set hive.merge.smallfiles.avgsize=16777216;
set hive.merge.mapfiles=true;set mapreduce.input.fileinputformat.split.minsize=1;
set mapreduce.input.fileinputformat.split.maxsize=256000000;
上面第6项说到了 360M的1000个文件读取的map数是25个？而且设置set hive.merge.smallfiles.avgsize=32M没有任何效果 ，这个很正常，这个参数代表的是小于32M以下的文件会被合并为一个大文件放到一个MAP里，注意这里并没有说这些小文件合并为多大了就不合并了？ 1000个100k的小文件是合成100个map还是10个 还是1个，肯定是有参数限制了的。
set hive.merge.mapfiles=false;
insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;
结果还是25个map，
此时就隐隐约约感觉有点奇怪了？我map阶段都不合并文件了？为啥还是这么多map呢？
答案就是 set tez.grouping.min-size=16777216=16M 这个参数规定了每个map的group里最少是16M，这里的16M是什么呢？就是上文说的14.4。
set tez.grouping.min-size=4194304; #4M
set hive.merge.mapfiles=true;
insert overwrite table cc_test.multi_join select * from cc_test.multi_join ;
可以看到map数量变了16M->4M，map数 25->98， 一个缩小4倍 一个增多4倍；
set tez.grouping.split-waves=1.7 # 这个参数经过测试是会影响map的数量的，但是影响不大，我以前看过源码稍微研究了下，有兴趣的自己看下
7.join优化之mapjoin
set hive.auto.convert.join=true #默认true
set hive.mapjoin.smalltable.filesize=25000000 #默认25M 注意是我这边集群默认
set hive.auto.convert.join.noconditionaltask=true #默认true
set hive.auto.convert.join.noconditionaltask.size=52428800 #50M
上面所有的示例都没有涉及到join优化，涉及到join优化先得说下mapjoin，mapjoin默认开启。具体有什么好处就不说了。但是我说一点，并不是所有的任务都是mapjoin好，我遇到过mr比mapjoin跑的快的。
未完待续。。。
