Hive 执行优化

Hive 执行优化

1、命令行执行显示优化

set hive.cli.print.current.db=true;
让提示符显示当前库

set hive.cli.print.header=true;
显示查询结果时显示字段名称

set hive.fetch.task.conversion=more;
查询不会执行mapreduce程序, 优化全局查找、字段查找、limit查找
出现在hive 0.10，0.10~0.13 默认值 minimal 0.14后 默认为 more

2、hive使用限制参数

set hive.mapred.mode = strict;
将不允许笛卡尔积 1.x 以下默认是nonstrict 2.x 默认是 strict,同时分区表必须要有分区字段

set hive.strict.checks.no.partition.filter = true;
对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行

set hive.strict.checks.orderby.no.limit = true;
使用了order by语句的查询，要求必须使用limit语句

set hive.strict.checks.cartesian.product = true;
会限制笛卡尔积的查询

3、hive指定队列与查询引擎

set mapreduce.job.queuename=hive;
指定队列

set hive.execution.engine = mr / tez / spark
替换引擎 2.0之前默认 mr 之后tez

4、默认建表改为orc

set hive.default.fileformat=ORC;
set hive.default.fileformat.managed=ORC;
改变建表格式为ORC，需要两个一起使用，或，hive.default.fileformat.managed为none时，可以不改

ALTER TABLE customer_address SET FILEFORMAT ORC

5、hive动态分区使用

set hive.exec.dynamic.partition=true;
动态分区1 Hive0.9以后默认为true
如果执行多个insert overwrite table __ partition 想要并发写入，需要把动态分区关闭

set hive.exec.dynamic.partition.mode=nonstrict;
动态分区2

set hive.exec.max.dynamic.partitions.pernode=100;
（默认100，一般可以设置大一点，比如1000，表示每个maper或reducer可以允许创建的最大动态分区个数） 动态分区3

set hive.exec.max.dynamic.partitions =1000;
(默认值 表示一个动态分区语句可以创建的最大动态分区个数，超出报错) 动态分区4

set hive.exec.max.created.files =10000;
(默认) 全局可以创建的最大文件个数，超出报错。 动态分区5

hive.error.on.empty.partition=false;
当有空分区生成时，是否抛出异常。一般不需要设置。动态分区6

6、小文件合并优化专题

set hive.input.format=
org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
Hive 0.5 之后的默认值

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

set hive.merge.mapredfiles = true;
hive 0.4默认false ，true 时在 MapReduce 的任务结束时合并小文件

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

set mapred.max.split.size=256000000;
每个 Map 最大分割大小

set mapred.min.split.size.per.node=1;
一个节点上 split 的最少值

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

alter table table_name partition (dt=“xxxx”) concatenate;
合并小文件只支持RCFILE和ORC文件类型

7、开启任务并行执行

set hive.exec.parallel=true;
默认值：false
当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all的时候）

set hive.exec.parallel.thread.number=16;
默认值：8
同一个sql允许并行任务的最大线程数

8、设置集群跑任务还在单节点跑任务

set hive.exec.mode.local.auto=true;
hive自动选择单节点还是分布式，出现在hive 0.7 默认值false
数据量小的查询，以分布式方式执行就涉及到跨网络传输、多节点协调 等，并且消耗资源。

9、任务执行task特别多优化

set mapred.job.reuse.jvm.num.tasks=10;
JVM重用对hive的性能具有非常大的 影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。
jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。

10、hive的压缩优化

set hive.exec.compress.output = true;
默认值false 最后一个最终输出(到本地/hdfs文件或Hive表)是否被压缩。

set hive.exec.compress.intermediate = true;
默认值false 控制Hive在多个map-reduce作业之间生成的中间文件是否被压缩。

Hive 最终是转为 MapReduce 程序来执行的，而MapReduce 的性能瓶颈在于网络 IO 和 磁盘 IO，要解决性能瓶颈，最主要的是减少数据量，对数据进行压缩是个好的方式。压缩虽然是减少了数据量，但是压缩过程要消耗CPU的，但是在Hadoop中， 往往性能瓶颈不在于CPU，CPU压力并不大，所以压缩充分利用了比较空闲的CPU

11、小表 join大表时，mapjoin优化

set hive.auto.convert.join=true;
hive 0.11 之后默认true,hive在进行join的时候会判断表的大小来决定是否进行mapJoin

set hive.mapjoin.smalltable.filesize=128000000;
默认25000000 25M,hive在进行join的时候会判断表的大小来决定是否进行mapJoin的大小阈值 字节数

set hive.mapjoin.check.memory.rows=1000000;
hive在进行join的时候会判断左表的大小来决定是否进行mapJoin的大小阈值–数据行数

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。

12、大表join大表数据倾斜空key优化sql

insert overwrite table join_test_table
select n.* from null_id_table n full join ori o on
case when n.id is null then concat(‘hive’, rand()) else n.id end = o.id;

13、优化 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 left semi join b on a.id = b.id;

扩展：left join 与 left semi join 区别

hive join属于 common join，但是 left semi join 则属于 map join的一种变体，也是加载到内存
注意：用LEFT SEMI JOIN 当左表中的记录，在右表上产生符合条件之后就返回，不会再继续查找右表记录了，
所以如果右表有重复，也不会产生重复的多条记录。
left semi join 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
left semi join 中最后 select 的结果只许出现左表，因为右表只有 join key 参与关联计算了，而 join on 默认是整个关系模型都参与计算了。
left semi join 可以简单理解为join on操作，但本人目前未发现相比于join on执行有高效的优化

14、数据倾斜及Group by 优化

set hive.map.aggr=true;
Hive 0.3默认,map端聚合，降低传给reduce的数据量

set hive.groupby.mapaggr.checkinterval = 100000
在Map端进行聚合操作的条目数目

set hive.groupby.skewindata=true
默认false,开启hive内置的数倾优化机制
注意：开启后只能有一个distinct，两个会报错 不同的列不支持去重操作
当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作

14、Reduce个数调整

set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
方法1：调整每个reduce所接受的数据量大小

set mapred.reduce.tasks = 20
方法2：直接设置reduce数量 （会生成20个文件）

什么情况下只有1个reduce，而且设置参数也没有用。。。。。
1、没有group by 汇总
比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’ group by pt;
写成 select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’;
2、用了order by
3、有笛卡尔积

15、Hive Multi-group by 的使用

from(
    select
        fbuyer_id,
        ftrade_id,
        fdeal_id,
        fbdeal_id
    from adm.adm_sku_oms_orders_non_third_trade_dt_de
    where event_type = 2
        and dt = '2021-04-12'
)a 
insert into temp.temp_left_semi_tmp0
select 
    a.fbuyer_id,count(*)
group by a.fbuyer_id
insert into temp.temp_left_semi_tmp1
select 
    a.ftrade_id,count(*)
group by a.ftrade_id

---------------------注意 若多次操作，不能连续操作同一张表