如何在Snowflake中查询存储过程执行时间并生成执行历史表？

一、Snowflake是否内置跟踪存储过程执行时间？ #

是的，Snowflake会自动跟踪所有存储过程的调用记录，这些数据存储在系统级的查询历史视图中。存储过程的调用会被标记为STORED_PROCEDURE_CALL类型的查询，包含调用的开始时间、结束时间、执行时长等核心信息，无需手动通过SnowSpark埋点统计。

二、如何获取存储过程的执行时间数据？ #

Snowflake提供两类视图可以查询这些数据，适用于不同场景：

1. 使用ACCOUNT_USAGE.QUERY_HISTORY（推荐用于历史数据） #

该视图保留长达1年左右的全账户查询历史，适合获取长期的存储过程执行记录。示例SQL：

SELECT
  QUERY_ID,
  QUERY_TEXT, -- 存储过程调用语句
  START_TIME, -- 调用开始时间
  END_TIME, -- 调用结束时间
  DATEDIFF(SECOND, START_TIME, END_TIME) AS EXECUTION_DURATION_SECONDS, -- 执行时长（秒）
  USER_NAME, -- 执行用户
  ROLE_NAME, -- 执行角色
  DATABASE_NAME, -- 存储过程所在数据库
  SCHEMA_NAME -- 存储过程所在Schema
FROM
  SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
  QUERY_TYPE = 'STORED_PROCEDURE_CALL'
  AND START_TIME >= DATEADD(DAY, -30, CURRENT_TIMESTAMP()) -- 筛选最近30天数据
ORDER BY
  START_TIME DESC;

2. 使用INFORMATION_SCHEMA.QUERY_HISTORY（用于近期数据） #

该视图仅保留最近7天的查询记录，查询性能更快，适合获取短期的执行数据：

SELECT
  QUERY_ID,
  QUERY_TEXT,
  START_TIME,
  END_TIME,
  DATEDIFF(SECOND, START_TIME, END_TIME) AS EXECUTION_DURATION_SECONDS,
  USER_NAME
FROM
  INFORMATION_SCHEMA.QUERY_HISTORY
WHERE
  QUERY_TYPE = 'STORED_PROCEDURE_CALL'
ORDER BY
  START_TIME DESC;

三、生成存储过程执行时间历史表 #

要长期留存这些数据并方便分析，可以创建一张专用历史表，通过定时任务自动同步数据：

1. 创建历史表 #

CREATE OR REPLACE TABLE YOUR_DATABASE.YOUR_SCHEMA.PROCEDURE_EXECUTION_HISTORY (
  QUERY_ID STRING PRIMARY KEY, -- 唯一标识每次调用
  QUERY_TEXT STRING,
  START_TIME TIMESTAMP_LTZ,
  END_TIME TIMESTAMP_LTZ,
  EXECUTION_DURATION_SECONDS NUMBER,
  USER_NAME STRING,
  ROLE_NAME STRING,
  DATABASE_NAME STRING,
  SCHEMA_NAME STRING,
  LOAD_TIMESTAMP TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() -- 数据加载时间
);

2. 创建定时同步任务 #

使用Snowflake任务自动每日同步前一天的存储过程调用记录，避免重复插入：

CREATE OR REPLACE TASK YOUR_DATABASE.YOUR_SCHEMA.SYNC_PROCEDURE_HISTORY_TASK
WAREHOUSE = YOUR_WAREHOUSE_NAME -- 指定执行任务的仓库
SCHEDULE = 'USING CRON 0 0 * * * UTC' -- 每日UTC零点执行
AS
MERGE INTO YOUR_DATABASE.YOUR_SCHEMA.PROCEDURE_EXECUTION_HISTORY t
USING (
  SELECT
    QUERY_ID,
    QUERY_TEXT,
    START_TIME,
    END_TIME,
    DATEDIFF(SECOND, START_TIME, END_TIME) AS EXECUTION_DURATION_SECONDS,
    USER_NAME,
    ROLE_NAME,
    DATABASE_NAME,
    SCHEMA_NAME
  FROM
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE
    QUERY_TYPE = 'STORED_PROCEDURE_CALL'
    AND START_TIME >= DATEADD(DAY, -1, CURRENT_TIMESTAMP())
    AND START_TIME < CURRENT_TIMESTAMP()
) s
ON t.QUERY_ID = s.QUERY_ID
WHEN NOT MATCHED THEN
INSERT (QUERY_ID, QUERY_TEXT, START_TIME, END_TIME, EXECUTION_DURATION_SECONDS, USER_NAME, ROLE_NAME, DATABASE_NAME, SCHEMA_NAME)
VALUES (s.QUERY_ID, s.QUERY_TEXT, s.START_TIME, s.END_TIME, s.EXECUTION_DURATION_SECONDS, s.USER_NAME, s.ROLE_NAME, s.DATABASE_NAME, s.SCHEMA_NAME);

3. 启动任务 #

ALTER TASK YOUR_DATABASE.YOUR_SCHEMA.SYNC_PROCEDURE_HISTORY_TASK RESUME;

注意事项 #

• 确保执行任务的角色拥有ACCOUNT_USAGE.QUERY_HISTORY的读取权限，以及历史表的增改权限。
• 可以根据需求调整任务的执行频率（比如每小时同步）或时间范围。

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