Hive優(yōu)化提示-如何寫好HQL

盡量將小表放在join的左邊，我們這邊使用的hive-0.12.0,所以是自動轉化的，既把小表自動裝入內(nèi)存，執(zhí)行map side join(性能好), 這

一、 Hive join優(yōu)化

1. 盡量將小表放在join的左邊，我們這邊使用的hive-0.12.0,所以是自動轉化的，既把小表自動裝入內(nèi)存，執(zhí)行map side join(性能好), 這是由參數(shù)hive.auto.convert.join=true 和hive.smalltable.filesize=25000000L）參數(shù)控制（默認是25M），如果表文件大小在25M左右，，可以適當調(diào)整此參數(shù)，進行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. 注意帶表分區(qū)的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;

一個更高效的實現(xiàn)是利用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內(nèi)表和外表的區(qū)別

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

Hive本地獨立模式安裝

Hive學習之WordCount單詞統(tǒng)計

Hive運行架構及配置部署

二、 合理設置map與reduce的個數(shù)。

1、如何合并小文件，減少map數(shù)？

如果一個表中的map數(shù)特別多，可能是由于文件個數(shù)特別多，而且文件特別小照成的，可以進行如下操作，合并文件，：

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、如何適當?shù)脑黾觤ap數(shù)？

如果表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數(shù)， reduce的個數(shù)基于以下參數(shù)設定：

hive.exec.reducers.bytes.per.reducer（每個reduce任務處理的數(shù)據(jù)量，默認為1000^3=1G）

hive.exec.reducers.max（每個任務最大的reduce數(shù)，默認為999）

計算reducer數(shù)的公式很簡單N=min(參數(shù)2，總輸入數(shù)據(jù)量/參數(shù)1)

即，如果reduce的輸入（map的輸出）總大小不超過1G,那么只會有一個reduce任務；所以調(diào)整以下參數(shù)：

set hive.exec.reducers.bytes.per.reducer=500000000; （500M）

set mapred.reduce.tasks = 15;

三、 如果設計和使用bucket,

Buckets 對指定列計算 hash，根據(jù) hash 值切分數(shù)據(jù)，目的是為了并行，每一個 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

所用場合：對某一列進行分區(qū)，比如對用戶ID進行分區(qū)，例如：

CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)

> PARTITIONED BY (dt STRING)

> CLUSTERED BY (user_id) INTO 96 BUCKETS; // 按照日期分區(qū)后，再按照user_id把日志放在96個籃子里。插入數(shù)據(jù)的時候：

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 的記錄非常多的時候，設置以下兩個參數(shù)：

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

這是針對數(shù)據(jù)傾斜的，設為ture的時候，任務的reduce會把原來一個job拆分成兩個，第一個的job中reduce處理處理不同的隨即分發(fā)過來的key的數(shù)據(jù)，生成中間結果，再由最后一個綜合處理。

六、 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里。所以經(jīng)常和sort by 來實現(xiàn)對某一字段的相同值分配到同一個reduce排序。

3、 Cluster by 實現(xiàn)了Distribute by+ sort by 的功能

Hive 的詳細介紹：請點這里
Hive 的下載地址：請點這里

本文永久更新鏈接地址：

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com