![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181707-6a74cfa3e1ab7.png\")
<\/p>\n\u4e0a\u4e00\u7bc7\u6211\u4eec\u628a\u96c6\u7fa4\u548c\u5f15\u64ce\u7684"\u786c\u4ef6\u5e95\u5ea7"\u914d\u597d\u4e86\u2014\u2014\u8f66\u9053\u4fee\u5bbd\u3001\u9650\u901f\u8bbe\u5bf9\u3002\u8fd9\u4e00\u7bc7\u5f00\u59cb\u770b\u8f66\u600e\u4e48\u5f00：\u4e00\u6761 SQL \u8fdb\u53bb，Hive \u5148\u628a\u5b83\u62c6\u6210 Stage \u548c Operator（\u7b2c 4 \u7ae0），\u7136\u540e\u6211\u4eec\u9010\u4e2a\u73af\u8282\u62a0\u6027\u80fd\u2014\u2014\u805a\u5408\u3001\u88c1\u526a\u3001Join\u3001\u5c0f\u6587\u4ef6\u3001\u5e76\u884c\u5ea6\u3001CBO\u3002\u53c2\u6570\u90fd\u7ed9\u4f60，\u4f46\u66f4\u91cd\u8981\u7684\u662f\u544a\u8bc9\u4f60\u6bcf\u4e2a\u53c2\u6570\u5728\u89e3\u51b3\u6267\u884c\u8ba1\u5212\u91cc\u7684\u54ea\u4e00\u6bb5\u74f6\u9888\u3002<\/p>\n
\u200b\u4f7f\u7528Explain\u53ef\u4ee5\u67e5\u770b Hive SQL \u7684\u6267\u884c\u8ba1\u5212<\/p>\n
\u200bExplain \u67e5\u770b\u7684\u6267\u884c\u8ba1\u5212，\u6709\u4e00\u7cfb\u5217\u7684Stage（\u8fd9\u662f Hive \u4e2d\u7684 Stage， \u8ddf Spark \u7684 Stage \u4e0d\u662f\u4e00\u56de\u4e8b）\u7ec4\u6210，\u8fd9\u4e2a Stage \u5177\u6709\u4f9d\u8d56\u5173\u7cfb，\u6bcf\u4e2a Stage \u5bf9\u5e94\u4e00\u4e2a MR Job \u6216\u8005 Spark Job，\u6216\u8005\u4e00\u4e2a\u6587\u4ef6\u7cfb\u7edf\u64cd\u4f5c（ load \u8bed\u53e5）\u7b49\u3002<\/p>\n
\u200b\u6bcf\u4e00\u4e2a Stage \u7531\u4e00\u7cfb\u5217\u7684 Operator \u7ec4\u6210，\u4e00\u4e2a Operator \u4ee3\u8868\u4e00\u4e2a\u903b\u8f91\u8349\u9519，\u4f8b\u5982：TableScan Operator， Select Operator,，Join Operator，Groupby Operator\u7b49\u3002<\/p>\n
\u200bStage \u4e0e Operator \u7684\u5bf9\u5e94\u5173\u7cfb：<\/p>\n
<\/p>\n
\u4ee5\u8fd9\u6761\u7b80\u5355SQL\u8bed\u53e5\u4e3a\u4f8b：<\/p>\n
hive>
\nselect
\n user_id,
\n count(*)
\nfrom dwd_user_login_inc
\nwhere dt = '2022-07-01'
\ngroup by user_id;<\/p>\n
\u53ef\u4ee5\u4f7f\u7528 Explain \u67e5\u770b\u6267\u884c\u8ba1\u5212，explain + \u6267\u884cSQL；<\/p>\n
\u5173\u7cfb\u5982\u4e0b：<\/p>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181708-6a74cfa4c7744.png\")
<\/p>\n
\u5f53\u6570\u636e\u91cf\u5de8\u5927\u65f6，Shuffle \u6210\u4e3a\u6700\u5927\u7684\u6027\u80fd\u74f6\u9888\u2014\u2014\u6d89\u53ca\u78c1\u76d8 I\/O\u3001\u7f51\u7edc\u4f20\u8f93\u3001\u5e8f\u5217\u5316\/\u53cd\u5e8f\u5217\u5316\u3002<\/p>\n
\u200b\u5728\u8fd9\u5206\u7ec4\u805a\u5408\u7684\u8fc7\u7a0b\u4e2d，\u5f71\u54cd\u6700\u5927\u7684\u5c31\u662f Shuffle \u9636\u6bb5，Shuffle \u9636\u6bb5\u9700\u8981\u8bfb\u5199\u78c1\u76d8，\u901f\u5ea6\u6162\u3002\u8868\u7684 Size \u8d8a\u5927，\u6536\u5230\u7684\u5f71\u54cd\u8d8a\u6df1\u3002\u6240\u4ee5\u6211\u4eec\u53ef\u4ee5\u5f00\u542f\u5206\u7ec4\u9884\u5148\u805a\u5408，\u5728 Map 1 \u4e2d\u5c31\u63d0\u524d\u5148\u805a\u5408，\u51cf\u5c11\u5230Shuffle\u9636\u6bb5\u7684\u6570\u636e\u91cf\u3002<\/p>\n
\u200b\u4f18\u5316\u601d\u8def\u4e3a map-side \u805a\u5408\u3002\u5728 map \u7aef\u7ef4\u62a4\u4e00\u4e2a hash table，\u5229\u7528 hash table \u5b8c\u6210\u90e8\u5206\u7684\u805a\u5408，\u7136\u540e\u5c06\u8fd9\u90e8\u5206\u805a\u5408\u7684\u7ed3\u679c\u7ecf\u8fc7 shuffle \u53d1\u9001\u5230 reduce \u7aef，\u5b8c\u6210\u6700\u7ec8\u7684\u805a\u5408\u3002 map-side \u805a\u5408\u80fd\u6709\u6548\u51cf\u5c11 shuffle \u7684\u6570\u636e\u91cf，\u63d0\u9ad8 \u5206\u7ec4\u805a\u5408 \u7684\u6548\u7387\u3002<\/p>\n
\u200bmap-side \u76f8\u5173\u7684\u53c2\u6570\u5982\u4e0b：<\/p>\n
# \u662f\u5426\u5728 Map \u7aef\u8fdb\u884c\u805a\u5408，\u9ed8\u8ba4 true
\nhive.map.aggr = true;
\n# hash table \u5360 map \u7aef\u5185\u5b58\u7684\u5927\u5c0f，\u9ed8\u8ba4 0.5
\nhive.map.aggr.hash.percentmemory = 0.5;
\n# Map \u7aef\u805a\u5408\u540e\u7684\u6570\u636e\u91cf \/ \u539f\u59cb\u6570\u636e\u91cf\u7684\u6bd4\u503c，\u82e5\u5927\u4e8e\u6b64\u503c\u5219\u5173\u95ed Map \u7aef\u805a\u5408（\u8ba4\u4e3a\u805a\u5408\u6548\u679c\u4e0d\u597d）
\nhive.map.aggr.hash.min.reduction = 0.5; \u9ed8\u8ba4 0.5
\n# \u54c8\u5e0c\u8868\u5185\u5b58\u4f7f\u7528\u8fbe\u5230\u6b64\u503c\u65f6\u5f3a\u5236\u5237\u5199,\u9ed8\u8ba4\u503c 0.9
\nhive.map.aggr.hash.force.flush.memory.threshold = 0.9
\n# \u6570\u636e\u503e\u659c\u5f0f\u662f\u5426\u542f\u7528\u4e24\u9636\u6bb5\u805a\u5408（\u8be6\u89c1\u540e\u7eed\u6570\u636e\u503e\u659c\u7ae0\u8282），\u9ed8\u8ba4 false
\nhive.groupby.skewindata = false<\/p>\n
\u200bExplain\u6267\u884c\u8ba1\u5212<\/p>\n
\u793a\u610f\u56fe：<\/p>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181709-6a74cfa5a3a2d.png\")
<\/p>\n
\u5982\u679c\u57fa\u4e8e Snappy \u538b\u7f29\u65b9\u5f0f\u7684\u8868，\u4e00\u4e2a\u5206\u533a\u4e2d\u7684\u6570\u636e\u843d\u76d8\u5927\u6982 4G ，\u5728\u5185\u5b58\u4e2d\u8fd0\u7b97\u5927\u6982 15G：<\/p>\n
— \u274c \u5168\u8868\u626b\u63cf（\u626b\u63cf\u6240\u6709\u5206\u533a）
\nSELECT * FROM orders WHERE SUBSTR(order_time, 1, 10) = '2026-07-25';<\/p>\n
— \u2705 \u5206\u533a\u88c1\u526a（\u53ea\u626b\u63cf\u76ee\u6807\u5206\u533a）
\nSELECT * FROM orders WHERE dt = '2026-07-25';<\/p>\n
— \u2705 \u591a\u5206\u533a\u8303\u56f4
\nSELECT * FROM orders WHERE dt BETWEEN '2026-07-01' AND '2026-07-25';<\/p>\n
— \u2705 \u52a8\u6001\u5206\u533a\u88c1\u526a（Spark 3.0+）
\nSET spark.sql.optimizer.dynamicPartitionPruning.enabled = true;<\/p>\n
SELECT o.*, u.user_name
\nFROM orders o
\nJOIN users u ON o.user_id = u.user_id
\nWHERE u.city = '\u5317\u4eac'; — \u81ea\u52a8\u88c1\u526a\u975e\u5317\u4eac\u5206\u533a<\/p>\n
— \u274c \u8bfb\u53d6\u6240\u6709\u5217（ORC\/Parquet\u662f\u5217\u5b58，\u8bfb\u6240\u6709\u5217=\u5168\u91cfI\/O）
\nSELECT * FROM orders WHERE dt = '2026-07-25';<\/p>\n
— \u2705 \u53ea\u8bfb\u9700\u8981\u7684\u5217
\nSELECT order_id, user_id, amount FROM orders WHERE dt = '2026-07-25';<\/p>\n
\u200b\u8c13\u8bcd\u4e0b\u63a8：\u5c06\u8fc7\u6ee4\u6761\u4ef6\u4ece\u6267\u884c\u8ba1\u5212\u7684\u4e0a\u5c42\u79fb\u52a8\u5230\u4e0b\u5c42（\u66f4\u9760\u8fd1\u6570\u636e\u6e90\u7684\u4f4d\u7f6e），\u4f7f\u5f97：\u5728\u6570\u636e\u88ab\u8bfb\u53d6\u3001\u4f20\u8f93\u3001\u8ba1\u7b97\u4e4b\u524d，\u5c31\u5c3d\u65e9\u5730\u4e22\u5f03\u4e0d\u9700\u8981\u7684\u6570\u636e\u3002<\/p>\n
— \u5f00\u542f\u8c13\u8bcd\u4e0b\u63a8（\u5c06WHERE\u6761\u4ef6\u4e0b\u63a8\u5230\u5b58\u50a8\u5c42\u8fc7\u6ee4）
\nSET hive.optimize.ppd = true;
\nSET hive.optimize.ppd.storage = true;<\/p>\n
— ORC\/Parquet \u6587\u4ef6\u4f1a\u5229\u7528 Row Group \u7684 min\/max \u7edf\u8ba1\u4fe1\u606f\u8df3\u8fc7\u4e0d\u76f8\u5173\u6570\u636e\u5757
\nSELECT order_id, amount
\nFROM orders
\nWHERE dt = '2026-07-25'
\n AND amount > 1000; — \u6b64\u6761\u4ef6\u4f1a\u4e0b\u63a8\u5230\u6587\u4ef6\u8bfb\u53d6\u5c42<\/p>\n
\u200bHive \u62e5\u6709\u591a\u79cd Join \u7b97\u6cd5，\u4ece\u57fa\u7840\u5230\u9ad8\u7ea7\u4f9d\u6b21\u4e3a：Common Join \u2192 Map Join \u2192 Bucket Map Join \u2192 Sort Merge Bucket Map Join\u3002\u4f18\u5316\u7a0b\u5ea6\u9010\u7ea7\u9012\u589e，\u9002\u7528\u6761\u4ef6\u4e5f\u9010\u7ea7\u4e25\u683c\u3002<\/p>\n
\u200bCommon Join \u662f Hive \u6700\u57fa\u7840，\u6700\u7a33\u5b9a\u7684 Join \u7b97\u6cd5，\u901a\u8fc7\u4e00\u4e2a\u5b8c\u6574\u7684 MapReduce \/ Spark Job \u5b8c\u6210<\/p>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181711-6a74cfa766adf.png\")
<\/p>\n
— \u8ba2\u5355\u8868 JOIN \u7528\u6237\u8868（\u9ed8\u8ba4\u8d70Common Join）<\/span> Stage-1: Map Reduce \u200bMap Join \u5c06 \u5c0f\u8868\u5b8c\u5168\u52a0\u8f7d\u5230\u5206\u5e03\u5f0f\u5185\u5b58，\u5728 Map \u9636\u6bb5\u76f4\u63a5\u5b8c\u6210 Join，\u5b8c\u5168\u8df3\u8fc7 Shuffle \u548c Reduce \u9636\u6bb5\u3002<\/p>\n — \u65b9\u5f0f\u4e00：\u4f7f\u7528 Hint \u663e\u5f0f\u6307\u5b9a Map Join(\u4e0d\u63a8\u8350)<\/span> — \u65b9\u5f0f\u4e8c：\u5f00\u542f\u81ea\u52a8\u8f6c\u6362（\u63a8\u8350）<\/span> SELECT<\/span> # \u662f\u5426\u81ea\u52a8\u5c06 Common Join \u8f6c\u4e3a Map Join，\u9ed8\u8ba4 true Common Join ； 100GB\u5927\u8868 JOIN 20MB\u5c0f\u8868 -> Shuffle 100GB -> \u8017\u65f6 30min \u200bBucket Map Join \u662f Map Join \u7684\u6269\u5c55，\u6253\u7834\u4e86"\u5c0f\u8868\u5fc5\u987b\u5b8c\u5168\u88c5\u5165\u5185\u5b58"\u7684\u9650\u5236，\u53ef\u7528\u4e8e \u5927\u8868 JOIN \u5927\u8868\u7684\u573a\u666f\u3002<\/p>\n \u200b\u6838\u5fc3\u601d\u60f3：\u5982\u679c\u4e24\u5f20\u5927\u8868\u90fd\u6309\u7167 Join Key \u8fdb\u884c\u4e86\u5206\u6876（Bucket），\u90a3\u4e48 \u6876N \u7684\u6570\u636e\u53ea\u4f1a\u4e0e\u53e6\u4e00\u5f20\u8868\u7684 \u6876M \u8fdb\u884c Join （N\u4e8eM\u5fc5\u987b\u662f\u6574\u6570\u500d\u5173\u7cfb）\u3002\u56e0\u6b64 Map \u7aef\u65e0\u9700\u7f13\u5b58\u5c0f\u8868\u5168\u91cf\u6570\u636e，\u53ea\u9700\u7f13\u5b58\u5bf9\u5e94\u6876\u53f7\u7684\u6570\u636e\u3002<\/p>\n Step 1 : \u521b\u5efa\u5206\u6876\u8868<\/p>\n — \u8ba2\u5355\u8868：\u6309user_id\u52068\u4e2a\u6876<\/span> — \u7528\u6237\u8868：\u6309user_id\u52068\u4e2a\u6876（\u62164\u4e2a\u6876，\u5fc5\u987b\u662f\u500d\u6570\u5173\u7cfb）<\/span> — \u63d2\u5165\u6570\u636e（\u5fc5\u987b\u4f7f\u7528INSERT\u624d\u80fd\u6b63\u786e\u5206\u6876）<\/span> INSERT<\/span> OVERWRITE TABLE<\/span> users_bucketed Step 2 ： \u6267\u884c Bucket Map Join<\/p>\n — \u8bbe\u7f6e\u53c2\u6570<\/span> — \u4f7f\u7528Hint\u6307\u5b9a<\/span> # \u662f\u5426\u542f\u7528 Bucket Map Join，\u9ed8\u8ba4 false \u200bSMB Join \u662f Bucket Map Join \u7684\u8fdb\u4e00\u6b65\u4f18\u5316\u3002\u5728 Bucket Map Join \u4e2d，\u6bcf\u4e2a\u6876\u7684\u6570\u636e\u52a0\u8f7d\u5230\u5185\u5b58\u4e4b\u540e\u4ecd\u9700\u8fdb\u884c Hash \u5339\u914d\u3002\u800c SMB Join \u8981\u6c42 \u6876\u5185\u6570\u636e\u6309 Join Key \u6392\u5e8f，\u8fd9\u6837\u4e24\u4e2a\u6876\u7684\u6570\u636e\u5c31\u53ef\u4ee5\u8fdb\u884c \u5f52\u5e76\u6392\u5e8f\u5f0f\u5339\u914d（Merge Join），\u65e0\u9700\u5c06\u4efb\u4f55\u4e00\u4e2a\u6876\u5b8c\u5168\u52a0\u8f7d\u5230\u5185\u5b58\u3002<\/p>\n Step 1 ： \u521b\u5efa\u6392\u5e8f\u5206\u6876\u8868<\/p>\n — \u6ce8\u610f：CLUSTERED BY + SORTED BY<\/span> CREATE<\/span> TABLE<\/span> users_smb (<\/span> — \u63d2\u5165\u6570\u636e<\/span> INSERT<\/span> OVERWRITE TABLE<\/span> users_smb Step 2 ： \u6267\u884c SMB Join<\/p>\n
\nSELECT<\/span>
\n o.<\/span>order_id,<\/span>
\n o.<\/span>amount,<\/span>
\n u.<\/span>user_name,<\/span>
\n u.<\/span>city
\nFROM<\/span> orders o
\nJOIN<\/span> users u ON<\/span> o.<\/span>user_id =<\/span> u.<\/span>user_id
\nWHERE<\/span> o.<\/span>dt =<\/span> '2026-07-25'<\/span>;<\/span><\/p>\n8.1.3 \u6267\u884c\u8ba1\u5212<\/h4>\n
\n Map Operator Tree:
\n TableScan (orders) \u2192 Select \u2192 Reduce Output (key: user_id, tag:0)
\n TableScan (users) \u2192 Select \u2192 Reduce Output (key: user_id, tag:1)
\n Reduce Operator Tree:
\n Join Operator (Inner Join, key: user_id) \u2192 Select \u2192 File Output<\/p>\n8.1.4 \u4f18\u7f3a\u70b9<\/h4>\n
\n
\u4f18\u70b9\u7f3a\u70b9<\/tr>\n \n\n \u6700\u7a33\u5b9a，\u9002\u7528\u6240\u6709\u573a\u666f<\/td>\n \u9700\u8981\u5b8c\u6574\u7684 Shuffle \u8fc7\u7a0b<\/td>\n<\/tr>\n \n \u65e0\u9700\u7279\u6b8a\u8868\u7ed3\u6784<\/td>\n \u7f51\u7edc I\/O \u5f00\u9500\u5927<\/td>\n<\/tr>\n \n \u65e0\u5185\u5b58\u9650\u5236<\/td>\n \u5927\u8868 Join \u5927\u8868\u65f6\u6027\u80fd\u5dee<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 8.2 Map Join<\/h3>\n
8.2.1 \u539f\u7406<\/h4>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181712-6a74cfa8312f6.png\")
<\/p>\n8.2.2 \u793a\u4f8bSQL<\/h4>\n
\nSELECT<\/span> \/*+ MAPJOIN(u) *\/<\/span>
\n o.<\/span>order_id,<\/span>
\n o.<\/span>amount,<\/span>
\n u.<\/span>user_name,<\/span>
\n u.<\/span>city
\nFROM<\/span> orders o
\nJOIN<\/span> users u ON<\/span> o.<\/span>user_id =<\/span> u.<\/span>user_id;<\/span><\/p>\n
\nSET<\/span> hive.<\/span>auto.<\/span>convert<\/span>.<\/span>join<\/span> =<\/span> true<\/span>;<\/span>
\nSET<\/span> hive.<\/span>mapjoin.<\/span>smalltable.<\/span>filesize =<\/span> 25000000<\/span>;<\/span> — \u5c0f\u8868\u9608\u503c25MB<\/span><\/p>\n
\n o.<\/span>order_id,<\/span>
\n o.<\/span>amount,<\/span>
\n u.<\/span>user_name,<\/span>
\n u.<\/span>city
\nFROM<\/span> orders o
\nJOIN<\/span> users u ON<\/span> o.<\/span>user_id =<\/span> u.<\/span>user_id;<\/span><\/p>\n8.2.3 \u53c2\u6570\u8bbe\u7f6e<\/h4>\n
\nhive.auto.convert.join = true
\n# \u5c0f\u8868\u5927\u5c0f\u9608\u503c，\u5c0f\u4e8e\u6b64\u503c\u81ea\u52a8 Map Join，\u9ed8\u8ba4 25000000(25MB)
\nhive.mapjoin.smalltable.filesize = 25000000
\n# \u662f\u5426\u65e0\u6761\u4ef6\u8f6c\u5316（\u4e0d\u9700\u8981\u8fd0\u884c\u65f6\u5224\u65ad），\u9ed8\u8ba4 true
\nhive.auto.convert.join.noconditionaltask = true
\n# \u65e0\u6761\u4ef6\u8f6c\u5316\u5927\u5c0f\u9608\u503c，\u9ed8\u8ba4 10000000(10MB)
\nhive.auto.convert.join.noconditionaltask.size = 10000000
\n# Map Join \u540e\u8ddf Group By \u65f6\u54c8\u5e0c\u8868\u7684\u5185\u5b58\u5360\u6bd4，\u9ed8\u8ba4 0.3
\nhive.mapjoin.followby.map.aggr.hash.percentmemory = 0.3<\/p>\n8.2.4 \u9002\u7528\u6761\u4ef6<\/h4>\n
\n
8.2.5 \u6027\u80fd\u5bf9\u6bd4<\/h4>\n
\nMap Join ： 100GB\u5927\u8868 JOIN 20MB\u5c0f\u8868 -> \u65e0 Shuffle -> \u8017\u65f6 5min
\n\u5982\u679c\u5927\u8868\u7684\u6570\u636e\u91cf\u8d8a\u5927，\u8fd9\u4e2a\u5dee\u8ddd\u4f1a\u8d8a\u6765\u8d8a\u5927，\u7279\u522b\u65f6\u9488\u5bf9\u4e8e\u5173\u8054 \u7701\u4efd\u8868(\u6570\u636e\u91cf\u5c0f) \u65f6，\u4f7f\u7528 Map Join \u80fd\u591f\u6781\u5927\u7684\u63d0\u5347\u6548\u7387<\/p>\n8.3 Bucket Map Join<\/h3>\n
8.3.1 \u539f\u7406<\/h4>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181712-6a74cfa860fe0.png\")
<\/p>\n8.3.2 \u793a\u4f8bSQL<\/h4>\n
\nCREATE<\/span> TABLE<\/span> orders_bucketed (<\/span>
\n order_id BIGINT<\/span>,<\/span>
\n user_id BIGINT<\/span>,<\/span>
\n amount DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n order_time TIMESTAMP<\/span>
\n)<\/span>
\nCLUSTERED<\/span> BY<\/span> (<\/span>user_id)<\/span> INTO<\/span> 8<\/span> BUCKETS
\nSTORED AS<\/span> ORC;<\/span><\/p>\n
\nCREATE<\/span> TABLE<\/span> users_bucketed (<\/span>
\n user_id BIGINT<\/span>,<\/span>
\n user_name STRING,<\/span>
\n city STRING,<\/span>
\n register_time TIMESTAMP<\/span>
\n)<\/span>
\nCLUSTERED<\/span> BY<\/span> (<\/span>user_id)<\/span> INTO<\/span> 8<\/span> BUCKETS
\nSTORED AS<\/span> ORC;<\/span><\/p>\n
\nINSERT<\/span> OVERWRITE TABLE<\/span> orders_bucketed
\nSELECT<\/span> order_id,<\/span> user_id,<\/span> amount,<\/span> order_time FROM<\/span> orders;<\/span><\/p>\n
\nSELECT<\/span> user_id,<\/span> user_name,<\/span> city,<\/span> register_time FROM<\/span> users;<\/span><\/p>\n
\nSET<\/span> hive.<\/span>optimize<\/span>.<\/span>bucketmapjoin =<\/span> true<\/span>;<\/span>
\nSET<\/span> hive.<\/span>auto.<\/span>convert<\/span>.<\/span>join<\/span> =<\/span> true<\/span>;<\/span><\/p>\n
\nSELECT<\/span> \/*+ MAPJOIN(u) *\/<\/span>
\n o.<\/span>order_id,<\/span>
\n o.<\/span>amount,<\/span>
\n u.<\/span>user_name,<\/span>
\n u.<\/span>city
\nFROM<\/span> orders_bucketed o
\nJOIN<\/span> users_bucketed u ON<\/span> o.<\/span>user_id =<\/span> u.<\/span>user_id;<\/span><\/p>\n8.3.3 \u53c2\u6570\u8bbe\u7f6e<\/h4>\n
\nhive.optimize.bucketmapjoin = false
\n# \u9700\u8981\u540c\u65f6\u5f00\u542f Map Join \u81ea\u52a8\u8f6c\u5316，\u9ed8\u8ba4 true
\nhive.auto.convert.join = true<\/p>\n8.3.4 \u9002\u7528\u6761\u4ef6（\u5fc5\u987b\u6ee1\u8db3）<\/h4>\n
8.4 Sort Merge Bucketed Map Join (SMB Join)<\/h3>\n
8.4.1 \u539f\u7406<\/h4>\n
![\"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/08\/20260806181712-6a74cfa8ca2f2.png\")
<\/p>\n8.4.2 \u793a\u4f8bSQL<\/h4>\n
\nCREATE<\/span> TABLE<\/span> orders_smb (<\/span>
\n order_id BIGINT<\/span>,<\/span>
\n user_id BIGINT<\/span>,<\/span>
\n amount DECIMAL<\/span>(<\/span>10<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n order_time TIMESTAMP<\/span>
\n)<\/span>
\nCLUSTERED<\/span> BY<\/span> (<\/span>user_id)<\/span> SORTED BY<\/span> (<\/span>user_id)<\/span> INTO<\/span> 8<\/span> BUCKETS
\nSTORED AS<\/span> ORC;<\/span><\/p>\n
\n user_id BIGINT<\/span>,<\/span>
\n user_name STRING,<\/span>
\n city STRING,<\/span>
\n register_time TIMESTAMP<\/span>
\n)<\/span>
\nCLUSTERED<\/span> BY<\/span> (<\/span>user_id)<\/span> SORTED BY<\/span> (<\/span>user_id)<\/span> INTO<\/span> 8<\/span> BUCKETS
\nSTORED AS<\/span> ORC;<\/span><\/p>\n
\nINSERT<\/span> OVERWRITE TABLE<\/span> orders_smb
\nSELECT<\/span> order_id,<\/span> user_id,<\/span> amount,<\/span> order_time FROM<\/span> orders;<\/span><\/p>\n
\nSELECT<\/span> user_id,<\/span> user_name,<\/span> city,<\/span> register_time FROM<\/span> users;<\/span><\/p>\n