處理Hive中的數據傾斜的方法
更新時間:2024年10月29日 10:19:25 作者:莫叫石榴姐
數據傾斜是大數據處理不可避免會遇到的問題,那么在Hive中數據傾斜又是如何導致的?通過本片本章,你可以清楚的認識為什么Hive中會發(fā)生數據傾斜;發(fā)生數據傾斜時我們又該用怎么的方案去解決不同的數據傾斜問題,需要的朋友可以參考下
1 groupby(大表分組-局部聚合+全局聚合)
示例1:
select label,sum(cnt) as all from ( select rd,label,sum(1) as cnt from ( select id,label,round(rand(),2) as rd,value from tmp1 ) as tmp group by rd,label ) as tmp group by label;
示例2:
select split(new_source,'\\_')[0] as source ,sum(cnt) as cnt from (select concat(source,'_', rand()*100) as new_source ,count(1) as cnt from test_table where day ='2022-01-01' group by concat(source,'_', rand()*100) )tt group by split(new_source,'\\_')[0]
2 join(大中表Join - 加salt + 小表膨脹)
示例1:
select label,sum(value) as all from ( select rd,label,sum(value) as cnt from ( select tmp1.rd as rd,tmp1.label as label,tmp1.value*tmp2.value as value from ( select id,round(rand(),1) as rd,label,value from tmp1 ) as tmp1 join ( select id,rd,label,value from tmp2 lateral view explode(split('0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9',',')) mytable as rd ) as tmp2 on tmp1.rd = tmp2.rd and tmp1.label = tmp2.label ) as tmp1 group by rd,label ) as tmp1 group by label;
示例2:
select source ,source_name ,sum(cnt) as cnt from (select t1.source ,new_source ,nvl(source_name,'未知') as source_name ,count(imei) as cnt from (select imei ,source ,concat(cast(rand()*10 as int ),'_',source ) as new_source from test_table_1 where day ='2022-01-01' ) t1 inner join ( select source_name ,concat(preflix,'_',source) as new_source from test_table_1 where day ='2022-01-01' lateral view explode(split('0,1,2,3,4,5,6,7,8,9,10',','))b as preflix ) t2 on t1.new_source =t2.new_source group by t1.source ,new_source ,nvl(source_name,'未知') ) tta group by source ,source_name
3 雙大表Join - 抽樣取傾斜key+BroadJoin
##優(yōu)化前: create table test.tmp_table_test_all as select imei ,lable_id ,nvl(label_name,'未知') from tmp_table_1 t1 left join (select lable_id ,label_name from tmp_table_2 where day ='2024-01-01') t2 on t1.lable_id =t2.lable_id where t1.day ='2024-01-01' ; ## 優(yōu)化后 : create table test.tmp_table_test_all_new as with tmp_table_test_1 as (select lable_id ,count(1) as cnt from tmp_table_1 t1 tablesample(5 percent) --抽樣取5%的數據,減少table scan的量 group by lable_id order by cnt desc limit 100 ) select imei ,lable_id ,nvl(label_name,'未知') as label_name from tmp_table_1 t1 left join tmp_table_test_1 t2 on t1.lable_id =t2.lable_id left join (select lable_id ,label_name from tmp_table_2 where day ='2024-01-01') t3 on t1.lable_id =t3.lable_id where t1.day ='2024-01-01' and t2.lable_id is null union all select imei ,lable_id ,nvl(label_name,'未知') as label_name from tmp_table_1 t1 inner join (select lable_id from tmp_table_test_1 t1 left join tmp_table_2 t2 on t1.lable_id =t2.lable_id where t2.day ='2024-01-01') t3 on t1.lable_id =t3.lable_id where t1.day ='2024-01-01' ;
4 小結
到此這篇關于處理Hive中的數據傾斜的方法的文章就介紹到這了,更多相關處理Hive數據傾斜內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
Linux下開啟和配置OpenGauss數據庫遠程連接的教程詳解
openGauss是一款開源關系型數據庫管理系統,采用木蘭寬松許可證v2發(fā)行,本文主要為大家介紹了Linux系統中如何開啟和配置OpenGauss數據庫的遠程連接,需要的小伙伴可以參考下2023-12-12IntellJ Idea 2020版添加sqlite數據庫的方法
這篇文章主要介紹了IntellJ Idea 2020版添加sqlite數據庫的方法,本文通過圖文并茂的形式給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-11-11