Hive最佳化提示-如何寫好HQL

來源:互聯網
上載者:User

Hive最佳化提示-如何寫好HQL

一、    Hive join最佳化
 
1.    盡量將小表放在join的左邊,我們這邊使用的hive-0.12.0,所以是自動轉化的,既把小表自動裝入記憶體,執行map side join(效能好), 這是由參數hive.auto.convert.join=true 和hive.smalltable.filesize=25000000L)參數控制(預設是25M),如果表檔案大小在25M左右,可以適當調整此參數,進行map side join,避免reduce side join。 也可以顯示聲明進行map join:特別適用於小表join大表的時候,SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a join b on a.key = b.key
 
2.    注意帶表分區的join, 如:
 
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) WHERE a.dt='2014-08-07' AND b.dt='2014-08-07'
 
因為hive是先join再where的,所以如果在b中找不到a表的記錄,b表中的所以列都會列出null,包括ds列,這樣left outer的查詢結果與where子句無關了,解決辦法:
 
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND a.dt='2014-08-07' AND b.dt='2014-08-07'')
 
3.    怎樣寫exist/in子句?
 
Hive不支援where子句中的子查詢,SQL常用的exist in子句需要改寫。這一改寫相對簡單。考慮以下SQL查詢語句:
 
SELECT a.key, a.value  FROM a  WHERE a.key in  (SELECT b.key  FROM B);
 
可以改寫為
 
SELECT a.key, a.value  FROM a LEFT OUTER JOIN b ON (a.key = b.key)  WHERE b.key <> NULL;
 
一個更高效的實現是利用left semi join改寫為:
 
SELECT a.key, a.val  FROM a LEFT SEMI JOIN b on (a.key = b.key);
 
4.    Hive join只支援等值串連,不支援非等值串連。
 
5.    合理的使用map join, 場合:小表A join 大表,

基於Hadoop叢集的Hive安裝

Hive內表和外表的區別

Hadoop + Hive + Map +reduce 叢集安裝部署

Hive本地獨立模式安裝

Hive學習之WordCount單詞統計

Hive運行架構及配置部署
 
二、    合理設定map與reduce的個數。
 
1、如何合并小檔案,減少map數?
 
如果一個表中的map數特別多,可能是由於檔案個數特別多,而且檔案特別小照成的,可以進行如下操作,合并檔案,:
 
  set mapred.max.split.size=100000000; // 100M
 
  set mapred.min.split.size.per.node=100000000;
 
  set mapred.min.split.size.per.rack=100000000;
 
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; //  合并小檔案
 
2、如何適當的增加map數?
 
如果表A只有一個檔案,大小為120M,包含幾千萬記錄,可以考慮用多個map任務完成
 
set mapred.reduce.tasks=10;
 
                  create table a_1 as
 
                  select * from a
 
                  distribute by rand(123); //將a表的記錄,隨機的分散到包含10個檔案的a_1表中
 
3、hive如何確定reduce數, reduce的個數基於以下參數設定:
 
hive.exec.reducers.bytes.per.reducer(每個reduce任務處理的資料量,預設為1000^3=1G)
 
hive.exec.reducers.max(每個任務最大的reduce數,預設為999)
 
計算reducer數的公式很簡單N=min(參數2,總輸入資料量/參數1)
 
即,如果reduce的輸入(map的輸出)總大小不超過1G,那麼只會有一個reduce任務;所以調整以下參數:
 
set hive.exec.reducers.bytes.per.reducer=500000000; (500M)
 
set mapred.reduce.tasks = 15;
 
三、    如果設計和使用bucket,
 
Buckets 對指定列計算 hash,根據 hash 值切分資料,目的是為了並行,每一個 Bucket 對應一個檔案。 將 user 列分散至 32 個 bucket, 首先對 user 列的值計算 hash,對應 hash 值為 0 的 HDFS 目錄為:/wh/pvs/dt=2014-08-01/ctry=US/part-00000; hash 值為 20 的 HDFS 目錄為:/wh/pvs/dt=2014-08-01/ctry=US/part-00020
 
所用場合:對某一列進行分區,比如對使用者ID進行分區,例如:
 
CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)
 
> PARTITIONED BY (dt STRING)
 
> CLUSTERED BY (user_id) INTO 96 BUCKETS; // 按照日期分區後,再按照user_id把日誌放在96個籃子裡。插入資料的時候:
 
hive> SET hive.enforce.bucketing = true;
 
hive> FROM raw_logs
 
> INSERT OVERWRITE TABLE weblog
 
> PARTITION (dt='2009-02-25')
 
> SELECT user_id, url, source_ip WHERE dt='2009-02-25'
 
四、    Count(distinct)
 
當count distinct 的記錄非常多的時候,設定以下兩個參數:
 
hive>  hive.map.aggr = true
 
hive> set hive.groupby.skewindata=true;
 
hive>  select count (distinct gid) from cookie_label_summary where i_date=20130924;
 
五、    Group by
 
Group By的方法是在reduce做一些操作,這樣會導致兩個問題:
 
map端彙總,提前一部分計算:hive.map.aggr = true 同時設定間隔:hive.groupby.mapaggr.checkinterval
 
均衡處理:hive.groupby.skewindata
 
這是針對資料扭曲的,設為ture的時候,任務的reduce會把原來一個job拆分成兩個,第一個的job中reduce處理處理不同的隨即分發過來的key的資料,產生中間結果,再由最後一個綜合處理。
 
六、    Order by, Sort by ,Dristribute by,Cluster By
 
1、    order by VS Sort by: order by是在全域的排序,只用一個reduce去跑,所以在set hive.mapred.mode=strict 模式下,order by 必須limit,否則報錯。Sort by只保證同一個reduce下排序正確。
 
2、    Distribute by with sort by: Distribute by 是按指定的列把map 輸出結果分配到reduce裡。所以經常和sort by 來實現對某一欄位的相同值分配到同一個reduce排序。
 
3、    Cluster by 實現了Distribute by+ sort by 的功能

Hive 的詳細介紹:請點這裡
Hive 的:請點這裡

本文永久更新連結地址:

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: