下班后我都學了什么1 | Hive

一、學習資料和方法 📝

1、學習途徑 ?

我有買過O’Reilly系列的《Hive編程指南》看，但是沒堅持下去，因為我的時間太碎片化，沒法集中精力地去鉆研一本書，有試過在地鐵上拿著書看，但是看完了仿佛什么都記不得，所以，后來改成每天在地鐵上用手機看視頻來學習，

眾所周知，b站是個學習網站🤔

2、學習資料 📖

• 視頻來源：b站上搜 “hive”，然后播放量最高的那個視頻就對啦，
• 學習資料：評論區前排就有分享課件！！！ 非常良心，都是免費的

（不上鏈接了，免得廣告嫌疑，雖然是免費的課程資源，但我覺得質量還是很好的，符合我的需要）

3、學習方法 💦

• 每天上下班通勤時間，1.5倍速看課程視頻，有sql基礎的，學起來不太費力，先整體過一遍，
  –> 耗時：1個月 （2021.3.4 - 2021.2021.4.1）
• 列印課件，把課件過一遍（相當于第二次學習），自己勾勾畫畫，把不會的圈出來，
  –> 耗時：3天 （2021年4月的第一周清明假期）
• 把盲點、難點對照課程視頻，又再看一遍，把不會的搞懂，查漏補缺，
  –> 耗時：2天 （2021年4月第二周周末）
• 整理筆記在博客
  –> 耗時：2天 （2021年4月第三、四周周末）

二、筆記 📒

這是我個人的筆記啦，挑我自己的重難點、按照個人理解記錄的啦，不是面面俱到，我的理解也可能不夠嚴謹，

1、什么是Hive

• Hive 是做什么的

Hive 是基于Hadoop的一個資料倉庫工具，可以將海量的結構化資料映射為一張表，并且提供類SQL查詢功能，能將SQL陳述句轉變成MapReduce任務來執行，

結合作業中的體會，我的理解是，
（1）埋點采集回來的原始資料都是json格式，json是結構化資料格式，按照key-value，還不方便資料分析師使用，
（2）數倉的同事，按照key-value把資料取出來，經過一系列加工處理（資料提取、轉化、加載），整理成一張張最終供資料分析師使用的表，存盤起來，
（3）而資料分析師，就是用HQL陳述句來查詢，雖然我們寫的是HQL陳述句，但Hive框架會轉換成MapReduce的方法來實作，所以提交代碼后控制臺會看到Map、Reduce等字眼，

• 總結

Hive的本質：將HQL轉化成MapReduce程式

• 資料存盤在 HDFS
• 分析資料底層的實作是MapReduce
• 執行程式運行在Yarn上

2、Hive的優缺點/ Hive VS 資料庫

我覺得這兩個問題類似，其實都是總結Hive的特點、適用場景，

Hive是為了資料倉庫而設計的，是為了支持大規模資料而產生的，理解它的這個出發點和特性，就比較好理解它同時所存在的優點和缺點，

以下是我自己整理和理解的：

• 優點
  （1） HQL是類SQL語法，不用重新學一種新的語言，簡單，
  （2）本身可以把HQL轉換成MapReduce，避免了去寫MapReduce，簡單，
  （3）Hive的優勢在于處理大資料，當資料規模大到超過資料庫的處理能力的時候，Hive可以利用MapReduce進行并行計算，
  （4）支持自定義函式

---

• 缺點
  （1）Hive執行延遲性高，因此常用于對實時要求不高的場合（資料分析），
  -> 延遲性高的原因有2個：一，Hive在查詢資料的時候，由于沒有索引，要掃描整個表，所以延遲較高；二，MapReduce本身具有較高延遲性，
  （2）Hive不支持update和delete，
  -> 我的理解是這樣的：由于Hive是針對資料倉庫設計的，資料倉庫的內容是讀多寫少，所以Hive不建議對資料改寫，資料在加載的時候就已經確定好了，所以不支持update，delete是洗掉表資料，也算是改寫資料，那也不允許，Hive是可以drop table的，這個是洗掉整個表，而資料庫是可以update和delete資料的，
  （3）Hive無法表達迭代式演算法，不擅長機器學習、資料挖掘等，
  -> 最近開始學spark了，spark就擅長迭代式演算法，所以對比Hive，對這一條就有比較深刻的理解，
  -> 迭代式演算法，是指第一個MapReduce的結果作為第二個MapReduce的輸入，Hive不擅長，就是指MapReduce不擅長，那MapReduce為什么不擅長呢？因為MapReduce的結果存盤在磁盤，等到作為第二個MapReduce的輸入的時候，又要去磁盤讀取，影響性能和時間，而比如Spark，就是基于記憶體進行資料的多次迭代，

3、Hive構架和運行機制

• Hive 構架
  

把Hive架構分為4塊，分別用不同顏色表示：
（1）紅色：用戶介面Client，提供了一些列互動介面比如CLI、JDBC，
（2）藍色：這部分是Hive主要的，Driver是驅動，包括決議器、編譯器、優化器、執行器，執行順序是：決議器 -> 編譯器 -> 優化器 -> 執行器，

• 決議器：比如檢查HQL語法，表是否存在，欄位是否存在等
• 編譯器：將HQL 轉化成MapReduce
• 優化器：優化
• 執行器：執行

（3）黃色：Meta store 元資料，用來存盤映射關系的，將HQL中的表與HDFS路徑做映射，因為Hive中的資料是存盤在HDFS的，相當于元資料記錄了每個表存在HDFS哪里，先通過表找到它的地址，再根據地址去HDFS里取資料，
（4）綠色：Hadoop相關的，HDFS進行存盤，MapReduce進行計算，

• Hive的運行機制

Hive的運行機制
（1）Hive通過給用戶提供的一些列互動介面，接收到用戶的HQL；
（2）使用Driver（決議器、編譯器、優化器、執行器），結合元資料（Mata Store），看HQL寫的對不對，表存盤在HDFS哪里，把HQL翻譯成MapReduce，優化然后提交到Hadoop中執行，
（3）最后將執行結果輸出到用戶互動介面

4、Hive的資料型別

• 基本資料型別
  
  我想說的是，Hive中的字符是string，而資料庫中是varchar，

• 集合資料型別
  
  第一次看到集合資料型別，array很好理解，就是陣列，但是會疑惑，struct和map有什么區別？ 因為看上去都是反應一種映射關系，
  
  通過這個例子就可以理解到map和stuct的區別，
  map型別的children，它里面都是相同結構的資料，即名字和年齡；而struct型別的address，里面允許更復雜的內容，可以是街道、城市，并不要求每一行的資料都要是同樣內容，

• 型別轉換
  （1）隱式轉換
  比如使用INT型別，TINYINT會自動轉換成INT型別，但是HIVE不會進行反向轉換，比如使用TINYINT型別，INT不會自動轉換為TINYINT型別，
  （2）CAST強制轉換
  例如 CAST(‘1’ AS INT)將把字串’1’ 轉換成整數 1；如果強制型別轉換失敗，如執行 CAST(‘X’ AS INT)，運算式回傳空值 NULL，

5、內部表 VS 外部表

我們知道HIVE中有 元資料 🆚 原資料，元資料存盤的是指向hdfs實際資料的路徑，原資料是hdfs里真實存盤的資料，

（1）敲黑板！！內部表和外部表的區別就在于這里啦，

• 內部表（也稱為管理表）：
  洗掉內部表，元資料和原資料同時洗掉， 所以不適合和其他工具共享資料，
• 外部表：
  洗掉外部表，則洗掉元資料，原資料不會被刪掉， 外部表就是Hive并非認為其完全擁有這份資料，所以真是的原資料不會被刪掉，

（2）創建外部表：create external table ……

create external table if not exists dept( deptno int,
dname string, loc int
)
row format delimited fields terminated by '\t';

（3）內部表與外部表的互相轉換：

alter table student2 set tblproperties('EXTERNAL'='TRUE'); 

6、排序 order by VS sort by VS cluster by

（1） order by 全域排序
只有1個Reducer，
-> 比如對所有員工按照工資降序排序，
select * from emp order by sal desc;

---

（2）sort by 對每個Reducer內部排序，與distribute by結合使用
distribute by類似partition by 進行磁區，可以控制某個指定行應該到哪個reducer，磁區規則根據磁區欄位的hash碼與reduce的個數進行模除后，余數相同的分到一個區，
-> 比如按照部門編號deptno磁區，再對每個磁區內員工的工資排序
select * from emp distribute by deptno sort by empno desc;

---

（3）當distribute by 和 sort by欄位相同時，可以用 cluster by，
cluster by同時具有distribute by和 sort by的功能，但排序只能升序，不能指定asc或desc
-> 以下兩種寫法等價
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

7、磁區表 VS 分桶表

（1）磁區表

• Hive中的磁區就是分目錄，磁區表的每個磁區，對應HDFS檔案系統上一個獨立的檔案夾， 該檔案夾下是該磁區所有的資料檔案，
• 查詢時通過where來指定磁區，提高查詢效率，

-> 比如創建磁區表，按照日期磁區，那么在hdfs上存盤資料的時候，資料按照日期分別存放在不同的檔案夾中，不同的檔案夾就是不一樣的存盤路徑，這樣在查詢的時候，比如限定where day = ‘20210418’，那么就會去20210418的檔案夾下取資料，而不會去20210417的檔案夾，

-- 創建磁區表
create table dept_partition
( deptno int
, dname string
, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

（2）分桶表

• 對于一張表或者磁區，Hive可以進一步組織成桶，分桶將資料集分解成更容易管理的若干部分，
• Hive的分桶采用對分桶欄位的值進行哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中，
• 分桶表可以用來做抽樣， 對表進行抽樣，也就是對表分成的桶抽樣，語法：TABLESAMPLE（BUCKET x OUT of y）；其中，x的值必須小于y

-> 比如在 20210418檔案夾下，存盤著這一天的所有資料，但是資料集太龐大了，我想進一步把資料集分割成一份一份的更小的資料集，也就是分成一個一個的桶，比如按照id分成4個桶，

-- 創建分桶表，分成4個桶
create table stu_buck(id int, name string) clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

-> ，因為資料集太龐大了，現在我想對20210418這一天的資料抽樣，那我可以相當于對剛剛分好的4個桶中，選1個桶的資料出來就好了，

select * from stu_buck tablesample(bucket 1 out of 4 on id);

（3）磁區表 🆚 分桶表

• 磁區針對的是資料的存盤路徑；
• 分桶針對的是資料檔案，

我的理解：

• 磁區是在hdfs中，按照磁區欄位把資料存在不同的檔案夾，即不同目錄下，也就是存盤在不同的地方，不同的地方就是不同的存盤路徑，
• 分桶是針對某一個資料檔案，把它分成幾份更小的資料檔案，

8、常用內置函式

8.1 空欄位賦值 —— NUL()

NVL：給值為 NULL 的資料賦值，它的格式是 NVL( value，default_value)，它的功能是如 果 value 為 NULL，則 NVL 函式回傳 default_value 的值，否則回傳 value 的值，如果兩個引數 都為 NULL ，則回傳 NULL，

-- 如果comm為空，則用-1代替
select comm,nvl(comm, -1) from emp; 

8.2 case when then else end

• Example
  

select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count from emp_sex
group by dept_id;

8.3 concat() & concat_ws() & collect_set()

• CONCAT(string A/col, string B/col…)：回傳輸入字串連接后的結果，支持任意個輸入字 符串;
• CONCAT_WS(separator, str1, str2,…)：它是一個特殊形式的 CONCAT()，第一個引數剩余參 數間的分隔符，分隔符可以是與剩余引數一樣的字串，如果分隔符是 NULL，回傳值也將 為 NULL，這個函式會跳過分隔符引數后的任何 NULL 和空字串，分隔符將被加到被連接 的字串之間;
  注意: CONCAT_WS must be "string or array
• COLLECT_SET(col)：函式只接受基本資料型別，它的主要作用是將某欄位的值進行去重 匯總，產生 Array 型別欄位，

• Example
  

-- 創建hive表并匯入資料
create table person_info
( name string, 
constellation string, 
blood_type string)
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/data/person_info.txt" into table

-- （1）先把星座和血型用CONCAT_WS連接，連接后的欄位命名 c_b
-- （2）按照c_b分組group by，同時對name欄位用collect_set去重匯總成array，再將array里的元素用CONCAT_WS以‘|’連接，
SELECT
t1.c_b, CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT
NAME,
CONCAT_WS(',',constellation,blood_type) c_b FROM person_info
)t1
GROUP BY t1.c_b

8.4 explode() 、LATERAL VIEW

• EXPLODE(col)：將 hive 一列中復雜的 Array 或者 Map 結構拆分成多行，
• LATERAL VIEW
  用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
  解釋：用于和 split, explode 等 UDTF 一起使用，它能夠將一列資料拆成多行資料，在此 基礎上可以對拆分后的資料進行聚合，

我的理解，

• EXPLODE就是炸開，把一行的多個元素給拆開成到多行，比如本來a，b，c在同一行，現在炸開成a一行，b一行，c一樣，

• lateral view是側寫，用explode炸開之后，本來a，b，c對應 id=001，現在炸開成a、b、c三行之后，a、b、c分別也應該要對應一個id，即第一行a對應 id=001，第二行b也對應id=001，第三行c也對應id=001，也就是用lateral view把炸開后的資料（即a、b、c），與原來的資料（即id=001）給聚合關聯起來，

• Example
  

-- 建hive表并匯入資料
create table movie_info( movie string, category string)
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/data/movie.txt" into table 

SELECT
movie, category_name
FROM
movie_info lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

8.5 視窗函式（開窗函式）

• OVER()：指定分析函式作業的資料視窗大小，這個資料視窗大小可能會隨著行的變而變化，
• CURRENT ROW： 當前行
• n PRECEDING： 往前 n 行資料 n FOLLOWING：往后 n 行資料
• UNBOUNDED：起點，
  – UNBOUNDED PRECEDING 表示從前面的起點，
  – UNBOUNDED FOLLOWING 表示到后面的終點
• LAG(col,n,default_val)：往前第 n 行資料
• LEAD(col,n, default_val)：往后第 n 行資料
• NTILE(n)：把有序視窗的行分發到指定資料的組中，各個組有編號，編號從 1 開始，對 于每一行，NTILE 回傳此行所屬的組的編號，注意：n 必須為 int 型別，

• Example

--（1）查詢在 2017 年 4 月份購買過的顧客及總人數
select name,count(*) over () 
from business
where substring(orderdate,1,7) = '2017-04' 
group by name;

--（2）查詢顧客的購買明細及月購買總額
select name
	,orderdate
	,cost
	,sum(cost) over(partition by month(orderdate)) 
from business;

--（3）將每個顧客的 cost 按照日期進行累加
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分組，組內資料相加 sum(cost) over(partition by name order by orderdate) as sample3,--按 name 分組，組內資料累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一樣,由起點到 當前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --當前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--當前行和前邊一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --當前行及后面所有行
from business;

--（4）查看顧客上次的購買時間
select name
	,orderdate
	,cost
	,lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1
	, lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;

--（5）查詢前 20%時間的訂單資訊
select * 
from (
	select name
		,orderdate
		,cost
		, ntile(5) over(order by orderdate) sorted 
	from business
) t
where sorted = 1;

8.6 rank() VS dense_rank() VS row_number

• RANK() 排序相同時會重復，總數不會變，比如4個人的排名分別為1，1，3，4；
  DENSE_RANK() 排序相同時會重復，總數會減少，比如4個人的排名分別為1，1，2，3；
• ROW_NUMBER() 會根據順序計算，比如4個人的排序分別為1，2，3，4，

• Example
  

-- 創建hive表并匯入資料
create table score
( name string
, subject string,
 score int)
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/data/score.txt' into table score;

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;

9、壓縮和存盤

9.1 壓縮

• MapReduce支持的壓縮編碼
  
• 壓縮引數配置
  

9.2 存盤

9.2.1 列式存盤和行式存盤

• 行存盤的特點
  查詢滿足條件的一整行資料的時候，列存盤則需要去每個聚集的欄位找到對應的每個列的值，行存盤只需要找到其中一個值，其余的值都在相鄰地方，所以此時行存盤查詢的速度更快，
• 列存盤的特點
  因為每個欄位的資料聚集存盤，在查詢只需要少數幾個欄位的時候，能大大減少讀取的資料量；每個欄位的資料型別一定是相同的，列式存盤可以針對性的設計更好的設計壓縮演算法，

Hive 支持的存盤資料的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET，

• TEXTFILE 和 SEQUENCEFILE 的存盤格式都是基于行存盤的；
• ORC 和 PARQUET 是基于列式存盤的，

9.2.2 TextFile 格式

默認格式，資料不做壓縮，磁盤開銷大，資料決議開銷大，
可結合 Gzip、Bzip2 使用， 但使用 Gzip 這種方式，hive 不會對資料進行切分，從而無法對資料進行并行操作，

9.2.3 Orc格式

Orc (Optimized Row Columnar)是 Hive 0.11 版里引入的新的存盤格式，
如下圖所示可以看到每個 Orc 檔案由 1 個或多個 stripe 組成，每個 stripe 一般為 HDFS的塊大小，每一個 stripe 包含多條記錄，這些記錄按照列進行獨立存盤， 對應到 Parquet 中的 row group 的概念，每個 Stripe 里有三部分組成，分別是 Index Data，Row Data，Stripe Footer：

• （1）Index Data： 一個輕量級的 index，默認是每隔 1W 行做一個索引，這里做的索引應該只是記錄某行的各欄位在 Row Data 中的 offset，
• （2）Row Data：存的是具體的資料，先取部分行，然后對這些行按列進行存盤，對每個列進行了編碼，分成多個 Stream 來存盤，
  （3）Stripe Footer：存的是各個 Stream 的型別，長度等資訊，
• 每個檔案有一個 File Footer，這里面存的是每個 Stripe 的行數，每個 Column 的資料型別資訊等；
  每個檔案的尾部是一個 PostScript，這里面記錄了整個檔案的壓縮型別以及 FileFooter 的長度資訊等，
  在讀取檔案時，會 seek 到檔案尾部讀 PostScript，從里面決議到 File Footer 長度，再讀 FileFooter，從里面決議到各個 Stripe 資訊，再讀各個 Stripe，即從后 往前讀，

9.2.4 Parquet 格式

Parquet 檔案是以二進制方式存盤的，所以是不可以直接讀取的，檔案中包括該檔案的 資料和元資料，因此 Parquet 格式檔案是自決議的，

• （1）行組(Row Group)：每一個行組包含一定的行數，在一個 HDFS 檔案中至少存盤一 個行組，類似于 orc 的 stripe 的概念，
• （2）列塊(Column Chunk)：在一個行組中每一列保存在一個列塊中，行組中的所有列連續的存盤在這個行組檔案中，一個列塊中的值都是相同型別的，不同的列塊可能使用不同的演算法進行壓縮，
• （3）頁(Page)：每一個列塊劃分為多個頁，一個頁是最小的編碼的單位，在同一個列塊的不同頁可能使用不同的編碼方式，

通常情況下，在存盤 Parquet 資料的時候會按照 Block 大小設定行組的大小，由于一般情況下每一個 Mapper 任務處理資料的最小單位是一個 Block，這樣可以把每一個行組由一個 Mapper 任務處理，增大任務執行并行度，Parquet 檔案的格式，

上圖展示了一個 Parquet 檔案的內容，一個檔案中可以存盤多個行組，檔案的首位都是 該檔案的 Magic Code，用于校驗它是否是一個 Parquet 檔案，Footer length 記錄了檔案元資料的大小，通過該值和檔案長度可以計算出元資料的偏移量，檔案的元資料中包括每一個行 組的元資料資訊和該檔案存盤資料的 Schema 資訊，除了檔案中每一個行組的元資料，每一 頁的開始都會存盤該頁的元資料，在 Parquet 中，有三種型別的頁：資料頁、字典頁和索引頁， 資料頁用于存盤當前行組中該列的值，字典頁存盤該列值的編碼字典，每一個列塊中最 多包含一個字典頁，索引頁用來存盤當前行組下該列的索引，目前 Parquet 中還不支持索引頁，

9.2.5 主流檔案存盤格式對比

從存盤檔案的壓縮比和查詢速度兩個角度對比，

• 存盤檔案的對比總結：ORC > Parquet > textFile
• 存盤檔案的查詢速度總結：查詢速度相近，
• 存盤方式和壓縮總結：在實際的專案開發當中，hive 表的資料存盤格式一般選擇：orc 或 parquet，壓縮方式一 般選擇 snappy，lzo，

10、調優

10.1 fetch抓取

• Fetch 抓取是指，Hive 中對某些情況的查詢可以不必使用 MapReduce 計算，例如：SELECT * FROM employees; 在這種情況下，Hive 可以簡單地讀取 employee 對應的存盤目錄下的檔案， 然后輸出查詢結果到控制臺，
• 在 hive-default.xml.template 檔案中 hive.fetch.task.conversion 默認是 more，老版本 hive 默認是 minimal，該屬性修改為 more 以后，在全域查找、欄位查找、limit 查找等都不走 mapreduce，

-- 把 hive.fetch.task.conversion 設定成 more，然后執行查詢陳述句，如下查詢方式都不 會執行 mapreduce 程式，
hive (default)> set hive.fetch.task.conversion=more; hive (default)> select * from emp;
hive (default)> select ename from emp;
hive (default)> select ename from emp limit 3;

10.2 表的優化

10.2.1 小表大表 Join（MapJOIN）

• 將 key 相對分散，并且資料量小的表放在 join 的左邊，可以使用 map join 讓小的維度表先進記憶體， 在 map 端完成 join，
• 實際測驗發現：新版的 hive 已經對小表 JOIN 大表和大表 JOIN 小表進行了優化，小表放在左邊和右邊已經沒有區別，

-- 設定自動選擇 Mapjoin
set hive.auto.convert.join = true; 默認為 true 

-- 大表小表的閾值設定（默認 25M 以下認為是小表）
set hive.mapjoin.smalltable.filesize = 25000000; 

10.2.2 大表 Join 大表

（1） 空 KEY 過濾

• 資料傾斜：
  有時 join 超時是因為某些 key 對應的資料太多，而相同 key 對應的資料都會發送到相同的reducer上，從而導致某些reducer的資源消耗遠高于其他reducer，結果導致“一個人累死，其他人閑死的局面”，違背了并行的初衷，一方面，該節點承受巨大的壓力；另一方面，其他節點計算完畢后要一直等該忙碌的節點，拖累了整體計算時間，

• 此時我們應該仔細分析這些例外的 key，很多情況下， 這些 key 對應的資料是例外資料，我們需要在 SQL陳述句中進行過濾，

（2）空 key 轉換

有時雖然某個 key 為空對應的資料很多，但是相應的資料不是例外資料，必須要包含在 join 的結果中，此時我們可以表 a 中 key 為空的欄位賦一個隨機的值，使得資料隨機均勻地分不到不同的 reducer 上，

-- 用nvl(n.id,rand())，對為空的欄位賦一個隨機的值，使得資料隨機均勻地分不到不同的 reducer上，
insert overwrite table jointable
select n.* 
from nullidtable n 
full join bigtable o 
on nvl(n.id,rand()) = o.id;

10.3 group by

• 默認情況下，Map 階段同一 Key 資料分發給一個 reduce，當一個 key 資料過大時就傾斜了，
• 并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端進行部分聚合，最后在 Reduce 端得出最終結果，

--（1）是否在 Map 端進行聚合，默認為 True
set hive.map.aggr = true 	

--（2）在 Map 端進行聚合操作的條目數目
set hive.groupby.mapaggr.checkinterval = 100000 	

--（3）有資料傾斜的時候進行負載均衡（默認是 false）
set hive.groupby.skewindata = true 

當選項設定為 true，生成的查詢計劃會有兩個 MR Job，

• 第一個 MR Job 中，Map 的輸出結果會隨機分布到 Reduce 中，每個 Reduce 做部分聚合操作，并輸出結果，這樣處理的結果是相同的 Group By Key 有可能被分發到不同的 Reduce 中，從而達到負載均衡的目的；
  第二 個 MR Job 再根據預處理的資料結果按照 Group By Key 分布到 Reduce 中（這個程序可以保證相同的 Group By Key 被分布到同一個 Reduce 中），最后完成最終的聚合操作，

10.4 Count(Distinct) 去重統計

• 資料量小的時候無所謂，資料量大的情況下，由于 COUNT DISTINCT 操作需要用一個 Reduce Task 來完成，這一個 Reduce 需要處理的資料量太大，就會導致整個 Job 很難完成，
• 一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替換,但是需要注意 group by 造成的資料傾斜問題，

10.5 笛卡爾積

盡量避免笛卡爾積，join 的時候不加 on 條件，或者無效的 on 條件，Hive 只能使用 1 個 reducer 來完成笛卡爾積，

10.6 where過濾

• 列處理：
  在 SELECT 中，只拿需要的列，如果有磁區，盡量使用磁區過濾，少用 SELECT *，
• 行處理：
  在磁區剪裁中，當使用外關聯時，把where條件寫在自查詢里邊，再做關聯，

select b.id 
from bigtable b
join 
(select id from bigtable where id <= 10) o 
on b.id = o.id;

10.7 合理設定 Map 及 Reduce 數

10.7.1 Map數

• （1）通常情況下，作業會通過 input 的目錄產生一個或者多個 map 任務， 主要的決定因素有：input 的檔案總個數，input 的檔案大小，集群設定的檔案塊大小，
• （2）是不是 map 數越多越好？ 答案是否定的，
  如果一個任務有很多小檔案（遠遠小于塊大小 128m），則每個小檔案也會被當做一個塊，用一個 map 任務來完成，而一個 map 任務啟動和初始化的時間遠遠大于邏輯處理的時間， 就會造成很大的資源浪費，而且，同時可執行的 map 數是受限的，
• （3）是不是保證每個 map 處理接近 128m 的檔案塊，就高枕無憂了？
  答案也是不一定，比如有一個 127m 的檔案，正常會用一個 map 去完成，但這個檔案只有一個或者兩個小欄位，卻有幾千萬的記錄，如果 map 處理的邏輯比較復雜，用一個 map 任務去做，肯定也比較耗時，

針對上面的問題 2 和 3，我們需要采取兩種方式來解決：即減少 map 數和增加 map 數；

（1）復雜檔案增加 Map 數

當 input 的檔案都很大，任務邏輯復雜，map 執行非常慢的時候，可以考慮增加 Map 數， 來使得每個 map 處理的資料量減少，從而提高任務的執行效率，

• 增加 map 的方法為：根據
  computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式， 調整 maxSize 最大值，讓 maxSize 最大值低于 blocksize 就可以增加 map 的個數，

-- 設定最大切片值為 100 個位元組
set mapreduce.input.fileinputformat.split.maxsize=100; 

（2）小檔案進行合并

在 map 執行前合并小檔案，減少 map 數：CombineHiveInputFormat 具有對小檔案進行合并的功能（系統默認的格式），HiveInputFormat 沒有對小檔案合并功能，

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 在 Map-Reduce 的任務結束時合并小檔案的設定： 

-- 在 map-only 任務結束時合并小檔案，默認 true
SET hive.merge.mapfiles = true; 	

-- 在 map-reduce 任務結束時合并小檔案，默認 false
SET hive.merge.mapredfiles = true; 	

-- 合并檔案的大小，默認 256M
SET hive.merge.size.per.task = 268435456; 
	 
-- 當輸出檔案的平均大小小于該值時，啟動一個獨立的 map-reduce 任務進行檔案 merge  
SET hive.merge.smallfiles.avgsize = 16777216; 	

10.7.2 Reduce數

（1）調整 reduce 個數方法一

-- 1 每個 Reduce 處理的資料量默認是 256MB
hive.exec.reducers.bytes.per.reducer=256000000 	

-- 2 每個任務最大的 reduce數，默認為 1009
hive.exec.reducers.max=1009 	

--3 計算 reducer 數的公式
-- N=min(引數 2，總輸入資料量/引數 1) 

（2）調整 reduce 個數方法二

-- 在 hadoop 的 mapred-default.xml 檔案中修改設定每個 job 的 Reduce個數，
set mapreduce.job.reduces = 15; 

（3）reduce 個數并不是越多越好

• 過多的啟動和初始化 reduce 也會消耗時間和資源；
• 另外，有多少個 reduce，就會有多少個輸出檔案，如果生成了很多個小檔案，那么如果這些小檔案作為下一個任務的輸入，則也會出現小檔案過多的問題；
• 在設定 reduce 個數的時候也需要考慮這兩個原則：處理大資料量利用合適的 reduce 數； 使單個 reduce 任務處理資料量大小要合適；

10.8 并行執行

• Hive 會將一個查詢轉化成一個或者多個階段，這樣的階段可以是 MapReduce 階段、抽 樣階段、合并階段、limit 階段，或者Hive 執行程序中可能需要的其他階段，默認情況下， Hive 一次只會執行一個階段，
• 不過，某個特定的 job可能包含眾多的階段，而這些階段可能并非完全互相依賴的，也就是說有些階段是可以并行執行的，這樣可能使得整個 job 的執行時間縮短， 不過，如果有更多的階段可以并行執行，那么 job 可能就越快完成，
• 通過設定引數 hive.exec.parallel 值為true，就可以開啟并發執行，
• 不過，在共享集群中， 需要注意下，如果 job 中并行階段增多，那么集群利用率就會增加，

-- 打開任務并行執行
set hive.exec.parallel=true;

-- 同一個 sql 允許最大并行度，默認為8，
set hive.exec.parallel.thread.number=16;

10.9 嚴格模式

Hive 可以通過設定防止一些危險操作：

10.9.1 防止磁區表不使用磁區過濾

• 將 hive.strict.checks.no.partition.filter 設定為 true 時，對于磁區表，除非 where 陳述句中含有磁區欄位過濾條件來限制范圍，否則不允許執行， 換句話說，就是用戶不允許掃描所有磁區，
• 進行這個限制的原因是，通常磁區表都擁有非常大的資料集，而且資料增加迅速，沒有進行磁區限制的查詢可能會消耗令人不可接受的巨大資源來處理這個表，

10.9.2 防止使用 order by 沒有 limit 過濾

• 將 hive.strict.checks.orderby.no.limit 設定為 true 時，對于使用了 order by 陳述句的查詢，要求必須使用 limit 陳述句，
• 因為 order by 為了執行排序程序會將所有的結果資料分發到同一個 Reducer 中進行處理，強制要求用戶增加這個 LIMIT 陳述句可以防止 Reducer 額外執行很長一段時間，

10.9.3 防止笛卡爾積

• 將 hive.strict.checks.cartesian.product 設定為 true 時，會限制笛卡爾積的查詢，
• 對關系型數 據庫非常了解的用戶可能期望在 行 JOIN 查詢的時候不使用 ON 陳述句而是使用 where 陳述句，這樣關系資料庫的執行優化器就可以高效地將 WHERE 陳述句轉化成那個 ON 陳述句，不幸的是，Hive 并不會執行這種優化，因此，如果表足夠大，那么這個查詢就會出現不可控的情況，