HiveSQL优化方法

Hive调优集锦
 Hive/HiveSQL常用优化方法全面总结
 关于Hive优化的四种方法总结
 HiveSQL优化
 Hive数据倾斜问题
 Hive常见的数据倾斜及调优技巧
 HiveSQL排序

Hive作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。对Hive的调优既包含对HiveSQL语句本身的优化，也包含Hive配置项和MR方面的调整。

减少数据量/扫描分区数，避免全表扫描

列裁剪和分区裁剪

所谓列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。当列很多或者数据量很大时，如果select *或者不指定分区，全列扫描和全表扫描效率都很低。

谓词下推

将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。
（先尽可能过滤数据，再进行关联）

避免数据倾斜

数据倾斜表现：

任务日志进度长度为99%，在日志监控进度条显示只有几个reduce进度一直没有完成。
某一reduce处理时长>平均处理时长
job数过多

数据倾斜原因分析：

key分布不均
业务数据本身存在不均匀情况
关联字段重复数据较多

sort by代替order by

HiveSQL中的order by与其他SQL方言中的功能一样，就是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。如果不加distribute by的话，map端数据就会随机分配到reducer。（如果两个字段相同，相当于cluster by）

select uid,upload_time,event_type,record_data   
from calendar_record_log    
where pt_date >= 20190201 and pt_date <= 20190224   
distribute by uid   
sort by upload_time desc,event_type desc;

用嵌套查询的方式实现全局排序：

--非全局排序
select name,Company from table distribute by Company sort by Company;
--全局排序
select * from (select name,Company from table distribute by Company sort by Company) t order by Company;

group by代替distinct

当要统计某一列的去重数时，如果数据量很大，count(distinct)就会非常慢，原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。这时可以用group by来改写，先group by再count(1)。
但是这样写会启动两个MR job（单纯distinct只会启动一个），所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。当数据集很小或者key的倾斜比较明显时，group by还可能会比distinct慢。

select count(id) from (select id from bigtable group by id) a;

如何用group by方式同时统计多个列：

select t.a,sum(t.b),count(t.c),count(t.d) from (    
select a,b,null c,null d from some_table    
union all   
select a,0 b,c,null d from some_table group by a,c  
union all   
select a,0 b,null c,d from some_table group by a,d  
) t;

union all代替left join

同上

减少job数

join基础优化

build table（小表）前置
多表join时key相同（将多个join合并为一个MR job来处理）
这种情况会将多个join合并为一个MR job来处理，如果条件不同，就会拆成两个MR job来计算。
大小表关联：map join
map join特别适合大小表join的情况。Hive会将build table和probe table在map端直接完成join过程，消灭了reduce，效率很高。

select /*+ MAPJOIN(time_dim) */ count(*) from store_sales join time_dim on ss_sold_time_sk = t_time_sk;

大大表关联：处理空值
大表与大表Join时，当其中一张表的NULL值（或其他值）比较多时，容易导致这些相同值在reduce阶段集中在某一个或几个reduce上，发生数据倾斜问题。
优化方法：
（1）将NULL值提取出来最后合并，这一部分只有map操作；非NULL值的数据分散到不同reduce上，不会出现某个reduce任务数据加工时间过长的情况，整体效率提升明显。这种方法由于有两次Table Scan会导致map增多。

SELECT a.user_Id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON a.user_id = b.user_id
where a.user_id IS NOT NULL
UNION ALL
SELECT a.user_Id,a.username,NULL
FROM user_info a
WHERE a.user_id IS NULL

（2）在Join时直接把NULL值打散成随机值来作为reduce的key值，不会出现某个reduce任务数据加工时间过长的情况，整体效率提升明显。这种方法解释计划只有一次map，效率一般优于第一种方法。

SELECT a.user_id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON (CASE WHEN a.user_id IS NULL THEN CONCAT ('dp_hive', RAND()) ELSE a.user_id END = b.user_id);

分桶表map join
map join对分桶表还有特别的优化。由于分桶表是基于一列进行hash存储的，因此非常适合抽样（按桶或按块抽样）。
倾斜均衡配置项
这个配置与上面group by的倾斜均衡配置项异曲同工，通过hive.optimize.skewjoin来配置，默认false。
如果开启了，在join过程中Hive会将计数超过阈值hive.skewjoin.key（默认100000）的倾斜key对应的行临时写进文件中，然后再启动另一个job做map join生成结果。通过hive.skewjoin.mapjoin.map.tasks参数还可以控制第二个job的mapper数量，默认10000。
再重复一遍，通过自带的配置项经常不能解决数据倾斜问题。join是数据倾斜的重灾区，后面还要介绍在SQL层面处理倾斜的各种方法。

优化SQL处理join数据倾斜

空值或无意义值
若不需要空值数据，就提前写where语句过滤掉。需要保留的话，将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值。
解决方法为：空值null key变成字符串加上随机数，可以把由于数据倾斜而导致的数据集中到一个reduce上处理的情形，打散到不同的reduce上，生成多个reduce！

on (case when a.id is null then concat('hive',rand()) else a.id end = b.id)

单独处理倾斜key
这其实是上面处理空值方法的拓展，不过倾斜的key变成了有意义的。一般来讲倾斜的key都很少，我们可以将它们抽样出来，对应的行单独存入临时表中，然后打上一个较小的随机数前缀（比如0~9），最后再进行聚合。
例子：
现有1TB文本文件words.txt，文件每行为若干个英文单词，单词间用空格分隔，文件中存在单词word1占据了总单词量的30%以上，其他单词出现频率较为平均。根据以上场景，请描述mapreduce如何统计每个单词出现的频次。
题中所述文本文件存在明显的数据倾斜问题，word1出现频次远大于其他单词，因此需要对word1在map阶段的输出key值进行构造，从而将word1均分给多个reduce计算。
map方法按行读取文件，每行文件按空格分隔为一个单词列表，依次读取每个单词。
若单词为word1，则map阶段的输出为<word1_randomInt(50),1>,即"word1_"加0-50 之间的随机整数。其他单词直接输出<单词,1>。
注：只要答出map阶段对单词word1的输出key值进行构造，以达到将word1均分为多个不同的key输出的目的即可，具体方法可有所区别。
转换不同数据类型
例如注册表中ID字段为int类型，登录表中ID字段即有string类型，也有int类型。当按照ID字段进行两表之间的join操作时，默认的Hash操作会按int类型的ID来进行分配，这样会导致所有string类型ID的记录统统都分配到一个Reduce里面去！
解决方法：把数字类型转换成字符串类型

on a.ID = cast(b.ID as string)

build table过大
有时，build table会大到无法直接使用map join的地步，比如全量用户维度表，而使用普通join又有数据分布不均的问题。这时就要充分利用probe table的限制条件，削减build table的数据量，再使用map join解决。代价就是需要进行两次join。

select /*+mapjoin(b)*/ a.uid,a.event_type,b.status,b.extra_info 
from calendar_record_log a  
left outer join (   
select /*+mapjoin(s)*/ t.uid,t.status,t.extra_info  
from (select distinct uid from calendar_record_log where pt_date = 20190228) s  
inner join user_info t on s.uid = t.uid 
) b on a.uid = b.uid    
where a.pt_date = 20190228;

配置优化

group by配置优化

map端预聚合
group by时，如果先起一个combiner在map端做部分预聚合，可以有效减少shuffle数据量。
预聚合的配置项是hive.map.aggr，默认值true，对应的优化器为GroupByOptimizer，简单方便。
通过hive.groupby.mapaggr.checkinterval参数也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值100000。
倾斜均衡配置项
group by时如果某些key对应的数据量过大，就会发生数据倾斜。Hive自带了一个均衡数据倾斜的配置项hive.groupby.skewindata，默认值false。
其实现方法是在group by时启动两个MR job。第一个job会将map端数据随机输入reducer，每个reducer做部分聚合，相同的key就会分布在不同的reducer中。第二个job再将前面预处理过的数据按key聚合并输出结果，这样就起到了均衡的效果。
但是，配置项毕竟是死的，单纯靠它有时不能根本上解决问题，因此还是建议自行了解数据倾斜的细节，并优化查询语句。

MapReduce配置优化

调整mapper数
mapper_num = MIN(split_num, MAX(default_num, mapred.map.tasks))
mapper数量与输入文件的split数息息相关。一般来讲，如果输入文件是少量大文件，就减少mapper数；如果输入文件是大量非小文件，就增大mapper数；至于大量小文件的情况，得参考下面“合并小文件”一节的方法处理。
调整reducer数
reducer_num = MIN(total_input_size / reducers.bytes.per.reducer, reducers.max)
reducer数量与输出文件的数量相关。如果reducer数太多，会产生大量小文件，对HDFS造成压力。如果reducer数太少，每个reducer要处理很多数据，容易拖慢运行时间或者造成OOM。
合并小文件
输入阶段合并、输出阶段合并
启用压缩
压缩job的中间结果数据和输出数据，可以用少量CPU时间节省很多空间。
JVM重用
在MR job中，默认是每执行一个task就启动一个JVM。如果task非常小而碎，那么JVM启动和关闭的耗时就会很长。可以通过调节参数mapred.job.reuse.jvm.num.tasks来重用。例如将这个参数设成5，那么就代表同一个MR job中顺序执行的5个task可以重复使用一个JVM，减少启动和关闭的开销。但它对不同MR job中的task无效。
并行执行
Hive中互相没有依赖关系的job间是可以并行执行的，最典型的就是多个子查询union all。在集群资源相对充足的情况下，可以开启并行执行。
本地模式
Hive也可以不将任务提交到集群进行运算，而是直接在一台节点上处理。因为消除了提交到集群的overhead，所以比较适合数据量很小，且逻辑不复杂的任务。
严格模式
所谓严格模式，就是强制不允许用户执行3种有风险的HiveSQL语句，一旦执行会直接失败。
1、查询分区表时不限定分区列的语句
2、两表join产生了笛卡尔积的语句
3、用order by来排序但没有指定limit的语句
推测执行
采用合适的存储格式
在HiveSQL的create table语句中，可以使用stored as ...指定表的存储格式。Hive表支持的存储格式有TextFile、SequenceFile、RCFile、Avro、ORC、Parquet等。
存储格式一般需要根据业务进行选择，在我们的实操中，绝大多数表都采用TextFile与Parquet两种存储格式之一。
TextFile是最简单的存储格式，它是纯文本记录，也是Hive的默认格式。虽然它的磁盘开销比较大，查询效率也低，但它更多地是作为跳板来使用。RCFile、ORC、Parquet等格式的表都不能由文件直接导入数据，必须由TextFile来做中转。
Parquet和ORC都是Apache旗下的开源列式存储格式。列式存储比起传统的行式存储更适合批量OLAP查询，并且也支持更好的压缩和编码。我们选择Parquet的原因主要是它支持Impala查询引擎，并且我们对update、delete和事务性操作需求很低。