一、分桶表的基本介紹

1、什么是分桶表？

????????分桶表主要是用于分文件的，在建表的時候，指定按照那些字段執行分桶操作，并可以設置需要分多少個桶，當插入數據的時候，執行MR的分區的操作，將數據分散各個分區（hive分桶）中，默認分發方案是：hash 取模

? ? ? ? 注意：對于分桶表，不能使用load data 的方式進行數據插入操作，因為load data 導入的數據不會有分桶結構

2、什么是分區表？

????????分區表是數據庫中一種特殊的表結構設計，它將數據按照預定義的規則分散存儲在多個物理區域（稱為 “分區”）中，但對用戶來說邏輯上仍然是一個整體表。分區表在邏輯上是一張表，但數據在物理上被拆分為多個獨立的存儲單元（如文件、文件夾或索引）。用戶無需關心數據具體存儲在哪個分區，只需像操作普通表一樣查詢即可。分區的目的是提高查詢性能、簡化數據管理（如數據歸檔、刪除），尤其適用于數據量極大的表。

3、分桶表和分區表的區別：

1）分桶對數據的處理比分區更加細粒度化；分區針對的是數據的存儲路徑；分桶針對的是數據文件；

2）分桶是按照列的哈希函數進行分割的，相對比較平均；而分區是按照列的值來進行分割的，容易造成數據傾斜；

3）分桶和分區兩者互不干擾，可以把分區表進一步分桶

4）簡單來說，分區就是把表分成一個一個文件夾，一個文件夾里面還可以在放文件夾，分桶是把表分成一個一個文件，文件里面就不能在放文件，所以分區可以分好幾層，分桶不可以

4、如何構建一個分桶表？

create table test_buck(id int, name string)

clustered by(id) sorted by (id asc) into 6 buckets ??-- 創建分桶表的SQL

row format delimited fields terminated by '\t';

5、如何向分桶表添加數據？

標準格式：

? ? ? ? 1）創建一張與分桶表一樣的臨時表，唯一區別是這個表不是一個分桶表

? ? ? ? 2）將數據加載到這個臨時表中

? ? ? ? 3）通過insert into + select 語句將數據導入到分桶表中

注意：sqoop不支持直接對分桶表導入數據

二、分桶表有什么作用

1、進行數據采樣

????????案例1: 數據質量校驗工作(一般會先判斷各個字段數據的結構信息是否完整)

????????案例2: ?在進行數據分析的時候, 一天需要編寫N多條SQL, 但是每編寫一條SQL后, 都需要對SQL做一個校驗, 如果直接面對完整的數據集做校驗, 會導致校驗時間過長, 影響開發進度, 此時可以先采樣出一部分數據

????????案例3: ?在計算一些比率值,或者 在計算相對指標的時候, 也會基于采樣數據來計算相對指標

????????????????比如: 計算當前月的銷售額相對上個月 環比增長了百分之多少?

????????????????????????可以選擇當前月和上個月抽取出百分之30的數據, 基于這個數據來計算

重點：如何進行數據采樣

采樣函數: tablesample(bucket x out of y on column)

放置位置: 緊緊放置表的后面 ?如果表有別名 必須放置別名的前面

例如：select * from 表?tablesample(bucket x out of y on column) 表的別名；

說明:

????x: ?從第幾個桶開始進行采樣

????y: ?抽樣比例(總桶數/y=分多少個桶)

????column: 分桶的字段, 可以省略的

注意:

???x 不能大于 y

???y 必須是表的分桶數量的倍數或者因子

案例:

????1) 假設 A表有10個桶, ?請分析, 下面的采樣函數, 會將那些桶抽取出來

?????????tablesample(bucket 2 out of 5 on xxx)

???????會抽取幾個桶呢? ???總桶 / y = ?分桶數量? ? 10/5=2

???????抽取第幾個編號的桶? ?(x+y)

???????????2,7

????2) ?假設 A表有20個桶, ?請分析, 下面的采樣函數, 會將那些桶抽取出來

??? ?tablesample(bucket 4 out of 4 on xxx)

??? ???會抽取幾個桶呢? ???總桶 / y = ?分桶數量? ?20/4=5

???????抽取第幾個編號的桶? ?(x+y)

???????????4,8,12,16,20

? ? tablesample(bucket 4 out of 40 on xxx)

? ? ? ? 會抽取幾個桶呢? ???總桶 / y = ?分桶數量???20/40=0.5
抽取第幾個編號的桶? ?(x+y)
抽4.5，只抽一個

2、提升查詢的效率（單表|多表）

單表

不分桶就只能從頭到尾一個一個慢慢查，而分桶之后先得到桶編號，在根據桶編號進入對應的桶查詢數據即可

多表

分為小表和大表、中型表和大表、大表和大表

????????Reduce 端 join：是在 MapReduce 框架下，針對表連接（如將兩個或多個數據表依據關聯鍵合并）這一特定操作的實現方式，是一種具體的業務處理策略 ，專注于解決數據關聯問題。

reduce端join的流程:

????????Map 階段：讀取不同表的數據，為每行數據添加標記以區分來源表，并將數據轉換為?<Join Key, Value>?形式輸出。例如，對于訂單表和用戶表，以關聯鍵（如用戶 ID）為 Join Key，為來自訂單表的數據標記?tag = order，來自用戶表的數據標記?tag = user。

????????Shuffle 階段：與 MapReduce 的 Shuffle 類似，根據 Join Key 的哈希值將數據分發到對應的 Reduce 節點，保證相同 Join Key 的數據匯聚到一處。

????????Reduce 階段：根據數據標記區分不同表的數據，然后執行表連接操作（如內連接、左連接等），生成最終的連接結果。

可能出現的問題:

1) 可能出現數據傾斜的問題

2) 導致reduce壓力較大

小表和大表

采用 map join的方案

????????在進行join的時候, 將小表的數據放置到每一個讀取大表的mapTask的內存中, 讓mapTask每讀取一次大表的數據都和內存中小表的數據進行join操作, 將join上的結果輸出到reduce端即可, 從而實現在map端完成join的操作

如何開啟map Join

?? set hive.auto.convert.join=true; ?-- 是否開啟map Join

?? set hive.auto.convert.join.noconditionaltask.size=512000000; -- 設置小表最大的閾值(設置block cache 緩存大小)

中型表和大表

中型表: 與小表相比 大約是小表3~10倍左右

解決方案:

? ? ? 1) 能提前過濾就提前過濾掉(一旦提前過濾后, 會導致中型表的數據量會下降, 有可能達到小表閾值)

??????2) 如果join的字段值有大量的null, 可以嘗試添加隨機數(保證各個reduce接收數據量差不多的, 減少數據傾斜問題)

??????3) 基于分桶表的: bucket map join

?bucket map join的生效條件:

??????1） set hive.optimize.bucketmapjoin = true; ?--開啟bucket map join 支持

??????2） 一個表的bucket數是另一個表bucket數的整數倍

??????3） bucket列 == join列

??????4） 必須是應用在map join的場景中

? 注意：如果表不是bucket的，則只是做普通join。

就是把中型表分桶變成小表，在通過小表和大表的方法即可

大表和大表

大表: 與小表相比 大約是小表10倍以上

核心原理

????????分桶：兩個表按?相同的 Join Key（如?user_id）哈希分桶（例如都分為 100 個桶），確保相同 Join Key 的數據在對應桶中。

????????排序：每個桶內的數據按 Join Key 排序，便于快速匹配。

????????Map 端計算：Join 時，Map 任務直接讀取兩個表的對應桶，用?歸并排序?算法逐行匹配，無需 Shuffle。

解決方案:

? ? 1）. 能提前過濾就提前過濾掉(減少join之間的數量, 提升reduce執行效率)

????2）. 如果join的字段值有大量的null, 可以嘗試添加隨機數(保證各個reduce接收數據量差不多的, 減少數據傾斜問題)

????3）. SMB Map join (sort merge bucket map join)

實現SMB map join的條件要求:

??????1） 一個表的bucket數等于另一個表bucket數(分桶數量是一致，一對一的關系)

??????2） bucket列 == join列 == sort 列

??????3） 必須是應用在bucket map join的場景中，也就是已經開啟了bucket map join的相關參數

??????4) 開啟相關的參數:

?????? -- 開啟SMB map join

?????? set hive.auto.convert.sortmerge.join=true;

?????? set hive.auto.convert.sortmerge.join.noconditionaltask=true;

?????? --寫入數據強制排序

?????? set hive.enforce.sorting=true;

?????? set hive.optimize.bucketmapjoin.sortedmerge = true; -- 開啟自動嘗試SMB連接