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

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 的：請點這裡

本文永久更新連結地址：