數倉工具—Hive語法之map join、reduce join、smb join(8)
常見的join實現方式
開始之前我們先說一下join 的定義，然後我們後面在說不同的join,有時候我們需要同時獲取兩張表或三張表或更多表的資訊，我們需要把不同的表關聯起來，然後獲取資料，這個就是join, 關聯的過程就是join 的過程
籠統的說，Hive中的Join可分為Common Join（Reduce階段完成join）和Map Join（Map階段完成join),以及Sort Merge Bucket Join 這種劃分方式體現在是實現功能的方式上是不同的
除此之外還有不同型別的join,例如 ，oin、 LEFT|RIGTH|FULL OUTER JOIN、 LEFT SEMI JOIN、主要體現在實現的功能是不一樣的
Map-side Join
map Join的主要思想就是，當關聯的兩個表是一個比較小的表和一個特別大的表的時候，我們把比較小的表直接放到記憶體中去，然後再對比較大的表格進行map操作，join就發生在map操作的時候，每當掃描大的表中的中的一行資料，就要去檢視小表的資料，哪條與之相符，繼而進行連線。
這樣的join並不會涉及reduce操作，自然沒有shuffle減少了資料通過網路傳輸造成的高成本和高延遲了，因為Join 是在map 端完成的，所以又叫做map join
從上面我們看到，處理小表的是一個local task ,它首先將我們的資料封裝成了HashTable(key,value)，然後將其輸出到檔案，然後由DistributedCache 進行載入，載入只有其他大表的Mapper 就可以遠端拉去DistributedCache中的資料，因為DistributedCache本身是用HashTable 的資料結構儲存的，所以真個join 的演算法複雜度和大表的大小
並不是所有的場景都適合用MapJoin,它通常會用在如下的一些情景：
-
在兩個個要連線的表中，有一個很大，有一個很小，這個小表可以存放在記憶體中而不影響效能。這樣我們就把小表檔案複製到每一個Map任務的本地，再讓Map把檔案讀到記憶體中待用。
-
用來解決資料清洗，例如我們的大表有資料傾斜，那這樣的話，某些Reduce 處理的資料就會非常多，因為mapjoin 發生在map端 ，直接沒有了reduce 這一環節了
-
mapjoin還有一個很大的好處是能夠進行不等連線的join操作，如果將不等條件寫在where中(hive 不支援不等值連線，你只能將條件解除安裝where 中)，那麼mapreduce過程中會進行笛卡爾積，執行效率特別低，然後再在where 中過濾出需要的資料
我們可以通過下面兩個引數來設定map join ,第一個引數是開啟map join ，第二個是限制小表的大小
set hive.auto.convert.join=true; //設定 MapJoin 優化自動開啟,預設是開啟的
set hive.mapjoin.smalltable.filesize=25000000 //設定小表不超過多大時開啟 mapjoin 優化，即25M
Hive0.7之前，需要使用hint提示 /*+ mapjoin(table) */才會執行MapJoin,否則執行Common Join，但在0.7版本之後，預設自動會轉換Map Join，由引數hive.auto.convert.join來控制，預設為true，有時候可能會由於map join導致OOM的異常，需要關閉map join
首先我們建立一張稍微比較大的表，大概500M
create table ods_user_log(
id int,
name string,
city string,
phone string,
acctime string)
row format delimited fields terminated by '\t'
stored as textfile;
LOAD DATA LOCAL INPATH '/Users/liuwenqiang/access.log' OVERWRITE INTO TABLE ods_user_log;
然後我們建立一個小表，就是從上面的表中抽取100條
create table ods_user_log_small as select * from ods_user_log limit 100;
接下來，我們建立一條join 語句查詢一下
select
a.*
from
ods_user_log a
inner join
ods_user_log_small b
on
a.id=b.id
limit 10
;
我們看到查詢耗時 28.614s,我擷取了部分處理日誌
2021-01-02 14:40:50,409 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:50,409 INFO [main] exec.FilterOperator (Operator.java:initialize(344)) - Initializing operator FIL[13]
2021-01-02 14:40:50,639 INFO [main] exec.SelectOperator (Operator.java:initialize(344)) - Initializing operator SEL[5]
2021-01-02 14:40:50,642 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:50,642 INFO [main] exec.SelectOperator (SelectOperator.java:initializeOp(73)) - SELECT struct<id:int,name:string,city:string,phone:string,acctime:string>
2021-01-02 14:40:50,642 INFO [main] exec.HashTableSinkOperator (Operator.java:initialize(344)) - Initializing operator HASHTABLESINK[15]
2021-01-02 14:40:50,642 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:50,642 INFO [main] mapjoin.MapJoinMemoryExhaustionHandler (MapJoinMemoryExhaustionHandler.java:<init>(61)) - JVM Max Heap Size: 239075328
2021-01-02 14:40:50,653 INFO [main] persistence.HashMapWrapper (HashMapWrapper.java:calculateTableSize(97)) - Key count from statistics is -1; setting map size to 100000
2021-01-02 14:40:50,675 INFO [main] Configuration.deprecation (Configuration.java:logDeprecation(1395)) - No unit for dfs.client.datanode-restart.timeout(30) assuming SECONDS
2021-01-02 14:40:50,675 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:50,654 INFO [main] mr.MapredLocalTask (MapredLocalTask.java:initializeOperators(516)) - fetchoperator for $hdt$_1:b initialized
2021-01-02 14:40:51,258 INFO [main] Configuration.deprecation (Configuration.java:logDeprecation(1395)) - mapred.input.dir is deprecated. Instead, use mapreduce.input.fileinputformat.inputdir
2021-01-02 14:40:51,321 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:51,320 INFO [main] mapred.FileInputFormat (FileInputFormat.java:listStatus(259)) - Total input files to process : 1
2021-01-02 14:40:51,352 INFO [main] sasl.SaslDataTransferClient (SaslDataTransferClient.java:checkTrustAndSend(239)) - SASL encryption trust check: localHostTrusted = false, remoteHostTrusted = false
2021-01-02 14:40:51,423 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:51,423 INFO [main] exec.TableScanOperator (Operator.java:logStats(1038)) - RECORDS_OUT_OPERATOR_TS_3:100, RECORDS_OUT_INTERMEDIATE:0,
2021-01-02 14:40:51,423 INFO [main] exec.SelectOperator (Operator.java:logStats(1038)) - RECORDS_OUT_OPERATOR_SEL_5:100, RECORDS_OUT_INTERMEDIATE:0,
2021-01-02 14:40:51,423 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:51,423 INFO [main] exec.FilterOperator (Operator.java:logStats(1038)) - RECORDS_OUT_INTERMEDIATE:0, RECORDS_OUT_OPERATOR_FIL_13:100,
2021-01-02 14:40:51,423 INFO [main] exec.HashTableSinkOperator (HashTableSinkOperator.java:flushToFile(293)) - Temp URI for side table: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3
2021-01-02 14:40:51 Dump the side-table for tag: 1 with group count: 9 into file: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01--.hashtable
2021-01-02 14:40:51,423 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main-LocalTask-MAPREDLOCAL-stdout] mr.MapredLocalTask (LogRedirector.java:run(65)) - 2021-01-02 14:40:51,423 INFO [main] exec.HashTableSinkOperator (SessionState.java:printInfo(1227)) - 2021-01-02 14:40:51 Dump the side-table for tag: 1 with group count: 9 into file: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01--.hashtable
2021-01-02 14:40:51 Uploaded 1 File to: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01--.hashtable (789 bytes)
2021-01-02 14:40:51,456 INFO [main] exec.HashTableSinkOperator (SessionState.java:printInfo(1227)) - 2021-01-02 14:40:51 Uploaded 1 File to: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01--.hashtable (789 bytes)
上面的處理日誌中有幾條重要資訊，可以幫助我們理解map join 的執行過程
- mr.MapredLocalTask Initializing operator
- Initializing operator HASHTABLESINK
- Dump the side-table for tag: 1 with group count: 9 into file: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01–.hashtable
- Uploaded 1 File to: file:/tmp/hive/local/66c753b7-56d4-429c-bb87-14eed166a61a/hive_2021-01-02_14-40-36_550_872109057128790183-2/-local-10004/HashTable-Stage-3/MapJoin-mapfile01–.hashtable (789 bytes)
從面的執行執行日誌中我們看到確實使用的實map join 的實現，也可以得出map join是自動開啟的，下面我們關閉掉map join 再執行一次
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
1 蘇健柏 渭南 14730379051 2020-03-29 21:12:15
2021-01-02 14:49:06,853 INFO [66c753b7-56d4-429c-bb87-14eed166a61a main] exec.ListSinkOperator (Operator.java:logStats(1038)) - RECORDS_OUT_INTERMEDIATE:0, RECORDS_OUT_OPERATOR_LIST_SINK_14:10,
Time taken: 43.117 seconds, Fetched: 10 row(s)
因為這次關閉了map join,所以你就看不到map join相關的日誌輸出，並且執行時間也長了很多，其實資料量越大，map join 的效果越明顯
Reduce-side(Common) Join
hive join操作預設使用的就是reduce join，reduce side join是一種最簡單的join方式，其主要思想如下：
在map階段，map函式同時讀取兩個檔案File1和File2，為了區分兩種來源的key/value資料對，對每條資料打一個標籤（tag）接下來通過shuffle 操作,就保證了相同key 的資料落在了桶一個reducer 中，然後在這個reducer 中完相應的join 邏輯
select u.name, o.orderid from order o join user u on o.uid = u.uid; 在map的輸出value中為不同表的資料打上tag標記，在reduce階段根據tag判斷資料來源，然後根據SQL的select 順序依次將需要的資料讀取出來進行返回
SMB Join（sort merge bucket）
前面我們學習過分桶，桶的物理意義就是表目錄下的一個檔案，前面我們也提到過關於桶可以提高join 的效率Hive的資料組織管理方式，但是我們沒有細講，今天我們看一下，桶可以保證相同key 的資料都分在了一個桶裡，這個時候我們關聯的時候不需要去掃描整個表的資料，只需要掃描對應桶裡的資料(因為key 相同的一定在一個桶裡)，smb的設計是為了解決大表和大表之間的join的，核心思想就是大表化成小表，然後map side join 解決是典型的分而治之的思想。
這裡有一點要注意，那就是資料落在那個桶裡不止和key 的值相關，還和桶的個數相關，因為我們是根據key 的雜湊值然後對桶的個數取餘數獲得一個值，然後根據這個值將資料放到對應的桶裡去的，所以看出來這個是和桶的個數是相關的，所以一般情況下我們要求不止是兩個分桶表的分桶欄位是相等的，還要求桶的個數是倍數關係(相等也是可以的)
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
表優化資料目標：相同資料儘量聚集在一起
那我們就在就從上面例子的表中，取資料建兩個新表
create table ods_user_bucket_log(
id int,
name string,
city string,
phone string,
acctime string)
CLUSTERED BY (`id` ) INTO 5 BUCKETS
row format delimited fields terminated by '\t'
stored as textfile;
create table ods_user_bucket_2_log(
id int,
name string,
city string,
phone string,
acctime string)
CLUSTERED BY (`id` ) INTO 5 BUCKETS
row format delimited fields terminated by '\t'
stored as textfile;
insert overwrite table ods_user_bucket_log select * from ods_user_log;
insert overwrite table ods_user_bucket_2_log select * from ods_user_log;
因為我們是分了5個桶，所以在HDFS 上應該也是有5個檔案的
下面我們執行一下SQL 看看
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
select
a.id,b.name
from
ods_user_bucket_log a
inner join
ods_user_bucket_2_log b
on
a.id=b.id
limit 10;
-- 非分桶表
reset;
select
a.id,b.name
from
ods_user_log a
inner join
ods_user_log_2 b
on
a.id=b.id
limit 10;
下面是桶表的執行情況
[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-vGiDV4HA-1609641870072)(/Users/liuwenqiang/Library/Application%20Support/typora-user-images/image-20210102204950105.png)]
下面是非桶表的執行情況
我們發現桶表的執行雖然快一些，但是差距不是很多，這是因為我們的表不是很大，表越大，效果越明顯
總結
- map join 對效能優化有特別明顯的效果，而且有很多的適用場景，例如大小表關聯、不等值連線、處理資料傾斜
- 關於SMB join主要用來處理大表關聯，hive並不檢查兩個join的表是否已經做好bucket且sorted，需要使用者自己去保證join的表，否則可能資料不正確
- Reduce-side(Common) Join 是我們最常見的join 型別，通過shuffle 來完成資料的分發
相關文章
- hadoop 多表join：Map side join及Reduce side join範例HadoopIDE
- MySQL Join語法MySql
- join、inner join、left join、right join、outer join的區別
- Hive JOIN使用詳解Hive
- sql之left join、right join、inner join的區別SQL
- sql的 INNER JOIN 語法SQL
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- sql的left join 、right join 、inner join之間的區別SQL
- Apache Spark Dataframe Join語法教程ApacheSpark
- outer join新舊語法分析語法分析
- sql中的join、left join、right joinSQL
- Mysql join 的本質，Nested Loop Join 和 Hash JoinMySqlOOP
- mysql left join轉inner joinMySql
- .join()
- 連線查詢簡析 join 、 left join 、 right join
- hash join\nest loop join\sort merge join的實驗OOP
- ZT：簡單介紹join,outer-join,semi-join,anti-join的區別
- LEFT JOIN 和JOIN 多表連線
- HASH JOIN ,MERGE JOIN ,NESTED LOOPOOP
- sql：left join和join區別SQL
- SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）SQL
- sql語言中join操作SQL
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- mysql update join,insert select 語法MySql
- mysql中的left join、right join 、inner join的詳細用法MySql
- nested loop,sort merge join,hash joinOOP
- JUC之Fork/Join框架框架
- Sql 之 join 001SQL
- JavaScript join()JavaScript
- FILTER JOINFilter
- SQL joinSQL
- left join,right join,inner join的條件on和where的區別
- sql語句中JOIN ON 的使用SQL
- HINT no_swap_join_inputs/swap_join_inputs
- sql left join 和 right join解釋SQL
- MapReduce實現之Reduce端重分割槽Join操作最佳化！
- SQL最佳化 之 -- joinSQL
- Inner Join, Left Outer Join和Association的區別