Snowflake中如何实现内置视图QUERY_HISTORY的变更跟踪？

由于Snowflake的内置视图（如information_schema.query_history()）不支持直接创建变更跟踪流，你可以通过以下两种方案实现需求：

方案一：同步到自定义表后启用变更跟踪 #

1. 创建与内置视图结构匹配的自定义表，用于存储历史查询数据：

CREATE OR REPLACE TABLE MY_QUERY_HISTORY (
    QUERY_ID VARCHAR(100),
    QUERY_TEXT VARCHAR(16777216),
    DATABASE_NAME VARCHAR(100),
    SCHEMA_NAME VARCHAR(100),
    QUERY_TYPE VARCHAR(100),
    START_TIME TIMESTAMP_LTZ,
    END_TIME TIMESTAMP_LTZ,
    TOTAL_ELAPSED_TIME NUMBER,
    LOAD_TIMESTAMP TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);

2. 创建定时任务，定期将新增的查询记录同步到自定义表：

CREATE OR REPLACE TASK SYNC_QUERY_HISTORY_TASK
WAREHOUSE = YOUR_WH_NAME
SCHEDULE = 'USING CRON 0 * * * * UTC' -- 每小时执行一次，可按需调整频率
AS
INSERT INTO MY_QUERY_HISTORY
SELECT 
    QUERY_ID, QUERY_TEXT, DATABASE_NAME, SCHEMA_NAME, QUERY_TYPE, START_TIME, END_TIME, TOTAL_ELAPSED_TIME, CURRENT_TIMESTAMP()
FROM INFORMATION_SCHEMA.QUERY_HISTORY()
WHERE START_TIME > (SELECT COALESCE(MAX(START_TIME), '1970-01-01') FROM MY_QUERY_HISTORY);

3. 启用任务：

ALTER TASK SYNC_QUERY_HISTORY_TASK RESUME;

4. 对自定义表创建流来跟踪变更：

CREATE OR REPLACE STREAM MY_QUERY_HISTORY_STREAM ON TABLE MY_QUERY_HISTORY;

方案二：通过任务直接处理新增记录 #

如果不需要持久化全量历史数据，可直接在任务中处理新增查询记录，跳过中间表：

CREATE OR REPLACE TASK PROCESS_NEW_QUERIES
WAREHOUSE = YOUR_WH_NAME
SCHEDULE = 'USING CRON 5 * * * * UTC'
AS
-- 这里编写处理逻辑，比如插入审计表、生成告警等
INSERT INTO QUERY_AUDIT_LOG
SELECT QUERY_ID, QUERY_TEXT, START_TIME, CURRENT_USER()
FROM INFORMATION_SCHEMA.QUERY_HISTORY()
WHERE START_TIME > DATEADD(HOUR, -1, CURRENT_TIMESTAMP()); -- 匹配任务调度频率，过滤最近1小时的记录

注意事项 #

• 调整任务调度频率时，需确保WHERE条件能精准过滤上次执行后新增的记录，避免重复同步。
• 按需选择同步字段，query_history包含大量字段，无需全部同步。
• 确保任务使用的仓库拥有读取information_schema.query_history()及写入目标表的权限。

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