Escaping Semi-Colons in task creation

[short-hi]

Hello, hopefully I haven't missed anything in the documentation, but I have a question:

Currently we have an issue with Snowflake Task creation when using SchemaChange. The script looks something like this:

create or replace task TSK_REFRESH_PIPES
    warehouse=DEV_XS
    schedule='120 MINUTE'
    COMMENT='Temporary task to schedule daily refresh. Needs to be replace by a AWS SQS/SNS'
    as BEGIN
                ALTER PIPE  PIPE_CUSTOMERS refresh;
                ALTER PIPE  PIPE_CUSTOMERS_ANSWERS refresh;
                ALTER PIPE  PIPE_CUSTOMERS_DATA refresh;
        END
;

The reason this will not run correctly through SchemaChange is because of the semi-colons after the "Alter pipe" statements. However, when removing them Snowflake won't accept the query as valid. Manually running the above statement through snowflake poses no issue.

Is there a way to escape semi-colons in SchemaChange?

[chriskallen]

We have the same issue with deploying procedures. The root cause is that the tool splits the sql on the ';' as it believes they are separate statements, which is incorrect in this case.

https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/connection.py#L669

[sfc-gh-jhansen]

Hmm, that's a good question. I'll have to do some research and see what the options are.

[xtressa]

What worked for me was enclosing the script like that:

EXECUTE IMMEDIATE $$
-- your script
$$
;

[UberDoodles]

We have the same issue with deploying procedures. The root cause is that the tool splits the sql on the ';' as it believes they are separate statements, which is incorrect in this case.

https://github.com/snowflakedb/snowflake-connector-python/blob/main/src/snowflake/connector/connection.py#L669

Similar to @xtressa 's suggestion, for stored procedures with multiple statements, we use $$ around the procedure body, and this has always worked well for us :

CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
$$
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
  SELECT CURRENT_TIMESTAMP;
$$;

You can also use single quotes around the procedure body, but this gets very messy because you have to escape any single-quotes in the procedure body :

CREATE OR REPLACE TASK test_logging
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON  0 * * * * America/Los_Angeles'
AS
'
  ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'';
  SELECT CURRENT_TIMESTAMP;
';

[sfc-gh-jhansen]

Hi there @short-hi and @chriskallen, did these suggestions help? Are any changes need to schemachange to support statements with semicolons inside?

[short-hi]

Hi @sfc-gh-jhansen , sorry for the late reply. We've tried the mentioned solution today and it works! Enclosing the code in dollar-signs prefixed with "EXECUTE IMMEDIATE" did the trick.

To make everything clear, the working code is below. Note that the "BEGIN" & "END" statements (which were omitted in the suggestions) are necessary for this trick to work.

create or replace task TSK_REFRESH_PIPES
warehouse=DEVELOPER
schedule='120 MINUTE'
COMMENT='Temporary task to schedule daily refresh. Needs to be replace by a AWS SQS/SNS'
as 
EXECUTE IMMEDIATE
$$ 
BEGIN       
    ALTER PIPE  PIPE_CUSTOMERS refresh;
    ALTER PIPE  PIPE_CUSTOMERS_ANSWERS refresh;
    ALTER PIPE  PIPE_CUSTOMERS_DATA refresh;
END       
$$
;

Thank you very much for the help! this makes our process a lot easier :)