hivesql优化

一.普通场景hivesql优化

1. 列裁剪

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

2. 分区裁剪

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

越早过滤越好where  --  减少下游处理的数据量

数据过滤

让关联的2张表数据变少

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

 

去重优化

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

sort by代替order by
order by将结果按某字段全局排序,这会导致所有map端数据都进入一个reducer中
ort by,那么还是会视情况启动多个reducer进行排序,并且保证每个reducer内局部有序,
    为了控制map端数据分配到reducer的key,往往还要配合distribute by一同使用。
    如果不加distribute by的话,map端数据就会随机分配到reducer。

小表在前大表在后

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

利用map join

https://mp.csdn.net/editor/html/113359842
map join特别适合大小表join,不等值关联的情况。Hive会将build table和probe table在map端直接完成join过程,
消灭了reduce,效率很高

set hive.auto.convert.join=true; //设置 MapJoin 优化自动开启,默认是开启的
set hive.mapjoin.smalltable.filesize=25000000 //设置小表不超过多大时开启 mapjoin 优化,即25M

#进行查询
select t1.a,t1.b 
from table t1 
join table2 t2 
on ( t1.a=t2.a and f.ftime=20110802)

二.数据倾斜下的sql优化

1)空值或无意义值
    
    事实表是日志类数据时,往往会有一些项没有记录到,我们视情况会将它置为null,
    如果缺失的项很多,在做join时这些空值就会非常集中,拖累进度。
        
    若不需要空值数据,就提前写where语句过滤掉
    需要保留的话,将空值key用随机方式打散,例如将用户ID为null的记录随机改为负值:
```sql

select a.uid,a.event_type,b.nickname,b.age    
from (    
select    
  (case when uid is null then cast(rand()*-10240 as int) else uid end) as uid,    
  event_type from calendar_record_log    
where pt_date >= 20190201    
) a left outer join (    
select uid,nickname,age from user_info where status = 4    
) b on a.uid = b.uid;

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

hive优化

hive优化
一个map或者reduce任务启动和初始化的时间远远大于逻辑处理的时间,就会造成很大的资源浪费。

map阶段优化
使单个map任务处理合适的数据量。

map参数设置
mapred.min.split.size:数据的最小分割单元;min值默认是1KB。
mapred.max.split.size:数据的最大分割单元;max值默认是256M。
通过调整max可以起到调整map数的作用,减小max可以增加map数;增加min可以减少map数。

map切分
假设input目录下有1个文件a,大小是780M,
那么map默认参数会把a分成7块(6个128M和1个 12M),从而产生7个map。

假设input目录下有3个文件a,b,c,大小分别为10M,20M,130M,
那么hadoop会把文件分成4块(10M,20M,128M,2M),从而产生4个map数。

解决方式
通过以下方法来在map执行前合并小文件,减少map数:
set mapred.max.split.size=128000000;              // 能分割块的最大块大小
set mapred.min.split.size.per.node=100000000;    // 每个节点处理的最小split
set mapred.min.split.size.per.rack=100000000;    // 每个机架处理的最小split
***前面三个参数确定合并文件块的大小,大于文件块大小128m的,按照128m来分隔,
小于128m,大于100m的,按照100m来分隔,把那些小于100m的(包括小文件和分隔大文件剩下的),
进行合并

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;  // 合并文件
***这个参数表示执行前进行小文件合并。

reduce阶段优化
Reduce设置的过大,那么将会产生很多小文件,
hive.exec.reducers.bytes.per.reducer(每个reduce任务处理的数据量,默认为1000^3=1G)    
hive.exec.reducers.max(每个任务最大的reduce数,默认为999)

合并小文件
是否合并Map输出文件:hive.merge.mapfiles=true(默认值为真)
是否合并Reduce 端输出文件:hive.merge.mapredfiles=false(默认值为假)
合并文件的大小:hive.merge.size.per.task=25610001000(默认值为 256000000)

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页