How to create a Snowflake Task that runs two queries

Question

I want to create a task that executes a copy command every minute in snowflake, the problem is that the path of the file that needs to be taken (the files are stored as parquet files in s3), it is determined by the current date, for that I have this code:

SET date_pattern = CONCAT('CALLS/', TO_CHAR(CURRENT_DATE(), 'YYYY/MM/DD'), '/', '*.parquet');

create or replace task sv_task_copy_command_trial
warehouse = compute_wh
schedule = '1 MINUTE' 
AS 
copy into expose_sf_data_trial.decoded.CALLS_COPY_COMMAND
 from (select $1:LOAD_ID::varchar::TIMESTAMP_LTZ,
              $1:TIMESTAMP::timestamp
      from @expose_sf_data_trial.decoded.trial_calls_copy_command)
      pattern='<date_pattern>';`

First a set the variable date_pattern and pass it into pattern value for the copy command (SF doesn't allow dynamic variables in the copy command, so it is not possible to unify this code).

So my question here is, how can I create a task for this? I will need the date_pattern to be created in the same session as the copy command it is going to be executed, otherwise this variable won't have any value.

The workaround I'm trying to do is create a task that executes both queries, first the set of the date_pattern and then the copy command. I've already checked the documentation and suggests the SP but I'd like to explore other ideas.

Thanks a lot for your time :)

1. Why not Snowpipe? 2. For such things you simply create a stored procedure that will have the logic that you explained: get the current_date and use it in the copy command. — aek, Jul 12 '23 at 14:36

score 0 · Answer 1 · answered Jul 12 '23 at 15:32

To run more than single query on task you could use Snowflake Scripting block:

create or replace task sv_task_copy_command_trial
warehouse = compute_wh
schedule = '1 MINUTE' 
AS 
BEGIN
   LET date_pattern = CONCAT('CALLS/', TO_CHAR(CURRENT_DATE(), 'YYYY/MM/DD'),
                             '/', '*.parquet');

   copy into expose_sf_data_trial.decoded.CALLS_COPY_COMMAND
   from (select $1:LOAD_ID::varchar::TIMESTAMP_LTZ,
              $1:TIMESTAMP::timestamp
        from @expose_sf_data_trial.decoded.trial_calls_copy_command)
        pattern=:date_pattern;
END;

How to create a Snowflake Task that runs two queries

1 Answers1