0x00 什么是hive
hive是構(gòu)建在hadoop之上的數(shù)據(jù)倉庫組件，是目前大數(shù)據(jù)領域最常用的數(shù)據(jù)倉庫開源實現(xiàn)框架，hive定義了一種類sql語言，稱為hql，hive能將hql轉(zhuǎn)換成mapreduce，提交到hadoop執(zhí)行，當然如果執(zhí)行引擎設置為其他，例如spark，會將hql轉(zhuǎn)換成spark job執(zhí)行
0x01 hive的執(zhí)行流程
參考資料：
hive官方文檔design
如下圖，hive的結(jié)構(gòu)以及執(zhí)行過程
[圖片上傳失敗...(image-6c2390-1574612092305)]
UI user interface
用戶接口，與用戶的交互接口，包括jdbc、odbc、cli、web interface，其中jdbc和odbc通過Thrift Server連接driverdriver、compiler
接到用戶提交的sql后，driver發(fā)送給compiler翻譯成執(zhí)行計劃，compiler先查詢元數(shù)據(jù)，確定sql語句沒有問題然后生成執(zhí)行計劃并優(yōu)化，返回給driver，然后發(fā)送給execution engineexecution engine
engine接到查詢計劃后，編譯成hadoop任務，發(fā)送給hadoop執(zhí)行，然后返回結(jié)果，如果是ddl語句，還會聯(lián)系metastore，另外hive的后續(xù)版本也加入了tez和spark執(zhí)行引擎，配置hive.execution.engine參數(shù)可以修改，默認是mr連接方式
通常在bin目錄下的hive腳本，執(zhí)行就可以直接訪問hive，也就是我們常用的hive cli，另外一種方式是hive0.11之后的新的cli連接方式beeline，需要啟動hiveserver2（默認端口10000），beeline是一種類似于jdbc的連接方式，beeline相對于hive cli有以下優(yōu)勢
1、hive cli需要將訪問元數(shù)據(jù)的用戶名和密碼信息配置在hive-site.xml中
2、hive cli只支持單用戶訪問
具體客戶端的區(qū)別見0x02
0x02 hive客戶端
運行時會啟動一個runJar進程，在本地編譯hql，然后提交到hadoop執(zhí)行
單用戶操作
安全性較低
hive0.11之后推出的新客戶端，hive推薦使用
基于SQLLine的jdbc客戶端
需要啟動hiveserver2（jdbc方式訪問也需要）
更純粹的客戶端，hql提交到hiveserver2編譯優(yōu)化執(zhí)行
有更豐富的權(quán)限控制
0x03 hive權(quán)限
HIVE LanguageManual Authorization
hive一般不需要做權(quán)限控制，因為大部分訪問都是內(nèi)網(wǎng)的訪問，但是hive也提供了幾種權(quán)限控制用例
首先從hive的兩個作用來考慮權(quán)限
1、作為數(shù)據(jù)存儲層
這種權(quán)限控制一般基于hdfs文件系統(tǒng)，只能做到表級別或表分區(qū)級別的讀寫控制
2、作為sql引擎
這個又分兩種
hive cli客戶端，這種和第一種類似，只能做到基于文件系統(tǒng)的權(quán)限控制
beeline客戶端，這種是基于hiveserver2的，能做到sql標準的權(quán)限控制，類似于mysql的權(quán)限控制
0x04 hive排序的幾種方式
- order by
order by和關系型數(shù)據(jù)庫的排序類似，不同的地方是，當hive開啟strict模式時，order by必須帶上limit，因為order by是在最后將所有數(shù)據(jù)到一個reduce中進行排序，執(zhí)行時間會很長
- sort by
sort by也是排序，但是sort by是將每個reduce中的數(shù)據(jù)排序，如果不止一個reduce，最終結(jié)果可能是部分有序
- distribute by
distribute by不是用來排序，主要是將key分區(qū)到不同的reduce，能保證相同的key在一個reducce處理，但是不保證同一個reduce中的key有序
- cluster by
cluster by相當于distribute by + sort by，除了將key分區(qū)以外還保證key在reduce上有序
0x05 hive strict模式
hive嚴格模式，通過hive.maper.mode=strict(nostrict)設置開啟或關閉，這個模式主要是為了防止用戶做一些非常耗時的操作而影響整個集群，例如：
1、分區(qū)表查詢不對分區(qū)做限制
2、order by不帶limit
。。。
這些操作在嚴格模式下都會報錯
0x06 內(nèi)部表和外部表
內(nèi)部表和外部表是hive的兩種表，主要區(qū)別是hive是否管理表的數(shù)據(jù)
- 內(nèi)部表（managed table）
默認建表都是內(nèi)部表
數(shù)據(jù)由hive管理，存儲在hive.metastore.warehouse.dir配置的hdfs目錄
drop表會將數(shù)據(jù)也刪除
適用于hive自己管理生命周期的表或者臨時表
- 外部表（external）
建表時要加上 create external table
drop表時不會刪除數(shù)據(jù)文件（如果設置了external.table.purge=true也會刪除數(shù)據(jù)）
適用于數(shù)據(jù)已經(jīng)存在的情況（并且數(shù)據(jù)較大）
0x07 分桶表
LanguageManual DDL BucketedTables
分桶表提供了比分區(qū)更細粒度的數(shù)據(jù)劃分方式，能將字段按hash取模劃分成指定份數(shù)
- 創(chuàng)建方法
CREATE TABLE user_info_bucketed(user_id BIGINT, firstname STRING, lastname STRING)
COMMENT 'A bucketed copy of user_info'
PARTITIONED BY(ds STRING)
CLUSTERED BY(user_id) INTO 256 BUCKETS;
- 插入數(shù)據(jù)
set hive.enforce.bucketing = true; -- (Note: Not needed in Hive 2.x onward)
FROM user_id
INSERT OVERWRITE TABLE user_info_bucketed
PARTITION (ds='2009-02-25')
SELECT userid, firstname, lastname WHERE ds='2009-02-25';
hive0.x和1.x時，在插入數(shù)據(jù)前需要執(zhí)行set hive.enforce.bucketing = true，這個參數(shù)會自動根據(jù)插入表的分桶數(shù)設置reduce個數(shù)，如果不設置這個參數(shù)需要設置好reduce（set mapred.reduce.tasks=?）個數(shù)，并且使用cluster by查詢語句
- 注意事項
分桶規(guī)則是按照hash函數(shù)來的，具體的hash函數(shù)是根據(jù)桶的個數(shù)來的，但是對于數(shù)據(jù)和字符串或其他數(shù)據(jù)類型使用的hash函數(shù)不同，最后得到的桶也不同，所以對于插入和查詢，確保數(shù)據(jù)類型不變，才能正確的查到數(shù)據(jù)
0x08 開窗函數(shù)
Windowing and Analytics Functions
開窗函數(shù)是為了解決聚合運算每組只返回一個值的問題，開窗函數(shù)可以為聚合運算每組返回多個值，oracle中也叫分析函數(shù)
window_func() over(partition by [col1,col2...] [order by [col1,col2...]] windowing_clause)
window_func() 窗口函數(shù)
包括所有聚合函數(shù)，還有一些常用的分析函數(shù)，例如rank()、row_number()、lead()等partition by 開窗的列
可以指定多個列，和group by的分組類似order by 排序字段
用來指定窗口中數(shù)據(jù)順序的列windowing_clause 開窗范圍
限定開窗的范圍，以當前行為參照，在前幾行或后幾行的范圍上計算聚合值
只有窗口函數(shù)是聚合函數(shù)時才能使用，例如count、sum、max等，但是rank、row_number不能使用，并且一定要有order by
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
- 常見用法
數(shù)據(jù)
| name | score |
|---|---|
| a | 2 |
| a | 3 |
| a | 4 |
| a | 7 |
| a | 9 |
1、如果窗口函數(shù)是聚合函數(shù)，并且windowing_clause省略時，并且有order by時，窗口函數(shù)計算范圍是第一行到當前行
-- sql1
select name,score,sum(score) over(partition by name order by score) as x from t
-- sql2
select name,score,sum(score) over(partition by name) as x from t
結(jié)果1: 窗口內(nèi)從第一行截止到當前行的和
| name | score | x |
|---|---|---|
| a | 2 | 2 |
| a | 3 | 5 |
| a | 4 | 9 |
| a | 7 | 16 |
| a | 9 | 25 |
結(jié)果2: 窗口內(nèi)所有數(shù)據(jù)和
| name | score | x |
|---|---|---|
| a | 2 | 25 |
| a | 3 | 25 |
| a | 4 | 25 |
| a | 7 | 25 |
| a | 9 | 25 |
2、rank、row_number都是排名函數(shù)，區(qū)別是row_number沒有相同的排名，rank有并列排名
3、lead和lag用法
lag(expr，Bigint offset, default) 取當前行之前的offset行數(shù)據(jù)，沒有就是default值
lead(expr，Bigint offset, default) 取當前行之后的offset行數(shù)據(jù)，沒有就是default值
4、windowing_clause用法
-- sql1
select name,score,sum(score) over(partition by name order by score rows BETWEEN 1 PRECEDING and 2 PRECEDING) as x from t
-- sql2
select name,score,sum(score) over(partition by name order by score rows BETWEEN 1 PRECEDING and 2 PRECEDING) as x from t
結(jié)果1：從當前行向上第一行到第二行求和
| name | score | x |
|---|---|---|
| a | 2 | \N |
| a | 3 | 2 |
| a | 4 | 5 |
| a | 7 | 7 |
| a | 9 | 11 |
結(jié)果2：從當前行向下第一行到第二行求和
| name | score | x |
|---|---|---|
| a | 2 | 7 |
| a | 3 | 11 |
| a | 4 | 16 |
| a | 7 | 9 |
| a | 9 | \N |
利用開窗函數(shù)的特性可以巧妙的解決很多問題，例如：查詢連續(xù)登陸N天的用戶
0x09 調(diào)優(yōu)
Cost-based optimization in Hive
所有優(yōu)化問題都可以從上到下優(yōu)化，優(yōu)化效果也是從上到下依次遞減，對于hive來說，掌握hive的執(zhí)行原理，根據(jù)具體的業(yè)務，選擇更好的存儲表和查詢方式往往可能得到更好的優(yōu)化結(jié)果。所以遇到優(yōu)化問題，可以從以下幾個方面考慮
1、數(shù)據(jù)組織方式是否可以優(yōu)化，例如只是查詢某一塊的數(shù)據(jù)，是否可以將表建為分區(qū)表或分桶表
2、數(shù)據(jù)是否存在傾斜，hive底層的mapreduce是分布式并行處理框架，所以不怕數(shù)據(jù)多，而怕數(shù)據(jù)傾斜
3、如果數(shù)據(jù)沒有傾斜，還有可能是資源不足造成的，可以優(yōu)化sql，嘗試減少job數(shù)
4、調(diào)整hive參數(shù)，增加資源
0x10 數(shù)據(jù)傾斜
hive中數(shù)據(jù)傾斜一般是因為輸入到reduce端的數(shù)據(jù)不均衡導致的，部分reduce處理的數(shù)據(jù)過多，一直未完成，具體表現(xiàn)就是進度一直卡在99%，也叫做長尾問題
在sql中通常join、group by、count distinct操作的時候可能會發(fā)生長尾問題
1、join
空值較多或者某個key數(shù)據(jù)量太大，可以從幾個方面考慮，如果是大小表join，可以使用mapjoin先將小表加載到內(nèi)存，然后在join，如果兩張表都比較大，就要盡量將大表去重，減少join的數(shù)據(jù)量，如果大表數(shù)據(jù)還是很大，就要從業(yè)務上考慮能否優(yōu)化處理方式
-- mapjoin
select /*+mapjoin(smallT)*/ from bigT left outer join smallT on bigT.key = smallT.key
2、group by
某個key值數(shù)據(jù)量太大，可以將key單獨提出來處理，或者使用隨機數(shù)將這個key拆分處理，最后在進行加總
-- 原sql
SELECT Key,COUNT(*) AS Cnt FROM TableName GROUP BY Key;
-- 假設長尾的Key已經(jīng)找到是KEY001
SELECT a.Key
, SUM(a.Cnt) AS Cnt
FROM (
SELECT Key
, COUNT(*) AS Cnt
FROM TableName
GROUP BY Key,
CASE
WHEN Key = 'KEY001' THEN Hash(Random()) % 50
ELSE 0
END
) a
GROUP BY a.Key;
3、count distinct key最終會將所有不同的key匯聚到最后一個reduce統(tǒng)計，如果key值太多，可能需要處理很長時間，我們可以先使用group by key，然后為每個組附上一個隨機數(shù)字段，然后針對隨機數(shù)進行group by，計算sum，最后在外層對每組隨機數(shù)的sum進行加總
select sum(t1.cnt) as res from (
select tag,count(1) as cnt from (
select key,rand()*100 as tag from t group by key
) t1 group by tag
) t2
-- 也可以先group by最后在count的方式
SELECT SUM(PV) AS Pv
, COUNT(*) AS UV
FROM (
SELECT COUNT(*) AS Pv
, uid
FROM UserLog
GROUP BY uid
) a;
需要注意的是，隨機數(shù)的解決方案都要借助子查詢的方式，如果長尾不是特別嚴重，這種處理方式不一定更好
0x11 hive參數(shù)
關于優(yōu)化的一些常用參數(shù)，并不完全
-- 設置reduce數(shù)
set mapred.reduce.tasks = 256
-- 開啟join傾斜優(yōu)化
set hive.optimize.skewjoin = true
-- 基于輸入文件大小，將join優(yōu)化為mapjoin
set hive.auto.convert.join = true
-- 小表閾值（byte），如果小表大小小于這個值，hive會使用mapjoin
set hive.mapjoin.smalltable.filesize=25000000
-- hive是否開啟對group by的傾斜優(yōu)化
set hive.groupby.skewindata=true
0x12 join優(yōu)化之Predicate Pushdown
謂詞下推，指將數(shù)據(jù)的篩選條件盡量下推到底層，避免過多的數(shù)據(jù)傳輸，hive參數(shù)為set hive.optimize.ppd=true，默認是開啟的，先來看兩個sql
select t1.*,t2.* from
(select * from a where pt = '20191120) t1
left outer join
(select * from b where pt = '20191120) t2
on t1.key = t2.key
select a.*,b.*
from a left outer join b
on a.key = b.key and b.pt = '20191120'
where a.pt = '20191120
outer join的執(zhí)行順序
1、將join的表做笛卡爾積
2、篩選on條件
3、補全主表，left補左表，right補右表
4、篩選where條件
對于第二個sql，a表的分區(qū)裁剪就會下推到join前執(zhí)行
因為執(zhí)行順序的關系，對于b表的分區(qū)裁剪不能放在where中，因為對于a存在，b補null的數(shù)據(jù)最終會過濾掉，導致結(jié)果和第一個sql不一致
所以對于outer join，分區(qū)的篩選條件放在on和where上是有很大差別的，特別是full outer join，使用的時候要非常注意，為了避免不必要的錯誤，一般對join的表使用子查詢，將分區(qū)篩選直接放在子查詢中，然后join，這是最簡便的方式。
詳情可參考：阿里云文檔join on條件
