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