Hive 優化器演進深度解析：MapJoin 自動化、Star Join 優化與 SMB Map Join 新特性

Hive 0.11 優化器關鍵增強

1. 小表 Hash 表載入記憶體，僅掃描大表 (MapJoin)。
2. 星型模型多維 Join 優化。
3. 大幅減少 Hint 依賴，自動轉換 MapJoin。
4. MapJoin 鏈合併為單一 Map 階段。

星型模型與雪花模型回顧

• 星型：事實表直接連接所有維表，反正規化，查詢少 Join、效能高、ETL 易並行。
• 雪花：維表進一步正規化，儲存省、查詢多 Join、ETL 複雜。
• 實務中星型更常見、效能更優。

MapJoin 演進歷程

0.11 前

• hive.auto.convert.join=false 預設關閉。
• 需 Hint：/*+ MAPJOIN(t) */ 或手動開啟參數。
• 限制：單 Key 多表、Hint 不一定生效、鏈式 MapJoin 不合併、Hash 表客戶端生成再分發。

0.11+ 自動化優化

• hive.auto.convert.join=true (預設開啟)。
• hive.auto.convert.join.noconditionaltask=true + noconditionaltask.size=10MB：n-1 張小表總 Hash 大小閾值內自動合併為單一 MapJoin。
• 外連結、笛卡爾積不可用 MapJoin。

Star Join 多維優化

多維表 Join 事實表時，自動識別星型結構，將多個 MapJoin 合併為單一 Map-only Job，事實表僅掃描一次，大幅降低 I/O。

SELECT COUNT(*) FROM store_sales ss
JOIN time_dim t ON ss.ss_sold_time_sk = t.t_time_sk
JOIN date_dim d ON ss.ss_sold_date_sk = d.d_date_sk
WHERE t.t_hour=8 AND d.d_year=2002;

自動轉為單 MapJoin，無需 Hint。

SMB (Sort-Merge-Bucket) Map Join

針對已分桶排序的大表 Join，避免 Shuffle，直接 Map 端合併。

SET hive.auto.convert.sortmerge.join=true;
SET hive.optimize.bucketmapjoin=true;
SET hive.optimize.bucketmapjoin.sortedmerge=true;
SET hive.auto.convert.sortmerge.join.noconditionaltask=true;

大表選擇策略可配置 (平均分區大小 / 最左表 / 表總大小)。

未來方向：Task 端生成 Hash 表

• 現狀：客戶端生成 → 上傳 HDFS → 分散式緩存分發 (網路開銷大、客戶端資源不可控)。
• 規劃：Map Task 並行讀取原始數據本地生成 Hash 表，利用數據本地性，但需注意多任務並發訪問外部源 (HBase/DB) 的壓力。

進一步優化建議

• 提高維表副本數 (dfs.replication)。
• 利用分散式緩存直接緩存維表數據。