Hive 高頻考點講解

1 Hive

Hive 是 FaceBook 開源的一款基於 Hadoop 數據倉庫工具，它可以將結構化的數據文件映射爲一張表，並提供類 SQL 查詢功能。

The Apache Hive ™ data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL 。

1.1 Hive 優缺點

1.1.1 優點

  1. 操作接口採用類 SQL 語法，提供快速開發的能力（簡單、容易上手）。

  2. 避免了去寫 MapReduce，減少開發人員的學習成本。

  3. Hive 的執行延遲比較高，因此 Hive 常用於數據分析，對實時性要求不高的場合。

  4. Hive 優勢在於處理大數據，對於處理小數據沒有優勢，因爲 Hive 的執行延遲比較高。

  5. Hive 支持用戶自定義函數，用戶可以根據自己的需求來實現自己的函數。

1.1.2 缺點

  1. Hive 的 HQL 表達能力有限，無法表達迭代式算法，不擅長數據挖掘方面。

  2. Hive 的效率比較低，Hive 自動生成的 MapReduce 作業，通常情況下不夠智能化。

  3. Hive 查詢無法做到跟 MySQL 一樣毫秒返回。

1.2 Hive 跟 MySQL 比較

1.2.1 對比

Hive 採用了類 SQL 的查詢語言 HQL(Hive Query Language)，因此很容易將 Hive 理解爲數據庫。其實從結構上來看，Hive 和數據庫除了擁有類似的查詢語言，再無類似之處。本文將從多個方面來闡述 Hive 和數據庫的差異。數據庫可以用在 Online 的應用中，但是 Hive 是爲數據倉庫而設計的，清楚這一點，有助於從應用角度理解 Hive 的特性。

j22rSP

1.2.2 Hive 不支持那些

  1. 支持等值查詢，不支持非等值連接

  2. 支持 and 多條件過濾，不支持 or 多條件過濾。

  3. 不支持 update 跟 delete。

1.3 Hive 底層

Hive 底層是 MapReduce 計算框架，Hive 只是將通讀性強且容易編程的 SQL 語句通過 Hive 軟件轉換成 MapReduce 程序在集羣上執行，Hive 可以看做 MapReduce 客戶端。操作的數據還是存儲在 HDFS 上的，而用戶定義的表結構等元信息被存儲到 MySQL 上了。以前要寫八股文 MapReduce 程序，現在只需要 HQL 查詢就可！

Hive 整體框架

  1. 用戶接口 Client

CLI（hive shell）、JDBC/ODBC(java 訪問 hive)、WEBUI（瀏覽器訪問 hive）

  1. 元數據 Metastore

  2. 元數據包括 表名、表所屬的數據庫（默認是 default）、表的擁有者、列 / 分區字段、表的類型（是否是外部表）、表的數據所在目錄等。

  3. 默認存儲在自帶的 derby 數據庫中 (單客戶連接)，推薦使用 MySQL 存儲 Metastore。

  4. Hadoop
    使用 HDFS 進行存儲，使用 MapReduce 進行計算。

  1. 驅動器 Driver

  1. 解析器 SQL Parser：將 SQL 字符串轉換成抽象語法樹 AST，這一步一般都用第三方工具庫完成，比如 antlr；對 AST 進行語法分析，比如表是否存在、字段是否存在、SQL 語義是否有誤。

  2. 編譯器 Physical Plan：將 AST 編譯生成邏輯執行計劃。

  3. 優化器 Query Optimizer：對邏輯執行計劃進行優化。

  4. 執行器 Execution：把邏輯執行計劃轉換成可以運行的物理計劃。對於 Hive 來說就是 MR/Spark。

HQL 執行流程

不要把 Hive 想的多麼神祕，你可以用簡單的 load 方式將數據加載到創建的表裏，也可以直接用 hadoop 指令將數據放入到指定目錄，這兩種方式都可以直接讓你通過 SQL 查詢到數據。

1.4  HQL 底層執行舉例

1.4.1 Join

Join 流程

1.4.2 group by

group by 流程

1.4.3 distinct

distinct 流程

2 開窗函數

有時想要同時顯示聚集前後的數據，這時引入了窗口函數，在 SQL 處理中，窗口函數都是最後一步執行，而且僅位於 Order by 字句之前。

2.1  數據準備

name,orderdate,cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

建表 導數據

create table business(
name string, 
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/datas/business.txt" into table business;

2.2 用法

相關函數說明

  1. OVER()：指定分析函數工作的數據窗口大小，這個數據窗口大小可能會隨着行的變而變化

  2. CURRENT ROW：當前行

  3. n PRECEDING：往前 n 行數據

  4. n FOLLOWING：往後 n 行數據

  5. UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING 表示到後面的終點

上面寫 over 裏面，下面寫 over 前面。

  1. LAG(col,n)：往前第 n 行數據

  2. LEAD(col,n)：往後第 n 行數據

  3. NTILE(n)：把有序分區中的行分發到指定數據的組中，各個組有編號，編號從 1 開始，對於每一行，NTILE 返回此行所屬的組的編號。注意：n 必須爲 int 類型。

2.3 開窗函數 demo

  1. 查詢在 2017 年 4 月份購買過的顧客及總人數
select name,count(*) over() from business where substring(orderdate,1,7) = '2017-04' group by name;
結果：
mart    2
jack    2
  1. 查詢顧客的購買明細及月購買總額
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
from business; 
解釋：按照月劃分數據  然後統計這個月的cost總和
jack    2017-01-01  10  205
jack    2017-01-08  55  205
tony    2017-01-07  50  205
jack    2017-01-05  46  205
tony    2017-01-04  29  205
tony    2017-01-02  15  205
jack    2017-02-03  23  23
mart    2017-04-13  94  341
jack    2017-04-06  42  341
mart    2017-04-11  75  341
mart    2017-04-09  68  341
mart    2017-04-08  62  341
neil    2017-05-10  12  12
neil    2017-06-12  80  80
  1. 查看顧客上次的購買時間
select name,orderdate,cost, 
lag(orderdate,1,'defaulttime') over(partition by name order by orderdate ) as time1, 
lag(orderdate,2,'defaulttime') over (partition by name order by orderdate) as time2  from business;
結果 : 
姓名      日期      價格  前一天日期    前兩天日期
jack    2017-01-01  10  defaulttime defaulttime
jack    2017-01-05  46  2017-01-01  defaulttime
jack    2017-01-08  55  2017-01-05  2017-01-01
jack    2017-02-03  23  2017-01-08  2017-01-05
jack    2017-04-06  42  2017-02-03  2017-01-08
mart    2017-04-08  62  defaulttime defaulttime
mart    2017-04-09  68  2017-04-08  defaulttime
mart    2017-04-11  75  2017-04-09  2017-04-08
mart    2017-04-13  94  2017-04-11  2017-04-09
neil    2017-05-10  12  defaulttime defaulttime
neil    2017-06-12  80  2017-05-10  defaulttime
tony    2017-01-02  15  defaulttime defaulttime
tony    2017-01-04  29  2017-01-02  defaulttime
tony    2017-01-07  50  2017-01-04  2017-01-02
  1. 查詢前 20% 時間的訂單信息
 select * from (
 select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business ) t where sorted = 1;
結果 : 
jack    2017-01-01  10  1
tony    2017-01-02  15  1
tony    2017-01-04  29  1
jack    2017-01-05  46  2
tony    2017-01-07  50  2
jack    2017-01-08  55  2
jack    2017-02-03  23  3
jack    2017-04-06  42  3
mart    2017-04-08  62  3
mart    2017-04-09  68  4
mart    2017-04-11  75  4
mart    2017-04-13  94  4
neil    2017-05-10  12  5
neil    2017-06-12  80  5

以下實驗均關注最後一列

  1. 所有行相加
select name,orderdate,cost,sum(cost) over() as sample1 from business; 
結果 : 
mart    2017-04-13  94  661
neil    2017-06-12  80  661
mart    2017-04-11  75  661
neil    2017-05-10  12  661
mart    2017-04-09  68  661

2. 按 name 分組，組內數據相加

select name,orderdate,cost,sum(cost) over(partition by name) as sample2
from business;
結果 : 
jack    2017-01-05  46  176
jack    2017-01-08  55  176
jack    2017-01-01  10  176
jack    2017-04-06  42  176
jack    2017-02-03  23  176
...
tony    2017-01-04  29  94
tony    2017-01-02  15  94
tony    2017-01-07  50  94
  1. 按 name 分組，組內數據累加
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate) as sample3
from business; 
跟下面類似
select name,orderdate,cost,
sum(cost) over(distribute by name sort by orderdate) as sample3 
from business; 
jack    2017-01-01  10  10
jack    2017-01-05  46  56
jack    2017-01-08  55  111
jack    2017-02-03  23  134
jack    2017-04-06  42  176
...
  1. 和 sample3 一樣, 由起點到當前行的聚合
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows
between UNBOUNDED PRECEDING and current row ) as sample4 
from business; 
結果 : 
jack    2017-01-01  10  10
jack    2017-01-05  46  56
jack    2017-01-08  55  111
jack    2017-02-03  23  134
jack    2017-04-06  42  176
...
  1. 當前行和前面一行做聚合
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows 
between 1 PRECEDING and current row) as sample5 
from business;
結果 : 
jack    2017-01-01  10  10
jack    2017-01-05  46  56 = 46 + 10
jack    2017-01-08  55  101 = 44 + 46
jack    2017-02-03  23  78  = 23 + 55
jack    2017-04-06  42  65  = 42 + 23
...
tony    2017-01-02  15  15
tony    2017-01-04  29  44
tony    2017-01-07  50  79
  1. 當前行和前邊一行及後面一行
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows
between 1 PRECEDING AND 1 FOLLOWING ) as sample6
from business;
結果 : 
jack    2017-01-01  10  56  = 10 + 46
jack    2017-01-05  46  111 = 46 + 10 + 55
jack    2017-01-08  55  124 = 55 + 46 + 23
jack    2017-02-03  23  120 = 23 + 55 + 42
jack    2017-04-06  42  65  = 42 + 23
...
tony    2017-01-02  15  44
tony    2017-01-04  29  94
tony    2017-01-07  50  79
  1. 當前行及後面所有行
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 from business;
結果 : 
jack    2017-01-01  10  176 = 10 + 46 + 55 + 23 + 42
jack    2017-01-05  46  166 = 46 + 55 + 23 + 42
jack    2017-01-08  55  120 = 55 + 23 + 42
jack    2017-02-03  23  65  =  23 + 42
jack    2017-04-06  42  42  =  42
mart    2017-04-08  62  299
mart    2017-04-09  68  237
mart    2017-04-11  75  169
mart    2017-04-13  94  94
neil    2017-05-10  12  92
neil    2017-06-12  80  80
tony    2017-01-02  15  94
tony    2017-01-04  29  79
tony    2017-01-07  50  50

2.4 Rank

函數說明

rank()：排序相同時會重複，總數不會變
dense_rank()：排序相同時會重複，總數會減少
row_number()：會根據順序計算

select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
結果 : 
name   subject score    rp      drp     rmp
孫悟空   數學    95      1       1       1
宋宋    數學    86      2       2       2
婷婷    數學    85      3       3       3
大海    數學    56      4       4       4
宋宋    英語    84      1       1       1
大海    英語    84      1       1       2
婷婷    英語    78      3（跳過2）2        3
孫悟空  英語    68      4       3(總數少)  4
大海    語文    94      1       1       1
孫悟空  語文    87      2        2        2
婷婷    語文    65      3       3       3
宋宋    語文    64      4       4       4

2.5 行轉列

  1. CONCAT(string A, string B)：

返回輸入字符串連接後的結果，支持任意個輸入字符串;

  1. CONCAT_WS(separator, str1, str2,…)：
    特殊形式的 CONCAT()。第一個參數剩餘參數間的分隔符。分隔符可以是與剩餘參數一樣的字符串。如果分隔符是 NULL，返回值也將爲 NULL。

  2. COLLECT_SET(col)：
    只接受基本數據類型，主要作用是將某字段的值進行去重彙總，產生 array 類型字段。多行彙總成一個 array 類型。

2.6 列轉行

  1. EXPLODE(col)：

將 hive 一列中複雜的 array 或者 map 結構拆分成多行。

  1. LATERAL VIEW
    用法：LATERAL VIEW udtf(expression) table Alias AS columnAlias
    解釋：用於和 split, explode 等 UDTF 一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。

3 Hive 調優

3.1 MapJoin

如果不指定 MapJoin 或者不符合 MapJoin 的條件，那麼 Hive 解析器會將 Join 操作轉換成 Common Join，也就是在 Reduce 階段完成 join。容易發生數據傾斜。可以用 MapJoin 把小表全部加載到內存在 map 端進行 join，避免 reducer 處理。

3.2 行列過濾

列處理：在 SELECT 時只拿需要的列，儘量使用分區過濾，少用 SELECT *。
行處理：在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在 Where 後面，那麼就會先全表關聯，之後再過濾。

3.3 合理設置 Map 數跟 Reduce 數

3.3.1  map 數不是越多越好

如果一個任務有很多小文件（遠遠小於塊大小 128m），則每個小文件也會被當做一個塊，用一個 map 任務來完成，而一個 map 任務啓動和初始化的時間遠遠大於邏輯處理的時間，就會造成很大的資源浪費 。而且，同時可執行的 map 數是受限的。此時我們就應該減少 map 數量。

3.3.2 Reduce 數不是越多越好

  1. 過多的啓動和初始化 Reduce 也會消耗時間和資源；

  2. 有多少個 Reduce，就會有多少個輸出文件，如果生成了很多個小文件，那麼如果這些小文件作爲下一個任務的輸入，則也會出現小文件過多的問題；

  3. Reduce 個數設置考慮這兩個原則：處理大數據量利用合適的 Reduce 數；使單個 Reduce 任務處理數據量大小要合適；

3.4 嚴格模式

嚴格模式 strict 下會有以下特點：

  1. 對於分區表，用戶不允許掃描所有分區。

  2. 使用了 order by 語句的查詢，要求必須使用 limit 語句。

  3. 限制笛卡爾積的查詢。

3.5 開啓 map 端 combiner

在不影響最終業務邏輯前提下，手動開啓 set hive.map.aggr=true；

3.6 壓縮

設置 map 端輸出中間結果壓縮，加速網絡傳輸。

3.7 小文件進行合併

在 Map 執行前合併小文件，減少 Map 數，CombineHiveInputFormat 具有對小文件進行合併的功能（系統默認的格式）。HiveInputFormat 沒有對小文件合併功能。

3.8 其他

  1. Fetch 抓取：指的是 Hive 中對某些情況的查詢可以不必使用 MapReduce 計算。

  2. 本地模式：Hive 可以通過本地模式在單臺機器上處理所有的任務。

  3. 數據分區：數據細化存儲方便訪問。

  4. JVM 重用：JVM 實例在同一個 job 中重新使用 N 次。

  5. 推測執行：根據一定的法則推測出拖後腿的任務，併爲這樣的任務啓動一個備份任務。

  6. 並行執行：一個 Hive 查詢被分解成多個階段，階段之間並非完全互相依賴的。

4 Hive 高頻考點

4.1 數據傾斜

4.1.1 定義

數據分佈不平衡，某些地方特別多，某些地方又特別少，導致的在處理數據的時候，有些很快就處理完了，而有些又遲遲未能處理完，導致整體任務最終遲遲無法完成，這種現象就是數據傾斜。

4.1.2 產生

  1. key 的分佈不均勻或者說某些 key 太集中

  2. 業務數據自身的特性，例如不同數據類型關聯產生數據傾斜

  3. SQL 語句導致的數據傾斜

4.1.3 解決

  1. 不影響最終業務邏輯前提下開啓 map 端 combiner。

  2. 開啓數據傾斜時負載均衡。

  3. 手動抽查做好分區規則。

  4. 使用 mapjoin，小表進內存 在 Map 端完成 Reduce。

4.2 分區表和分桶表對比？

4.2.1 分區表

  1. 分區使用的是表外字段，需要指定字段類型

  2. 分區通過關鍵字 partitioned by(partition_name string) 聲明

  3. 分區劃分粒度較粗

  4. 將數據按區域劃分開，查詢時不用掃描無關的數據，加快查詢速度

4.2.2 分桶表

分桶邏輯：對分桶字段求哈希值，用哈希值與分桶的數量取餘決定數據放到哪個桶裏。

  1. 分桶使用的是表內字段，已經知道字段類型，不需要再指定。

  2. 分桶表通過關鍵字 clustered by(column_name) into … buckets 聲明

  3. 分桶是更細粒度的劃分、管理數據，可以對錶進行先分區再分桶的劃分策略

  4. 優點在於用於數據取樣時候能夠起到優化加速的作用

4.3 動態分區

  1. 靜態分區與動態分區的主要區別在於靜態分區是手動指定，而動態分區是通過數據來進行判斷。

  2. 靜態分區的列是在編譯時期，通過用戶傳遞來決定的，動態分區只有在 SQL 執行時才能決定。

  3. 系統默認開啓，非嚴格模式，動態分區最大值。

4.4 Hive 中視圖跟索引

4.4.1 視圖

視圖是一種使用查詢語句定義的虛擬表，是數據的一種邏輯結構，創建視圖時不會把視圖存儲到磁盤上，定義視圖的查詢語句只有在執行視圖的語句時纔會被執行。視圖是隻讀的，不能向視圖中插入或是加載數據

4.4.2 Hive 索引

Hive 支持在表中建立索引。但是索引需要額外的存儲空間，因此在創建索引時需要考慮索引的必要性。

Hive 不支持直接使用 DROP TABLE 語句刪除索引表。如果創建索引的表被刪除了，則其對應的索引和索引表也會被刪除；如果表的某個分區被刪除了，則該分區對應的分區索引也會被刪除。

4.5 Sort By、Order By、Distrbute By、Cluster By

  1. Sort By：分區內有序

  2. Order By：全局排序，只有一個 Reducer

  3. Distrbute By：類似 MR 中 Partition，進行分區，結合 sort by 使用

  4. Cluster By：當 Distribute by 和 Sorts by 字段相同時，可以使用 Cluster by 方式。Cluster by 還兼具 Sort by 的功能，但只能是升序排序。

4.6  內部表 跟外部表

4.6.1 內部表

如果 Hive 中沒有特別指定，則默認創建的表都是管理表，也稱內部表。由 Hive 負責管理表中的數據，管理表不共享數據。刪除管理表時，會刪除管理表中的數據和元數據信息。

4.6.2 外部表

當一份數據需要被共享時，可以創建一個外部表指向這份數據。刪除該表並不會刪除掉原始數據，刪除的是表的元數據。

4.7 UDF 、UDAF、UDTF

  1. UDF ：一進一出，類似 upper，trim

  2. UDAF：多進一出，聚集函數，類似 count、max、min。

  3. UDTF：一進多出，如 lateral view explore()

4.8 HQL 如何轉變爲 MapReduce

  1. Antlr 定義 SQL 語法規則，完成 SQL 詞法，語法解析，SQL 轉化爲 抽象語法樹 AST Tree。

  2. 遍歷 AST Tree，抽象出查詢的基本組成單元 QueryBlock。

  3. 遍歷 QueryBlock，翻譯爲執行操作樹 OperatorTree。

  4. 邏輯層優化 OperatorTree 變換，合併不必要的 ReduceSinkOperator，減少 Shuffle 數量。

  5. 遍歷 OperatorTree 翻譯爲 MapReduce 任務。

  6. 物理層優化器進行 MapReduce 任務變換，生成最終執行計劃。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Js7MRNs94EgryqDuJqtEtA

猜你喜歡