如何在Snowflake中获取表间关联路径及等价SQL查询

一、MySQL/PostgreSQL查询的Snowflake等价写法 #

你提供的MySQL/PostgreSQL查询用于获取public schema内的表引用关系，在Snowflake中可以通过关联INFORMATION_SCHEMA下的系统视图实现等价效果，具体语句如下：

SELECT
    rc.CONSTRAINT_NAME AS name,
    kcu.TABLE_NAME AS parent_table,
    kcu.COLUMN_NAME AS parent_column,
    rc.REFERENCED_TABLE_NAME AS referenced_table,
    rc.REFERENCED_COLUMN_NAME AS referenced_column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE
    kcu.TABLE_SCHEMA = 'PUBLIC'
    AND rc.REFERENCED_TABLE_NAME IS NOT NULL;

关键说明： #

• Snowflake中REFERENTIAL_CONSTRAINTS存储外键约束的核心关联信息（被引用表、列），KEY_COLUMN_USAGE存储约束绑定的列细节，两者通过CONSTRAINT_NAME关联可得到完整字段映射。
• Snowflake的schema名称默认大小写敏感，若你的schema为小写public，可将条件中的'PUBLIC'改为对应值。

二、获取Snowflake中的表连接路径关系 #

如果需要获取多级表连接路径（如A→B→C这类跨层级关联），可通过递归CTE实现，示例语句如下：

WITH RECURSIVE table_relationships AS (
    -- 基础层级：获取所有直接外键关联
    SELECT
        kcu.TABLE_NAME AS source_table,
        kcu.COLUMN_NAME AS source_column,
        rc.REFERENCED_TABLE_NAME AS target_table,
        rc.REFERENCED_COLUMN_NAME AS target_column,
        rc.CONSTRAINT_NAME AS constraint_name,
        1 AS depth,
        CAST(kcu.TABLE_NAME || ' -> ' || rc.REFERENCED_TABLE_NAME AS VARCHAR(1000)) AS path
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    WHERE
        kcu.TABLE_SCHEMA = 'PUBLIC'
        AND rc.REFERENCED_TABLE_NAME IS NOT NULL

    UNION ALL

    -- 递归层级：遍历多级关联链路
    SELECT
        tr.source_table,
        tr.source_column,
        rc.REFERENCED_TABLE_NAME AS target_table,
        rc.REFERENCED_COLUMN_NAME AS target_column,
        rc.CONSTRAINT_NAME AS constraint_name,
        tr.depth + 1 AS depth,
        CAST(tr.path || ' -> ' || rc.REFERENCED_TABLE_NAME AS VARCHAR(1000)) AS path
    FROM table_relationships tr
    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
        ON tr.target_table = rc.TABLE_NAME
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
        ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    WHERE
        kcu.TABLE_SCHEMA = 'PUBLIC'
        AND rc.REFERENCED_TABLE_NAME IS NOT NULL
        -- 避免循环引用导致无限递归，可根据场景调整或移除
        AND rc.REFERENCED_TABLE_NAME != tr.source_table
)
SELECT * FROM table_relationships
ORDER BY depth, source_table, path;

关键说明： #

• 递归CTE分为两部分：基础查询提取所有直接外键关联，递归查询基于已有关联继续遍历被引用表的外键关系。
• depth字段标记关联层级，path字段展示完整的表连接链路。
• 循环引用过滤条件可根据实际业务场景调整，若存在合法循环关联，可移除该限制。

内容的提问来源于stack exchange，提问作者Judy T Raj