(持续更新）2021-01-24-HiveSQL常用优化手段（Map Reduce）-HK

日常积累-HiveSQL的压缩方法+SQL整体优化（Map Reduce）

子目录：

• 配置parquet压缩
• 设置压缩+自动分区（Parquet和ORC）
• SQL整体优化（多任务并行、减少任务数量）

正文：
Map Reduce：
一个Hive查询会生成多个Map Reduce Job，每个Map Reduce Job有Map，Reduce，Spill，Shuffle，Sort等多个阶段

1. 配置parquet压缩

两种配置方式：
（1）在创建表时配置

create table mytable(a int,b int) 
STORED AS PARQUET TBLPROPERTIES('parquet.compression'='SNAPPY');
#其中mytable(a int,b int)是需要配置压缩的任务表

（2）在创建后配置

通过修改表的属性添加压缩

ALTER TABLE mytable SET TBLPROPERTIES ('parquet.compression'='SNAPPY');

或者在select时，set配置
（只会压缩后续入库的数据，添加压缩之前的数据不会压缩，若要压缩需要重跑所有之前的数据）

set parquet.compression=snappy; 
insert overwrite table etl_table_snappy 
select `(colunmn_1)?+.+` 
from etl_table;

2. 设置压缩+自动分区

set parquet.compression=snappy;
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;

作用：节省空间，提高数据检索效率。
为了不改变元数据的分割性，常见的组合压缩是parquet + snappy、orc + snappy

（（On-Line Analytical Processing）联机分析处理）
Parquet和ORC都是Apache旗下的开源列式存储格式。列式存储比起传统的行式存储更适合批量OLAP查询，并且也支持更好的压缩和编码。
Parquet支持Impala查询引擎，并且一般来说，工作对update、delete和事务性操作需求很低。
细节请参考官网：
https://parquet.apache.org/
https://orc.apache.org/

3. SQL整体优化

对Hive查询优化可以分为：

• 对Map Reduce的单个步骤优化
• 对Map Reduce的全局优化
• 对整个SQL查询（多个Map Reduce Job）优化

前两个会单独总结，这次总结一下第三个。

（1）多任务并行
执行多个子查询union all或join，使多个任务并行。

首先设置并行参数：

 set Hive.exec.parallel = True
 #此时默认并行度为8，即最多8个任务并行

可以通过设置并行度来扩大并行任务列（需要谨慎对待，过大的并行度会占用过多资源）

这里设置为9:

set Hive.exec.parallel. thread.number = 9

示例：
多任务并行

 set Hive.exec.parallel = True

select * from   
(  
   select count(*) from T_1   
   where job_date = 20210124 and job_id = 10068 
   union all   
   select count(*) from T_1   
   where job_date = 20210123 and job_id = 13245  
   union all   
   select count(*) from T_1   
   where job_date = 20210122 and job_id = 16783 
)T_recent_3

（2）减少任务数量
这个方法使通过优化实现思路，减少任务冗余。

示例
需求：找出即做过A又做过B的用户。
一般思路：面向任务明细表，先取出做过A的，再取出做过B的，最后取交集。

select count(*)   
from   
(select distinct user_id   
   from T_1 
  where Done_name = ‘A’) A  
join   
(select distinct user_id   
   from T_1 
  where Owner_name = ‘B’) B   
on A.user_id = B.user_id;

优化：
思路：面向统计的方法，使用group by代替join。
优点：不带子查询，只需要一个job即可跑完，更符合Map Reduce模式。

select count(*)   
from T_1 
where  Done_name = ‘A’ && Owner_name = ‘B’