做数仓的迟早会遇到一类需求把一张带上下级关系的表展开成一颗完整的树。比如组织架构比如商品类目比如BOM物料清单再比如接口调用链。在Oracle或者SQL Server里写个递归CTE就解决了但切到Hive环境你翻遍文档也找不到递归关键字。很多人第一次接到这种需求时都懵了不知道该怎么下手。这篇文章会把这套“类递归计算”完整讲透。我会从一个最典型的组织架构展开需求出发拆解Hive里实现递归的几种常见思路然后给出一个可以直接搬到生产环境的迭代方案包括建表、造数、脚本实现、参数调优和坑点排查。适合数仓工程师、数据分析师以及所有需要在Hive里处理层级数据的开发者。读完后你可以直接照着抄也能理解每一步背后的原理遇到变形需求时不至于抓瞎。1. 需求拆解与方案选型1.1 类递归到底在算什么先明确一个概念真正的递归是程序在函数内部调用自身逐层深入再逐层返回。Hive SQL是声明式语言没有循环也没有函数自调用所以我们要做的“类递归”本质上是把一颗树按层级逐层展开把原来只有父子两级的表变成包含所有祖先、所有后代关系的宽表。举个例子。某公司的组织架构表长这样emp_id emp_name manager_id 1001 张三 null 1002 李四 1001 1003 王五 1002 1004 赵六 1002业务方要的不是这种明细而是一张“每个员工对应他所有上级领导”的关系表还要知道隔了几层最好还能带出完整的管理链路。这就是典型的类递归需求。我接过类似需求的场景还包括商品类目树把叶子类目对应的所有父级类目列出来做汇总统计。BOM清单一个成品由哪些半成品组成半成品又由哪些原料组成需要展开到最底层。数据血缘表的字段级血缘追溯上游所有源头表。权限继承角色继承关系下某个用户最终拥有哪些权限。它们的共同特点是单张表里只有 id 和 parent_id 两个字段但层级深度不确定可能是2层也可能是10层甚至某些分支下还挂着成千上万个节点。1.2 Hive里实现类递归的三条主流路线我在不同的项目里见过三种主流做法各有各的适用场景这里一并列出来。路线A多层自连接固定深度展开假设你知道最大层级最多5层那可以直接写多个 LEFT JOIN一层一层往里拼。SQL长是长了点但逻辑直白执行效率也可以接受最常见的写法是这样SELECT t1.id AS root_id, t2.id AS level1_id, t3.id AS level2_id, t4.id AS level3_id FROM base_table t1 LEFT JOIN base_table t2 ON t1.id t2.parent_id LEFT JOIN base_table t3 ON t2.id t3.parent_id LEFT JOIN base_table t4 ON t3.id t4.parent_id;这条路最大的问题是层级一旦超过你的预估就会丢数据。业务方很难保证两年后不会多加一级组织到时候改SQL不是不能改但改完之后整张中间表的逻辑都要跟着变维护成本不低。路线B迭代临时表 循环动态深度展开这是我个人最推荐的做法。思路是先用一条SQL把根节点都拿出来存到临时表然后在shell脚本里写一个while循环每一步用当前临时表去 JOIN 原表把下一层节点追加进来直到某一轮没有新增数据为止。每一步只处理一层逻辑非常清晰层级再深也不怕完全由数据自己决定。路线C写UDF或者用Spark SQLHive可以注册自定义UDF也可以在Hive里去调用Spark的API但这属于重武器。我在大多数场景下都不建议杀鸡用牛刀。除非你的计算逻辑极其复杂比如要在树上做路径枚举、做概率传播、做依赖图分析否则写UDF的开发和调优成本都远高于迭代方案。三条路线的对比可以用一张表说清楚方案层级扩展性实现复杂度运维成本适用范围多层自连接固定改SQL才能扩低中层级浅且稳定迭代临时表动态靠数据判停中低层级未知最通用UDF / Spark无限高高逻辑复杂需通用1.3 为什么我推荐迭代法而不是自连接抛开多层自连接本身的僵化问题我最看重迭代法的还有一点中间过程可控。每一轮循环产生的新层数据都单独落在临时表里出问题时你随时可以查“卡在哪一层了”排查效率比一坨几十行的自连接SQL高得多。而且迭代法的SQL本身不复杂每个循环体里只有两类操作一次JOIN查出下一层一次UNION ALL把结果累加。哪怕组里的新人来做维护理解起来也不困难。生产环境的稳定性很多时候不是靠多高深的技术而是靠逻辑简单、问题好定位。2. 核心细节迭代法的原始思路与实现拆解2.1 前置准备基础表与会话设置先准备一张测试用的层级表以员工组织架构为例。普通数仓里这张表通常是同步过来的主数据表字段一般就是 id、name、parent_id、status 这些。CREATE TABLE IF NOT EXISTS dim_employee ( emp_id STRING COMMENT 员工ID, emp_name STRING COMMENT 员工姓名, manager_id STRING COMMENT 直接上级ID根节点为NULL, status INT COMMENT 在职状态1在职 0离职 ) COMMENT 员工组织架构维度表 STORED AS ORC;造几条测试数据覆盖多层级和同一个上级下挂多个下级的情况INSERT OVERWRITE TABLE dim_employee SELECT stack(9, 1001, 张三, NULL, 1, 1002, 李四, 1001, 1, 1003, 王五, 1002, 1, 1004, 赵六, 1002, 1, 1005, 孙七, 1004, 1, 1006, 周八, 1004, 1, 1007, 吴九, 1003, 1, 1008, 郑十, 1007, 1, 1009, 钱十一, 1001, 1 );会话层面有两个参数值得提前设置。一个是 hive.exec.max.created.files因为循环每跑一轮就要写一批文件文件数容易涨上去建议调大。另一个是 hive.auto.convert.join 相关的参数迭代时的JOIN表其实很小开启自动mapjoin能省掉不必要的reduce阶段。SET hive.exec.max.created.files200000; SET hive.auto.convert.jointrue; SET hive.mapjoin.smalltable.filesize50000000;2.2 迭代的“三件套”起始层、扩展层、累加层迭代法的核心就三句话先找根再扩层最后累计。先说起始层。根节点的定义要明确在这个例子里 manager_id 为空的节点就是根INSERT OVERWRITE TABLE tmp_tree_01 SELECT emp_id AS root_id, emp_id AS node_id, emp_name AS node_name, 1 AS level, emp_id AS path FROM dim_employee WHERE manager_id IS NULL AND status 1;这里的临时表字段设计是有讲究的。root_id 用来标识这棵树属于哪个根节点node_id 是当前展开到的节点level 是当前节点离根的距离path 则是从根到当前节点的完整路径。后面所有层的数据都是往这张表里追加。再说扩展层。每一轮循环要做的事是把当前临时表里最新一层的节点拿到原表里去查找它们的直接下级INSERT INTO TABLE tmp_tree_01 SELECT t.root_id, d.emp_id, d.emp_name, t.level 1, concat(t.path, -, d.emp_id) FROM tmp_tree_01 t JOIN dim_employee d ON t.node_id d.manager_id LEFT JOIN tmp_tree_01 exist ON exist.root_id t.root_id AND exist.node_id d.emp_id WHERE t.level ${hiveconf:cur_level} AND d.status 1 AND exist.node_id IS NULL;这段SQL是精髓我拆开解释一下。t.level ${hiveconf:cur_level}是只处理当前层不是把整个临时表重新算一遍这是控制循环次数和性能的关键。如果不加这个条件每轮循环都会把之前所有层再扫一遍数据膨胀得非常快。LEFT JOIN tmp_tree_01 exist ... WHERE exist.node_id IS NULL是去重防环路。因为一张树表里同一棵树上可能存在交叉引用或者临时表里已经存在某个节点就不能再往里加了。这个半连接anti join能保证每个节点只会被加入一次也顺便解决了部分环路风险。最后是累加逻辑。INSERT INTO 是追加每一轮跑完临时表里多一层数据最后由所有层的UNION结果自然形成整棵树。2.3 判停条件与最大层级限制循环不能无限跑得有判停条件。我在生产脚本里会同时做两件事每轮循环后查一下“本轮新增了几行”如果为0就退出同时设置一个最大迭代次数上限防止意外情况下死循环把集群跑爆。“本轮新增几行”怎么查可以直接在每轮之后执行SELECT COUNT(*) FROM tmp_tree_01 WHERE level ${hiveconf:cur_level};然后把结果写到一个文本文件或者变量里shell里判断是否为0。最大层级上限用shell变量来控制比如设成50层。正常公司不会有50层组织架构BOM也不会下探到50层这个上限更多是为异常数据兜底MAX_LEVEL50 while [ $CURRENT_LEVEL -le $MAX_LEVEL ]; do # 执行一轮扩展SQL # 判断新增行数 done之所以要两层保险是因为真实数据里总会有“奇葩”。比如某条记录的 parent_id 指向了自己或者A的上级是B、B的上级是A。这种情况下如果只判断新增行数循环会一直跑因为每一轮都能找到“新”节点。有了最大层级上限至少能在50层时强制退出保护集群计算资源。2.4 为什么用临时表而不是视图有人会问能不能直接建个视图每次查询时动态递归Hive对递归视图是不支持的因为视图在解析时就会展开成固定SQL无法表达动态深度。而且视图每次查询都要重新计算整棵树性能开销非常大。临时表的优势在于中间结果可以复用。迭代过程中每一轮产生的数据都物化在表里下一轮只需要基于当前层去扩展。如果某个中间环节出问题也能直接查临时表定位。生产环境里我习惯给临时表名加上当天日期后缀例如 tmp_tree_20250110方便回溯和清理。3. 实操过程跑通一个多级分类展开的完整案例3.1 场景定义与预期结果下面用一个电商商品分类的场景来实际跑一遍。分类树表叫 dim_category字段包括 category_id类目ID、category_name类目名称、parent_id父类目ID。数据如下A 全部分类 null A1 手机数码 A A2 家用电器 A A11 智能手机 A1 A12 手机配件 A1 A121 手机壳 A12 A21 大家电 A2 A211 冰箱 A21我们的目标结果是每个叶子类目一行包含它的所有祖先链。也就是说 A121 这一行要输出 root_idA、pathA-A1-A12-A121、level4。这样业务方做汇总时不管按哪个层级看都能直接通过这张展开表过滤出对应子树的全部数据。3.2 完整脚本实现与关键参数说明整个实现我会放在一个bash脚本里用hive -e执行SQL循环用shell的while控制。脚本全貌如下#!/bin/bash DB_NAMEdw_dim TMP_BASEtmp_category_tree TABLE_NAMEdim_category MAX_LEVEL30 CUR_LEVEL1 # 每一轮开始前把上次残留清掉 hive -e DROP TABLE IF EXISTS ${DB_NAME}.${TMP_BASE}; # 第一层根结点 hive --database ${DB_NAME} -e CREATE TABLE ${TMP_BASE} AS SELECT category_id AS root_id, category_id AS node_id, category_name AS node_name, 1 AS level, category_id AS path FROM ${TABLE_NAME} WHERE parent_id IS NULL; while [ ${CUR_LEVEL} -le ${MAX_LEVEL} ]; do # 当前层扩展 hive --database ${DB_NAME} -e SET hive.exec.max.created.files200000; SET hive.auto.convert.jointrue; INSERT INTO TABLE ${TMP_BASE} SELECT t.root_id, d.category_id, d.category_name, t.level 1, concat(t.path, -, d.category_id) FROM ${TMP_BASE} t JOIN ${TABLE_NAME} d ON t.node_id d.parent_id LEFT JOIN ${TMP_BASE} exist ON exist.root_id t.root_id AND exist.node_id d.category_id WHERE t.level ${CUR_LEVEL} AND d.category_id IS NOT NULL AND exist.node_id IS NULL; # 检查本轮新增行数 CNT$(hive --database ${DB_NAME} -S -e SELECT COUNT(*) FROM ${TMP_BASE} WHERE level $((CUR_LEVEL 1)); | tail -1) if [ ${CNT} 0 ]; then echo 第 ${CUR_LEVEL} 层无扩展迭代结束 break fi CUR_LEVEL$((CUR_LEVEL 1)) done echo 最终层级深度${CUR_LEVEL}这个脚本有几个地方值得特别注意。tail -1取count值是因为hive -S模式下仍然可能在前面输出日志真正的查询结果在最后一行需要取最后一个非空值。分区表在建临时表的时候要格外小心。如果原表是分区表JOIN时最好加上分区过滤条件否则全表扫描一次耗时很长。我在上面的示例里简化为非分区表实际生产中建临时表前先EXPLAIN一下执行计划确认没有全表扫描再跑。迭代次数不是由我们拍脑袋定的而是由数据本身决定。分类树只有4层那么跑到第4轮时第5轮的新增行数为0脚本自然退出不会多跑无用功。3.3 结果校验与边界情况验证跑完之后先做基础校验总行数应该等于非根节点数加根节点数同时每棵树的节点数应该和原表里该子树下的节点数一致。-- 按根节点统计临时表的节点数 SELECT root_id, COUNT(*) AS node_cnt FROM tmp_category_tree GROUP BY root_id ORDER BY node_cnt DESC;对照原数据A树下应该有7个节点A1、A2、A11、A12、A121、A21、A211如果COUNT出来是7说明没有重复、没有遗漏。再看路径字段用最长路径那一行检查链路SELECT * FROM tmp_category_tree WHERE path LIKE %A121% LIMIT 10;这里能看到 A121 对应的 path 是 A-A1-A12-A121level 是4完全符合预期。边界情况主要验证两类单节点树只有根没有子节点以及根节点缺失的孤立数据。单节点树在第一个循环自然结束因为第二层查不到任何数据。孤立数据是指某些节点的 parent_id 在表中不存在这种情况下它永远不会被任何树包含在迭代结果里自然也不会出现。如果业务方要求孤立节点也要保留需要单独处理比如把它当成根节点补进结果里。3.4 性能优化分区、压缩与小文件合并迭代类计算最怕的就是数据越跑越慢。我在这类任务上做过几轮针对性优化效果很直接。第一个优化点是每轮循环尽量只扫必要的数据。扩展层的核心是WHERE t.level ${CUR_LEVEL}这个条件能保证临时表只读取一层数据参与JOIN。如果临时表建立时按level做了分区那性能还能再上一个台阶但大多数场景下ORC存储加谓词下推已经够用。第二个优化点是控制小文件。每轮INSERT都会产生一批文件循环几十轮下来文件数量可能上千。对后续查询而言上千个小文件意味着上千个task启动开销非常浪费资源。解决方案是每轮结束后对临时表执行一次合并INSERT OVERWRITE TABLE tmp_category_tree SELECT * FROM tmp_category_tree;这条SQL看起来像是自我复制但实际上通过一次完整的读写能把小文件合并成少量大文件。更好的做法是定期做比如每5轮做一次避免每次都做全表重写。第三个优化点是压缩格式。临时表创建时直接指定ORC加snappy压缩能显著减少IOCREATE TABLE tmp_category_tree STORED AS ORC TBLPROPERTIES (orc.compressSNAPPY) AS SELECT ...;在Hive里ORC文件默认就带轻量级索引和谓词下推能力比纯文本的中间表快不少。对于临时表这种反复读写的场景换掉默认存储格式是最划算的一笔投资。4. 常见问题与排查技巧实录4.1 死循环环路数据导致的无限迭代迭代方案最大的隐患就是环路数据。前几天我排查一个线上任务脚本看起来没毛病但跑了几个小时都停不下来最后是MAX_LEVEL兜底才结束。一查数据果然有两条记录的parent_id互相指向对方。这种环路数据怎么提前发现有个简单SQL可以先扫一遍SELECT t1.id, t2.id FROM dim_category t1 JOIN dim_category t2 ON t1.parent_id t2.id AND t2.parent_id t1.id;如果查出来有结果说明存在双向环路。还有一个更普遍的检查方式如果全表最大层级超过预设值大概率有环路。我一般会在迭代脚本跑完后加一个校验脚本输出所有 level 大于 MAX_LEVEL 的节点方便快速定位异常数据。防环路的另一个手段就是前面提到的半连接去重。只要每个节点只允许被加入一次即使数据有环路循环也会因为节点已经存在而停止增长配合MAX_LEVEL就更稳妥了。4.2 数据倾斜某个根节点下面挂了几百万子节点类递归计算天然会有数据倾斜问题因为真实世界里的树就不平衡。比如某大型公司组织架构销售部门可能挂了上万人而总部职能部门只有几十个人。迭代过程里每次扩展最大的那个根节点都会成为长尾任务。处理办法有几个思路。第一开启mapjoin。因为参与JOIN的临时表数据量通常不大mapjoin能把JOIN操作下推到map端避开reduce阶段的单点压力。第二把临时表按root_id做分桶。分桶能让相同root_id的数据分布到同一个桶里配合SMB join能显著缓解长尾。更彻底的方案是把树的展开逻辑拆成两段先算出所有节点到根的完整路径再统计时按路径前缀聚合。但这个方案对业务SQL改动较大我在迭代法跑通、性能可接受的前提下一般不去过度优化。先让功能跑起来再观察瓶颈在哪是数仓调优的基本节奏。4.3 临时表无限膨胀与路径超长随着层级加深path字段会越来越长。如果你的业务层级真的非常深比如到了30层以上concat拼接的路径字符串可能超过几千字符。这在Hive里不会报错但会拖慢查询和存储的效率。解决方案是分两步走迭代过程中只保留root_id、node_id、level三个最小字段path在迭代结束后用专门的SQL一次性回填。或者干脆不要path只在最终结果里关联原表获取名称路径展示交给BI层去TREE结构。别让路径拼接成为迭代过程中的性能包袱。还有一点临时表的清理要及时。我见过有人把跨天用的临时表残留了一周占了几个T的存储空间。建议在脚本最后统一DROP并且设置一个定时任务清理超过N天未使用的临时表。4.4 固定深度展开的快捷方案与适用判断不是所有递归需求都必须上迭代法。如果业务上已经明确层级不会超过3层比如用户→订单→订单明细这种固定关系写固定SQL是最省事的。SELECT l1.id AS user_id, l2.id AS order_id, l3.id AS detail_id FROM orders l2 JOIN users l1 ON l2.user_id l1.id LEFT JOIN order_details l3 ON l2.id l3.order_id;这种情况下用迭代法反而有点小题大做。怎么判断我的经验是数一下你做过或即将要做的这类需求如果层级深度在5层以内且表结构稳定自连接就够了如果层级未知、可能变化、树也不平衡直接上迭代框架。搞一套可复用的迭代模板脚本第一次花半小时搭好后面所有树形展开需求都套用是最划算的投入。5. 迭代方案的可扩展玩法树形展开只是类递归计算的基础需求。实际做业务分析时往往还要在这棵树上做更多文章。一个是路径维度的聚合分析。展开表拿到后想统计每个二级类目下的总营业额直接按path的第二段分组就行SELECT split(path, -)[1] AS level2_id, SUM(order_amount) AS total_amount FROM dw_order_detail JOIN tmp_category_tree ON dw_order_detail.category_id tmp_category_tree.node_id GROUP BY split(path, -)[1];另一个是找叶子节点。展开表里 level 最大的那些节点就是叶子一条SQL就能过滤出来不需要额外打标。再配合维度表join很容易算出每个叶子节点归属的完整链路。还有一种是多根树的合并展示。如果业务上有多个独立的根比如多品牌分类迭代法天然支持因为每个root_id都是一棵独立的树。最终展示时只要用root_id做过滤条件互不干扰。我自己后续扩展的想法是把迭代循环封装成一个通用脚本入口传一个表名、两个字段名id、parent_id自动生成完整展开结果表。这样业务方再提“帮我展开一下XX树”的需求直接从模板复制改参数就行连循环逻辑都不用重写。最后再说几句实操体验类递归这套东西我前前后后在好几个项目里用过踩过的坑不算少换个环境就换一批问题。但核心思路始终没变过先找根再扩层判停靠新增行数保险靠最大层级防坑靠半连接去重。只要把这四条刻在脑子里任何层级数据的展开需求你都能hold住。如果你第一次跑这类脚本我建议先拿几十条数据的小表试跑每轮循环后打印一下当前层数和新增行数把整个过程看清楚了再上生产。生产环境里跑之前一定记得把落临时表的目录权限、保留周期和清理策略定好别让临时表变成事故表。最后一点提醒不管方案多稳结果都要和业务方确认过口径再发布树形数据的定义根怎么算、层怎么算、含不含自己差一个字符结果就完全不一样。 
长河君 · 2026/10/17 21:04:19 · 阅读 1.2k Hive类递归实现：迭代法展开层级树的完整方案
做数仓的迟早会遇到一类需求把一张带上下级关系的表展开成一颗完整的树。比如组织架构比如商品类目比如BOM物料清单再比如接口调用链。在Oracle或者SQL Server里写个递归CTE就解决了但切到Hive环境你翻遍文档也找不到递归关键字。很多人第一次接到这种需求时都懵了不知道该怎么下手。这篇文章会把这套“类递归计算”完整讲透。我会从一个最典型的组织架构展开需求出发拆解Hive里实现递归的几种常见思路然后给出一个可以直接搬到生产环境的迭代方案包括建表、造数、脚本实现、参数调优和坑点排查。适合数仓工程师、数据分析师以及所有需要在Hive里处理层级数据的开发者。读完后你可以直接照着抄也能理解每一步背后的原理遇到变形需求时不至于抓瞎。1. 需求拆解与方案选型1.1 类递归到底在算什么先明确一个概念真正的递归是程序在函数内部调用自身逐层深入再逐层返回。Hive SQL是声明式语言没有循环也没有函数自调用所以我们要做的“类递归”本质上是把一颗树按层级逐层展开把原来只有父子两级的表变成包含所有祖先、所有后代关系的宽表。举个例子。某公司的组织架构表长这样emp_id emp_name manager_id 1001 张三 null 1002 李四 1001 1003 王五 1002 1004 赵六 1002业务方要的不是这种明细而是一张“每个员工对应他所有上级领导”的关系表还要知道隔了几层最好还能带出完整的管理链路。这就是典型的类递归需求。我接过类似需求的场景还包括商品类目树把叶子类目对应的所有父级类目列出来做汇总统计。BOM清单一个成品由哪些半成品组成半成品又由哪些原料组成需要展开到最底层。数据血缘表的字段级血缘追溯上游所有源头表。权限继承角色继承关系下某个用户最终拥有哪些权限。它们的共同特点是单张表里只有 id 和 parent_id 两个字段但层级深度不确定可能是2层也可能是10层甚至某些分支下还挂着成千上万个节点。1.2 Hive里实现类递归的三条主流路线我在不同的项目里见过三种主流做法各有各的适用场景这里一并列出来。路线A多层自连接固定深度展开假设你知道最大层级最多5层那可以直接写多个 LEFT JOIN一层一层往里拼。SQL长是长了点但逻辑直白执行效率也可以接受最常见的写法是这样SELECT t1.id AS root_id, t2.id AS level1_id, t3.id AS level2_id, t4.id AS level3_id FROM base_table t1 LEFT JOIN base_table t2 ON t1.id t2.parent_id LEFT JOIN base_table t3 ON t2.id t3.parent_id LEFT JOIN base_table t4 ON t3.id t4.parent_id;这条路最大的问题是层级一旦超过你的预估就会丢数据。业务方很难保证两年后不会多加一级组织到时候改SQL不是不能改但改完之后整张中间表的逻辑都要跟着变维护成本不低。路线B迭代临时表 循环动态深度展开这是我个人最推荐的做法。思路是先用一条SQL把根节点都拿出来存到临时表然后在shell脚本里写一个while循环每一步用当前临时表去 JOIN 原表把下一层节点追加进来直到某一轮没有新增数据为止。每一步只处理一层逻辑非常清晰层级再深也不怕完全由数据自己决定。路线C写UDF或者用Spark SQLHive可以注册自定义UDF也可以在Hive里去调用Spark的API但这属于重武器。我在大多数场景下都不建议杀鸡用牛刀。除非你的计算逻辑极其复杂比如要在树上做路径枚举、做概率传播、做依赖图分析否则写UDF的开发和调优成本都远高于迭代方案。三条路线的对比可以用一张表说清楚方案层级扩展性实现复杂度运维成本适用范围多层自连接固定改SQL才能扩低中层级浅且稳定迭代临时表动态靠数据判停中低层级未知最通用UDF / Spark无限高高逻辑复杂需通用1.3 为什么我推荐迭代法而不是自连接抛开多层自连接本身的僵化问题我最看重迭代法的还有一点中间过程可控。每一轮循环产生的新层数据都单独落在临时表里出问题时你随时可以查“卡在哪一层了”排查效率比一坨几十行的自连接SQL高得多。而且迭代法的SQL本身不复杂每个循环体里只有两类操作一次JOIN查出下一层一次UNION ALL把结果累加。哪怕组里的新人来做维护理解起来也不困难。生产环境的稳定性很多时候不是靠多高深的技术而是靠逻辑简单、问题好定位。2. 核心细节迭代法的原始思路与实现拆解2.1 前置准备基础表与会话设置先准备一张测试用的层级表以员工组织架构为例。普通数仓里这张表通常是同步过来的主数据表字段一般就是 id、name、parent_id、status 这些。CREATE TABLE IF NOT EXISTS dim_employee ( emp_id STRING COMMENT 员工ID, emp_name STRING COMMENT 员工姓名, manager_id STRING COMMENT 直接上级ID根节点为NULL, status INT COMMENT 在职状态1在职 0离职 ) COMMENT 员工组织架构维度表 STORED AS ORC;造几条测试数据覆盖多层级和同一个上级下挂多个下级的情况INSERT OVERWRITE TABLE dim_employee SELECT stack(9, 1001, 张三, NULL, 1, 1002, 李四, 1001, 1, 1003, 王五, 1002, 1, 1004, 赵六, 1002, 1, 1005, 孙七, 1004, 1, 1006, 周八, 1004, 1, 1007, 吴九, 1003, 1, 1008, 郑十, 1007, 1, 1009, 钱十一, 1001, 1 );会话层面有两个参数值得提前设置。一个是 hive.exec.max.created.files因为循环每跑一轮就要写一批文件文件数容易涨上去建议调大。另一个是 hive.auto.convert.join 相关的参数迭代时的JOIN表其实很小开启自动mapjoin能省掉不必要的reduce阶段。SET hive.exec.max.created.files200000; SET hive.auto.convert.jointrue; SET hive.mapjoin.smalltable.filesize50000000;2.2 迭代的“三件套”起始层、扩展层、累加层迭代法的核心就三句话先找根再扩层最后累计。先说起始层。根节点的定义要明确在这个例子里 manager_id 为空的节点就是根INSERT OVERWRITE TABLE tmp_tree_01 SELECT emp_id AS root_id, emp_id AS node_id, emp_name AS node_name, 1 AS level, emp_id AS path FROM dim_employee WHERE manager_id IS NULL AND status 1;这里的临时表字段设计是有讲究的。root_id 用来标识这棵树属于哪个根节点node_id 是当前展开到的节点level 是当前节点离根的距离path 则是从根到当前节点的完整路径。后面所有层的数据都是往这张表里追加。再说扩展层。每一轮循环要做的事是把当前临时表里最新一层的节点拿到原表里去查找它们的直接下级INSERT INTO TABLE tmp_tree_01 SELECT t.root_id, d.emp_id, d.emp_name, t.level 1, concat(t.path, -, d.emp_id) FROM tmp_tree_01 t JOIN dim_employee d ON t.node_id d.manager_id LEFT JOIN tmp_tree_01 exist ON exist.root_id t.root_id AND exist.node_id d.emp_id WHERE t.level ${hiveconf:cur_level} AND d.status 1 AND exist.node_id IS NULL;这段SQL是精髓我拆开解释一下。t.level ${hiveconf:cur_level}是只处理当前层不是把整个临时表重新算一遍这是控制循环次数和性能的关键。如果不加这个条件每轮循环都会把之前所有层再扫一遍数据膨胀得非常快。LEFT JOIN tmp_tree_01 exist ... WHERE exist.node_id IS NULL是去重防环路。因为一张树表里同一棵树上可能存在交叉引用或者临时表里已经存在某个节点就不能再往里加了。这个半连接anti join能保证每个节点只会被加入一次也顺便解决了部分环路风险。最后是累加逻辑。INSERT INTO 是追加每一轮跑完临时表里多一层数据最后由所有层的UNION结果自然形成整棵树。2.3 判停条件与最大层级限制循环不能无限跑得有判停条件。我在生产脚本里会同时做两件事每轮循环后查一下“本轮新增了几行”如果为0就退出同时设置一个最大迭代次数上限防止意外情况下死循环把集群跑爆。“本轮新增几行”怎么查可以直接在每轮之后执行SELECT COUNT(*) FROM tmp_tree_01 WHERE level ${hiveconf:cur_level};然后把结果写到一个文本文件或者变量里shell里判断是否为0。最大层级上限用shell变量来控制比如设成50层。正常公司不会有50层组织架构BOM也不会下探到50层这个上限更多是为异常数据兜底MAX_LEVEL50 while [ $CURRENT_LEVEL -le $MAX_LEVEL ]; do # 执行一轮扩展SQL # 判断新增行数 done之所以要两层保险是因为真实数据里总会有“奇葩”。比如某条记录的 parent_id 指向了自己或者A的上级是B、B的上级是A。这种情况下如果只判断新增行数循环会一直跑因为每一轮都能找到“新”节点。有了最大层级上限至少能在50层时强制退出保护集群计算资源。2.4 为什么用临时表而不是视图有人会问能不能直接建个视图每次查询时动态递归Hive对递归视图是不支持的因为视图在解析时就会展开成固定SQL无法表达动态深度。而且视图每次查询都要重新计算整棵树性能开销非常大。临时表的优势在于中间结果可以复用。迭代过程中每一轮产生的数据都物化在表里下一轮只需要基于当前层去扩展。如果某个中间环节出问题也能直接查临时表定位。生产环境里我习惯给临时表名加上当天日期后缀例如 tmp_tree_20250110方便回溯和清理。3. 实操过程跑通一个多级分类展开的完整案例3.1 场景定义与预期结果下面用一个电商商品分类的场景来实际跑一遍。分类树表叫 dim_category字段包括 category_id类目ID、category_name类目名称、parent_id父类目ID。数据如下A 全部分类 null A1 手机数码 A A2 家用电器 A A11 智能手机 A1 A12 手机配件 A1 A121 手机壳 A12 A21 大家电 A2 A211 冰箱 A21我们的目标结果是每个叶子类目一行包含它的所有祖先链。也就是说 A121 这一行要输出 root_idA、pathA-A1-A12-A121、level4。这样业务方做汇总时不管按哪个层级看都能直接通过这张展开表过滤出对应子树的全部数据。3.2 完整脚本实现与关键参数说明整个实现我会放在一个bash脚本里用hive -e执行SQL循环用shell的while控制。脚本全貌如下#!/bin/bash DB_NAMEdw_dim TMP_BASEtmp_category_tree TABLE_NAMEdim_category MAX_LEVEL30 CUR_LEVEL1 # 每一轮开始前把上次残留清掉 hive -e DROP TABLE IF EXISTS ${DB_NAME}.${TMP_BASE}; # 第一层根结点 hive --database ${DB_NAME} -e CREATE TABLE ${TMP_BASE} AS SELECT category_id AS root_id, category_id AS node_id, category_name AS node_name, 1 AS level, category_id AS path FROM ${TABLE_NAME} WHERE parent_id IS NULL; while [ ${CUR_LEVEL} -le ${MAX_LEVEL} ]; do # 当前层扩展 hive --database ${DB_NAME} -e SET hive.exec.max.created.files200000; SET hive.auto.convert.jointrue; INSERT INTO TABLE ${TMP_BASE} SELECT t.root_id, d.category_id, d.category_name, t.level 1, concat(t.path, -, d.category_id) FROM ${TMP_BASE} t JOIN ${TABLE_NAME} d ON t.node_id d.parent_id LEFT JOIN ${TMP_BASE} exist ON exist.root_id t.root_id AND exist.node_id d.category_id WHERE t.level ${CUR_LEVEL} AND d.category_id IS NOT NULL AND exist.node_id IS NULL; # 检查本轮新增行数 CNT$(hive --database ${DB_NAME} -S -e SELECT COUNT(*) FROM ${TMP_BASE} WHERE level $((CUR_LEVEL 1)); | tail -1) if [ ${CNT} 0 ]; then echo 第 ${CUR_LEVEL} 层无扩展迭代结束 break fi CUR_LEVEL$((CUR_LEVEL 1)) done echo 最终层级深度${CUR_LEVEL}这个脚本有几个地方值得特别注意。tail -1取count值是因为hive -S模式下仍然可能在前面输出日志真正的查询结果在最后一行需要取最后一个非空值。分区表在建临时表的时候要格外小心。如果原表是分区表JOIN时最好加上分区过滤条件否则全表扫描一次耗时很长。我在上面的示例里简化为非分区表实际生产中建临时表前先EXPLAIN一下执行计划确认没有全表扫描再跑。迭代次数不是由我们拍脑袋定的而是由数据本身决定。分类树只有4层那么跑到第4轮时第5轮的新增行数为0脚本自然退出不会多跑无用功。3.3 结果校验与边界情况验证跑完之后先做基础校验总行数应该等于非根节点数加根节点数同时每棵树的节点数应该和原表里该子树下的节点数一致。-- 按根节点统计临时表的节点数 SELECT root_id, COUNT(*) AS node_cnt FROM tmp_category_tree GROUP BY root_id ORDER BY node_cnt DESC;对照原数据A树下应该有7个节点A1、A2、A11、A12、A121、A21、A211如果COUNT出来是7说明没有重复、没有遗漏。再看路径字段用最长路径那一行检查链路SELECT * FROM tmp_category_tree WHERE path LIKE %A121% LIMIT 10;这里能看到 A121 对应的 path 是 A-A1-A12-A121level 是4完全符合预期。边界情况主要验证两类单节点树只有根没有子节点以及根节点缺失的孤立数据。单节点树在第一个循环自然结束因为第二层查不到任何数据。孤立数据是指某些节点的 parent_id 在表中不存在这种情况下它永远不会被任何树包含在迭代结果里自然也不会出现。如果业务方要求孤立节点也要保留需要单独处理比如把它当成根节点补进结果里。3.4 性能优化分区、压缩与小文件合并迭代类计算最怕的就是数据越跑越慢。我在这类任务上做过几轮针对性优化效果很直接。第一个优化点是每轮循环尽量只扫必要的数据。扩展层的核心是WHERE t.level ${CUR_LEVEL}这个条件能保证临时表只读取一层数据参与JOIN。如果临时表建立时按level做了分区那性能还能再上一个台阶但大多数场景下ORC存储加谓词下推已经够用。第二个优化点是控制小文件。每轮INSERT都会产生一批文件循环几十轮下来文件数量可能上千。对后续查询而言上千个小文件意味着上千个task启动开销非常浪费资源。解决方案是每轮结束后对临时表执行一次合并INSERT OVERWRITE TABLE tmp_category_tree SELECT * FROM tmp_category_tree;这条SQL看起来像是自我复制但实际上通过一次完整的读写能把小文件合并成少量大文件。更好的做法是定期做比如每5轮做一次避免每次都做全表重写。第三个优化点是压缩格式。临时表创建时直接指定ORC加snappy压缩能显著减少IOCREATE TABLE tmp_category_tree STORED AS ORC TBLPROPERTIES (orc.compressSNAPPY) AS SELECT ...;在Hive里ORC文件默认就带轻量级索引和谓词下推能力比纯文本的中间表快不少。对于临时表这种反复读写的场景换掉默认存储格式是最划算的一笔投资。4. 常见问题与排查技巧实录4.1 死循环环路数据导致的无限迭代迭代方案最大的隐患就是环路数据。前几天我排查一个线上任务脚本看起来没毛病但跑了几个小时都停不下来最后是MAX_LEVEL兜底才结束。一查数据果然有两条记录的parent_id互相指向对方。这种环路数据怎么提前发现有个简单SQL可以先扫一遍SELECT t1.id, t2.id FROM dim_category t1 JOIN dim_category t2 ON t1.parent_id t2.id AND t2.parent_id t1.id;如果查出来有结果说明存在双向环路。还有一个更普遍的检查方式如果全表最大层级超过预设值大概率有环路。我一般会在迭代脚本跑完后加一个校验脚本输出所有 level 大于 MAX_LEVEL 的节点方便快速定位异常数据。防环路的另一个手段就是前面提到的半连接去重。只要每个节点只允许被加入一次即使数据有环路循环也会因为节点已经存在而停止增长配合MAX_LEVEL就更稳妥了。4.2 数据倾斜某个根节点下面挂了几百万子节点类递归计算天然会有数据倾斜问题因为真实世界里的树就不平衡。比如某大型公司组织架构销售部门可能挂了上万人而总部职能部门只有几十个人。迭代过程里每次扩展最大的那个根节点都会成为长尾任务。处理办法有几个思路。第一开启mapjoin。因为参与JOIN的临时表数据量通常不大mapjoin能把JOIN操作下推到map端避开reduce阶段的单点压力。第二把临时表按root_id做分桶。分桶能让相同root_id的数据分布到同一个桶里配合SMB join能显著缓解长尾。更彻底的方案是把树的展开逻辑拆成两段先算出所有节点到根的完整路径再统计时按路径前缀聚合。但这个方案对业务SQL改动较大我在迭代法跑通、性能可接受的前提下一般不去过度优化。先让功能跑起来再观察瓶颈在哪是数仓调优的基本节奏。4.3 临时表无限膨胀与路径超长随着层级加深path字段会越来越长。如果你的业务层级真的非常深比如到了30层以上concat拼接的路径字符串可能超过几千字符。这在Hive里不会报错但会拖慢查询和存储的效率。解决方案是分两步走迭代过程中只保留root_id、node_id、level三个最小字段path在迭代结束后用专门的SQL一次性回填。或者干脆不要path只在最终结果里关联原表获取名称路径展示交给BI层去TREE结构。别让路径拼接成为迭代过程中的性能包袱。还有一点临时表的清理要及时。我见过有人把跨天用的临时表残留了一周占了几个T的存储空间。建议在脚本最后统一DROP并且设置一个定时任务清理超过N天未使用的临时表。4.4 固定深度展开的快捷方案与适用判断不是所有递归需求都必须上迭代法。如果业务上已经明确层级不会超过3层比如用户→订单→订单明细这种固定关系写固定SQL是最省事的。SELECT l1.id AS user_id, l2.id AS order_id, l3.id AS detail_id FROM orders l2 JOIN users l1 ON l2.user_id l1.id LEFT JOIN order_details l3 ON l2.id l3.order_id;这种情况下用迭代法反而有点小题大做。怎么判断我的经验是数一下你做过或即将要做的这类需求如果层级深度在5层以内且表结构稳定自连接就够了如果层级未知、可能变化、树也不平衡直接上迭代框架。搞一套可复用的迭代模板脚本第一次花半小时搭好后面所有树形展开需求都套用是最划算的投入。5. 迭代方案的可扩展玩法树形展开只是类递归计算的基础需求。实际做业务分析时往往还要在这棵树上做更多文章。一个是路径维度的聚合分析。展开表拿到后想统计每个二级类目下的总营业额直接按path的第二段分组就行SELECT split(path, -)[1] AS level2_id, SUM(order_amount) AS total_amount FROM dw_order_detail JOIN tmp_category_tree ON dw_order_detail.category_id tmp_category_tree.node_id GROUP BY split(path, -)[1];另一个是找叶子节点。展开表里 level 最大的那些节点就是叶子一条SQL就能过滤出来不需要额外打标。再配合维度表join很容易算出每个叶子节点归属的完整链路。还有一种是多根树的合并展示。如果业务上有多个独立的根比如多品牌分类迭代法天然支持因为每个root_id都是一棵独立的树。最终展示时只要用root_id做过滤条件互不干扰。我自己后续扩展的想法是把迭代循环封装成一个通用脚本入口传一个表名、两个字段名id、parent_id自动生成完整展开结果表。这样业务方再提“帮我展开一下XX树”的需求直接从模板复制改参数就行连循环逻辑都不用重写。最后再说几句实操体验类递归这套东西我前前后后在好几个项目里用过踩过的坑不算少换个环境就换一批问题。但核心思路始终没变过先找根再扩层判停靠新增行数保险靠最大层级防坑靠半连接去重。只要把这四条刻在脑子里任何层级数据的展开需求你都能hold住。如果你第一次跑这类脚本我建议先拿几十条数据的小表试跑每轮循环后打印一下当前层数和新增行数把整个过程看清楚了再上生产。生产环境里跑之前一定记得把落临时表的目录权限、保留周期和清理策略定好别让临时表变成事故表。最后一点提醒不管方案多稳结果都要和业务方确认过口径再发布树形数据的定义根怎么算、层怎么算、含不含自己差一个字符结果就完全不一样。