hive sql的优化

1. 将大表放后头

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

因此通常需要将小表放前面，或者标记哪张表是大表：/*streamtable(table_name) */

2. 使用相同的连接键

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

3. 尽量尽早地过滤数据

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

4. 尽量原子化操作

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

5. 并行执行

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

set hive.exec.parallel=true,可以开启并发执行。

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

会比较耗系统资源

6. 调整mapper和reducer的个数

6.1 Map阶段优化

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

举例：

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

b) 假设input目录下有3个文件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.CombineHiveInputFormat; – 执行前进行小文件合并

2）增加map数

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

set mapred.reduce.tasks=?

6.2 Reduce阶段优化

调整方式：

– set mapred.reduce.tasks=?

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

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

7.严格模式

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

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

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

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

8.数据倾斜

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

数据倾斜的原因：

1)、key分布不均匀

2)、业务数据本身的特性

3)、建表时考虑不周

4)、某些SQL语句本身就有数据倾斜

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

解决方案：
参数调节 hive.map.aggr=true

9 参考

知识星球：大数据；