利用分区表优化
场景:在业务环境中,以某个字段为筛选条件的需求增加
解决方法:建立以这个字段为分区的分区表,这样进行查询时只需要指定这个分区就不再需要进行全表扫描
利用分桶表优化
场景:需要频繁进行采样
解决方法:分桶表会使用hash算法将写入的数据随机的分入桶中,如果要进行采样的话,直接选择一个桶即可。
补充三种采样的方法 :
-- 分桶抽象
select * from tmp_table tablesample(bucket 3 out of 32);
-- 3是抽取的分桶号,分桶号从0开始,32为总分桶数
-- 随机采样
select * from tmp_table order by rand() limit 100;
-- rand会随机打乱排序,然后抽取头100条数据 但是使用了order by效率过低
select * from tmp_table distribute by rand() sort by rand() limit 100;
--使用distribute by rand()分桶打乱来提高效率
-- 数据块抽样
select * from tmp_table tablesample(10 percent) -- 抽10%数据
select * from tmp_table tablesample(5 rows) -- 抽取行数
select * from tmp_table tablesample(5 M) -- 抽取数据块大小
文件存储格式
在建表的时候,是可以通过stored as
来指定表的存储格式,常见的存储格式有:
- TextFile
- 默认存储结构,行存储。
- 一行就是一条数据,以换行符分割,如果不进行压缩,磁盘开销较大,解析成本较高
- 建议使用可切分的压缩方式压缩
- SequenceFile
- HadoopAPI提供的二进制存储文件,使用方便,可分割,可压缩
- 一般选择block进行压缩
- RCFile
- 按行分块,数据块按列存储
- 比起提高查询速度,更多的是降低存储
- ORC
- 按行分块,数据块按列存储
- RCFile的升级版,在降低存储的情况下,查询性能大幅度提升
- 数据可压缩
- 基于列创建索引
- ParquetFile
- 列式存储
- 对于大型查询、指定列查询都是高效的
- 一般使用snappy压缩
- 支持impala查询引擎
数据进入数仓后,文件格式尽量选择后两者。
如何选择文件压缩格式
压缩格式 | 是否可拆分 | 是否自带 | 压缩率 | 速度 | 是否hadoop自带 |
---|---|---|---|---|---|
gzip | 否 | 是 | 很高 | 非常快 | 是 |
lzo | 是 | 是 | 比较高 | 很快 | 否,要安装 |
snappy | 否 | 是 | 比较高 | 很快 | 否,要安装 |
bzip2 | 是 | 否 | 最高 | 慢 | 是 |
选择压缩格式主要从三个方面:
- 压缩比率
- 压缩解压速度
- 是否支持切片
列裁剪
场景:查询的表是一个有数十列的大宽表,但是只需要选择一个列的数据
解决方法:开启列裁剪,这样就可以只选择想要的列
# 列裁剪,只选择需要用到的列,默认开启
set hive.optimize.cp = true;
谓词下推
谓词下推会将where谓词逻辑尽可能的提前执行,减少下游所需处理的数据量。
# 谓词下推,默认开启
set hive.optimize.ppd = true;
举个例子:
select a.*,b.* from a join b on a.id=b.id where b.age >20;
-- 谓词下推后
select a.*,c.* from a join(select * from b where age > 20) c on a.id=c.id;
分区裁剪
和列裁剪一样,就是只读取需要的分区。
# 分区裁剪,只选择需要用到的分区,默认开启
set hive.optimize.pruner = true;
合并小文件
在执行MR程序时,一般一个文件都需要一个MapTask来处理,如果文件数过多,那么就会启动大量的MapTask任务,从而浪费大量资源。进行小文件的合并可以减少MapTask任务数量,从而加快执行速度。
# map端输入、合并文件之后按照block大小分割
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
# map端输入、不合并
set hive.input.format = org.apache.hadoop.hive.ql.io.HiveInputFormat;
# 是否合并map端输出,默认为true
set hive.merge.mapfiles=true;
# 是否合并reduce端输出,默认为false
set hive.merge.mapredfiles=true;
# 合并文件的大小,默认为256000000(256M)
set hive.merge.size.per.task = 256000000;
# 每个map最大分割大小
set mapred.max.split.size = 256000000;
# 一个节点上split最小值 比如节点上所有的小文件大小之和不足256M,那么就合并成一个文件
set mapred.min.split.size.per.node = 1;
# 一个机架上split的最小值 假如机架中有四个节点,每个节点只有一个40M的文件,那么就把机架上的文件合并起来组成一个文件
set mapred.min.split.size.per.rack = 1;
Join优化
- 优先过滤后在进行join操作,最大限度的减少参与join的数据量
- 小表join大表,最好启动mapjoin,hive自动启用mapjoin,但是小表不能超过25M,可以设置
- join表的顺序从小到大
mapJoin
注意:这个措施,只要能用就用
mapJoin会将小表的数据分发到各个map的内存中,在map进程中进行join操作,从而提高速度。
# 自动将join转换为mapJoin
set hive.auto.convert.join = true;
# 小表大小
set hive.mapjoin.smalltable.filesize = 25000000;
# hive会基于表的size自动将普通join转换为mapjoin
set hive.auto.convert.join.noconditionaltask = true;
#多大的表可以自动触发内层LocalTask,默认大小10M
set hive.auto.convert.join.noconditionaltask.size = 10000000;
Sort-Merge-Bucket(SMB) map Join
也是map join的一种优化方式,前提为参与join的表必须是分桶表与分桶排序表。
具体实现:
- 针对参与join的这两张表做相同的hash散列
- 桶中数据要排序
- 这两张表的分桶个数成倍数
- 开启smb的开关
# 当用户执行smb map join时,如果不能执行,则禁止查询
set hive.enforce.sortmergrbucktmapjoin = false;
# 当join的表符合smb条件,join是否会自动转换为smb map join
set hive.auto.convert.sortmerge.join = true;
# 当两个分桶表join时,如果join on的是分桶字段,小表的分桶数是大表的倍数时,可以启用mapjoin来提高效率
set hive.optimize.bucketmapjoin = false;
set hive.optimize.bucketmapjoin.sortedmerge = false;
Join导致的数据倾斜
如果确认是因为Join造成的数据倾斜,可以进行如下配置:
# join的键对应的记录条数超过这个值就进行分拆,根据具体修改
set hive.skewjoin.key = 100000;
# 如果是join过程出现倾斜应该设置为true
set hive.optimize.skewjoin = false;
开启后,如果job处理的记录条数超过限制,就会重启一个job处理多余的数据。
# 设置后可以控制第二个job的mapper数量。
set hive.skewjoin.map.tasks = 100000;
CBO优化
在源码中遇见过这个优化,在hive1.1.0之后这个优化默认开启。
他会选择最优的join算法进行join。
set hive.cho.enable = true;
set hive.compute.query.using.stats = true;
set hive.stas.fetch.column.stats = true;
set hive.stats.fetch.partition.stats = true;
怎样做笛卡尔积
什么叫笛卡尔积:两张表做没有连接条件的连接
hive设置为严格模式时,不允许使用笛卡尔积,这说明hive对笛卡尔积的支持本身是较弱的。
原因是因为找不到Join key,做笛卡尔积时只有一个reduce,造成了很大的压力与延迟。
hive做笛卡尔积的精髓在于复制,小表复制了N倍,大表的数据就被随机分为了N份,就会产生N个reduce,就不会出现数据倾斜。
group by
https://blog.csdn.net/qq_41106844/article/details/107412769
order by
order by只能是在一个reduce进程中进行,所以如果对一个大数据集进行order by,会导致一个reduce进程中处理的数据相当大,造成查询执行缓慢。
1.如果在操作结果上进行order by,不要在中间的大数据集上进行排序,如果结果数据较少,可以在结果排序
2.如果是取排序后的前N条数据,那么可以使用distribute by和sort by在各个reduce上进行排序后取前N条,然后汇总后再取前N条。这样避免了数据全排序,所以执行效率会有很大提高。
现在有个需求:
求全年级分数最高的三个人
-- 使用order by
select * from student order by mark desc limit 3;
-- 使用distribute by+sort by
set mapreduce.job.reduces = 3;
create table student_orderby_result as
select
*
from
student
distribute by (
case
when
mark>80
then
0
when
mark<60
then
2
else
1
end)
sort by (mark desc);
第二种方式最主要的问题是如何均匀分桶,也就是如何保证数据的均匀分布。
建议使用采样的方式先估计一下数据的分布。
count distinct优化
当要对某一列去重时,如果数据量很大,count(distinct)就会非常慢,原因也是因为reduce数量少。
第一种解决方式是使用group by优化。
-- 原句
select count(distinct age) from student;
-- group by 优化 单个字段推荐
select count(1) from ( select age from student group by age)tmp;
-- 子句优化
select count(1) from (select distinct id from student) tmp;
in/exists语句
in/exists
语句应该全部换成left semi join
语句
vectorization技术
在计算scan/filter/aggregation
的时候,vectorization
技术以设置批处理的增量大小为1024行,单次增加处理量来提高效率。
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
多重模式
如果多条SQL对某种表进行扫描,同时做不同的逻辑,那么就可以使用多重扫描,对表进行一次扫描做不同逻辑。
from student
select .....
insert ....
...
但是呢,也有些限制:
- 单个SQL最多有128条语句
- 对于分区表,一个分区不能出现两次(读写锁)
- 对于同一张表的不同分区,不能同时有insert overwrite和insert into操作
例子:日志表与用户表做链接
select * from log a left outer join users b on a.user_id = b.user_id;
users表中有600w+记录,肯定不能使用mapjoin进行分发,而join又会遇到数据倾斜的问题。
改进方案
-- **sql3_start** 获取活跃用户的日志信息
select
*
from
log a
left outer join(
-- **sql2_start** 获取活跃用户的用户信息
select
d.*
from
-- **sql1_start**获取有活动的用户id信息
(select distinct user_id from log) c
-- **sql1_end**
join
user d
on
c.user_id =d.user_id
) x
-- **sql2_end**
on
a.user_id = x.user_id
;
-- **sql3_end**