hive优化
hive优化
参考博客：
https://blog.csdn.net/weixin_42072754/article/details/103301537
https://blog.csdn.net/young_0609/article/details/84593316
1. hive性能低下场景
- 模型设计不合理：根据实际的业务场景，需要建立分区表的就建立分区表，需要建立分桶表的就建立分桶表。
- 不合理的MapReduce的task数：比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够。
- 小文件很多：这也是会产生不必要task的个数，时间多消耗在task的初始化。
- 数据倾斜：hive在底层执行的是hadoop的MR程序，造成运行压力过大是因为数据倾斜。
- jobs数多：jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联对此汇总，产生几十个jobs，将会需要30分钟以上的时间且大部分时间被用于作业分配，初始化和数据输出。M/R作业初始化的时间是比较耗时间资源的一个部分。
- 使用count(distict())：在数据量大的情况下，效率较低，如果多COUNT(DISTINCT)效率更低，因为COUNT(DISTINCT)是按GROUP BY字段分组，按DISTINCT字段排序，一般这种分布式方式是很倾斜的；比如：男UV，女UV，淘宝一天30亿的PV，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。
2. hive优化
2.1 架构优化
2.1.1 执行引擎
Hive支持多种执行引擎，分别是 MapReduce、Tez、Spark、Flink。可以通过hive-site.xml文件中的hive.execution.engine属性控制。
2.1.2 优化器
与关系型数据库类似，Hive会在真正执行计算之前，生成和优化逻辑执行计划与物理执行计划。Hive有两种优化器：Vectorize(向量化优化器) 与 Cost-BasedOptimization (CBO 成本优化器)。
-
矢量化查询执行
set hive.vectorized.execution.enabled = true; -- 默认 false set hive.vectorized.execution.reduce.enabled = true; -- 默认 false备注：要使用矢量化查询执行，必须用ORC格式存储数据
-
成本优化器
SET hive.cbo.enable=true; --从 v0.14.0默认 true SET hive.compute.query.using.stats=true; -- 默认false SET hive.stats.fetch.column.stats=true; -- 默认false SET hive.stats.fetch.partition.stats=true; -- 默认true定期执行表（analyze）的分析，分析后的数据放在元数据库中。
2.1.3 分区表
对于一张比较大的表，将其设计成分区表可以提升查询的性能，对于一个特定分区的查询，只会加载对应分区路径的文件数据，所以执行速度会比较快。
分区字段的选择是影响查询性能的重要因素，尽量避免层级较深的分区，这样会造成太多的子文件夹。一些常见的分区字段可以是：
- 日期或时间。如year、month、day或者hour，当表中存在时间或者日期字段时
- 地理位置。如国家、省份、城市等
- 业务逻辑。如部门、销售区域、客户等等
2.1.4 分桶表
与分区表类似，分桶表的组织方式是将HDFS上的文件分割成多个文件。
分桶可以加快数据采样，也可以提升join的性能(join的字段是分桶字段)，因为分桶可以确保某个key对应的数据在一个特定的桶内(文件)，巧妙地选择分桶字段可以大幅度提升join的性能。
通常情况下，分桶字段可以选择经常用在过滤操作或者join操作的字段。
2.1.5 文件格式
存储格式一般需要根据业务进行选择，生产环境中绝大多数表都采用TextFile、ORC、Parquet存储格式之一。
TextFile是最简单的存储格式，它是纯文本记录，也是Hive的默认格式。其磁盘开销大，查询效率低，更多的是作为跳板来使用。RCFile、ORC、Parquet等格式的表都不能由文件直接导入数据，必须由TextFile来做中转。
Parquet和ORC都是Apache旗下的开源列式存储格式。列式存储比起传统的行式存储更适合批量OLAP查询，并且也支持更好的压缩和编码。选择Parquet的原因主要是它支持Impala查询引擎，并且对update、delete和事务性操作需求很低。
2.1.6 数据压缩
压缩技术可以减少map与reduce之间的数据传输，从而可以提升查询性能，关于压缩的配置可以在hive的命令行中或者hive-site.xml文件中进行配置。
SET hive.exec.compress.intermediate=true
关于压缩的编码器可以通过mapred-site.xml, hive-site.xml进行配置，也可以通过命令行进行配置，如：
-- 中间结果压缩
SET hive.intermediate.compression.codec=org.apache.hadoop.io.compress .SnappyCodec ;
-- 输出结果压缩
SET hive.exec.compress.output=true;
SET mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodc
2.2 SQL优化
2.2.1 列裁剪
即在查询的时候只查询需要用到的列，而不是全部列都查询。比如：
select a, b from T where e < 10;
裁剪所对应的参数项为：hive.optimize.cp=true（默认值为真）
2.2.2 分区裁剪
在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如：
-- 先关联再where
SELECT a.id
FROM bigtable a
LEFT JOIN ori b ON a.id = b.id
WHERE b.id <= 10;
正确写法是写在ON后面,先Where再关联
SELECT a.id
FROM ori a
LEFT JOIN bigtable b ON (b.id <= 10 AND a.id = b.id);
或者直接写成子查询
SELECT a.id
FROM bigtable a
RIGHT JOIN (SELECT id
FROM ori
WHERE id <= 10
) b ON a.id = b.id;
分区裁剪参数为：hive.optimize.pruner=true（默认值为真）
2.2.3 join操作
-
应该将条目少的表/子查询放在 Join 操作符的左边。原因是在 Join 操作的 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生 OOM 错误的几率。
对于一条语句中有多个 Join 的情况，如果 Join 的条件相同，比如查询：
SELECT pv.pageid, u.age FROM page_view p JOIN user u ON (pv.userid = u.userid) JOIN newuser x ON (u.userid = x.userid);- 如果 Join 的 key 相同，不管有多少个表，都会则会合并为一个 Map-Reduce任务
- 在做 OUTER JOIN 的时候也是一样
如果Join 的条件不相同，比如：
SELECT pv.pageid, u.age FROM page_view p JOIN user u ON (pv.userid = u.userid) JOIN newuser x on (u.age = x.age);Map-Reduce 的任务数目和 Join 操作的数目是对应的。
-
如果能使用mapjoin就尽量使用mapjoin
SELECT /*+ MAPJOIN(pv) */ pv.pageid, u.age FROM page_view pv JOIN user u ON (pv.userid = u.userid);设置mapjoin的参数
hive.auto.convert.join=true # 执行mapjoin，默认为true hive.join.emit.interval = 1000 hive.mapjoin.size.key = 10000 hive.mapjoin.cache.numrows = 25000 # 表示缓存多少行数据到内存，默认值25000。 -
分桶表使用mapjoin
分桶连接：Hive 建表的时候支持hash 分区通过指定clustered by (col_name,xxx )into number_buckets buckets 关键字.当连接的两个表的join key 就是bucketcolumn 的时候，就可以通过设置
hive.optimize.bucketmapjoin= true来执行优化。原理：通过两个表分桶在执行连接时会将小表的每个分桶映射成hash表，每个task节点都需要这个小表的所有hash表，但是在执行时只需要加载该task所持有大表分桶对应的小表部分的hash表就可以，所以对内存的要求是能够加载小表中最大的hash块即可。
备注：小表与大表的分桶数量需要是倍数关系，这个是因为分桶策略决定的，分桶时会根据分桶字段对桶数取余后决定哪个桶的，所以要保证成倍数关系。
优点：比map join对内存的要求降低，能在逐行对比时减少数据计算量（不用比对小表全量）
缺点：只适用于分桶表
hive.optimize.bucketmapjoin=true # 参数配置项 -
处理null值或无意义值
日志类数据中往往会有一些项没有记录到，其值为null，或者空字符串、-1等。如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度【备注：这个字段是连接字段】。
若不需要空值数据，就提前写 where 语句过滤掉。需要保留的话，将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值：
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;单独处理倾斜key
如果倾斜的 key 有实际的意义，一般来讲倾斜的key都很少，此时可以将它们单独抽取出来，对应的行单独存入临时表中，然后打上一个较小的随机数前缀（比如0~9），最后再进行聚合。
-
调整map数
通常情况下，作业会通过输入数据的目录产生一个或者多个map任务。主要因素包括：
-
输入文件总数
-
输入文件大小
-
HDFS文件块大小
map越多越好吗。当然不是，合适的才是最好的。
如果一个任务有很多小文件（<< 128M），每个小文件也会被当做一个数据块，用一个 Map Task 来完成。一个 Map Task 启动和初始化时间 >> 处理时间，会造成资源浪费，而且系统中同时可用的map数是有限的。
对于小文件采用的策略是合并。
-
-
调整reduce数
reducer数量的确定方法比mapper简单得多。使用参数
mapred.reduce.tasks可以直接设定reducer数量。如果未设置该参数，Hive会进行自行推测，逻辑如下：- 参数
hive.exec.reducers.bytes.per.reducer用来设定每个reducer能够处理的最大数据量，默认值256M。 - 参数
hive.exec.reducers.max用来设定每个job的最大reducer数量，默认值999（1.2版本之前）或1009（1.2版本之后） - 得出reducer数：
reducer_num = MIN(total_input_size / reducers.bytes.per.reducer, reducers.max)。即： min(输入总数据量 / 256M, 1009)
- 参数
2.2.4 group by 和聚合函数一起使用
会在map端做预聚合，需要修改的参数为：
hive.map.aggr=true #（用于设定是否在 map 端进行聚合，默认值为真）
hive.groupby.mapaggr.checkinterval=100000 #（用于设定 map 端进行聚合操作的条目数）
2.2.5 sort by 代替 order by
HiveSQL中的order by与其他关系数据库SQL中的功能一样，是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by 一同使用。如果不加 distribute by 的话，map端数据就会随机分配到reducer。
2.2.6 group by 代替 count(distinct)
当要统计某一列的去重数时，如果数据量很大，count(distinct) 会非常慢。原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。此时可以用group by 来改写：
-- 原始SQL
select count(distinct uid) from tab;
-- 优化后的SQL
select count(1) from (select uid from tab group by uid) tmp;
这样写会启动两个MR job（单纯distinct只会启动一个），所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。当数据集很小或者key的倾斜比较明显时，group by还可能会比distinct慢。
2.3 参数优化
2.3.1 合并小文件
-
在map执行前合并小文件
# 缺省参数 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFo rmat; -
在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.2 Fetch模式
Fetch模式是指Hive中对某些情况的查询可以不必使用MapReduce计算。select col1, col2 from tab ;
可以简单地读取表对应的存储目录下的文件，然后输出查询结果到控制台。在开启fetch模式之后，在全局查找、字段查找、limit查找等都不启动 MapReduce 。
# Default Value: minimal in Hive 0.10.0 through 0.13.1, more in Hive 0.14.0 and later
hive.fetch.task.conversion=more
