Hive优化十大原则

一、表链接优化

1.将大表放最后

Hive假定查询中最后一个表是大表，他会将其他表先缓存起来，然后扫描最后那个表。

因此通常需要将小表放在前面，或者标记那张表是大表：/*streamtable（table_name）*/

2.使用相同的链接键

当对3个或者更多个表进行join连接时，如果每个on子句都是用相同的连接键的话，那么只会产生一个MapReduce job。

3.尽量尽早地过滤数据

减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用的字段。

4.尽量原子化操作

尽量避免一个sql包含复杂的逻辑，可以使用中间表来完成复杂的逻辑。

二、使用insert into替换union all

如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into语句，实际测试过程中，执行时间能提升50%

例如：

insert overwrite table tableName partition (dt='2018-03-13')
select ... from (select ... from tableName1
union all
select ... from tableName2 union all select ... from tableName3)temp
where ...;

可以改写成：

insert into table tableName partition (dt = '2018-03-13') select ... from tableName1 where ...;
insert into table tableName partition (dt = '2018-03-13') select ... from tableName2 where ...;
insert into table tableName partition (dt = '2018-03-13') select ... from tableName3 where ...;

三、order by 和 sort by 

order by：对查询结果进行全局排序，消耗时间长，需要设置参数（set hive.mapred.mode=nostrict）。

sort by：局部排序，并非全局排序，提高效率。

四、transform+python

一种嵌入在hive取数据流程中的自定义函数，通过transform语句可以把在hive中不方便实现的功能在python中实现，然后写入hive表中。

语法：

select transform({column names1})

useing '**.py'

as {column names2}

from {table name}

如果除python脚本外还有其他依赖资源，可以使用ADD ARVHIVE

五、limit语句快速出结果

一般情况下，limit语句还是需要执行整个查询语句，然后再返回部分结果。

有一个配置属性可以开启，避免这种情况---对数据源及逆行抽样

hive.limit.optimize.enable=true --- 开启对数据源进行采样的功能

hive.limit.row.max.size --- 设置最小的采样容量

hive.limit.optimize.limit.file --- 设置最大的采样样本数

缺点：有可能部分数据永远不会被处理到

六、本地模式

对于小数据集，为查询触发执行任务消耗时间>实际执行job的时间，因此可以通过本地模式，在单台机器上（活某些时候在单个进程上）处理所有的任务。

set oldjobtracker=${hiveconf:mapred.job.tracker};
set mapred.job.tracker=local;
set mapred.tmp.dir=/home/edward/tmp;
sql语句
set mapred.job.tracker=${oldjobtracker};

--可以通过设置属性hive.exec.mode.local.auto的之为true，来让hive在适当的时候自动启动这个优化，也可以将这个配置写在$HOME/.hiverc文件中。

--当一个job满足如下条件才能真正使用本地模式：

1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)

2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)

3.job的reduce数必须为0或者1

可用参数hive.mapred.local.mem(默认0)控制child jvm使用的最大内存数。

七、并行执行

hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。不过，如果某些阶段不是相互依赖，是可以并行执行的。

set hive.execparallel=true 可以开启并发执行

set hive.exec.parallel.thread.number=16 同一个sql允许最大并行度，默认为8

会比较耗系统资源

八、调整mapper和reducer的个数

1.Map阶段优化

map个数的主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小（默认128M，不可自定义）。

举例：

a、假设input目录下有一个文件a，大小为780M，那么Hadoop会将该文件a分割成7个块（6个128M的块和1个12M的块），从而产生7个map数

b、假设input目录下有三个文件a,b,c大小分别为10M，20M,130M,那么Hadoop会分割成4个块（10M,20M,128M,2M)，从而生产4个map数，即：如果文件大于块大小（128M）,那么会拆份，如果小于块大小，则把该文件当成一个块。

map执行时间：map任务启动和初始化的时间+逻辑处理的时间。

1）减少map数

若有大量小文件（小于128M），会产生多个map，处理方法是：

set mapred.max.split.size=100000000;

set mapred.min.split.size.per.node=100000000;

set mapred.min.split.size.per.rack=100000000;

--前面三个参数确定合并文件块的大小，大于文件块大小128M的，按照128M来分割，小于128M,大于100M的，按照100M来分割，把那些小于100M的（包括小文件和分割大文件剩下的）进行合并

set hive.input.format=org.apache.hadoop.hive.ql.io.ConmbineHiveInputFormat; --执行前进行小文件合并

2）增加map数

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

set mapred.reduce.tasks=?

2.Reduce阶段

调整方式：

--set mapred.reduce.tasks=?

--set hive.exec.reducers.bytes.per.reducer=?

一般根据输入文件的总大小，用它的estimation函数来自动计算reduce的个数：reduce个数=InputFileSize/butes per reducer

九、严格模式

set hive.marped.mode=strict ---防止用户执行那些可能意想不到的不好的影响的查询

--分区表，必须选定分区范围

--对于使用order by的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个reducer中进行处理。

--限制笛卡尔积查询：两张表join时必须有on语句

十、数据倾斜

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多，最长时长远大于平均时长。

原因：

1.key分布不均匀

2.业务数据本身的特性

3.建表时考虑不周

4.某些sql语句本身就有数据倾斜

关键词	情形	后果
join	其中一个表较小，但是key集中	分发到某一个或几个reduce上的数据远高于平均值
join	大表与大表，但是分桶的判断字段0值或者空值过多	这些空值都由一个reduce处理，非常慢
group by	group by 维度过小，某值的数量过多	处理某只的reduce非常耗时
count distinct	某特殊值过多	处理次特殊值reduce耗时

解决方案：hive.map.aggr=true

参考文献：

1. 《hive编程指南》Edward Capriolo