數倉工具—Hive語法之map join、reduce join、smb join(8)

不二人生發表於2021-01-03

常見的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

image-20210102141134910

從上面我們看到,處理小表的是一個local task ,它首先將我們的資料封裝成了HashTable(key,value),然後將其輸出到檔案,然後由DistributedCache 進行載入,載入只有其他大表的Mapper 就可以遠端拉去DistributedCache中的資料,因為DistributedCache本身是用HashTable 的資料結構儲存的,所以真個join 的演算法複雜度和大表的大小

並不是所有的場景都適合用MapJoin,它通常會用在如下的一些情景:

  1. 在兩個個要連線的表中,有一個很大,有一個很小,這個小表可以存放在記憶體中而不影響效能。這樣我們就把小表檔案複製到每一個Map任務的本地,再讓Map把檔案讀到記憶體中待用。

  2. 用來解決資料清洗,例如我們的大表有資料傾斜,那這樣的話,某些Reduce 處理的資料就會非常多,因為mapjoin 發生在map端 ,直接沒有了reduce 這一環節了

  3. 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
;

image-20210102144136474

我們看到查詢耗時 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 的執行過程

  1. mr.MapredLocalTask Initializing operator
  2. Initializing operator HASHTABLESINK
  3. 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
  4. 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 順序依次將需要的資料讀取出來進行返回

image-20201206211916602

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個檔案的

image-20210102203022993

下面我們執行一下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)]

下面是非桶表的執行情況

image-20210103094552839

我們發現桶表的執行雖然快一些,但是差距不是很多,這是因為我們的表不是很大,表越大,效果越明顯

總結

  1. map join 對效能優化有特別明顯的效果,而且有很多的適用場景,例如大小表關聯、不等值連線、處理資料傾斜
  2. 關於SMB join主要用來處理大表關聯,hive並不檢查兩個join的表是否已經做好bucket且sorted,需要使用者自己去保證join的表,否則可能資料不正確
  3. Reduce-side(Common) Join 是我們最常見的join 型別,通過shuffle 來完成資料的分發

相關文章