如何在Snowflake中获取父Query ID与根ID并解决子存储过程失败捕获问题

我编写了一个脚本，用于获取所有存储过程（包括父存储过程中执行的子存储过程）的状态。当所有子存储过程成功完成时，脚本运行正常，但如果有子存储过程执行失败，就无法捕获并填充相关详情。

我的脚本执行步骤如下：

• 识别存储过程名称；
• 检查QUERY_HISTORY以获取存储过程的所有相关详情；
• 查询ACCESS_HISTORY以捕获子存储过程或关联查询及其对应的Parent Query ID和Root Query ID。

问题似乎在于ACCESS_HISTORY仅保留成功完成的查询数据。我的需求是无论查询成功还是失败，都能获取父查询ID或根查询ID，只要存储过程运行过，就应出现在结果中。请问我该如何修改实现方式以达成需求？

原始脚本

WITH PARENT_RUN AS (
    SELECT
    Q.QUERY_ID,
    Q.QUERY_TEXT,
    Q.DATABASE_NAME AS RUN_DB,
    Q.SCHEMA_NAME AS RUN_SCHEMA,
    Q.QUERY_TYPE,
    Q.SESSION_ID,
    Q.USER_NAME,
    Q.ROLE_NAME,
    Q.WAREHOUSE_NAME,
    Q.WAREHOUSE_SIZE,
    Q.WAREHOUSE_TYPE,
    Q.QUERY_TAG,
    Q.EXECUTION_STATUS,
    Q.ERROR_CODE,
    Q.ERROR_MESSAGE,
    Q.START_TIME,
    Q.END_TIME,
    Q.EXECUTION_TIME,
    Q.COMPILATION_TIME,
    Q.QUEUED_PROVISIONING_TIME,
    A.PARENT_QUERY_ID,
    A.ROOT_QUERY_ID
    FROM
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY Q
    JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY A
    ON Q.QUERY_ID = A.QUERY_ID
    WHERE
        Q.QUERY_TYPE = 'CALL'
            AND (Q.QUERY_TEXT ILIKE 'CALL <parent Proc Name>(%')
        AND Q.START_TIME > '2000-01-01 00:00:00'
    ),
    CHILD_QUERY_ID AS (
        SELECT
            A.QUERY_ID
        FROM PARENT_RUN Q
        JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY A
        ON Q.QUERY_ID = A.PARENT_QUERY_ID OR Q.QUERY_ID = A.ROOT_QUERY_ID
    ),
    CHILD_RUN AS (
    SELECT
    Q.QUERY_ID,
    Q.QUERY_TEXT,
    Q.DATABASE_NAME AS RUN_DB,
    Q.SCHEMA_NAME AS RUN_SCHEMA,
    Q.QUERY_TYPE,
    Q.SESSION_ID,
    Q.USER_NAME,
    Q.ROLE_NAME,
    Q.WAREHOUSE_NAME,
    Q.WAREHOUSE_SIZE,
    Q.WAREHOUSE_TYPE,
    Q.QUERY_TAG,
    Q.EXECUTION_STATUS,
    Q.ERROR_CODE,
    Q.ERROR_MESSAGE,
    Q.START_TIME,
    Q.END_TIME,
    Q.EXECUTION_TIME,
    Q.COMPILATION_TIME,
    Q.QUEUED_PROVISIONING_TIME,
    A.PARENT_QUERY_ID,
    A.ROOT_QUERY_ID
    FROM
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY Q
    JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY A
    ON Q.QUERY_ID = A.QUERY_ID
    JOIN CHILD_QUERY_ID C
    ON Q.QUERY_ID = C.QUERY_ID
    WHERE
        Q.QUERY_TYPE = 'CALL' AND Q.START_TIME >  '2000-01-01 00:00:00'
    ),
    FINAL_AUDIT AS (
        SELECT * FROM PARENT_RUN
        UNION 
        SELECT * FROM CHILD_RUN
    )

    SELECT 
        QUERY_ID,
        QUERY_TEXT,
        START_TIME,
        END_TIME,
        EXECUTION_TIME,
        EXECUTION_STATUS,
        QUERY_TYPE,
        SESSION_ID,
        USER_NAME,
        ROLE_NAME,
        WAREHOUSE_NAME,
        WAREHOUSE_SIZE,
        WAREHOUSE_TYPE,
        QUERY_TAG,
        ERROR_CODE,
        ERROR_MESSAGE,
        RUN_DB,
        RUN_SCHEMA,
        PROCEDURE_FQN,
        COMPILATION_TIME,
        QUEUED_PROVISIONING_TIME,
        PARENT_QUERY_ID,
        ROOT_QUERY_ID
    FROM FINAL_AUDIT 

核心问题在于原脚本依赖ACCESS_HISTORY关联查询，但ACCESS_HISTORY仅记录成功完成的操作，失败的子存储过程调用不会被收录。要解决这个问题，需改用QUERY_HISTORY本身的PARENT_QUERY_ID和ROOT_QUERY_ID字段——所有CALL语句（无论成功失败）都会在QUERY_HISTORY中记录这些关联ID。

修改后的脚本 #

WITH RECURSIVE PROC_CALL_HISTORY AS (
    -- 初始步骤：获取目标父存储过程的所有调用记录
    SELECT
        Q.QUERY_ID,
        Q.QUERY_TEXT,
        Q.DATABASE_NAME AS RUN_DB,
        Q.SCHEMA_NAME AS RUN_SCHEMA,
        Q.QUERY_TYPE,
        Q.SESSION_ID,
        Q.USER_NAME,
        Q.ROLE_NAME,
        Q.WAREHOUSE_NAME,
        Q.WAREHOUSE_SIZE,
        Q.WAREHOUSE_TYPE,
        Q.QUERY_TAG,
        Q.EXECUTION_STATUS,
        Q.ERROR_CODE,
        Q.ERROR_MESSAGE,
        Q.START_TIME,
        Q.END_TIME,
        Q.EXECUTION_TIME,
        Q.COMPILATION_TIME,
        Q.QUEUED_PROVISIONING_TIME,
        Q.PARENT_QUERY_ID,
        Q.ROOT_QUERY_ID,
        1 AS CALL_LEVEL
    FROM
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY Q
    WHERE
        Q.QUERY_TYPE = 'CALL'
        AND Q.QUERY_TEXT ILIKE 'CALL <parent Proc Name>(%'
        AND Q.START_TIME > '2000-01-01 00:00:00'

    UNION ALL

    -- 递归步骤：获取所有关联的子存储过程调用（无论成功失败）
    SELECT
        CHILD.Q.QUERY_ID,
        CHILD.Q.QUERY_TEXT,
        CHILD.Q.DATABASE_NAME AS RUN_DB,
        CHILD.Q.SCHEMA_NAME AS RUN_SCHEMA,
        CHILD.Q.QUERY_TYPE,
        CHILD.Q.SESSION_ID,
        CHILD.Q.USER_NAME,
        CHILD.Q.ROLE_NAME,
        CHILD.Q.WAREHOUSE_NAME,
        CHILD.Q.WAREHOUSE_SIZE,
        CHILD.Q.WAREHOUSE_TYPE,
        CHILD.Q.QUERY_TAG,
        CHILD.Q.EXECUTION_STATUS,
        CHILD.Q.ERROR_CODE,
        CHILD.Q.ERROR_MESSAGE,
        CHILD.Q.START_TIME,
        CHILD.Q.END_TIME,
        CHILD.Q.EXECUTION_TIME,
        CHILD.Q.COMPILATION_TIME,
        CHILD.Q.QUEUED_PROVISIONING_TIME,
        CHILD.Q.PARENT_QUERY_ID,
        CHILD.Q.ROOT_QUERY_ID,
        PARENT.CALL_LEVEL + 1 AS CALL_LEVEL
    FROM
        PROC_CALL_HISTORY PARENT
    JOIN
        SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY CHILD
    ON
        CHILD.PARENT_QUERY_ID = PARENT.QUERY_ID
        AND CHILD.QUERY_TYPE = 'CALL'
        AND CHILD.START_TIME > '2000-01-01 00:00:00'
)

SELECT 
    QUERY_ID,
    QUERY_TEXT,
    START_TIME,
    END_TIME,
    EXECUTION_TIME,
    EXECUTION_STATUS,
    QUERY_TYPE,
    SESSION_ID,
    USER_NAME,
    ROLE_NAME,
    WAREHOUSE_NAME,
    WAREHOUSE_SIZE,
    WAREHOUSE_TYPE,
    QUERY_TAG,
    ERROR_CODE,
    ERROR_MESSAGE,
    RUN_DB,
    RUN_SCHEMA,
    CALL_LEVEL,
    COMPILATION_TIME,
    QUEUED_PROVISIONING_TIME,
    PARENT_QUERY_ID,
    ROOT_QUERY_ID
FROM PROC_CALL_HISTORY
ORDER BY CALL_LEVEL, START_TIME;

关键修改点 #

1. 使用递归CTE：通过RECURSIVE关键字遍历所有层级的存储过程调用，从父存储过程开始，递归捕获所有子存储过程（包括失败的）
2. 移除ACCESS_HISTORY依赖：直接用QUERY_HISTORY的PARENT_QUERY_ID关联父子调用，确保失败的CALL也能被捕获
3. 新增CALL_LEVEL字段：直观区分父存储过程（层级1）和子存储过程（层级2及以上）
4. 性能优化：用UNION ALL替代UNION，避免不必要的去重操作
5. 结果排序：按调用层级和启动时间排序，逻辑更清晰

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