大数据之HiveSQL调优

1、HiveSQL调优概述

1.1、优化原因

①数据倾斜
②数据冗余
③Job或I/O过多
④MapReduce分配不合理

1.2、优化方式

①对HiveSQL语句本身的优化
②Hive配置项的调整
③MR的调整

2、HiveSQL本身优化

2.1、列裁剪和分区裁剪

列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区

select uid,event_type,record_data	
from calendar_record_log	
where pt_date >= 20190201 and pt_date <= 20190224	
and status = 0;

2.2、谓词下推

将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。

select a.uid,a.event_type,b.topic_id,b.title	
from calendar_record_log a	
left outer join (	
select uid,topic_id,title from forum_topic	
where pt_date = 20190224 and length(content) >= 100	
) b on a.uid = b.uid	
where a.pt_date = 20190224 and status = 0;

2.3、sort by代替order by

order by将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中
ort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序，
	为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。
	如果不加distribute by的话，map端数据就会随机分配到reducer。

	
select uid,upload_time,event_type,record_data	
from calendar_record_log	
where pt_date >= 20190201 and pt_date <= 20190224	
distribute by uid	
sort by upload_time desc,event_type desc;

``

2.5、Join优化

hive中的Join可分为Common Join（Reduce阶段完成join）和Map Join（Map阶段完成join)

2.5.1、小表前置

Hive在解析带join的SQL语句时，会默认将最后一个表作为probe table，将前面的表作为build table并试图将它们读进内存，在维度建模数据仓库中，事实表就是probe table，维度表就是build table

select a.event_type,a.event_code,a.event_desc,b.upload_time	
from calendar_event_code a	
inner join (		
select event_type,upload_time from calendar_record_log	
where pt_date = 20190225	
) b on a.event_type = b.event_type;

2.5.2、多表join时key相同

这种情况会将多个join合并为一个MR job来处理

select a.event_type,a.event_code,a.event_desc,b.upload_time	
from calendar_event_code a	
inner join (	
select event_type,upload_time from calendar_record_log	
where pt_date = 20190225	
) b on a.event_type = b.event_type	
inner join (	
select event_type,upload_time from calendar_record_log_2	
where pt_date = 20190225	
) c on a.event_type = c.event_type;

2.5.3、利用map join

map join特别适合大小表join,不等值关联的情况。Hive会将build table和probe table在map端直接完成join过程，
消灭了reduce，效率很高。

①在Hive0.11前，必须使用MAPJOIN来标记显示地启动该优化操作，由于其需要将小表加载进内存所以要注意小表的大小

select /*+ mapjoin(t1)*/ t1.a,t1.b 
from table t1 
join table2 t2 
on ( t1.a=t2.a and f.ftime=20110802)

②在Hive0.11后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin，可以通过以下两个属性来设置该优化的触发时机

set hive.auto.convert.join=true; //设置 MapJoin 优化自动开启,默认是开启的
set hive.mapjoin.smalltable.filesize=25000000 //设置小表不超过多大时开启 mapjoin 优化，即25M

#进行查询
select t1.a,t1.b 
from table t1 
join table2 t2 
on ( t1.a=t2.a and f.ftime=20110802)

##### 2.5.4、优化SQL处理join倾斜
1）空值或无意义值
	
	事实表是日志类数据时，往往会有一些项没有记录到，我们视情况会将它置为null，
	如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度。
		
	若不需要空值数据，就提前写where语句过滤掉。
	需要保留的话，将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值：
```sql
select a.uid,a.event_type,b.nickname,b.age	
from (	
select	
  (case when uid is null then cast(rand()*-10240 as int) else uid end) as uid,	
  event_type from calendar_record_log	
where pt_date >= 20190201	
) a left outer join (	
select uid,nickname,age from user_info where status = 4	
) b on a.uid = b.uid;

2）单独处理key
这其实是上面处理空值方法的拓展，不过倾斜的key变成了有意义的。一般来讲倾斜的key都很少，我们可以将它们抽样出来，对应的行单独存入临时表中，然后打上一个较小的随机数前缀（比如0~9），最后再进行聚合。SQL语句与上面的相仿，不再赘述。

3）不同数据类型

	如果不转换类型，计算key的hash值时默认是以int型做的，
	这就导致所有“真正的”string型key都分配到一个reducer上。所以要注意类型转换：

select a.uid,a.event_type,b.record_data	
from calendar_record_log a	
left outer join (	
select uid,event_type from calendar_record_log_2	
where pt_date = 20190228	
) b on a.uid = b.uid and b.event_type = cast(a.event_type as string)	
where a.pt_date = 20190228;

4）build table过大

	全量用户维度表过大，无法使用map join的地步，而使用普通join又有数据不均的问题。

	处理方式：这时就要充分利用probe table的限制条件，削减build table的数据量，再使用map join解决

select /*+mapjoin(b)*/ a.uid,a.event_type,b.status,b.extra_info	
from calendar_record_log a	
left outer join (	
select /*+mapjoin(s)*/ t.uid,t.status,t.extra_info	
from (select distinct uid from calendar_record_log where pt_date = 20190228) s	
inner join user_info t on s.uid = t.uid	
) b on a.uid = b.uid	
where a.pt_date = 20190228;

3、hive优化

一个map或者reduce任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。

3.1、map阶段优化

使单个map任务处理合适的数据量。

3.1.1、map参数设置

mapred.min.split.size：数据的最小分割单元；min值默认是1KB。
mapred.max.split.size：数据的最大分割单元；max值默认是256M。
通过调整max可以起到调整map数的作用，减小max可以增加map数；增加min可以减少map数。

3.1.2、map切分

假设input目录下有1个文件a，大小是780M，
那么map默认参数会把a分成7块（6个128M和1个 12M），从而产生7个map。

假设input目录下有3个文件a,b,c，大小分别为10M，20M，130M，
那么hadoop会把文件分成4块（10M，20M，128M，2M），从而产生4个map数。

3.1.3、解决方式

通过以下方法来在map执行前合并小文件，减少map数：
set mapred.max.split.size=128000000;  			// 能分割块的最大块大小
set mapred.min.split.size.per.node=100000000;	// 每个节点处理的最小split
set mapred.min.split.size.per.rack=100000000;	// 每个机架处理的最小split
***前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，
小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），
进行合并。

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  // 合并文件
***这个参数表示执行前进行小文件合并。

3.2、reduce阶段优化

Reduce设置的过大，那么将会产生很多小文件，
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）	
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）

3.3、合并小文件

是否合并Map输出文件：hive.merge.mapfiles=true（默认值为真）
是否合并Reduce 端输出文件：hive.merge.mapredfiles=false（默认值为假）
合并文件的大小：hive.merge.size.per.task=25610001000（默认值为 256000000）