HIVE SQL优化

原创
2019/06/19 20:05
阅读数 1.1K

作为数据仓库的利器,大部分的数仓工作者大部分时间都在写Hive Sql,根据作者经验整理出单纯的Hive Sql调优。

一. 普通场景下的sql优化

1. 列裁剪

由于数仓底层存储大都采用列式存储,如ORC/PARQUET,所以可以采用列裁剪的方式减少扫描的字段。

2. 分区裁剪

即查询数据表时增加分区的条件,数仓通常是集团级的数据存储,数据量非常大,所以大多都采用分区,加速数据统计效率,所以分区裁剪必不可少。

3. 利用hive的优化机制减少job

不论是outer join还是inner join,如果join的key相同,不管有多少表都会合为一个Mapreduce。

4. job输入输出优化

善用multi-insert、union all,不同表的union all相当于multi-insert,相同表的union all则是map一次输出多条。

insert overwrite table tmp select from a where condition1;
insert overwrite table tmp select from a where condition2;

from a
insert overwrite table tmp select where condition1
insert overwrite table tmp select where condition2;

5. 数据过滤

在join之前过滤掉不需要的数据

select t1.dim1,t1.measure1,t2.measure2
from 
     (select dim1,measure1 from a where dt = '2019-01-01') t1
join (select dim1,measure2 from b where dt = '2019-01-01') t2 on t1.dim1 = t2.dim1

6. 小表在前大表在后

在reduce阶段,位于join操作符的左边的表会加载到内存,可提高运行效率

7. mapjoin

小表与大表join时,左表在join前,然后采用mapjoin的方式,可避免大小表join产生数据倾斜的问题,即小表生成HashTableFiles上传至Distributed Cache,每个Mapper从Distributed Cache读取HashTableFiles到内存中,顺序扫描大表,在Map阶段直接进行Join,将数据传递给下一个MapReduce任务。

8.left semi join

left semi join是in/exists的更高效实现,以左表为主表,当关联到相同key的记录时则break进行下一个key的关联,比inner join要高效,另外left semi join只可取左表的字段,右表仅仅作为in操作的数据源,不可做数据提取,下面这段sql t2.measure2不可取值

select t1.dim1,t1.measure1
from 
     (select dim1,measure1 from a where dt = '2019-01-01') t1
left semi join (select dim1,measure2 from b where dt = '2019-01-01') t2 on t1.dim1 = t2.dim1

9. 合理使用union all和multi-insert

太多不同表不建议使用union all,可以采用插入临时表分区的方式再统一处理。

insert overwrite table test1(tag='1')
select field1,field2 from a;
insert overwrite table test1(tag='2')
select field1,field2 from b;
insert overwrite table test1(tag='3')
select field1,field2 from c;

10. 有效利用lateral view explode

如果要按照不同的维度做聚合,可先将维度concat,然后使用lateral view explode(split)的方式再做group by

11. 去重优化

一般禁止用distinct去重,除非数据量小到单台服务器可做去重无压力的情况下可使用。大表场景下可采用group by 去重

12. 排序优化

由于order by是全局排序,一个reduce实现,所以不能并行效率低下,如果可接收部分有序即分区内有序,则使用distribute by sort by的方式,会按照distribute by的分区进行排序,即单个reduce的情况下有序,效率高,cluster by不能指定排序规则,所以使用场景较少。

二. 数据倾斜下的sql优化

一切一切的数据倾斜解决方案都是围绕怎么打散key来解决。 现象为reduce 99%或者map 99%或者map/reduce oom

1. 空值产生的倾斜

如果join 的key空值较多容易产生数据倾斜,可以采用concat(user_id,rand())将key拼接上随机数生成新key,即可分散到不同的reduce上面。

2. 不同类型关联产生数据倾斜

如果join的两个key数据类型不同,则需要转换为同一个类型,因为默认的hash会按照int型发送到reduce上,会导致非int类型的记录全部发送到一个reduce上面。

3. 关联的key某个值数据较多

可以采用加入随机数聚合的方式

select key,sum(pv) as pv
from
      (
          select key,round(rand()*1000) as rnd,sum(pv) as pv
          from a 
          group by key,round(rand()*1000)
      ) t
group by key

4. count(distinct)产生数据倾斜

select count(distinct user_id) from a
可替换为
select count(1) from(select user_id from a group by user_id) t

除了上述说到的一些数据倾斜情况以及解决方案外,罗列一些现阶段遇到的一些问题及解决方案:

1.java heap space

map端和reduce端都有可能出现,除了数据量大的情况下容易出现,数据量小逻辑很复杂的时候也很容易出现

map端如果出现,则将map端输入的文件大小切片设置小一点,输入数据少一点,不要一味的去增大jvm内存。然后设置map阶段完毕时合并碎文件 reduce端出现:合并map输出文件的前提下出现此类情况,尽量将数据分配到每个reduce均匀且不要太大

2.GC overhead limit exceeded

还是先看是map端还是reduce端。同样如果map和reduce两个阶段的逻辑都很复杂(分组聚合不止在reduce端,map端也可以,还可以设置map端聚合的条数限制)。 map端:可通过设置map端gc内存,注意gc的内存不要大于map端设置的内存,两者要协调好比例。一般设置2G-3G即可,map端内存则设置为4096即可。 reduce端:同样reduce端一个道理,但要注意的是如果数据量大的情况下最好还是先将数据量缩小(拆分sql聚合部分为多个部分,增加并行度)

3.绝大部分的数据倾斜都可以通过sql自身的优化解决掉。

解决数据倾斜的第一步不要想着加各种参数,先优化sql。最通俗的看是如何发生数据倾斜的则是通过explain执行计划。tez引擎会优化DAG,称为CBO,注意mr和tez的执行计划有所不同。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部