雪花周期性卸载到S3级

Question

我想在雪花上设置一个任务，它每周一早上12点后运行，并在名称中有日期范围的子目录(例如，S3 )中卸载上周的所有时间戳数据。

我创造了舞台

  CREATE OR REPLACE STAGE s3_stage
  URL='s3://my_bucket/'
  CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...')
  FILE_FORMAT = (TYPE ='PARQUET');

这项工作如预期的那样奏效：

COPY INTO @s3_stage/sample (SELECT * FROM MY_TABLE LIMIT 100);   

为了获得上周的完整数据，我使用CTE提供了以下查询

WITH prev_monday AS (
    SELECT timestamp_from_parts(year(current_date()), month(current_date()), day(current_date()) - EXTRACT ('dayofweek_iso',current_date()) + 1 -7,0,0,0) as date
),
last_monday AS (
    SELECT timestamp_from_parts(year(current_date()), month(current_date()), day(current_date()) - EXTRACT ('dayofweek_iso',current_date()) + 1,0,0,0) as date
)
SELECT  * FROM 
MY_TABLE as t,
prev_monday as pm,
last_monday as lm 
where t.timestamp >= pm.date and t.timestamp < lm.date;

这也如预期的那样起作用。

我还可以再次使用相同的CTE和concat生成请求的s3路径：

WITH prev_monday AS (
    SELECT timestamp_from_parts(year(current_date()), month(current_date()), day(current_date()) - EXTRACT ('dayofweek_iso',current_date()) + 1 -7,0,0,0) as date
),
last_monday AS (
    SELECT timestamp_from_parts(year(current_date()), month(current_date()), day(current_date()) - EXTRACT ('dayofweek_iso',current_date()) + 1,0,0,0) as date
)
select concat('@s3_stage/data', pm.date::DATE, '---', lm.date::DATE,'/')
FROM
prev_monday as pm,
last_monday as lm ;

然而，由于两个问题，我很难将一切统一起来：

显然，

1. WITH prev_monday AS .... COPY INTO ....不是有效的语法
2. s3 path (指向COPY INTO的参数)没有指定为字符串(即没有单引号)。所以我不能用concat生成这个

有什么想法或其他方法我可以试试吗？

Answer 1

正如Nick所说，可以将您的所有逻辑(和SQL)放在一个存储过程中并调用它们。下面的文档展示了如何在JS过程中调用多个SQL：

https://community.snowflake.com/s/article/Executing-Multiple-SQL-Statements-in-a-Stored-Procedure

这一项对于开始编写存储过程也是有用的：

Create stage to copy data from S3 to Snowflake from within Snowflake Scripting block?