0

I've created this task in SF using SnowSQL:

CREATE OR REPLACE TASK sv_copy_command_test_task
    WAREHOUSE = compute_wh
    SCHEDULE = '1 MINUTE'
AS
BEGIN
SET 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,
               'mainnet' as CHAIN_ID
      from @expose_sf_data_trial.decoded.trial_calls_copy_command)
      pattern=$DATE_PATTERN;
END;/
!set sql_delimiter=";"

The task has been successfully created but I'm getting the following error in the execution:

SQL compilation error: error line 29 at position 14
Session variable '$DATE_PATTERN' does not exist

I'm creating the DATE_PATTERN variable in the first query, so SF is running the queries in distinct sessions? I don't really know what I'm missing here.

Thanks a lot in advance for your time.

svalls
  • 11
  • 3
  • Hi - have you tried reading the documentation about what’s permissible in the parameter? https://docs.snowflake.com/en/sql-reference/sql/create-task – NickW Jul 13 '23 at 12:42
  • Hi, yes I went through that part of the docs, in parameter is specified `Single SQL statement`, but then in the examples of that same doc you have `Multiple SQL Statements Using SnowSQL`. Actually I've been trying this multiple sql statements and is working fine with other sql queries. Don't really know why it is having troubles with the variables. – svalls Jul 13 '23 at 13:24
  • Hi - I'm not sure where you are getting "Multiple SQL Statements Using SnowSQL" from? It supports 1) Single SQL statement 2) Call to a stored procedure 3) Procedural logic using Snowflake Scripting. In case this is what is confusing you, multiple SQL statements and Snowflake Scripting are not the same thing – NickW Jul 13 '23 at 13:35
  • Hi, this `Multiple SQL Statements Using SnowSQL` is in the same url doc that you added: https://docs.snowflake.com/en/sql-reference/sql/create-task#examples. If you go to examples is the third one after Store Procedure. – svalls Jul 13 '23 at 13:39

1 Answers1

0

I think the docs could be clearer but what that is showing is SQL Scripting commands. Therefore you need to define them using a DECLARE block or using LET within your code. When you reference them outside of a SQL command you can just use the name; within a SQL command they need to be be prefixed with a colon.

So your code probably needs to be something like this (I haven't tested this; the RETURN is just there to show the second way of referencing variables):

CREATE OR REPLACE TASK sv_copy_command_test_task
    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,
             'mainnet' as CHAIN_ID
      from @expose_sf_data_trial.decoded.trial_calls_copy_command)
      pattern= :DATE_PATTERN;
RETURN DATE_PATTERN;
END;

Documentation for using variables within a SQL Scripting block is here

NickW
  • 8,430
  • 2
  • 6
  • 19