如何在Hive/Impala中查询组织的完整祖先层级关系？

因为你只有只读权限，只能用纯SQL实现，Hive（2.1及以上版本）和Impala（2.0及以上版本）都支持递归CTE（Common Table Expressions），这是解决层级祖先链查询的最佳方案。

先明确你的表字段对应关系 #

从你给出的直接查询语句来看，字段对应关系如下：

• organisations表：organisation_id（组织ID）、organisation_name（组织名称）
• relationships表：relationship_orgid（子组织ID）、relationship_id（对应上级组织ID）

递归CTE查询完整祖先链 #

以下是实现查询的SQL语句，会输出每个组织的完整祖先路径（从自身向上到最顶层组织）：

WITH RECURSIVE org_ancestors AS (
    -- 锚点查询：初始化所有组织，祖先链先包含自身
    SELECT 
        o.organisation_id AS org_id,
        o.organisation_name AS org_name,
        o.organisation_id AS ancestor_id,
        o.organisation_name AS ancestor_name,
        CAST(o.organisation_name AS STRING) AS ancestor_chain,
        0 AS depth
    FROM organisations o

    UNION ALL

    -- 递归部分：不断向上关联上级组织，拼接祖先链
    SELECT 
        oa.org_id,
        oa.org_name,
        o.organisation_id AS ancestor_id,
        o.organisation_name AS ancestor_name,
        CONCAT(oa.ancestor_chain, ' -> ', o.organisation_name) AS ancestor_chain,
        oa.depth + 1 AS depth
    FROM org_ancestors oa
    JOIN relationships r 
        ON oa.ancestor_id = r.relationship_orgid  -- 当前祖先作为子组织，找它的上级
    JOIN organisations o 
        ON r.relationship_id = o.organisation_id  -- 关联上级组织的信息
)
-- 最终查询：按组织ID分组，取最深的那条记录（即完整的祖先链）
SELECT 
    org_id,
    org_name,
    MAX(ancestor_chain) AS full_ancestor_chain
FROM org_ancestors
GROUP BY org_id, org_name
ORDER BY org_id;

语句解释 #

1. 锚点查询：先把每个组织自身作为初始的祖先链，depth（层级）设为0。
2. 递归部分：每次从之前的递归结果里，找到当前祖先的上级组织，把上级名称拼接到祖先链后面，层级+1，直到某个组织没有上级（无法匹配relationships表）时，递归自动停止。
3. 最终聚合：因为每个组织会在递归中生成多条记录（对应每一层祖先），所以用MAX(ancestor_chain)取最长的那条，就是完整的从自身到顶层的祖先链。

结果示例 #

输出会类似你需要的格式：

org_id | org_name | full_ancestor_chain
-------|----------|----------------------
1001   | baby     | baby -> mother -> grandmother -> great-grandmother

注意事项 #

• 如果你的Hive版本低于2.1，递归CTE不支持，这种情况可以用多层自连接（但只能固定查询N层，不够灵活），比如连接3次relationships表查3级祖先，但无法处理不确定深度的链条。
• 如果数据中存在循环引用（比如A的上级是B，B的上级是A），需要在递归部分加条件避免无限循环，比如增加WHERE depth < 10限制最大递归深度，或者记录已访问的组织ID。

内容的提问来源于stack exchange，提问作者user1019450