Hive 實現增量更新：基於 FULL OUTER JOIN 與 UNION ALL 的最佳實踐

業務場景

保險客戶表 customer (id, age, name) 按天分區。每日增量數據置於 customer_temp，需合併更新：新增客戶入庫、現有客戶更新屬性、刪除邏輯暫不考慮。

建表與初始化

CREATE TABLE customer (
  id INT, age TINYINT, name STRING
) PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/home/hadoop/hivetestdata/customer.txt'
INTO TABLE customer PARTITION (dt='201506');

增量更新核心邏輯

採用 UNION ALL + LEFT OUTER JOIN 模式，避免 FULL OUTER JOIN 在 Hive 中的效能與語義陷阱：

SELECT * FROM customer_temp
UNION ALL
SELECT a.* FROM customer a
LEFT OUTER JOIN customer_temp b ON a.id = b.id
WHERE b.id IS NULL;

邏輯：customer_temp 為最新全量（含更新與新增），customer 中 不在 temp 的舊記錄 保留。結果即為合併後最新全量。

常見錯誤寫法分析

網路上常見 COALESCE + FULL OUTER JOIN 寫法：

SELECT customer.id,
       COALESCE(customer_temp.age, customer.age),
       customer.name,
       COALESCE(customer_temp.dt, customer.dt)
FROM customer_temp
FULL OUTER JOIN customer ON customer_temp.id = customer.id;

缺陷：若 customer_temp 有新客戶 (id=5)，customer.id 為 NULL，導致結果主鍵缺失，下游處理異常。

總結與建議

• 推薦 UNION ALL + LEFT JOIN 排除法，語義清晰、效能穩定。
• 若刪除需求出現，可在 temp 標記刪除旗標，合併時過濾。
• 大數據量場景建議配合 MAPJOIN (小表載入記憶體) 或 Bucket Map Join 優化。