HQL语法优化，Job优化（map端和reduce端）

  • 一、HQL语法优化
  • 1.1 列裁剪与分区裁剪
  • 1.2 Group By
  • 1.3 Vectorization
  • 1.4 多重模式
  • 1.5 in/exists语句
  • 1.6 CBO优化
  • 1.7 谓词下推
  • 1.8 MapJoin
  • 1.9 大表、大表SMB Join
  • 1.10 笛卡尔积
  • 二、Hive Map优化
  • 2.1 复杂文件增加Map数
  • 2.2 小文件进行合并
  • 2.3 Map端聚合
  • 2.4 推测执行
  • 三、Hive Reduce优化
  • 3.1 合理设置Reduce数


一、HQL语法优化

1.1 列裁剪与分区裁剪

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

当列很多或者数据量很大时，如果 select *或者不指定分区，全列扫描和全表扫描效率都很低。

Hive 在读数据的时候，可以只读取查询中所需要用到的列，而忽略其他的列。这样做可以节省读取开销：中间表存储开销和数据整合开销。

1.2 Group By

默认情况下，Map阶段同一Key数据分发给一个Reduce，当一个key数据过大时就倾斜了。

hive job hive job语法_ci


并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

开启Map端聚合参数设置

（1）是否在Map端进行聚合，默认为True

set hive.map.aggr = true;

（2）在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000;

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true;

当选项设定为 true，生成的查询计划会有两个 MR Job。

第一个MR Job中，Map的输出结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；

第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作（虽然能解决数据倾斜，但是不能让运行速度的更快）。

hive (default)> select deptno from emp group by deptno;

Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 23.68 sec   HDFS Read: 19987 HDFS Write: 9 SUCCESS
Total MapReduce CPU Time Spent: 23 seconds 680 msec
OK
deptno
10
20
30

优化以后

hive (default)> set hive.groupby.skewindata = true;
hive (default)> select deptno from emp group by deptno;

Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 28.53 sec   HDFS Read: 18209 HDFS Write: 534 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 5   Cumulative CPU: 38.32 sec   HDFS Read: 15014 HDFS Write: 9 SUCCESS
Total MapReduce CPU Time Spent: 1 minutes 6 seconds 850 msec
OK
deptno
10
20
30

1.3 Vectorization

vectorization：矢量计算的技术，在计算类似scan, filter, aggregation的时候， vectorization技术以设置批处理的增量大小为 1024 行单次来达到比单条记录单次获得更高的效率。

hive job hive job语法_sql_02

set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;

1.4 多重模式

如果你碰到一堆SQL，并且这一堆SQL的模式还一样。都是从同一个表进行扫描，做不同的逻辑。有可优化的地方：如果有n条SQL，每个SQL执行都会扫描一次这张表。

insert .... select id,name,sex, age from student where age > 17;
insert .... select id,name,sex, age from student where age > 18;
insert .... select id,name,sex, age from student where age > 19;

隐藏了一个问题：这种类型的SQL有多少个，那么最终。这张表就被全表扫描了多少次

insert int t_ptn partition(city=A). select id,name,sex, age from student where city= A;
insert int t_ptn partition(city=B). select id,name,sex, age from student where city= B;
insert int t_ptn partition(city=c). select id,name,sex, age from student where city= c;

修改为：
from student
insert int t_ptn partition(city=A) select id,name,sex, age where city= A
insert int t_ptn partition(city=B) select id,name,sex, age where city= B

如果一个 HQL 底层要执行 10 个 Job，那么能优化成 8 个一般来说，肯定能有所提高，多重插入就是一个非常实用的技能。一次读取，多次插入，有些场景是从一张表读取数据后，要多次利用。

1.5 in/exists语句

在Hive的早期版本中，in/exists语法是不被支持的，但是从 hive-0.8x 以后就开始支持这个语法。但是不推荐使用这个语法。虽然经过测验，Hive-2.3.6 也支持 in/exists 操作，但还是推荐使用 Hive 的一个高效替代方案：left semi join

比如说： in / exists 实现

select a.id, a.name from a where a.id in (select b.id from b);
select a.id, a.name from a where exists (select id from b where a.id = b.id);

可以使用join来改写：
select a.id, a.name from a join b on a.id = b.id;

应该转换成：left semi join 实现
select a.id, a.name from a left semi join b on a.id = b.id;

1.6 CBO优化

join的时候表的顺序的关系：前面的表都会被加载到内存中。后面的表进行磁盘扫描

select a.*, b.*, c.* from a join b on a.id = b.id join c on a.id = c.id;

Hive 自 0.14.0 开始，加入了一项 “Cost based Optimizer” 来对 HQL 执行计划进行优化，这个功能通过 “hive.cbo.enable” 来开启。在 Hive 1.1.0 之后，这个 feature 是默认开启的，它可以 自动优化 HQL中多个 Join 的顺序，并选择合适的 Join 算法。

CBO，成本优化器，代价最小的执行计划就是最好的执行计划。传统的数据库，成本优化器做出最优化的执行计划是依据统计信息来计算的。

Hive 的成本优化器也一样，Hive 在提供最终执行前，优化每个查询的执行逻辑和物理执行计划。这些优化工作是交给底层来完成的。根据查询成本执行进一步的优化，从而产生潜在的不同决策：如何排序连接，执行哪种类型的连接，并行度等等。

要使用基于成本的优化（也称为 CBO），请在查询开始设置以下参数：

set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;

1.7 谓词下推

将 SQL 语句中的 where 谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是 PredicatePushDown，配置项为hive.optimize.ppd，默认为true。

案例实操：

1）打开谓词下推优化属性
hive (default)> set hive.optimize.ppd = true; #谓词下推，默认是true

2）查看先关联两张表，再用where条件过滤的执行计划
hive (default)> explain select o.id from bigtable b join bigtable o  on o.id = b.id where o.id <= 10;

3）查看子查询后，再关联表的执行计划
hive (default)> explain select b.id from bigtable b join (select id from bigtable where id <= 10) o on b.id = o.id;

1.8 MapJoin

MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操 作，这样就不用进行 Reduce 步骤，从而提高了速度。

如果不指定 MapJoin 或者 不符合 MapJoin 的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成Join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在Map端进行Join，避免Reducer处理。

1）开启MapJoin参数设置

（1）设置自动选择MapJoin
set hive.auto.convert.join=true; #默认为true

（2）大表小表的阈值设置（默认25M 以下认为是小表）：
set hive.mapjoin.smalltable.filesize=25000000;

2）MapJoin工作机制

MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中，在 Map 进程中进行 Join 操作，这样就不用进行 Reduce 步骤，从而提高了速度。

1.9 大表、大表SMB Join

SMB Join ：Sort Merge Bucket Join

Hive中的MapJoin，CommonJoin，SMBJoin详细介绍

1.10 笛卡尔积

Join的时候不加on条件，或者无效的on条件，因为找不到 Join key，Hive 只能使用1个 Reducer 来完成笛卡尔积。

当 Hive 设定为严格模式（hive.mapred.mode=strict，nonstrict）时，不允许在 HQL 语句中出现笛卡尔积。

二、Hive Map优化

2.1 复杂文件增加Map数

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

增加map的方法为：根据
computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。

案例实操：

1）执行查询
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

2）设置最大切片值为100个字节
hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1

2.2 小文件进行合并

1）在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

2）在Map-Reduce的任务结束时合并小文件的设置：

在map-only任务结束时合并小文件，默认true
set hive.merge.mapfiles = true;

在map-reduce任务结束时合并小文件，默认false
set hive.merge.mapredfiles = true;

合并文件的大小，默认256M
set hive.merge.size.per.task = 268435456;

当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
set hive.merge.smallfiles.avgsize = 16777216;

2.3 Map端聚合

set hive.map.aggr=true;相当于map端执行combiner

2.4 推测执行

set mapred.map.tasks.speculative.execution = true #默认是true

三、Hive Reduce优化

3.1 合理设置Reduce数

1）调整reduce个数方法一

（1）每个Reduce处理的数据量默认是256MB
set hive.exec.reducers.bytes.per.reducer = 256000000

（2）每个任务最大的reduce数，默认为1009
set hive.exec.reducers.max = 1009

（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)(参数2 指的是上面的1009，参数1值得是256M)

2）调整reduce个数方法二

在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;

3）reduce个数并不是越多越好

（1）过多的启动和初始化reduce也会消耗时间和资源；

（2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置reduce个数的时候也需要考虑这两个原则：

  • 处理大数据量利用合适的reduce数；
  • 使单个reduce任务处理数据量大小要合适；