【筆記】Hive知識整合（33）

- Hive 和 Mysql 的技術差異點

語法：Hive的語法更嚴格。

儲存：Hive不真實儲存。

資料模型：mysql也有分割槽，但是沒必要搞它讓它存更多資料，入倉就可以了。數倉會分表分割槽分桶。

資料型別：Hive除了基本資料型別之外還有複合資料型別Array，Map，Struct，Uniontype

排序：Hive除了order by 之外還有 sort by +distribute by ，cluster by

子查詢：（不同版本的支援程度不一樣）H不支援 if/case when裡的子查詢 並且 父查詢的引用僅在子查詢的where子句中支援。

H支援抽樣查詢。

Hive語句注意事項

- DDL資料定義語言

【這裡需要補一張圖譜切片】

指定欄位分隔符和行分隔符。

分割符指定順序不能錯

分割槽表存在的意義是避免全表掃描，提高查詢效率。有動態分割槽和靜態分割槽兩種方式。

檢視分割槽就是 show partitions emp_partition

分桶表：

分桶表是根據雜湊分堆。作用是提高join查詢效率。把滿足條件的桶做join取代全表join。另一個就是方便抽樣。

建立分桶表

分割槽表和分桶表在邏輯上是一樣的，但是在物理上和全表是有差別的，是為了提高查詢效率的存在。

內部表刪除的時候刪除元資料和表資料，外部表刪除只刪除元資料，表資料還存在。

- 資料匯入匯出和刪除

DML資料操作語言

裝載插入資料，因為用的是雲服務Hue所以local省略不寫

注意這個裝載，語句執行完之後檔案就沒了，所以重複執行的時候會報錯。

overwrite是重寫，不加overwrite就是追加。

怎麼檢視多個分割槽？建一個普通表，把分表的資料插到這個表裡。

向分桶表裡裝載資料和普通表一樣：

Hue不支援中文條件查詢，低版本還不支援一些功能（向分桶表裝載資料）

- 匯出資料

因為用的Hue，所以直接匯出到HDFS上了

- 刪除資料

刪除部分資訊的思路是用where留下有用的資訊，把這些資訊重寫進檔案覆蓋掉原來的。

DQL資料查詢語言

- 查詢資料

H多了複合型別。

函式上多了拆分和合並字串，主要是對於複合型別。

表關聯多了差集和交集。

語句最佳化是處理資料傾斜問題的。

主要講解這些差異點。

select 語句結構

select 的順序有所差異

提到了正則查詢的一些符號，自己看。

排序-

H的排序 普通表、分割槽表、分桶表都是一樣的，邏輯上一樣，只是物理實現不一樣。

sort by 和distribute by 搭配使用可以實現一個需求：分城市對各城市人員薪資進行排序。

但是distribute by是基於雜湊分配的，就會導致相同的城市會撞一起。

這種情況出現的話一般還是用order by ，這樣的缺點是慢。

檢視-

create view 檢視名 as select 。。。

意思就是說這是虛的，基表變化會影響它。

子查詢-

開窗函式-

group by 聚合之後只返回類別數量相當的記錄。

開窗函式返回記錄數量相當的記錄。

偏移函式-

向上向下，在檢查連續，或者計算使用者停留時長等情況下使用。

就是lead 和 lag 。

抽樣查詢-

業務分析一般不用，抽樣主要用在建模和演算法測試，省的整個資料拿進去搞費時間。

order by rand（）

distribute by rand（） order by rand（） 在資料量大的時候更隨機一些

tablesample（）

按條抽取，按資料塊抽取（按大小 比如抽5M），分桶抽樣

調優-

分兩個方面一個是環境調優一個是語句調優，環境調優就是建立分割槽分桶數倉分層。

求最值的時候用排序選第一，取代表join選max

用group by 取代distinct （資料量大的時候效果更好）

with as 代替子查詢 ， 主要是讓語句看起來更清晰

grouping sets可以一次性實現多個聚合，整個grouping sets 語句 也可以替換成 with cube，效果一樣的。

join的時候小表在前，大表在後；連線鍵名字統一；提前過濾資料

解決資料傾斜問題

distinct 處理

資料傾斜問題-

map上的傾斜 ： 比如說檔案劃分的不均勻 一個129M的檔案會被切成 128M和1M 這樣肯定不均衡。

reduce上的傾斜：空值產生傾斜、大小表連線、資料型別不一致

hive已經有這個功能了，要做的就是把小表放在前邊就可以了

總結-

1 Hive 資料表分割槽、分桶的作用是為了提高查詢效率，分割槽表產生不用的目錄避免全表掃描，分桶表產生不同的檔案讓join的速度變快和方便桶抽樣。

2 H常用的3個複合資料型別和訪問方式：

3 H對部分子查詢支援不完善：if/case when後邊不能子查詢，子查詢對父查詢引用只能在where子句中進行引用

4 抽樣查詢的幾種方法：隨機、塊抽樣、桶抽樣

5 常用調優：distinct，group by ，join ，資料傾斜

擴充套件內容： explode（炸裂） 和虛擬表

方法二不受名字個數的限制