Execute SnowSQL command conditionally

Question

StackOverflow.

I have some batch jobs which run under SnowSQL in Linux. I'm trying to achieve the following:

• If a condition is true, then execute a !system statement.

I have tried the following with no luck so far.

SFUSER#[email protected]>SET SCRIPT =
                                  $$
                                  BEGIN
                                      LET count1 := 0;
                                      IF (count1 = 0) THEN
                                          !system 'echo asdfwerg > meow0111';
                                      END IF;

                                      RETURN 'MEOW';
                                  END;
                                  $$
                                  ;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.099s



SFUSER#[email protected]>EXECUTE IMMEDIATE $SCRIPT;
001003 (42000): SQL compilation error:
syntax error line 4 at position 7 unexpected '('.
syntax error line 4 at position 15 unexpected '='.

SFUSER#[email protected]>

However, when I try with the RETURN statement or a SELECT, I can successfully execute the script:

SFUSER#[email protected]>SET SCRIPT =
                                  $$
                                  BEGIN
                                      LET count1 := 0;
                                      IF (count1 = 0) THEN
                                          RETURN 'echo asdfwerg > meow0111';
                                      END IF;

                                      RETURN 'MEOW';
                                  END;
                                  $$
                                  ;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.077s


SFUSER#[email protected]>EXECUTE IMMEDIATE $SCRIPT;
+--------------------------+
| anonymous block          |
|--------------------------|
| echo asdfwerg > meow0111 |
+--------------------------+
1 Row(s) produced. Time Elapsed: 0.441s
SFUSER#[email protected]>

Is there any way I can execute the !system statement from within the Snowflake script?

Thank you.

Answer 1

Those SnowSQL commands are specific to SnowSQL only, they are not part of the SQL, have to be run by themselves and you can't combine them with SQL query or SQL Scripting.

This is not supported.