Snowflake定时任务配置问题：每周导出数据至带日期目录的S3 Stage

问题背景 #

需要配置每周一凌晨12点运行的任务，将上周带时间戳的数据导出到S3 Stage的日期范围子目录（如s3://my_bucket/data/2022-07-04---2022-07-11/），已完成Stage创建、数据查询和路径生成，但无法整合为可执行的定时任务，遇到两个核心问题：

• WITH CTE 无法直接与 COPY INTO 语句结合使用
• COPY INTO 的目标路径不支持直接用concat等函数动态生成

解决思路 #

利用Snowflake的**存储过程（Stored Procedure）动态生成并执行COPY INTO语句，再通过任务（Task）**调度存储过程的执行。存储过程可以灵活计算日期范围、拼接目标路径，最终执行动态构造的导出SQL。

步骤1：创建存储过程 #

存储过程内部完成日期范围计算、目标路径拼接，然后执行COPY INTO语句：

CREATE OR REPLACE PROCEDURE EXPORT_LAST_WEEK_DATA()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    PREV_MONDAY DATE;
    LAST_MONDAY DATE;
    TARGET_PATH VARCHAR;
    COPY_SQL VARCHAR;
BEGIN
    -- 计算上周一开始时间（周一0点）和本周一开始时间
    SELECT 
        DATEADD(WEEK, -1, DATE_TRUNC('WEEK', CURRENT_DATE())) INTO PREV_MONDAY;
    SELECT DATE_TRUNC('WEEK', CURRENT_DATE()) INTO LAST_MONDAY;
    
    -- 拼接S3目标路径
    TARGET_PATH := '@s3_stage/data/' || PREV_MONDAY || '---' || LAST_MONDAY || '/';
    
    -- 构造COPY INTO语句
    COPY_SQL := 'COPY INTO ' || TARGET_PATH || ' FROM (
        SELECT * FROM MY_TABLE 
        WHERE timestamp >= ''' || PREV_MONDAY || ''' AND timestamp < ''' || LAST_MONDAY || '''
    )';
    
    -- 执行导出语句
    EXECUTE IMMEDIATE COPY_SQL;
    
    RETURN '导出完成，目标路径：' || TARGET_PATH;
END;
$$;

代码说明：

• 用DATE_TRUNC('WEEK', CURRENT_DATE())简化周一日期计算（Snowflake中WEEK默认以周一为起始，若需确认可查看WEEK_OF_YEAR_POLICY参数）
• 通过字符串拼接生成完整的COPY INTO语句，用EXECUTE IMMEDIATE执行动态SQL
• 避免了CTE与COPY INTO的语法冲突，同时实现了动态路径的生成

步骤2：测试存储过程 #

先手动执行存储过程验证功能是否正常：

CALL EXPORT_LAST_WEEK_DATA();

步骤3：创建定时任务 #

创建每周一凌晨12点运行的任务，调用上述存储过程：

CREATE OR REPLACE TASK EXPORT_LAST_WEEK_TASK
WAREHOUSE = YOUR_WAREHOUSE_NAME -- 替换为你的计算仓库名称
SCHEDULE = 'USING CRON 0 0 * * MON UTC' -- UTC时间每周一0点，需根据时区调整
AS
CALL EXPORT_LAST_WEEK_DATA();

任务说明：

• CRON 0 0 * * MON UTC 表示UTC时间每周一凌晨0点执行，若你的业务时区不是UTC，需调整CRON表达式（比如北京时间周一0点对应UTC周日16点，CRON为0 16 * * SUN UTC）
• 确保任务使用的仓库有足够权限执行导出操作
• 任务创建后默认暂停，需手动启动：

ALTER TASK EXPORT_LAST_WEEK_TASK RESUME;

额外优化建议 #

• 可在存储过程中添加错误处理逻辑，比如捕获导出异常并返回错误信息
• 若数据量较大，可在COPY INTO语句中添加MAX_FILE_SIZE等参数控制文件大小
• 可通过查询INFORMATION_SCHEMA.TASK_HISTORY查看任务执行记录

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