ODI 12c可重用映射/组件下数据血缘查询无结果求助

问题原因 #

当映射中使用可重用映射/组件时，元数据会以嵌套层级存储在ODI仓库中，原始SQL仅遍历了顶层映射的直接组件，未递归解析可重用映射内部的结构，因此无法返回对应记录。

解决方法 #

使用递归CTE（Common Table Expression）遍历所有层级的映射组件，包含可重用映射的嵌套结构，修改后的查询如下：

WITH RECURSIVE map_hierarchy AS (
    -- 基础层：顶层映射的组件
    SELECT 
        m.i_mapping AS parent_map_id,
        m.name AS parent_map_name,
        mcomp.i_map_comp AS comp_id,
        mcomp.i_owner_mapping AS owner_map_id,
        mr.i_ref_id AS ref_id,
        mr.ref_type AS ref_type,
        t.table_name AS target_table,
        1 AS hierarchy_level
    FROM 
        dnogp2_bia_odirepo.snp_mapping m
        INNER JOIN dnogp2_bia_odirepo.snp_map_comp mcomp ON m.i_mapping = mcomp.i_owner_mapping
        LEFT JOIN dnogp2_bia_odirepo.snp_map_ref mr ON mcomp.i_map_ref = mr.i_map_ref
        LEFT JOIN dnogp2_bia_odirepo.snp_table t ON mr.i_ref_id = t.i_table
    WHERE 
        m.name LIKE '%SALES_ENGG_REVISION%'

    UNION ALL

    -- 递归层：解析可重用映射内部的组件
    SELECT 
        mh.parent_map_id,
        mh.parent_map_name,
        mcomp.i_map_comp AS comp_id,
        mcomp.i_owner_mapping AS owner_map_id,
        mr.i_ref_id AS ref_id,
        mr.ref_type AS ref_type,
        t.table_name AS target_table,
        mh.hierarchy_level + 1 AS hierarchy_level
    FROM 
        map_hierarchy mh
        INNER JOIN dnogp2_bia_odirepo.snp_mapping rm ON mh.ref_id = rm.i_mapping  -- 关联可重用映射
        INNER JOIN dnogp2_bia_odirepo.snp_map_comp mcomp ON rm.i_mapping = mcomp.i_owner_mapping
        LEFT JOIN dnogp2_bia_odirepo.snp_map_ref mr ON mcomp.i_map_ref = mr.i_map_ref
        LEFT JOIN dnogp2_bia_odirepo.snp_table t ON mr.i_ref_id = t.i_table
    WHERE 
        mh.ref_type = 'MAPPING'  -- 仅处理可重用映射类型的引用
)
-- 关联表达式和属性表，获取列映射信息
SELECT 
    mh.parent_map_name AS mapping_name,
    mh.target_table,
    ma.name AS target_column,
    me.txt AS source_column
FROM 
    map_hierarchy mh
    INNER JOIN dnogp2_bia_odirepo.snp_map_attr ma ON mh.comp_id = ma.i_owner_map_comp
    INNER JOIN dnogp2_bia_odirepo.snp_map_expr me ON ma.i_map_attr = me.i_owner_map_attr
    INNER JOIN dnogp2_bia_odirepo.snp_map_expr_ref mer ON me.i_map_expr = mer.i_owner_map_expr
    INNER JOIN dnogp2_bia_odirepo.snp_map_cp mc ON mer.i_scoping_map_cp = mc.i_map_cp
WHERE 
    mh.target_table IS NOT NULL  -- 过滤出目标表相关的映射
ORDER BY 
    mh.parent_map_name, mh.target_table, ma.name;

关键说明 #

• 递归CTE map_hierarchy 先获取顶层映射的组件，再递归解析可重用映射内部的所有层级组件
• 通过 ref_type = 'MAPPING' 判断当前引用是否为可重用映射，避免循环递归
• 最后关联属性表和表达式表，获取目标列与源列的映射逻辑

内容的提问来源于stack exchange，提问作者A R Divya