HiveSQL优化方法

2020-09-27  本文已影响0人  天线嘟嘟茄

Hive/HiveSQL常用优化方法全面总结
关于Hive优化的四种方法总结
HiveSQL优化
Hive数据倾斜问题
Hive常见的数据倾斜及调优技巧
HiveSQL排序

Hive作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。对Hive的调优既包含对HiveSQL语句本身的优化，也包含Hive配置项和MR方面的调整。

减少数据量/扫描分区数，避免全表扫描

列裁剪和分区裁剪

所谓列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。当列很多或者数据量很大时，如果select *或者不指定分区，全列扫描和全表扫描效率都很低。

谓词下推

将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。
（先尽可能过滤数据，再进行关联）

避免数据倾斜

数据倾斜表现：

数据倾斜原因分析：

sort by代替order by

HiveSQL中的order by与其他SQL方言中的功能一样，就是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。如果不加distribute by的话，map端数据就会随机分配到reducer。（如果两个字段相同，相当于cluster by）

select uid,upload_time,event_type,record_data   
from calendar_record_log    
where pt_date >= 20190201 and pt_date <= 20190224   
distribute by uid   
sort by upload_time desc,event_type desc;

用嵌套查询的方式实现全局排序：

--非全局排序
select name,Company from table distribute by Company sort by Company;
--全局排序
select * from (select name,Company from table distribute by Company sort by Company) t order by Company;
group by代替distinct

当要统计某一列的去重数时，如果数据量很大，count(distinct)就会非常慢，原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。这时可以用group by来改写，先group by再count(1)。
但是这样写会启动两个MR job（单纯distinct只会启动一个），所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。当数据集很小或者key的倾斜比较明显时，group by还可能会比distinct慢。

select count(id) from (select id from bigtable group by id) a;

如何用group by方式同时统计多个列：

select t.a,sum(t.b),count(t.c),count(t.d) from (    
select a,b,null c,null d from some_table    
union all   
select a,0 b,c,null d from some_table group by a,c  
union all   
select a,0 b,null c,d from some_table group by a,d  
) t;
union all代替left join

同上

减少job数

join基础优化
select /*+ MAPJOIN(time_dim) */ count(*) from store_sales join time_dim on ss_sold_time_sk = t_time_sk;

SELECT a.user_Id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON a.user_id = b.user_id
where a.user_id IS NOT NULL
UNION ALL
SELECT a.user_Id,a.username,NULL
FROM user_info a
WHERE a.user_id IS NULL

（2）在Join时直接把NULL值打散成随机值来作为reduce的key值，不会出现某个reduce任务数据加工时间过长的情况，整体效率提升明显。这种方法解释计划只有一次map，效率一般优于第一种方法。

SELECT a.user_id,a.username,b.customer_id
FROM user_info a
LEFT JOIN customer_info b
ON (CASE WHEN a.user_id IS NULL THEN CONCAT ('dp_hive', RAND()) ELSE a.user_id END = b.user_id);
优化SQL处理join数据倾斜
on (case when a.id is null then concat('hive',rand()) else a.id end = b.id)

on a.ID = cast(b.ID as string)

select /*+mapjoin(b)*/ a.uid,a.event_type,b.status,b.extra_info 
from calendar_record_log a  
left outer join (   
select /*+mapjoin(s)*/ t.uid,t.status,t.extra_info  
from (select distinct uid from calendar_record_log where pt_date = 20190228) s  
inner join user_info t on s.uid = t.uid 
) b on a.uid = b.uid    
where a.pt_date = 20190228;

配置优化

group by配置优化
MapReduce配置优化
上一篇 下一篇

猜你喜欢

热点阅读