? 無意間在部門的svn看到以前老員工的Hive優化文檔，看看了，寫的挺詳細的，結合hadoop In action(陸) 一書的內容在這里做個匯總
1,列裁剪
?? 在讀數據的時候，只讀取查詢中需要用到的列，而忽略其他列。例如，對于查詢：
1
SELECT
a,b
FROM
T
WHERE
e
<
10
;
? 其中，T 包含 5 個列 (a,b,c,d,e)，列 c，d 將會被忽略，只會讀取a, b, e 列
? 這個選項默認為真：? hive.optimize.cp = true
2，分區裁剪
?? 在查詢的過程中減少不必要的分區。例如，對于下列查詢：
?
1
select
*
2
from
3
(
select
c1,
count
(
1
)
4
fromT
5
group
by
c1
6
) subq
7
where
subq.prtn
=
100
;
8
9
select
*
10
from
T1
11
join
12
(
select
*
13
from
T2
14
) subq
15
on
T1.c1
=
subq.c2
16
where
subq.prtn
=
100
;
??? 會在子查詢中就考慮 subq.prtn = 100 條件，從而減少讀入的分區數目。
??? 此選項默認為真： hive.optimize.pruner=true
3,Join 操作
? 在使用寫有 Join 操作的查詢語句時有一條原則：應該將條目少的表/子查詢放在 Join 操作符的左邊。原因是在 Join 操作的 Reduce 階段，位于 Join 操作符左邊的表的內容會被加載進內存，將條目少的表放在左邊，可以有效減少發生 OOM 錯誤的幾率。
下面是從網上看到的，做一下備注，源地址 http://www.cnblogs.com/end/archive/2013/01/15/2861448.html
長期觀察hadoop處理數據的過程，有幾個顯著的特征:
1.不怕數據多，就怕數據傾斜。
2．對jobs數比較多的作業運行效率相對比較低，比如即使有幾百行的表，如果多次關聯多次匯總，產生十幾個jobs，沒半小時是跑不完的。map reduce作業初始化的時間是比較長的。
3.對sum，count來說，不存在數據傾斜問題。
4.對count(distinct ),效率較低，數據量一多，準出問題，如果是多count(distinct )效率更低。
?
優化可以從幾個方面著手：
1. 好的模型設計事半功倍。
2. 解決數據傾斜問題。
3. 減少job數。
4. 設置合理的map reduce的task數，能有效提升性能。(比如，10w+級別的計算，用160個reduce，那是相當的浪費，1個足夠)。
5. 自己動手寫sql解決數據傾斜問題是個不錯的選擇。set hive.groupby.skewindata=true;這是通用的算法優化，但算法優化總是漠視業務，習慣性提供通用的解決方法。 Etl開發人員更了解業務，更了解數據，所以通過業務邏輯解決傾斜的方法往往更精確，更有效。
6. 對count(distinct)采取漠視的方法，尤其數據大的時候很容易產生傾斜問題，不抱僥幸心理。自己動手，豐衣足食。
7. 對小文件進行合并，是行至有效的提高調度效率的方法，假如我們的作業設置合理的文件數，對云梯的整體調度效率也會產生積極的影響。
8. 優化時把握整體，單個作業最優不如整體最優。
?
問題1 ：如日志中，常會有信息丟失的問題，比如全網日志中的user_id ，如果取其中的user_id 和bmw_users 關聯，就會碰到數據傾斜的問題。
方法：解決數據傾斜問題
解決方法1.
User_id為空的不參與關聯，例如：
1
Select
*
2
From
log
a
3
Join
4
bmw_users b
5
On
a.
user_id
is
not
null
And
a.
user_id
=
b.
user_id
6
Union
all
7
Select
*
8
from
log
a
9
where
a.
user_id
is
null
;
解決方法2 ：
1
Select
*
2
from
log
a
3
left
outer
join
bmw_users b
4
on
case
when
a.
user_id
is
null
then
concat(‘dp_hive’,
rand
() )
5
else
a.
user_id
end
=
b.
user_id
;
?
總結： 2比1效率更好，不但io少了，而且作業數也少了。1方法log讀取兩次，jobs是2。2方法job數是1 。 這個優化適合無效 id( 比如-99,’’,null 等) 產生的傾斜問題。 把空值的key變成一個字符串加上隨機數，就能把傾斜的數據分到不同的reduce上,解決數據傾斜問題。因為空值不參與關聯，即使分到不同的reduce上，也不影響最終的結果。附上hadoop通用關聯的實現方法（關聯通過二次排序實現的，關聯的列為parition key,關聯的列c1和表的tag組成排序的group key,根據parition key分配reduce。同一reduce內根據group key排序）。
問題2 ：不同數據類型id 的關聯會產生數據傾斜問題。
一張表s8的日志，每個商品一條記錄，要和商品表關聯。但關聯卻碰到傾斜的問題。s8的日志中有字符串商品id,也有數字的商品id,類型是string的，但商品中的數字id是bigint的。猜測問題的原因是把s8的商品id轉成數字id做hash來分配reduce，所以字符串id的s8日志，都到一個reduce上了，解決的方法驗證了這個猜測。
方法：把數字類型轉換成字符串類型
1
select *
from
(
Select
*
2
from
s8_log a
3
Left
outer
join
4
r_auction_auctions b
5
On
a.auction_id
=
cast
(b.auction_id
as
string);
6 ) a;
問題3 ：利用hive 對UNION ALL 的優化的特性
hive 對union all 優化只局限于非嵌套查詢。
比如以下的例子：
1
select
*
2
from
3
(
select
*
from
t1
4
Group
by
c1,c2,c3
5
Union
all
6
Select
*
from
t2
7
Group
by
c1,c2,c3
8
) t3
9
Group
by
c1,c2,c3;
10
從業務邏輯上說，子查詢內的group by 怎么都看顯得多余（功能上的多余,除非有count(distinct)），如果不是因為hive bug或者性能上的考量(曾經出現如果不子查詢group by ，數據得不到正確的結果的hive bug)。所以這個hive按經驗轉換成
1
select
*
from
2
(
select
*
from
t1
3
Union
all
4
Select
*
from
t2
5
) t3
6
Group
by
c1,c2,c3;
經過測試，并未出現union all的hive bug,數據是一致的。mr的作業數有3減少到1。
t1相當于一個目錄，t2相當于一個目錄，那么對map reduce程序來說，t1,t2可以做為map reduce 作業的mutli inputs。那么，這可以通過一個map reduce 來解決這個問題。Hadoop的計算框架，不怕數據多，就怕作業數多。
但如果換成是其他計算平臺如oracle，那就不一定了，因為把大的輸入拆成兩個輸入，分別排序匯總后merge(假如兩個子排序是并行的話)，是有可能性能更優的（比如希爾排序比冒泡排序的性能更優）。
問題4 ：比如推廣效果表要和商品表關聯，效果表中的auction id 列既有商品id, 也有數字id, 和商品表關聯得到商品的信息 。那么以下的hive sql性能會比較好
1
Select
*
from
effect a
2
Join
3
(
select
auction_id
4
from
auctions
5
union
all
6
Select
auction_string_id
as
auction_id
7
from
auctions
8
) b
9
On
a.auction_id
=
b.auction_id;
比分別過濾數字id,字符串id然后分別和商品表關聯性能要好。
這樣寫的好處,1個MR作業,商品表只讀取一次，推廣效果表只讀取一次。把這個sql換成MR代碼的話，map的時候，把a表的記錄打上標簽a,商品表記錄每讀取一條，打上標簽b，變成兩個<key ,value>對，<b,數字id>，<b,字符串id>。所以商品表的hdfs讀只會是一次。
問題5 ：先join 生成臨時表，在union all 還是寫嵌套查詢，這是個問題 。比如以下例子：
1
Select
*
2
From
3
(
select
*
4
From
t1
5
Uion
all
6
select
*
7
From
t4
8
Union
all
9
Select
*
10
From
t2
11
Join
t3
12
On
t2.id
=
t3.id
13
) x
14
Group
by
c1,c2;
這個會有4個jobs。假如先join生成臨時表的話t5,然后union all，會變成2個jobs。
1
Insert
overwrite
table
t5
2
Select
*
3
From
t2
4
Join
t3
5
On
t2.id
=
t3.id
6
;
7
Select
*
from
(t1
union
all
t4
union
all
t5) ;
hive 在union all 優化上可以做得更智能（把子查詢當做臨時表），這樣可以減少開發人員的負擔。出現這個問題的原因應該是union all 目前的優化只局限于非嵌套查詢。如果寫MR 程序這一點也不是問題，就是multi inputs 。
問題6 ：使用map join 解決數據傾斜的常景下小表關聯大表的問題，但如果小表很大，怎么解決 。這個使用的頻率非常高，但如果小表很大，大到map join會出現bug或異常，這時就需要特別的處理。云瑞和玉璣提供了非常給力的解決方案。以下例子：
1
Select
*
from
log
a
2
Left
outer
join
members b
3
On
a.memberid
=
b.memberid;
Members有600w+的記錄，把members分發到所有的map上也是個不小的開銷，而且map join不支持這么大的小表。如果用普通的join，又會碰到數據傾斜的問題。
解決方法：
1
Select
/*
+mapjoin(x)
*/
*
2
from
log
a
3
Left
outer
join
4
(
select
/*
+mapjoin(c)
*/
d.
*
5
From
6
(
select
distinct
memberid
7
from
log
8
) c
9
Join
10
members d
11
On
c.memberid
=
d.memberid
12
) x
13
On
a.memberid
=
b.memberid;
先根據log取所有的memberid，然后mapjoin 關聯members取今天有日志的members的信息，然后在和log做mapjoin。
假如，log里memberid有上百萬個，這就又回到原來map join問題。所幸，每日的會員uv不會太多，有交易的會員不會太多，有點擊的會員不會太多，有傭金的會員不會太多等等。所以這個方法能解決很多場景下的數據傾斜問題。
問題7 ：HIVE 下通用的數據傾斜解決方法,double 被關聯的相對較小的表，這個方法在mr 的程序里常用。 還是剛才的那個問題：
1
Select
*
2
from
log
a
3
Left
outer
join
4
(
select
/*
+mapjoin(e)
*/
memberid,
number
5
From
members d
6
Join
num e
7
) b
8
On
a.memberid
=
b.memberid
And
mod(a.pvtime,
30
)
+
1
=
b.
number
;
Num表只有一列number，有30行，是1,30的自然數序列。就是把member表膨脹成30份，然后把log數據根據memberid和pvtime分到不同的reduce里去，這樣可以保證每個reduce分配到的數據可以相對均勻。就目前測試來看，使用mapjoin的方案性能稍好。后面的方案適合在map join無法解決問題的情況下。
?
長遠設想，把如下的優化方案做成通用的hive 優化方法
1. 采樣log 表，哪些memberid 比較傾斜，得到一個結果表tmp1 。由于對計算框架來說，所有的數據過來，他都是不知道數據分布情況的，所以采樣是并不可少的。Stage1
2. 數據的分布符合社會學統計規則，貧富不均。傾斜的key 不會太多，就像一個社會的富人不多，奇特的人不多一樣。所以tmp1 記錄數會很少。把tmp1 和members 做map join 生成tmp2, 把tmp2 讀到distribute file cache 。這是一個map 過程。Stage2
3.??? map 讀入members 和log ，假如記錄來自log, 則檢查memberid 是否在tmp2 里，如果是，輸出到本地文件a, 否則生成<memberid,value> 的key,value 對，假如記錄來自member, 生成<memberid,value> 的key,value 對，進入reduce 階段。Stage3.
4. 最終把a 文件，把Stage3 reduce 階段輸出的文件合并起寫到hdfs 。
這個方法在hadoop 里應該是能實現的。Stage2 是一個map 過程，可以和stage3 的map 過程可以合并成一個map 過程。
這個方案目標就是：傾斜的數據用mapjoin, 不傾斜的數據用普通的join ，最終合并得到完整的結果。用hive sql 寫的話，sql 會變得很多段，而且log 表會有多次讀。傾斜的key 始終是很少的，這個在絕大部分的業務背景下適用。那是否可以作為hive 針對數據傾斜join 時候的通用算法呢？
?
問題8 ：多粒度( 平級的)uv 的計算優化 ，比如要計算店鋪的uv。還有要計算頁面的uv,pvip.
方案1:
1
Select
shopid,
count
(
distinct
uid)
2
From
log
3
group
by
shopid;
4
Select
pageid,
count
(
distinct
uid)
5
From
log
6
group
by
pageid;
由于存在數據傾斜問題，這個結果的運行時間是非常長的。
方案二：
1
From
log
2
Insert
overwrite
table
t1 (type
=
’
1
’)
3
Select
shopid
4
Group
by
shopid ,acookie
5
Insert
overwrite
table
t1 (type
=
’
2
’)
6
Group
by
pageid,acookie;
1
--
店鋪uv:
2
3
Select
shopid,
sum
(
1
)
4
From
t1
5
Where
type
=
’
1
’
6
Group
by
shopid ;
7
8
--
頁面uv:
9
10
Select
pageid,
sum
(
1
)
11
From
t1
12
Where
type
=
’
1
’
13
Group
by
pageid ;
這里使用了multi insert 的方法，有效減少了hdfs 讀，但multi insert 會增加hdfs 寫，多一次額外的map 階段的hdfs 寫。使用這個方法，可以順利的產出結果。
方案三：
1
Insert
into
t1
2
Select
type,type_name,’’
as
uid
3
From
4
(
Select
‘page’
as
type,Pageid
as
type_name,Uid
5
From
log
6
Union
all
7
Select
‘shop’
as
type,Shopid
as
type_name,Uid
8
From
log
9
) y
10
Group
by
type,type_name,uid;
11
12
Insert
into
t2
13
Select
type,type_name,
sum
(
1
)
14
From
t1
15
Group
by
type,type_name;
16
From
t2
17
Insert
into
t3
18
Select
type,type_name,uv
19
Where
type
=
’page’
20
Select
type,type_name,uv
21
Where
type
=
’shop’ ;
最終得到兩個結果表t3,頁面uv表，t4,店鋪結果表。從io上來說，log一次讀。但比方案2少次hdfs寫（multi insert有時會增加額外的map階段hdfs寫）。作業數減少1個到3，有reduce的作業數由4減少到2，第三步是一個小表的map過程，分下表，計算資源消耗少。但方案2每個都是大規模的去重匯總計算。
這個優化的主要思路是，map reduce 作業初始化話的時間是比較長，既然起來了，讓他多干點活 ，順便把頁面按uid去重的活也干了，省下log的一次讀和作業的初始化時間，省下網絡shuffle的io，但增加了本地磁盤讀寫。效率提升較多。
這個方案適合平級的不需要逐級向上匯總的多粒度uv 計算，粒度越多，節省資源越多，比較通用。
?
問題9 ：多粒度，逐層向上匯總的uv 結算。 比如4個維度，a,b,c,d，分別計算a,b,c,d,uv；
a,b,c,uv;a,b,uv;a;uv,total uv4個結果表。這可以用問題8的方案二，這里由于uv場景的特殊性，多粒度，逐層向上匯總，就可以使用一次排序，所有uv計算受益的計算方法。
案例： 目前mm_log日志一天有25億+的pv數，要從mm日志中計算uv，與ipuv,一共計算
三個粒度的結果表
（memberid,siteid,adzoneid,province,uv,ipuv）? R_TABLE_4
（memberid,siteid,adzoneid,uv,ipuv） R_TABLE_3
(memberid,siteid,uv,ipuv) R_TABLE_2
第一步：按memberid,siteid,adzoneid,province, 使用group 去重 ,產生臨時表，對cookie,ip
打上標簽放一起，一起去重，臨時表叫T_4;
1
Select
memberid,siteid,adzoneid,province,type,
user
2
From
3
(
Select
memberid,siteid,adzoneid,province,‘a’ type ,cookie
as
user
4
from
mm_log
5
where
ds
=
20101205
6
Union
all
7
Select
memberid,siteid,adzoneid,province,‘i’ type ,ip
as
user
8
from
mm_log
9
where
ds
=
20101205
10
) x
11
group
by
memberid,siteid,adzoneid,province,type,
user
;
?
第二步：排名 ,產生表T_4_NUM.Hadoop最強大和核心能力就是parition 和 sort.按type，acookie分組，
Type，acookie，memberid,siteid,adzoneid,province排名。
1
Select
*
,row_number(type,
user
,memberid,siteid,adzoneid )
as
adzone_num , row_number(type,
user
,memberid,siteid )
as
site_num
2
,row_number(type,
user
,memberid )
as
member_num
3
,row_number(type,
user
)
as
total_num
4
from
5
(
select
*
6
from
T_4
7
distribute
by
type,
user
sort
by
type,
user
, memberid,siteid,adzoneid
8
) x;
?
這樣就可以得到不同層次粒度上user的排名，相同的user id在不同的粒度層次上，排名等于1的記錄只有1條。取排名等于1的做sum，效果相當于Group by user去重后做sum操作。
第三步：不同粒度uv統計，先從最細粒度的開始統計，產生結果表R_TABLE_4,這時，結果集只有10w的級別。
如統計memberid,siteid,adzoneid,provinceid粒度的uv使用的方法就是
Select memberid,siteid,adzoneid, provinceid,
sum(case when? type =’a’ then cast(1) as bigint end ) as province_uv ,
sum(case when? type =’i’ then cast(1) as bigint end ) as province_ip ,
sum(case when adzone_num =1 and type =’a’ then cast(1) as bigint end ) as adzone_uv ,
sum(case when adzone_num =1 and type =’i’ then cast(1) as bigint end ) as adzone_ip ,
sum(case when site_num =1 and type =’a’ then cast(1) as bigint end ) as site_uv ,
sum(case when site_num =1 and type =’i’ then cast(1) as bigint end ) as site_ip ,
sum(case when member_num =1 and type =’a’ then cast(1) as bigint end ) as member_uv ,
sum(case when member_num =1 and type =’i’ then cast(1) as bigint end ) as member_ip ,
sum(case when total_num =1 and type =’a’ then cast(1) as bigint end ) as total_uv ,
sum(case when total_num =1 and type =’i’ then cast(1) as bigint end ) as total_ip ,
from T_4_NUM
group by memberid,siteid,adzoneid, provinceid ;
廣告位粒度的uv的話，從R_TABLE_4統計，這是源表做10w級別的統計
Select memberid,siteid,adzoneid,sum(adzone_uv),sum(adzone_ip)
From R_TABLE_4
Group by memberid,siteid,adzoneid；
memberid,siteid的uv計算 ，
memberid的uv計算,
total uv 的計算也都從R_TABLE_4匯總。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
