如何在Snowflake中按月逐小时迭代时间旅行查询？

问题背景 #

需要针对指定年月（比如2023年12月）的每一天每小时，执行带AT(TIMESTAMP)的时间旅行查询，但直接用子查询传入动态时间戳会报错「AT needs a constant timestamp」，要求用纯Snowflake SQL实现，输出指定CSV格式结果。

解决方案 #

Snowflake的AT(TIMESTAMP)要求时间戳为常量，因此我们可以通过动态生成批量查询语句的方式实现：

1. 生成目标年月的所有小时级时间戳 #

用生成器函数+CTE生成指定年月的每小时时间戳，适配任意年月：

-- 设置目标年月和查询条件
SET target_year = 2023;
SET target_month = 12;
SET your_condition = 'WHERE TABLE_NAME IN (''ABC'', ''GHJ'', ''ERT'')'; -- 替换为你的实际条件

WITH hourly_timestamps AS (
    -- 生成目标月第一天0点开始的每小时时间戳
    SELECT DATEADD(HOUR, seq4(), DATE_FROM_PARTS($target_year, $target_month, 1)::TIMESTAMP) AS query_ts
    FROM TABLE(GENERATOR(ROWCOUNT => 744)) -- 31天*24小时=744，覆盖所有月份的最大小时数
    -- 过滤超出目标月的时间戳
    WHERE query_ts < DATEADD(MONTH, 1, DATE_FROM_PARTS($target_year, $target_month, 1))::TIMESTAMP
)

2. 拼接查询语句并批量执行 #

将每个时间戳对应的查询用UNION ALL拼接，通过EXECUTE IMMEDIATE执行：

SET target_year = 2023;
SET target_month = 12;
SET your_condition = 'WHERE TABLE_NAME IN (''ABC'', ''GHJ'', ''ERT'')';

WITH hourly_timestamps AS (
    SELECT DATEADD(HOUR, seq4(), DATE_FROM_PARTS($target_year, $target_month, 1)::TIMESTAMP) AS query_ts
    FROM TABLE(GENERATOR(ROWCOUNT => 744))
    WHERE query_ts < DATEADD(MONTH, 1, DATE_FROM_PARTS($target_year, $target_month, 1))::TIMESTAMP
),
batch_queries AS (
    -- 拼接所有小时的查询语句
    SELECT LISTAGG(
        'SELECT TABLE_NAME, ''' || TO_CHAR(query_ts, 'YYYY-MM-DD HH24:MI:SS') || ''' AS LAST_MODIFIED_UTC FROM MY_SCHEMA.MY_TABLE AT(TIMESTAMP => ''' || TO_CHAR(query_ts, 'YYYY-MM-DD HH24:MI:SS') || '''::TIMESTAMP) ' || $your_condition,
        ' UNION ALL '
    ) AS full_sql
    FROM hourly_timestamps
)
-- 执行拼接好的SQL
SELECT EXECUTE IMMEDIATE (SELECT full_sql FROM batch_queries);

3. 结果导出 #

执行完成后，在Snowflake UI中直接导出结果为CSV即可，格式完全符合需求：

TABLE_NAME,LAST_MODIFIED_UTC
ABC,'2023-12-23 00:00:00'
ABC,'2023-12-23 01:00:00'
GHJ,'2023-12-23 05:00:00'

可选：封装为存储过程（重复执行更方便） #

如果需要多次执行不同年月的查询，可创建存储过程：

CREATE OR REPLACE PROCEDURE RUN_HOURLY_TIME_TRAVEL(target_year INT, target_month INT, condition_str STRING)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    start_ts TIMESTAMP := DATE_FROM_PARTS(target_year, target_month, 1)::TIMESTAMP;
    end_ts TIMESTAMP := DATEADD(MONTH, 1, start_ts);
    full_sql STRING;
BEGIN
    WITH hourly_timestamps AS (
        SELECT DATEADD(HOUR, seq4(), start_ts) AS query_ts
        FROM TABLE(GENERATOR(ROWCOUNT => 744))
        WHERE query_ts < end_ts
    )
    SELECT LISTAGG(
        'SELECT TABLE_NAME, ''' || TO_CHAR(query_ts, 'YYYY-MM-DD HH24:MI:SS') || ''' AS LAST_MODIFIED_UTC FROM MY_SCHEMA.MY_TABLE AT(TIMESTAMP => ''' || TO_CHAR(query_ts, 'YYYY-MM-DD HH24:MI:SS') || '''::TIMESTAMP) ' || condition_str,
        ' UNION ALL '
    ) INTO full_sql
    FROM hourly_timestamps;
    
    EXECUTE IMMEDIATE full_sql;
    RETURN '查询执行完成，共处理' || DATEDIFF(HOUR, start_ts, end_ts) || '个时间点';
END;
$$;

-- 调用示例：查询2023年12月的小时数据
CALL RUN_HOURLY_TIME_TRAVEL(2023, 12, 'WHERE TABLE_NAME IN (''ABC'', ''GHJ'', ''ERT'')');

内容的提问来源于stack exchange，提问作者Varun K Das