算實(shí)踐：臨時(shí)表+循環(huán)迭代實(shí)現(xiàn)任意層級展開)
Hive SQL 寫遞歸計(jì)算一直是數(shù)倉工程師繞不過去的一道坎。業(yè)務(wù)方一句“我要看全鏈路組織架構(gòu)”或者“把這個(gè) BOM 的末級物料全部展開”落到 Hive SQL 上就是一場噩夢。MySQL 里一條WITH RECURSIVE能搞定的事在 Hive 里就是另一套邏輯。我之前用“寫死 20 層自連接”的方式湊合過一版姑且叫 1.0后來業(yè)務(wù)層級超過 20 層SQL 直接廢掉才認(rèn)真做了這套 2.0 方案。這套 2.0 的核心思路并不復(fù)雜臨時(shí)表 循環(huán)迭代每一輪只掃增量數(shù)據(jù)再用一張總表防環(huán)路、去重復(fù)。實(shí)測下來千萬級節(jié)點(diǎn)、幾十層深度的場景都能穩(wěn)定跑完更重要的是不會因?yàn)槟硞€(gè)環(huán)導(dǎo)致任務(wù)跑到天荒地老。這篇文章就完整記錄一下這套方案的設(shè)計(jì)思路、完整代碼、性能調(diào)優(yōu)和排坑實(shí)錄給還在 Hive 里硬剛遞歸的朋友們一個(gè)可以直接抄的作業(yè)。1. 為什么需要一套 2.0 方案Hive 本質(zhì)上是為分布式批處理設(shè)計(jì)的標(biāo)準(zhǔn)的 SQL 遞歸查詢能力一直不是它的強(qiáng)項(xiàng)。低版本 Hive 根本不支持WITH RECURSIVE新版本雖然借助 Calcite 加了這個(gè)語法但生產(chǎn)環(huán)境里依賴它跑大規(guī)模遞歸任務(wù)的人依然很少后面我會具體講原因。所以大部分?jǐn)?shù)倉團(tuán)隊(duì)實(shí)際上都在用“土辦法”實(shí)現(xiàn)遞歸。1.1 1.0 方案寫死層數(shù)的自連接1.0 版本的思路很直白既然不知道最深多少層那就假設(shè)一個(gè)最大值比如 20 層然后用LEFT JOIN一層層往上拼最后用UNION ALL合并所有層的結(jié)果。這種方案應(yīng)付有限的、明確已知層級的需求確實(shí)沒問題。我最早處理一個(gè)“三二級部門展開”的需求時(shí)就是這么干的。但問題也很明顯深度是寫死的。一旦需求變成“動態(tài)層級”SQL 就廢了。SQL 冗長得可怕。20 層JOIN的 SQL 要寫幾百行維護(hù)起來頭大。性能不穩(wěn)定。每一層JOIN都會對上一步的全量結(jié)果再掃一遍越到后面越慢。1.2 2.0 方案要解決的核心問題2.0 在設(shè)計(jì)之初定了幾個(gè)明確目標(biāo)支持任意深度最多只受集群資源和參數(shù)限制。每一輪迭代只處理新發(fā)現(xiàn)的增量數(shù)據(jù)而不是全量重算。防環(huán)路。不能因?yàn)?A→B→C→A 這種環(huán)導(dǎo)致死循環(huán)。每一輪結(jié)果都帶有層級標(biāo)記方便業(yè)務(wù)側(cè)按“第幾層”做過濾和分析。這套方案從設(shè)計(jì)、編碼到調(diào)優(yōu)大概花了一周落地之后穩(wěn)定跑了很久也經(jīng)歷了多次數(shù)據(jù)量和深度翻倍的考驗(yàn)。下面把方案完整拆開講。2. 遞歸計(jì)算的主流實(shí)現(xiàn)路線對比在做 2.0 之前我把當(dāng)時(shí)業(yè)界常見的幾種 Hive 遞歸方案都捋了一遍這里直接給結(jié)論方便你做技術(shù)選型。2.1 寫死層數(shù)的自連接方案就是 1.0 的做法適用于層級明確且較淺的場景。優(yōu)點(diǎn)是實(shí)現(xiàn)簡單、不依賴任何腳本缺點(diǎn)是靈活性差、性能不可控。-- 示例展開 3 層部門 SELECT t1.dep_id AS root_dep_id, t3.dep_id AS child_dep_id, CASE WHEN t2.dep_id IS NULL THEN 1 WHEN t3.dep_id IS NULL THEN 2 ELSE 3 END AS depth FROM ods_dep_rel t1 LEFT JOIN ods_dep_rel t2 ON t1.dep_id t2.parent_dep_id LEFT JOIN ods_dep_rel t3 ON t2.dep_id t3.parent_dep_id WHERE t1.parent_dep_id IS NULL;層數(shù)少的時(shí)候還能忍層數(shù)多起來這段 SQL 會膨脹到你不想維護(hù)。2.2 原生 WITH RECURSIVE 方案從 Hive 4.0 開始社區(qū)通過 Calcite 引入了原生遞歸 CTE 支持。理論上你可以寫這樣的語句WITH RECURSIVE dep_tree AS ( SELECT dep_id, parent_dep_id, dep_name, 0 AS loop_id FROM ods_dep_rel WHERE parent_dep_id IS NULL UNION ALL SELECT c.dep_id, c.parent_dep_id, c.dep_name, t.loop_id 1 FROM dep_tree t JOIN ods_dep_rel c ON t.dep_id c.parent_dep_id ) SELECT * FROM dep_tree;但在實(shí)際生產(chǎn)環(huán)境中我對這套語法保持謹(jǐn)慎態(tài)度。一方面是因?yàn)楹芏嗥髽I(yè)的 Hive 還是 3.x 版本另一方面是原生遞歸 CTE 在底層執(zhí)行時(shí)容易出現(xiàn)性能抖動資源消耗不太好預(yù)估。小數(shù)據(jù)量驗(yàn)證邏輯沒問題一旦上了十億級節(jié)點(diǎn)還是老老實(shí)實(shí)用迭代方案穩(wěn)妥。2.3 臨時(shí)表 循環(huán)迭代方案這就是 2.0 的核心路線。思路是用一張“結(jié)果總表”保存已經(jīng)找到的所有節(jié)點(diǎn)再用一張“增量表”保存本輪新發(fā)現(xiàn)的節(jié)點(diǎn)每輪迭代從增量表出發(fā)向外擴(kuò)展用總表做去重和防環(huán)。這個(gè)方案的優(yōu)勢很明顯每一輪只掃增量數(shù)據(jù)而不是全量數(shù)據(jù)性能可控深度上限可以人為配置邏輯完全透明每輪跑了多少數(shù)據(jù)一目了然。缺點(diǎn)是需要寫 Shell 腳本來控制循環(huán)對開發(fā)者的工程能力有一定要求。2.4 自定義 UDF / Transform 腳本方案還可以用 Python 腳本配合TRANSFORM語法在腳本內(nèi)部實(shí)現(xiàn)遞歸邏輯。這種方式邏輯自由度最高但受限于單機(jī)內(nèi)存和腳本執(zhí)行模型數(shù)據(jù)量大時(shí)容易成為瓶頸。我一般只在“遞歸內(nèi)部有復(fù)雜業(yè)務(wù)規(guī)則”時(shí)才會考慮它普通展開場景用循環(huán)迭代就夠了。下面用一張表把這幾種路線的特點(diǎn)匯總一下方案深度上限實(shí)現(xiàn)復(fù)雜度性能表現(xiàn)適用場景寫死層數(shù)自連接固定層數(shù)低層數(shù)越多越差淺層級、需求穩(wěn)定原生 WITH RECURSIVE無低受引擎影響大小數(shù)據(jù)量、Hive 4.0臨時(shí)表循環(huán)迭代可配置中可控需調(diào)優(yōu)企業(yè)級大規(guī)模數(shù)據(jù)Python UDF 遞歸受單機(jī)內(nèi)存限制高中低小數(shù)據(jù)量、復(fù)雜規(guī)則我最終選擇的是“臨時(shí)表 循環(huán)迭代”下面詳細(xì)講它的設(shè)計(jì)邏輯。3. 2.0 方案核心設(shè)計(jì)雙表迭代框架3.1 結(jié)果總表 增量表的協(xié)同工作方式整套方案的核心是兩個(gè)表結(jié)果總表TOTAL和增量表INC。每一輪的迭代動作可以做如下拆解從上一輪的增量表中取數(shù)據(jù)去關(guān)聯(lián)子節(jié)點(diǎn)。關(guān)聯(lián)出來的新節(jié)點(diǎn)和結(jié)果總表做LEFT JOIN如果已經(jīng)存在則過濾掉。過濾后的數(shù)據(jù)寫入新的增量表。將新增量合并進(jìn)結(jié)果總表。如果新增量為空或者達(dá)到了設(shè)定的最大迭代次數(shù)循環(huán)終止。這個(gè)流程里最關(guān)鍵的優(yōu)化點(diǎn)在于每輪迭代的輸入是“上一輪的增量”而不是“歷史全量”。這樣一來數(shù)據(jù)增長不是隨層數(shù)線性疊加的整體掃描量小很多這正是 2.0 比 1.0 性能好的根本原因。3.2 環(huán)路檢測與終止條件設(shè)計(jì)防環(huán)路是遞歸計(jì)算里最不能忽略的一環(huán)。環(huán)路指的是數(shù)據(jù)里出現(xiàn)了 A→B→C→A 這種循環(huán)引用。在雙表迭代的設(shè)計(jì)下環(huán)路其實(shí)已經(jīng)天然被結(jié)果總表擋住了任何節(jié)點(diǎn)只要被訪問過再次出現(xiàn)時(shí)會被總表過濾掉。所以迭代不會因?yàn)榄h(huán)路而無限循環(huán)最多遍歷完全部節(jié)點(diǎn)就自然會停下來。不過為了更早地剪枝節(jié)省無謂的 IO還可以在迭代 SQL 里加一個(gè)路徑字段用find_in_set判斷當(dāng)前節(jié)點(diǎn)是否已經(jīng)出現(xiàn)在路徑上。這種做法的代價(jià)是每行都要攜帶一段路徑字符串層級很深時(shí)字符串會很長影響性能。我的經(jīng)驗(yàn)是一般組織架構(gòu)類數(shù)據(jù)層級小于 20 層用路徑檢測沒問題如果是 BOM 展開這種層級可能上百的場景僅靠總表過濾就夠了路徑檢測不做也罷。下面是我在實(shí)際腳本里用的兩個(gè)終止條件本輪增量為空說明已經(jīng)遞歸到底了正常結(jié)束。迭代次數(shù)達(dá)到預(yù)設(shè)的MAX_LOOP說明可能存在異常數(shù)據(jù)比如深層次環(huán)強(qiáng)制退出并告警。3.3 數(shù)據(jù)傾斜與發(fā)散節(jié)點(diǎn)的處理思路遞歸迭代中最容易出性能問題的是“發(fā)散節(jié)點(diǎn)”。舉個(gè)例子某個(gè)根部門下面掛了十萬個(gè)葉子部門那某一輪迭代中這十萬條子節(jié)點(diǎn)記錄會全部由這一個(gè)根部門產(chǎn)生。在 Hive 的分布式計(jì)算模型下這意味著某一個(gè) Reduce 要處理巨大的數(shù)據(jù)量跑得比其它所有 Reduce 都慢也就是典型的數(shù)據(jù)傾斜。處理思路有幾種對關(guān)聯(lián)鍵做加鹽處理把大節(jié)點(diǎn)的數(shù)據(jù)打散到多個(gè) Reduce。拆根并行。如果業(yè)務(wù)允許把不同的根節(jié)點(diǎn)分組拆成多個(gè)獨(dú)立任務(wù)并行跑。如果增量表和關(guān)聯(lián)表都不大比如小于 2GB直接加MAPJOIN提示把關(guān)聯(lián)表加載到內(nèi)存里繞開 Shuffle。另外還有一個(gè)容易忽略的問題臟數(shù)據(jù)導(dǎo)致的“假發(fā)散”。如果源表里存在大量parent_dep_id為空字符串或特殊字符的孤兒節(jié)點(diǎn)它們會在某一輪被同時(shí)關(guān)聯(lián)出來造成一輪迭代的數(shù)據(jù)量暴漲。建表時(shí)對這種情況要有心理準(zhǔn)備清洗數(shù)據(jù)的時(shí)候要一并處理。4. 實(shí)操全流程完整跑通一個(gè)部門樹遞歸展開下面用一個(gè)經(jīng)典場景——部門樹展開——走一遍完整流程。假設(shè)有一張部門關(guān)系表ods_dep_rel我把它簡化成下面這種結(jié)構(gòu)CREATE TABLE IF NOT EXISTS ods_dep_rel ( dep_id STRING COMMENT 部門id, parent_dep_id STRING COMMENT 上級部門id, dep_name STRING COMMENT 部門名稱, project_id STRING COMMENT 業(yè)務(wù)線標(biāo)識 ) COMMENT 部門關(guān)系原始表 PARTITIONED BY (dt STRING COMMENT 數(shù)據(jù)日期) STORED AS ORC;需求非常明確給定業(yè)務(wù)線project_id找出每個(gè)根部門下所有層級的子部門并記錄每個(gè)子部門相對于根部門的層級深度。4.1 初始化 SQL生成第 0 層數(shù)據(jù)第 0 層就是根節(jié)點(diǎn)。實(shí)際業(yè)務(wù)里根節(jié)點(diǎn)的定義可能不同有的是parent_dep_id IS NULL有的是parent_dep_id 還有的是設(shè)置了專門的根標(biāo)識字段這里以最常見的parent_dep_id IS NULL為例。-- init_dep_tree.sql INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT dep_id AS root_dep_id, dep_id AS dep_id, dep_name AS dep_name, 0 AS loop_id, cast(dep_id AS STRING) AS path_str FROM ods_dep_rel WHERE dt ${hiveconf:dt} AND project_id ${hiveconf:project_id} AND parent_dep_id IS NULL;這輪產(chǎn)出的tmp_dep_tree_prev表既是初始結(jié)果集也是第一輪迭代的增量輸入。-- 創(chuàng)建結(jié)果總表如果不存在 CREATE TABLE IF NOT EXISTS tmp_dep_tree_total ( root_dep_id STRING, dep_id STRING, dep_name STRING, loop_id INT, path_str STRING ) STORED AS ORC;然后把初始數(shù)據(jù)寫入總表INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_prev;注意這里用的是INSERT INTO因?yàn)榭偙硎侵疠喿芳拥摹?.2 單輪迭代 SQL增量展開的核心邏輯每一輪迭代的核心 SQL 是同一套邏輯只是輸入表不同。落到腳本里我寫成參數(shù)化的 SQL 文件-- expand_once.sql -- 輸入tmp_dep_tree_prev上一輪增量 -- 輸出tmp_dep_tree_cur本輪新增量 INSERT OVERWRITE TABLE tmp_dep_tree_cur SELECT t.root_dep_id AS root_dep_id, c.dep_id AS dep_id, c.dep_name AS dep_name, t.loop_id 1 AS loop_id, concat_ws(,, t.path_str, c.dep_id) AS path_str FROM tmp_dep_tree_prev t JOIN ods_dep_rel c ON t.dep_id c.parent_dep_id LEFT JOIN tmp_dep_tree_total total ON c.dep_id total.dep_id AND t.root_dep_id total.root_dep_id WHERE c.dt ${hiveconf:dt} AND c.project_id ${hiveconf:project_id} AND total.dep_id IS NULL AND NOT find_in_set(c.dep_id, t.path_str) 0;這段 SQL 里最核心的是三個(gè)地方JOIN以上一輪增量表的dep_id去匹配源表的parent_dep_id找到所有直接子節(jié)點(diǎn)。LEFT JOIN ... WHERE total.dep_id IS NULL過濾掉已經(jīng)在結(jié)果總表里出現(xiàn)過的節(jié)點(diǎn)這就是上面說的防環(huán)路、防重復(fù)。find_in_set(c.dep_id, t.path_str)從路徑上再做一次檢查防止環(huán)路在極深路徑下帶來額外的重復(fù)計(jì)算。每個(gè)細(xì)節(jié)都很重要。LEFT JOIN的過濾條件必須同時(shí)帶root_dep_id否則不同根節(jié)點(diǎn)下出現(xiàn)同一個(gè)部門 ID 時(shí)會被錯誤過濾掉導(dǎo)致結(jié)果缺失。4.3 Shell 循環(huán)控制腳本讓迭代自動收斂有了初始化 SQL 和單輪迭代 SQL剩下就是把循環(huán)串起來。我用的是 Bash 腳本 hive -f的方式每一輪迭代完成后檢查增量條數(shù)決定繼續(xù)還是終止。#!/bin/bash # 遞歸計(jì)算 2.0部門樹自動展開 set -e DT2024-01-01 PROJECT_IDP001 MAX_LOOP50 CUR_LOOP0 HIVE_CONF-hiveconf dt${DT} -hiveconf project_id${PROJECT_ID} # 1. 初始化第 0 層 hive ${HIVE_CONF} -f ./init_dep_tree.sql hive ${HIVE_CONF} -e INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_prev; # 2. 循環(huán)迭代 while [ ${CUR_LOOP} -lt ${MAX_LOOP} ]; do echo Loop: ${CUR_LOOP} # 執(zhí)行單輪擴(kuò)展 hive ${HIVE_CONF} -f ./expand_once.sql # 統(tǒng)計(jì)本輪增量行數(shù) INC_CNT$(hive -S ${HIVE_CONF} -e SELECT count(1) FROM tmp_dep_tree_cur; | tr -d ) echo Increment count: ${INC_CNT} if [ ${INC_CNT} -eq 0 ]; then echo No new increment, recursion finished. break fi # 合并增量到結(jié)果總表 hive ${HIVE_CONF} -e INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_cur; # 輪轉(zhuǎn)表當(dāng)前增量變?yōu)橄乱惠喌妮斎?hive ${HIVE_CONF} -e INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT * FROM tmp_dep_tree_cur; CUR_LOOP$((CUR_LOOP 1)) done # 3. 輸出最終結(jié)果 hive ${HIVE_CONF} -e SELECT root_dep_id, dep_id, dep_name, loop_id FROM tmp_dep_tree_total ORDER BY root_dep_id, loop_id; 這段腳本有三個(gè)經(jīng)驗(yàn)點(diǎn)值得分享tr -d 是必要的。hive -S -e輸出的count結(jié)果往往帶一些空白字符不清理直接做數(shù)值比較會出問題。用INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT * FROM tmp_dep_tree_cur做表切換而不是TRUNCATE后原地插入是為了保持上一輪數(shù)據(jù)直到本輪完全成功。一旦中間某一步失敗舊的增量數(shù)據(jù)還在排查和恢復(fù)都會容易很多。循環(huán)里沒必要用break后再單獨(dú)把結(jié)果導(dǎo)出去直接到最后統(tǒng)一輸出即可。4.4 結(jié)果校驗(yàn)確認(rèn)遞歸沒有算錯遞歸算完了怎么確認(rèn)結(jié)果對不對我的經(jīng)驗(yàn)是至少做兩層校驗(yàn)。第一層總數(shù)校驗(yàn)。用源表跑一個(gè)不依賴遞歸的聚合查詢比如每個(gè)部門的直接子部門數(shù)再和自己算出來的結(jié)果做交叉比對。如果源表本身有“該部門所有后代部門數(shù)”這樣的冗余字段校驗(yàn)會更直接。第二層層級校驗(yàn)。隨機(jī)挑幾個(gè)已知深度的部門對照結(jié)果表里的loop_id確認(rèn)深度是否正確。這一步雖然樸素但能有效發(fā)現(xiàn)初始化時(shí)根節(jié)點(diǎn)定義錯誤導(dǎo)致的全盤偏移。-- 校驗(yàn)示例檢查是否有節(jié)點(diǎn)的 loop_id 與路徑深度不一致 -- 正常情況下 path_str 里逗號的數(shù)量加 1 應(yīng)該等于 loop_id SELECT root_dep_id, dep_id, loop_id, path_str, (LENGTH(path_str) - LENGTH(REPLACE(path_str, ,, )) 1) AS path_depth FROM tmp_dep_tree_total WHERE loop_id ! (LENGTH(path_str) - LENGTH(REPLACE(path_str, ,, )) 1);這個(gè) SQL 利用了逗號計(jì)數(shù)的技巧很直觀。有經(jīng)驗(yàn)的讀者可能已經(jīng)想到這其實(shí)也驗(yàn)證了路徑拼接邏輯是否正確。5. 性能調(diào)優(yōu)與踩坑記錄2.0 方案里踩過的坑一點(diǎn)也不比業(yè)務(wù)邏輯少。選幾個(gè)最有代表性的寫出來。5.1 迭代輪次過多導(dǎo)致的小文件問題每一輪迭代都會生成一批新的 ORC 文件如果最大迭代深度是 30 層結(jié)果總表里會累積很多小文件。時(shí)間久了總表在做LEFT JOIN過濾時(shí)甚至?xí)驗(yàn)樵獢?shù)據(jù)膨脹而變慢。我的處理方式是在腳本結(jié)束后對結(jié)果總表做一次合并壓縮。INSERT OVERWRITE TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_total DISTRIBUTE BY root_dep_id;DISTRIBUTE BY root_dep_id會讓同一個(gè)根節(jié)點(diǎn)的數(shù)據(jù)落到同一個(gè) Reduce不僅合并了小文件還能讓后續(xù)查詢按根節(jié)點(diǎn)過濾時(shí)更快。5.2 大增量 join 時(shí)的任務(wù)卡頓當(dāng)某一輪增量特別大比如某個(gè)中間層節(jié)點(diǎn)數(shù)量暴漲時(shí)JOIN過程容易出現(xiàn)長尾。我踩過最厲害的一次是在第 4 層迭代時(shí)單個(gè) Reduce 跑到 3 小時(shí)沒結(jié)束。排查之后發(fā)現(xiàn)是數(shù)據(jù)傾斜一個(gè)超大父節(jié)點(diǎn)帶了幾十萬條子節(jié)點(diǎn)記錄全部路由到了同一個(gè) Reduce。解決方案是在迭代 SQL 的JOIN關(guān)聯(lián)鍵上做前綴加鹽或者直接把源表按parent_dep_id分桶。分桶后JOIN的 Shuffle 數(shù)據(jù)量會小很多實(shí)測性能提升非常明顯。不過加鹽方案會改變關(guān)聯(lián)邏輯對非傾斜節(jié)點(diǎn)來說反而增加計(jì)算量。我的建議是先通過COUNT按parent_dep_id聚合確認(rèn)傾斜節(jié)點(diǎn)的量級再決定是否加鹽不要盲目優(yōu)化。5.3 分區(qū)參數(shù)穿透導(dǎo)致的串?dāng)?shù)問題這個(gè)坑很隱蔽。在源表ods_dep_rel存在多分區(qū)的情況下如果迭代 SQL 里忘記寫c.dt ${hiveconf:dt}那么JOIN會掃描全分區(qū)。某一次我切換日期參數(shù)后忘了清理老的臨時(shí)表結(jié)果遞歸結(jié)果里混了上一個(gè)日期的歷史數(shù)據(jù)排查了很久。從那以后我養(yǎng)成了一個(gè)習(xí)慣所有迭代 SQL 的WHERE條件里源表分區(qū)條件必須寫全無論哪個(gè)環(huán)境執(zhí)行都能保證只讀當(dāng)天數(shù)據(jù)。5.4 常見報(bào)錯速查表報(bào)錯信息或現(xiàn)象可能原因解決辦法迭代一直不停止達(dá)到 MAX_LOOP存在深層次環(huán)路且總表防環(huán)邏輯有問題檢查LEFT JOIN的關(guān)聯(lián)條件是否帶了root_dep_id檢查源數(shù)據(jù)是否有自關(guān)聯(lián)某一輪增量數(shù)據(jù)暴漲臟數(shù)據(jù)導(dǎo)致發(fā)散節(jié)點(diǎn)或孤兒節(jié)點(diǎn)大量掛載清洗源數(shù)據(jù)剔除parent_dep_id為空的孤兒記錄對關(guān)聯(lián)鍵做分桶結(jié)果總表小文件過多每輪迭代產(chǎn)生新文件未合并結(jié)束后用INSERT OVERWRITE ... DISTRIBUTE BY root_dep_id合并count(1)判斷不準(zhǔn)導(dǎo)致提前退出hive -S -e返回字符串帶空格用tr -d 清理后再做比較不同根節(jié)點(diǎn)下相同部門被過濾LEFT JOIN條件漏掉root_dep_id補(bǔ)上AND t.root_dep_id total.root_dep_id定位不到數(shù)據(jù)問題臨時(shí)表太多搞不清哪張是上一輪數(shù)據(jù)固定使用prev/cur輪轉(zhuǎn)命名不要動態(tài)拼接表名6. 這套方案還能怎么擴(kuò)展如果只是做部門樹展開這套腳本已經(jīng)夠用了。但遞歸計(jì)算的場景遠(yuǎn)不止組織架構(gòu)。這里列幾個(gè)我實(shí)際做過的擴(kuò)展方向。6.1 BOM 物料多級展開BOM 展開和部門樹最大的區(qū)別在于子節(jié)點(diǎn)關(guān)聯(lián)父節(jié)點(diǎn)時(shí)還帶一個(gè)“用量”字段遞歸展開時(shí)要把路徑上的用量做乘積最終得到每一個(gè)末級物料的累計(jì)需求量。實(shí)現(xiàn)時(shí)只需要在迭代 SQL 的SELECT子句里多帶一個(gè)用量字段合并時(shí)做累乘即可。防環(huán)和終止邏輯完全復(fù)用。6.2 關(guān)系圖譜中的多度人脈查找比如“找出某個(gè)用戶 3 度以內(nèi)的所有關(guān)聯(lián)用戶”。這個(gè)場景本質(zhì)上就是限制深度的遞歸查找只需要在循環(huán)控制腳本里把MAX_LOOP設(shè)為 3并且在初始化 SQL 里固定好起始用戶即可。6.3 數(shù)據(jù)血緣追蹤數(shù)據(jù)倉庫的表和字段之間存在“上游依賴”關(guān)系想查某個(gè)指標(biāo)字段的完整血緣鏈路可以用同樣的迭代框架把“父節(jié)點(diǎn)”換成“上游字段”把“子節(jié)點(diǎn)”換成“下游字段”。血緣數(shù)據(jù)的層級往往很深而且環(huán)路的可能性比組織架構(gòu)大不少這時(shí)候雙表防環(huán)機(jī)制的可靠性就顯得格外重要。最后說點(diǎn)實(shí)在的這套 2.0 方案做完之后我再也沒有因?yàn)椤斑f歸查不出來”被業(yè)務(wù)方找過。回看整個(gè)優(yōu)化過程最值得投入精力的其實(shí)不是循環(huán)腳本本身而是對增量驅(qū)動和防環(huán)機(jī)制的理解。很多做數(shù)據(jù)開發(fā)的朋友一聽到 Hive 遞歸就想著去裝插件、換引擎其實(shí)標(biāo)準(zhǔn) Hive 的臨時(shí)表迭代方案處理絕大多數(shù)生產(chǎn)需求都足夠了。如果看完這篇文章你只想記住一個(gè)點(diǎn)那我建議記住這句迭代 增量表 結(jié)果總表過濾是 Hive 里做遞歸的通用范式。這個(gè)范式一旦建立起來不管遇到組織架構(gòu)、BOM 展開還是關(guān)系圖譜都能快速套用最多就是改改表結(jié)構(gòu)、調(diào)調(diào)終止條件而已。下一次再有人跟你說“Hive 做不了遞歸”你可以把這篇文章甩給他。 Hive遞歸計(jì)算實(shí)踐：臨時(shí)表+循環(huán)迭代實(shí)現(xiàn)任意層級展開
Hive SQL 寫遞歸計(jì)算一直是數(shù)倉工程師繞不過去的一道坎。業(yè)務(wù)方一句“我要看全鏈路組織架構(gòu)”或者“把這個(gè) BOM 的末級物料全部展開”落到 Hive SQL 上就是一場噩夢。MySQL 里一條WITH RECURSIVE能搞定的事在 Hive 里就是另一套邏輯。我之前用“寫死 20 層自連接”的方式湊合過一版姑且叫 1.0后來業(yè)務(wù)層級超過 20 層SQL 直接廢掉才認(rèn)真做了這套 2.0 方案。這套 2.0 的核心思路并不復(fù)雜臨時(shí)表 循環(huán)迭代每一輪只掃增量數(shù)據(jù)再用一張總表防環(huán)路、去重復(fù)。實(shí)測下來千萬級節(jié)點(diǎn)、幾十層深度的場景都能穩(wěn)定跑完更重要的是不會因?yàn)槟硞€(gè)環(huán)導(dǎo)致任務(wù)跑到天荒地老。這篇文章就完整記錄一下這套方案的設(shè)計(jì)思路、完整代碼、性能調(diào)優(yōu)和排坑實(shí)錄給還在 Hive 里硬剛遞歸的朋友們一個(gè)可以直接抄的作業(yè)。1. 為什么需要一套 2.0 方案Hive 本質(zhì)上是為分布式批處理設(shè)計(jì)的標(biāo)準(zhǔn)的 SQL 遞歸查詢能力一直不是它的強(qiáng)項(xiàng)。低版本 Hive 根本不支持WITH RECURSIVE新版本雖然借助 Calcite 加了這個(gè)語法但生產(chǎn)環(huán)境里依賴它跑大規(guī)模遞歸任務(wù)的人依然很少后面我會具體講原因。所以大部分?jǐn)?shù)倉團(tuán)隊(duì)實(shí)際上都在用“土辦法”實(shí)現(xiàn)遞歸。1.1 1.0 方案寫死層數(shù)的自連接1.0 版本的思路很直白既然不知道最深多少層那就假設(shè)一個(gè)最大值比如 20 層然后用LEFT JOIN一層層往上拼最后用UNION ALL合并所有層的結(jié)果。這種方案應(yīng)付有限的、明確已知層級的需求確實(shí)沒問題。我最早處理一個(gè)“三二級部門展開”的需求時(shí)就是這么干的。但問題也很明顯深度是寫死的。一旦需求變成“動態(tài)層級”SQL 就廢了。SQL 冗長得可怕。20 層JOIN的 SQL 要寫幾百行維護(hù)起來頭大。性能不穩(wěn)定。每一層JOIN都會對上一步的全量結(jié)果再掃一遍越到后面越慢。1.2 2.0 方案要解決的核心問題2.0 在設(shè)計(jì)之初定了幾個(gè)明確目標(biāo)支持任意深度最多只受集群資源和參數(shù)限制。每一輪迭代只處理新發(fā)現(xiàn)的增量數(shù)據(jù)而不是全量重算。防環(huán)路。不能因?yàn)?A→B→C→A 這種環(huán)導(dǎo)致死循環(huán)。每一輪結(jié)果都帶有層級標(biāo)記方便業(yè)務(wù)側(cè)按“第幾層”做過濾和分析。這套方案從設(shè)計(jì)、編碼到調(diào)優(yōu)大概花了一周落地之后穩(wěn)定跑了很久也經(jīng)歷了多次數(shù)據(jù)量和深度翻倍的考驗(yàn)。下面把方案完整拆開講。2. 遞歸計(jì)算的主流實(shí)現(xiàn)路線對比在做 2.0 之前我把當(dāng)時(shí)業(yè)界常見的幾種 Hive 遞歸方案都捋了一遍這里直接給結(jié)論方便你做技術(shù)選型。2.1 寫死層數(shù)的自連接方案就是 1.0 的做法適用于層級明確且較淺的場景。優(yōu)點(diǎn)是實(shí)現(xiàn)簡單、不依賴任何腳本缺點(diǎn)是靈活性差、性能不可控。-- 示例展開 3 層部門 SELECT t1.dep_id AS root_dep_id, t3.dep_id AS child_dep_id, CASE WHEN t2.dep_id IS NULL THEN 1 WHEN t3.dep_id IS NULL THEN 2 ELSE 3 END AS depth FROM ods_dep_rel t1 LEFT JOIN ods_dep_rel t2 ON t1.dep_id t2.parent_dep_id LEFT JOIN ods_dep_rel t3 ON t2.dep_id t3.parent_dep_id WHERE t1.parent_dep_id IS NULL;層數(shù)少的時(shí)候還能忍層數(shù)多起來這段 SQL 會膨脹到你不想維護(hù)。2.2 原生 WITH RECURSIVE 方案從 Hive 4.0 開始社區(qū)通過 Calcite 引入了原生遞歸 CTE 支持。理論上你可以寫這樣的語句WITH RECURSIVE dep_tree AS ( SELECT dep_id, parent_dep_id, dep_name, 0 AS loop_id FROM ods_dep_rel WHERE parent_dep_id IS NULL UNION ALL SELECT c.dep_id, c.parent_dep_id, c.dep_name, t.loop_id 1 FROM dep_tree t JOIN ods_dep_rel c ON t.dep_id c.parent_dep_id ) SELECT * FROM dep_tree;但在實(shí)際生產(chǎn)環(huán)境中我對這套語法保持謹(jǐn)慎態(tài)度。一方面是因?yàn)楹芏嗥髽I(yè)的 Hive 還是 3.x 版本另一方面是原生遞歸 CTE 在底層執(zhí)行時(shí)容易出現(xiàn)性能抖動資源消耗不太好預(yù)估。小數(shù)據(jù)量驗(yàn)證邏輯沒問題一旦上了十億級節(jié)點(diǎn)還是老老實(shí)實(shí)用迭代方案穩(wěn)妥。2.3 臨時(shí)表 循環(huán)迭代方案這就是 2.0 的核心路線。思路是用一張“結(jié)果總表”保存已經(jīng)找到的所有節(jié)點(diǎn)再用一張“增量表”保存本輪新發(fā)現(xiàn)的節(jié)點(diǎn)每輪迭代從增量表出發(fā)向外擴(kuò)展用總表做去重和防環(huán)。這個(gè)方案的優(yōu)勢很明顯每一輪只掃增量數(shù)據(jù)而不是全量數(shù)據(jù)性能可控深度上限可以人為配置邏輯完全透明每輪跑了多少數(shù)據(jù)一目了然。缺點(diǎn)是需要寫 Shell 腳本來控制循環(huán)對開發(fā)者的工程能力有一定要求。2.4 自定義 UDF / Transform 腳本方案還可以用 Python 腳本配合TRANSFORM語法在腳本內(nèi)部實(shí)現(xiàn)遞歸邏輯。這種方式邏輯自由度最高但受限于單機(jī)內(nèi)存和腳本執(zhí)行模型數(shù)據(jù)量大時(shí)容易成為瓶頸。我一般只在“遞歸內(nèi)部有復(fù)雜業(yè)務(wù)規(guī)則”時(shí)才會考慮它普通展開場景用循環(huán)迭代就夠了。下面用一張表把這幾種路線的特點(diǎn)匯總一下方案深度上限實(shí)現(xiàn)復(fù)雜度性能表現(xiàn)適用場景寫死層數(shù)自連接固定層數(shù)低層數(shù)越多越差淺層級、需求穩(wěn)定原生 WITH RECURSIVE無低受引擎影響大小數(shù)據(jù)量、Hive 4.0臨時(shí)表循環(huán)迭代可配置中可控需調(diào)優(yōu)企業(yè)級大規(guī)模數(shù)據(jù)Python UDF 遞歸受單機(jī)內(nèi)存限制高中低小數(shù)據(jù)量、復(fù)雜規(guī)則我最終選擇的是“臨時(shí)表 循環(huán)迭代”下面詳細(xì)講它的設(shè)計(jì)邏輯。3. 2.0 方案核心設(shè)計(jì)雙表迭代框架3.1 結(jié)果總表 增量表的協(xié)同工作方式整套方案的核心是兩個(gè)表結(jié)果總表TOTAL和增量表INC。每一輪的迭代動作可以做如下拆解從上一輪的增量表中取數(shù)據(jù)去關(guān)聯(lián)子節(jié)點(diǎn)。關(guān)聯(lián)出來的新節(jié)點(diǎn)和結(jié)果總表做LEFT JOIN如果已經(jīng)存在則過濾掉。過濾后的數(shù)據(jù)寫入新的增量表。將新增量合并進(jìn)結(jié)果總表。如果新增量為空或者達(dá)到了設(shè)定的最大迭代次數(shù)循環(huán)終止。這個(gè)流程里最關(guān)鍵的優(yōu)化點(diǎn)在于每輪迭代的輸入是“上一輪的增量”而不是“歷史全量”。這樣一來數(shù)據(jù)增長不是隨層數(shù)線性疊加的整體掃描量小很多這正是 2.0 比 1.0 性能好的根本原因。3.2 環(huán)路檢測與終止條件設(shè)計(jì)防環(huán)路是遞歸計(jì)算里最不能忽略的一環(huán)。環(huán)路指的是數(shù)據(jù)里出現(xiàn)了 A→B→C→A 這種循環(huán)引用。在雙表迭代的設(shè)計(jì)下環(huán)路其實(shí)已經(jīng)天然被結(jié)果總表擋住了任何節(jié)點(diǎn)只要被訪問過再次出現(xiàn)時(shí)會被總表過濾掉。所以迭代不會因?yàn)榄h(huán)路而無限循環(huán)最多遍歷完全部節(jié)點(diǎn)就自然會停下來。不過為了更早地剪枝節(jié)省無謂的 IO還可以在迭代 SQL 里加一個(gè)路徑字段用find_in_set判斷當(dāng)前節(jié)點(diǎn)是否已經(jīng)出現(xiàn)在路徑上。這種做法的代價(jià)是每行都要攜帶一段路徑字符串層級很深時(shí)字符串會很長影響性能。我的經(jīng)驗(yàn)是一般組織架構(gòu)類數(shù)據(jù)層級小于 20 層用路徑檢測沒問題如果是 BOM 展開這種層級可能上百的場景僅靠總表過濾就夠了路徑檢測不做也罷。下面是我在實(shí)際腳本里用的兩個(gè)終止條件本輪增量為空說明已經(jīng)遞歸到底了正常結(jié)束。迭代次數(shù)達(dá)到預(yù)設(shè)的MAX_LOOP說明可能存在異常數(shù)據(jù)比如深層次環(huán)強(qiáng)制退出并告警。3.3 數(shù)據(jù)傾斜與發(fā)散節(jié)點(diǎn)的處理思路遞歸迭代中最容易出性能問題的是“發(fā)散節(jié)點(diǎn)”。舉個(gè)例子某個(gè)根部門下面掛了十萬個(gè)葉子部門那某一輪迭代中這十萬條子節(jié)點(diǎn)記錄會全部由這一個(gè)根部門產(chǎn)生。在 Hive 的分布式計(jì)算模型下這意味著某一個(gè) Reduce 要處理巨大的數(shù)據(jù)量跑得比其它所有 Reduce 都慢也就是典型的數(shù)據(jù)傾斜。處理思路有幾種對關(guān)聯(lián)鍵做加鹽處理把大節(jié)點(diǎn)的數(shù)據(jù)打散到多個(gè) Reduce。拆根并行。如果業(yè)務(wù)允許把不同的根節(jié)點(diǎn)分組拆成多個(gè)獨(dú)立任務(wù)并行跑。如果增量表和關(guān)聯(lián)表都不大比如小于 2GB直接加MAPJOIN提示把關(guān)聯(lián)表加載到內(nèi)存里繞開 Shuffle。另外還有一個(gè)容易忽略的問題臟數(shù)據(jù)導(dǎo)致的“假發(fā)散”。如果源表里存在大量parent_dep_id為空字符串或特殊字符的孤兒節(jié)點(diǎn)它們會在某一輪被同時(shí)關(guān)聯(lián)出來造成一輪迭代的數(shù)據(jù)量暴漲。建表時(shí)對這種情況要有心理準(zhǔn)備清洗數(shù)據(jù)的時(shí)候要一并處理。4. 實(shí)操全流程完整跑通一個(gè)部門樹遞歸展開下面用一個(gè)經(jīng)典場景——部門樹展開——走一遍完整流程。假設(shè)有一張部門關(guān)系表ods_dep_rel我把它簡化成下面這種結(jié)構(gòu)CREATE TABLE IF NOT EXISTS ods_dep_rel ( dep_id STRING COMMENT 部門id, parent_dep_id STRING COMMENT 上級部門id, dep_name STRING COMMENT 部門名稱, project_id STRING COMMENT 業(yè)務(wù)線標(biāo)識 ) COMMENT 部門關(guān)系原始表 PARTITIONED BY (dt STRING COMMENT 數(shù)據(jù)日期) STORED AS ORC;需求非常明確給定業(yè)務(wù)線project_id找出每個(gè)根部門下所有層級的子部門并記錄每個(gè)子部門相對于根部門的層級深度。4.1 初始化 SQL生成第 0 層數(shù)據(jù)第 0 層就是根節(jié)點(diǎn)。實(shí)際業(yè)務(wù)里根節(jié)點(diǎn)的定義可能不同有的是parent_dep_id IS NULL有的是parent_dep_id 還有的是設(shè)置了專門的根標(biāo)識字段這里以最常見的parent_dep_id IS NULL為例。-- init_dep_tree.sql INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT dep_id AS root_dep_id, dep_id AS dep_id, dep_name AS dep_name, 0 AS loop_id, cast(dep_id AS STRING) AS path_str FROM ods_dep_rel WHERE dt ${hiveconf:dt} AND project_id ${hiveconf:project_id} AND parent_dep_id IS NULL;這輪產(chǎn)出的tmp_dep_tree_prev表既是初始結(jié)果集也是第一輪迭代的增量輸入。-- 創(chuàng)建結(jié)果總表如果不存在 CREATE TABLE IF NOT EXISTS tmp_dep_tree_total ( root_dep_id STRING, dep_id STRING, dep_name STRING, loop_id INT, path_str STRING ) STORED AS ORC;然后把初始數(shù)據(jù)寫入總表INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_prev;注意這里用的是INSERT INTO因?yàn)榭偙硎侵疠喿芳拥摹?.2 單輪迭代 SQL增量展開的核心邏輯每一輪迭代的核心 SQL 是同一套邏輯只是輸入表不同。落到腳本里我寫成參數(shù)化的 SQL 文件-- expand_once.sql -- 輸入tmp_dep_tree_prev上一輪增量 -- 輸出tmp_dep_tree_cur本輪新增量 INSERT OVERWRITE TABLE tmp_dep_tree_cur SELECT t.root_dep_id AS root_dep_id, c.dep_id AS dep_id, c.dep_name AS dep_name, t.loop_id 1 AS loop_id, concat_ws(,, t.path_str, c.dep_id) AS path_str FROM tmp_dep_tree_prev t JOIN ods_dep_rel c ON t.dep_id c.parent_dep_id LEFT JOIN tmp_dep_tree_total total ON c.dep_id total.dep_id AND t.root_dep_id total.root_dep_id WHERE c.dt ${hiveconf:dt} AND c.project_id ${hiveconf:project_id} AND total.dep_id IS NULL AND NOT find_in_set(c.dep_id, t.path_str) 0;這段 SQL 里最核心的是三個(gè)地方JOIN以上一輪增量表的dep_id去匹配源表的parent_dep_id找到所有直接子節(jié)點(diǎn)。LEFT JOIN ... WHERE total.dep_id IS NULL過濾掉已經(jīng)在結(jié)果總表里出現(xiàn)過的節(jié)點(diǎn)這就是上面說的防環(huán)路、防重復(fù)。find_in_set(c.dep_id, t.path_str)從路徑上再做一次檢查防止環(huán)路在極深路徑下帶來額外的重復(fù)計(jì)算。每個(gè)細(xì)節(jié)都很重要。LEFT JOIN的過濾條件必須同時(shí)帶root_dep_id否則不同根節(jié)點(diǎn)下出現(xiàn)同一個(gè)部門 ID 時(shí)會被錯誤過濾掉導(dǎo)致結(jié)果缺失。4.3 Shell 循環(huán)控制腳本讓迭代自動收斂有了初始化 SQL 和單輪迭代 SQL剩下就是把循環(huán)串起來。我用的是 Bash 腳本 hive -f的方式每一輪迭代完成后檢查增量條數(shù)決定繼續(xù)還是終止。#!/bin/bash # 遞歸計(jì)算 2.0部門樹自動展開 set -e DT2024-01-01 PROJECT_IDP001 MAX_LOOP50 CUR_LOOP0 HIVE_CONF-hiveconf dt${DT} -hiveconf project_id${PROJECT_ID} # 1. 初始化第 0 層 hive ${HIVE_CONF} -f ./init_dep_tree.sql hive ${HIVE_CONF} -e INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_prev; # 2. 循環(huán)迭代 while [ ${CUR_LOOP} -lt ${MAX_LOOP} ]; do echo Loop: ${CUR_LOOP} # 執(zhí)行單輪擴(kuò)展 hive ${HIVE_CONF} -f ./expand_once.sql # 統(tǒng)計(jì)本輪增量行數(shù) INC_CNT$(hive -S ${HIVE_CONF} -e SELECT count(1) FROM tmp_dep_tree_cur; | tr -d ) echo Increment count: ${INC_CNT} if [ ${INC_CNT} -eq 0 ]; then echo No new increment, recursion finished. break fi # 合并增量到結(jié)果總表 hive ${HIVE_CONF} -e INSERT INTO TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_cur; # 輪轉(zhuǎn)表當(dāng)前增量變?yōu)橄乱惠喌妮斎?hive ${HIVE_CONF} -e INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT * FROM tmp_dep_tree_cur; CUR_LOOP$((CUR_LOOP 1)) done # 3. 輸出最終結(jié)果 hive ${HIVE_CONF} -e SELECT root_dep_id, dep_id, dep_name, loop_id FROM tmp_dep_tree_total ORDER BY root_dep_id, loop_id; 這段腳本有三個(gè)經(jīng)驗(yàn)點(diǎn)值得分享tr -d 是必要的。hive -S -e輸出的count結(jié)果往往帶一些空白字符不清理直接做數(shù)值比較會出問題。用INSERT OVERWRITE TABLE tmp_dep_tree_prev SELECT * FROM tmp_dep_tree_cur做表切換而不是TRUNCATE后原地插入是為了保持上一輪數(shù)據(jù)直到本輪完全成功。一旦中間某一步失敗舊的增量數(shù)據(jù)還在排查和恢復(fù)都會容易很多。循環(huán)里沒必要用break后再單獨(dú)把結(jié)果導(dǎo)出去直接到最后統(tǒng)一輸出即可。4.4 結(jié)果校驗(yàn)確認(rèn)遞歸沒有算錯遞歸算完了怎么確認(rèn)結(jié)果對不對我的經(jīng)驗(yàn)是至少做兩層校驗(yàn)。第一層總數(shù)校驗(yàn)。用源表跑一個(gè)不依賴遞歸的聚合查詢比如每個(gè)部門的直接子部門數(shù)再和自己算出來的結(jié)果做交叉比對。如果源表本身有“該部門所有后代部門數(shù)”這樣的冗余字段校驗(yàn)會更直接。第二層層級校驗(yàn)。隨機(jī)挑幾個(gè)已知深度的部門對照結(jié)果表里的loop_id確認(rèn)深度是否正確。這一步雖然樸素但能有效發(fā)現(xiàn)初始化時(shí)根節(jié)點(diǎn)定義錯誤導(dǎo)致的全盤偏移。-- 校驗(yàn)示例檢查是否有節(jié)點(diǎn)的 loop_id 與路徑深度不一致 -- 正常情況下 path_str 里逗號的數(shù)量加 1 應(yīng)該等于 loop_id SELECT root_dep_id, dep_id, loop_id, path_str, (LENGTH(path_str) - LENGTH(REPLACE(path_str, ,, )) 1) AS path_depth FROM tmp_dep_tree_total WHERE loop_id ! (LENGTH(path_str) - LENGTH(REPLACE(path_str, ,, )) 1);這個(gè) SQL 利用了逗號計(jì)數(shù)的技巧很直觀。有經(jīng)驗(yàn)的讀者可能已經(jīng)想到這其實(shí)也驗(yàn)證了路徑拼接邏輯是否正確。5. 性能調(diào)優(yōu)與踩坑記錄2.0 方案里踩過的坑一點(diǎn)也不比業(yè)務(wù)邏輯少。選幾個(gè)最有代表性的寫出來。5.1 迭代輪次過多導(dǎo)致的小文件問題每一輪迭代都會生成一批新的 ORC 文件如果最大迭代深度是 30 層結(jié)果總表里會累積很多小文件。時(shí)間久了總表在做LEFT JOIN過濾時(shí)甚至?xí)驗(yàn)樵獢?shù)據(jù)膨脹而變慢。我的處理方式是在腳本結(jié)束后對結(jié)果總表做一次合并壓縮。INSERT OVERWRITE TABLE tmp_dep_tree_total SELECT * FROM tmp_dep_tree_total DISTRIBUTE BY root_dep_id;DISTRIBUTE BY root_dep_id會讓同一個(gè)根節(jié)點(diǎn)的數(shù)據(jù)落到同一個(gè) Reduce不僅合并了小文件還能讓后續(xù)查詢按根節(jié)點(diǎn)過濾時(shí)更快。5.2 大增量 join 時(shí)的任務(wù)卡頓當(dāng)某一輪增量特別大比如某個(gè)中間層節(jié)點(diǎn)數(shù)量暴漲時(shí)JOIN過程容易出現(xiàn)長尾。我踩過最厲害的一次是在第 4 層迭代時(shí)單個(gè) Reduce 跑到 3 小時(shí)沒結(jié)束。排查之后發(fā)現(xiàn)是數(shù)據(jù)傾斜一個(gè)超大父節(jié)點(diǎn)帶了幾十萬條子節(jié)點(diǎn)記錄全部路由到了同一個(gè) Reduce。解決方案是在迭代 SQL 的JOIN關(guān)聯(lián)鍵上做前綴加鹽或者直接把源表按parent_dep_id分桶。分桶后JOIN的 Shuffle 數(shù)據(jù)量會小很多實(shí)測性能提升非常明顯。不過加鹽方案會改變關(guān)聯(lián)邏輯對非傾斜節(jié)點(diǎn)來說反而增加計(jì)算量。我的建議是先通過COUNT按parent_dep_id聚合確認(rèn)傾斜節(jié)點(diǎn)的量級再決定是否加鹽不要盲目優(yōu)化。5.3 分區(qū)參數(shù)穿透導(dǎo)致的串?dāng)?shù)問題這個(gè)坑很隱蔽。在源表ods_dep_rel存在多分區(qū)的情況下如果迭代 SQL 里忘記寫c.dt ${hiveconf:dt}那么JOIN會掃描全分區(qū)。某一次我切換日期參數(shù)后忘了清理老的臨時(shí)表結(jié)果遞歸結(jié)果里混了上一個(gè)日期的歷史數(shù)據(jù)排查了很久。從那以后我養(yǎng)成了一個(gè)習(xí)慣所有迭代 SQL 的WHERE條件里源表分區(qū)條件必須寫全無論哪個(gè)環(huán)境執(zhí)行都能保證只讀當(dāng)天數(shù)據(jù)。5.4 常見報(bào)錯速查表報(bào)錯信息或現(xiàn)象可能原因解決辦法迭代一直不停止達(dá)到 MAX_LOOP存在深層次環(huán)路且總表防環(huán)邏輯有問題檢查LEFT JOIN的關(guān)聯(lián)條件是否帶了root_dep_id檢查源數(shù)據(jù)是否有自關(guān)聯(lián)某一輪增量數(shù)據(jù)暴漲臟數(shù)據(jù)導(dǎo)致發(fā)散節(jié)點(diǎn)或孤兒節(jié)點(diǎn)大量掛載清洗源數(shù)據(jù)剔除parent_dep_id為空的孤兒記錄對關(guān)聯(lián)鍵做分桶結(jié)果總表小文件過多每輪迭代產(chǎn)生新文件未合并結(jié)束后用INSERT OVERWRITE ... DISTRIBUTE BY root_dep_id合并count(1)判斷不準(zhǔn)導(dǎo)致提前退出hive -S -e返回字符串帶空格用tr -d 清理后再做比較不同根節(jié)點(diǎn)下相同部門被過濾LEFT JOIN條件漏掉root_dep_id補(bǔ)上AND t.root_dep_id total.root_dep_id定位不到數(shù)據(jù)問題臨時(shí)表太多搞不清哪張是上一輪數(shù)據(jù)固定使用prev/cur輪轉(zhuǎn)命名不要動態(tài)拼接表名6. 這套方案還能怎么擴(kuò)展如果只是做部門樹展開這套腳本已經(jīng)夠用了。但遞歸計(jì)算的場景遠(yuǎn)不止組織架構(gòu)。這里列幾個(gè)我實(shí)際做過的擴(kuò)展方向。6.1 BOM 物料多級展開BOM 展開和部門樹最大的區(qū)別在于子節(jié)點(diǎn)關(guān)聯(lián)父節(jié)點(diǎn)時(shí)還帶一個(gè)“用量”字段遞歸展開時(shí)要把路徑上的用量做乘積最終得到每一個(gè)末級物料的累計(jì)需求量。實(shí)現(xiàn)時(shí)只需要在迭代 SQL 的SELECT子句里多帶一個(gè)用量字段合并時(shí)做累乘即可。防環(huán)和終止邏輯完全復(fù)用。6.2 關(guān)系圖譜中的多度人脈查找比如“找出某個(gè)用戶 3 度以內(nèi)的所有關(guān)聯(lián)用戶”。這個(gè)場景本質(zhì)上就是限制深度的遞歸查找只需要在循環(huán)控制腳本里把MAX_LOOP設(shè)為 3并且在初始化 SQL 里固定好起始用戶即可。6.3 數(shù)據(jù)血緣追蹤數(shù)據(jù)倉庫的表和字段之間存在“上游依賴”關(guān)系想查某個(gè)指標(biāo)字段的完整血緣鏈路可以用同樣的迭代框架把“父節(jié)點(diǎn)”換成“上游字段”把“子節(jié)點(diǎn)”換成“下游字段”。血緣數(shù)據(jù)的層級往往很深而且環(huán)路的可能性比組織架構(gòu)大不少這時(shí)候雙表防環(huán)機(jī)制的可靠性就顯得格外重要。最后說點(diǎn)實(shí)在的這套 2.0 方案做完之后我再也沒有因?yàn)椤斑f歸查不出來”被業(yè)務(wù)方找過。回看整個(gè)優(yōu)化過程最值得投入精力的其實(shí)不是循環(huán)腳本本身而是對增量驅(qū)動和防環(huán)機(jī)制的理解。很多做數(shù)據(jù)開發(fā)的朋友一聽到 Hive 遞歸就想著去裝插件、換引擎其實(shí)標(biāo)準(zhǔn) Hive 的臨時(shí)表迭代方案處理絕大多數(shù)生產(chǎn)需求都足夠了。如果看完這篇文章你只想記住一個(gè)點(diǎn)那我建議記住這句迭代 增量表 結(jié)果總表過濾是 Hive 里做遞歸的通用范式。這個(gè)范式一旦建立起來不管遇到組織架構(gòu)、BOM 展開還是關(guān)系圖譜都能快速套用最多就是改改表結(jié)構(gòu)、調(diào)調(diào)終止條件而已。下一次再有人跟你說“Hive 做不了遞歸”你可以把這篇文章甩給他。