日常积累-HiveSQL的压缩方法+SQL整体优化(Map Reduce)
子目录:
- 配置parquet压缩
- 设置压缩+自动分区(Parquet和ORC)
- SQL整体优化(多任务并行、减少任务数量)
正文:
Map Reduce:
一个Hive查询会生成多个Map Reduce Job,每个Map Reduce Job有Map,Reduce,Spill,Shuffle,Sort等多个阶段
- 配置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;
- 设置压缩+自动分区
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/
- 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’