ODI 12c工作库可复用映射的数据血缘SQL查询优化

要解决可复用映射内部列血缘的追踪问题，核心是利用ODI 12c元数据表的递归关联关系，通过递归CTE逐层拆解可复用映射的内部组件和列映射逻辑。以下是具体实现方案：

核心元数据表关联关系 #

ODI 12c中与可复用映射血缘相关的关键表包括：

• SNP_REP_MAP：存储可复用映射的基础配置（ID、名称等）
• SNP_REP_MAP_COMP：记录可复用映射内部的组件（源、目标、转换节点）
• SNP_REP_MAP_COL：存储可复用映射内部的列级映射关系
• SNP_MAP_COMP：普通映射中的组件信息，包含引用的可复用映射实例
• SNP_MAP_COL：普通映射的列映射关系，关联可复用映射实例的输入输出列

递归血缘查询SQL示例 #

以下SQL实现从目标列EMPLOEE_1.SAL反向追踪到源列SAL的完整链路，自动拆解可复用映射内部逻辑：

WITH COLUMN_LINEAGE AS (
    -- 初始化：定位目标列所在的普通映射组件
    SELECT 
        mc.MAP_ID,
        mc.MAP_COMP_ID,
        mc.TYPE AS COMP_TYPE,
        mcol.COL_NAME AS TARGET_COL,
        mcol.SOURCE_COL_NAME AS SOURCE_COL,
        mcol.SOURCE_MAP_COMP_ID,
        mc.REP_MAP_ID,
        1 AS LEVEL_ORDER
    FROM SNP_MAP_COL mcol
    JOIN SNP_MAP_COMP mc ON mcol.MAP_COMP_ID = mc.MAP_COMP_ID
    LEFT JOIN SNP_REP_MAP rm ON mc.REP_MAP_ID = rm.REP_MAP_ID
    WHERE mcol.COL_NAME = 'SAL' 
      AND mc.TABLE_NAME = 'EMPLOEE_1' -- 指定目标表名
    UNION ALL
    -- 递归：拆解可复用映射内部的列映射关系
    SELECT 
        rmc.REP_MAP_ID AS MAP_ID,
        rmc.MAP_COMP_ID,
        rmc.TYPE AS COMP_TYPE,
        rmcol.COL_NAME AS TARGET_COL,
        rmcol.SOURCE_COL_NAME AS SOURCE_COL,
        rmcol.SOURCE_MAP_COMP_ID,
        rmc.REP_MAP_ID,
        cl.LEVEL_ORDER + 1 AS LEVEL_ORDER
    FROM COLUMN_LINEAGE cl
    -- 关联可复用映射的内部列映射
    JOIN SNP_REP_MAP_COL rmcol ON cl.REP_MAP_ID = rmcol.REP_MAP_ID 
        AND cl.SOURCE_COL = rmcol.COL_NAME
    JOIN SNP_REP_MAP_COMP rmc ON rmcol.MAP_COMP_ID = rmc.MAP_COMP_ID
    WHERE cl.COMP_TYPE = 'REP_MAP' -- 仅处理可复用映射类型组件
)
-- 格式化输出血缘链路
SELECT 
    LEVEL_ORDER AS 链路阶段,
    CASE 
        WHEN COMP_TYPE = 'REP_MAP' THEN '可复用映射: ' || (SELECT REP_MAP_NAME FROM SNP_REP_MAP WHERE REP_MAP_ID = REP_MAP_ID)
        ELSE '普通表组件: ' || (SELECT TABLE_NAME FROM SNP_MAP_COMP WHERE MAP_COMP_ID = MAP_COMP_ID)
    END AS 组件信息,
    SOURCE_COL AS 源列,
    TARGET_COL AS 目标列
FROM COLUMN_LINEAGE
ORDER BY LEVEL_ORDER ASC;

关键调整说明 #

1. 正向追踪适配：如果需要从源列SAL追踪到目标列EMPLOEE_1.SAL，只需将初始化部分的WHERE条件改为匹配源列，同时调整递归关联逻辑为从源列指向目标列。
2. 多层嵌套支持：递归CTE会自动处理可复用映射嵌套的场景，无论嵌套多少层都能完整拆解。
3. 扩展信息：若需添加映射名称、项目名称等信息，可关联SNP_MAP（映射表）、SNP_PROJECT（项目表）补充字段。
4. 表前缀适配：如果ODI工作库的表前缀不是默认的SNP，需替换为实际前缀。

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