记一次HQL优化过程

记一次相对较大的“数据量”尿崩问题的解决过程~~

故事背景

故事背景是这样子的，小伙伴写了个狗屎一样的HQL，数据量大了之后直接被压挂了，当我看到他的SQL的一刹那，我觉得我可以拯救下这个可怜的孩子，从此踏上了一条慢慢调参路！

首先，SQL的大致逻辑是，表A关联客户信息表B两次。简单表述SQL如下：

A left join B
on a.id_1 = b.id
left join B
on a.id_2 = b.id

他原来的写法就不恢复了，脑回路太清奇，原谅我想不起来他原来是怎么写的。

出发

好了，我们开始，SQL重新写完之后，开始调参，首先进入脑子的方案

表B没有分桶，活该速度慢，改！

create table B
partition by day
clustered by (id)
sorted by (id)
into 6 buckets
row …………;

id建桶，使用ID做关联，对了还改了ORC的文件存储格式；希望获取部分列的时候速度更快一丢丢；

导入数据，发现只有300多M，岂不是可以用MapJoin？！！搞一波！

set hive.auto.convert.sortmerge.join=true;  
set hive.optimize.bucketmapjoin = true;  
set hive.optimize.bucketmapjoin.sortedmerge = true;  
set hive.auto.convert.sortmerge.join.noconditionaltask=true; 

果真，Application中，只有Map阶段，没有Reduce任务；但是！第二个Stage执行的时候又崩了！

心态逐渐扭曲

这时候我犯了弱智的错误，**不看Log！！！**想当然的修改SQL，既然两个Stage，就把第一个left join缓存到中间结果表不就OK了？

乃义务！

with tmp_table as (……) ，发现只能表征简单的表结构，有嵌套的查询语句没办法用。

这难不倒我，不就是create table as嘛，干！一通操作猛如虎，发现还是崩…………

这时候我又犯了一个更弱智的错误，不看Log！！！！

但是，看了下生成的结果中间表，膨胀了20多倍！！卧槽，不应该呀，查了下Hive中间结果默认不压缩，使用textfile。安排！

set hive.exec.compress.output=true;
set hive.exec.compress.intermediate=true;
set hive.default.fileformat=SequenceFile;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

Stage中间结果压缩，文件格式SequenceFile，没毛病了吧！

顺带由把小文件合并参数加上，妥妥了吧！

-- Map阶段小文件合并
set hive.merge.mapfiles=true;   
-- Reduce阶段小文件合并
set hive.merge.maprefiles=true;
-- 超过这个文件大小，启动单独的MR程序进行合并
set hive.merge.size.per.task=1280000;

执行，Stage2尿崩！心态逐渐开始扭曲。

这个时候又犯了个更致命的问题，还是不看Log！！！像个无头苍蝇，一遍一遍的实验。

改Map、Reduce个数、内存参数。各种实验。

这时候发现Yarn的CPU和Mem的使用率较低，安排！

set mapreduce.map.memory.mb=1024;
set mapreduce.map.cpu.vcores=2;
set mapreduce.reduce.memory.mb=1024;
set mapreduce.map.reduce.cpu.vcores=2;

冷静冷静

Run起来！CPU、Mem蹭蹭涨，然鹅，Stage-2继续尿崩……

崩溃过后，终于开始冷静，查看Log…………Container Beyond physics memory…………容器内存超出限制了……Task大量OOM。

所以应该两个处理思路：

1. 调大Container参数，需要修改Yarn集群配置；
2. 限制MR任务的Reduce阶段Task；

先改Yarn，没用，还是一样崩，内存限制也没有变化，先不管了，试试第二种方法：

set mapred.map.child.java.opts=-Xmx1024m;
set mapred.reduce.child.java.opts=-Xmx2048m;

依然尿崩，但这个时候我冷静了，直觉告诉我，马上要见真章了！

顺带的发现Stage-2的数据量比Stage-1的数据量大很多，不知道因为啥，已经超出了我的知识边界，再说吧！（埋个彩蛋，这个地方是核爆！）

仔细分析下，我Reduce限制1G数据拆分一个Reduce，reduce task最大大小是2G，会不会是每个Reduce中接入的原始数据过多，导致内存崩了？

保持Reduce task内存限制不变，调小Reduce切分数据量的大小。256M切分一个Reduce，走起！

靠谱！

漫长的5个小时后，终于第一次没崩的跑完了整个流程。

”算是“圆满完成任务了吧~

Surprise

好奇心驱使，看下跑出来的最终结果！

卧槽，预计数据量是1亿条，跑出来了70多亿条！！！！炸裂呀！！！70多倍的膨胀！！！

分区问题？还是？？？冷静冷静！

这说明SQL查询逻辑有问题，从最源头就有问题！！！压根没有到需要优化的程度！

这就说明了比不看Log更严重的问题是：

不摸排数据，不验证SQL正确性，直接硬上！！！

所以优化最最最重要的事：

先保证SQL正确！！数据正常！！！

好了，开始排查问题，拿10条记录用left join下，发现出来了100多条记录。

查文档，才知道Hive的left join == left outer join。

没有left inner join的写法，所以当B表的id值有大量重复时候，是都会在最终结果有所保留的。

例如：A表
a,10
b,5

B表
a,3
a,4
b,5
b,6

SQL：A left join B on a.id = b.id
结果就是
a,10,3
a,10,4
b,5,5
b,5,6

讲道理，这个确实是我的知识盲区。之前没有注意到的点。

但是，按理说我这个程序的B表是一个用户索引表，用户ID应该就是唯一的。

排查数据分布，喵了个咪的，海量的重复数据，每个用户50多条记录！！！这ETL做的是个鬼…………

安排安排……保证B表唯一。

重新Run起来，10分钟！只要10分钟！！！结果校验没有问题。

成功破案，撒花~~~

又到了课堂总结的时刻

犯了几个错误(按重要程度排序)：

1. 不摸排原始数据，不验证SQL正确性；这是主要原因，源头没卡住，后续的都是无用功；
2. 不查Log，报错信息很明显，但是前期的很多工作，都是凭空想象的优化；
3. 定位问题之后，没有分析，优化参数也是在猜，一遍一遍的试，重复动作太多，没有深挖遇到的问题，MR程序还很慢，浪费了大量的时间！

不过经过这一番折腾，也算深挖了不少优化点

• Map、Reduce个数、CPU、内存设置
• Task内存设置
• MapJoin
• 压缩，文件格式、分桶表
• 小文件合并

一顿操作猛如虎，能想到的都上了，除了数据倾斜，其他的优化思路，算是试验了不少。

还试验了Shuffle的分布

set mapreduce.reduce.shuffle.merge.percent=0.3;

虽然不知道这玩意有啥用。

虽然这次算是侥幸定位了问题，但是还是有运气成分在，而且定位问题太慢，黄花菜都凉了。继续补一补MapReduce、Yarn的原理；另外Hive Opterator和Stage划分这部分的缺陷也急需补课。

这次“刨根问底”告一段落，下一回合，Yarn搞起！