Executing SQL statements¶

Using variables for SQL templates¶

In certain situations, you might want to change your SQL queries based on the context. The snow sql command supports client-side variable substitution that lets you use variables in the command that are resolved locally before submitting the query. Variables in the SQL string take the form <% variable_name %>, and the -D (or --variable) option specifies the value of the variable.

Note

You can currently use the SnowSQL &variable_name and <% variable_name %> syntax for templates. However, Snowflake recommends using the <% variable_name %> syntax.

For example, to specify a database using a client-side variable, you can enter a command similar to the following:

snow sql -q "select * from <% database %>.logs" -D "database=dev"

When executed, the command substitutes the value dev in the <% database %> variable to create the dev.logs filename and then sends the select * from dev.logs SQL query to Snowflake for processing.

You can also specify multiple variable inputs, as shown:

snow sql \
-q "grant usage on database <% database %> to <% role %>" \
-D "database=dev" \
-D "role=eng_rl"

This example generates the following SQL query:

grant usage on database dev to eng_rl

Storing variables in the snowflake.yml project definition file¶

Specifying variables as snow sql command-line options might not always be practical, or perhaps you might not want to specify sensitive values on the command line. In such cases, you can define variables and values in the snowflake.yml project definition file. Then you can just specify the variable names in the form <% ctx.env.<variable_name> %> instead of using the -D "<variable> = <value>" option.

Using the example from the previous section, you could store the database and role variables in snowflake.yml file and change the query to:

snow sql -q "grant usage on database <% ctx.env.database %> to <% ctx.env.role %>"

In this example, the snow sql command looks for the variable definitions in the project definition file and extracts the values without making them visible on the command line. The snowflake.yml file should be located either in the current working directory or in the location specified with the -p option.

For more information about storing these values in the project definition file, see Use variables in SQL.

Running SQL queries asynchronously¶

Snowflake CLI lets you run one or more SQL queries asynchronously. Instead of waiting for a result, the snow sql command schedules the queries at Snowflake and returns a query ID. After a query finishes, you can get the result using the !result query command or the SQL RESULT_SCAN command.

To run a SQL query asynchronously, end the query with ;> instead of ;, as shown:

snow sql -q 'select "My async query" ;>'

The following example runs a single query asynchronously:

snow sql -q "select 'This is async query';>"

select 'This is async query'
+--------------------------------------+
| scheduled query ID                   |
|--------------------------------------|
| 01bc3011-080f-f2d7-0001-c1be14bae7c2 |
+--------------------------------------+

You can then use the returned query ID in the !result query command to display the query result:

snow sql -q '!result 01bc3011-080f-f2d7-0001-c1be14bae7c2'

path-to-private-key-file
+-----------------------+
| 'THIS IS ASYNC QUERY' |
|-----------------------|
| This is async query   |
+-----------------------+

You can also execute multiple queries in the query string, both asynchronously and synchronously, as shown:

snow sql -q "select 'This is async query';> select 'Not an async query'; select 'Another async query';>"

select 'This is async query'
+--------------------------------------+
| scheduled query ID                   |
|--------------------------------------|
| 01bc3b8c-0109-2e81-0000-0f2d0e5a4a32 |
+--------------------------------------+

select 'Not an async query';
+----------------------+
| 'NOT AN ASYNC QUERY' |
|----------------------|
| Not an async query   |
+----------------------+

select 'Another async query'
+--------------------------------------+
| scheduled query ID                   |
|--------------------------------------|
| 01bc3b8c-0109-2e81-0000-0f2d0e5a4a36 |
+--------------------------------------+

Working with SQL query commands¶

Snowflake CLI provides the following commands that you can use inside your SQL queries:

• !source, which executes SQL in local files or URLs.

• !queries, which lists all SQL queries.

• !result, which displays the result of a SQL query.

• !abort, which aborts an active SQL query.

snow sql -q '!source my_sql_code.sql'

select emp_id FROM employees;
!source code_file_2.sql

snow sql -q '!result 01bc3011-080f-f2d7-0001-c1be14bae7c2'

+-----------------------+
| 'THIS IS ASYNC QUERY' |
|-----------------------|
| This is async query   |
+-----------------------+

snow sql -q '!abort 01bc3011-080f-f2d7-0001-c1be14bae7c2'

+-------------------------------------------------------------+
| SYSTEM$CANCEL_QUERY('01BC3011-080F-F2D7-0001-C1BE14BAE7C2') |
|-------------------------------------------------------------|
| Identified SQL statement is not currently executing.        |
+-------------------------------------------------------------+

Entering multiple commands in a single transaction¶

The --single-transaction option lets you enter multiple SQL commands to execute as an all-or-nothing set of commands. By executing commands in a single transaction, you can ensure that all of the commands are completed successfully before committing any of the changes. If any of the commands fail, none of the changes from the successful commands persist.

The following examples show successful and unsuccessful transactions:

• Successful command execution

  snow sql -q "insert into my_tbl values (123); insert into my_tbl values (124);" --single-transaction
  

  Copy

  BEGIN;
  +----------------------------------+
  | status                           |
  |----------------------------------|
  | Statement executed successfully. |
  +----------------------------------+
  
  insert into my_tbl values (123);
  +-------------------------+
  | number of rows inserted |
  |-------------------------|
  | 1                       |
  +-------------------------+
  
  insert into my_tbl values (124);
  +-------------------------+
  | number of rows inserted |
  |-------------------------|
  | 1                       |
  +-------------------------+
  
  COMMIT
  +----------------------------------+
  | status                           |
  |----------------------------------|
  | Statement executed successfully. |
  +----------------------------------+
  

  You can then verify that the commands were committed to the database:

  snow sql -q "select count(*) from my_tbl"
  

  Copy

  select count(*) from my_tbl
  +----------+
  | COUNT(*) |
  |----------|
  | 2        |
  +----------+
  

• Unsuccessful single transaction

  snow sql -c patcli -q "insert into my_tbl values (123); insert into my_tbl values (124); select BAD;" --single-transaction
  

  Copy

  BEGIN;
  +----------------------------------+
  | status                           |
  |----------------------------------|
  | Statement executed successfully. |
  +----------------------------------+
  
  insert into my_tbl values (123);
  +-------------------------+
  | number of rows inserted |
  |-------------------------|
  | 1                       |
  +-------------------------+
  
  insert into my_tbl values (124);
  +-------------------------+
  | number of rows inserted |
  |-------------------------|
  | 1                       |
  +-------------------------+
  
  select BAD;
  ╭─ Error ───────────────────────────────────────────────────────────────────────────────╮
  │ 000904 (42000): 01bc3b84-0810-0247-0001-c1be14ee11ce: SQL compilation error: error    │
  │ line 1 at position 7                                                                  │
  │ invalid identifier 'BAD'                                                              │
  ╰───────────────────────────────────────────────────────────────────────────────────────╯
  

You can then verify that the commands were not committed to the database:

snow sql -q "select count(*) from my_tbl"

Copy

select count(*) from my_tbl
+----------+
| COUNT(*) |
|----------|
| 0        |
+----------+

Entering SQL commands in interactive mode¶

The snow sql command supports an interactive mode that lets you enter SQL commands one at a time. Interactive mode provides the following features:

• Syntax highlighting

  

• Code completion while typing

  

• Searchable history

  Pressing CTRL-R: lets you search your command history:

  

• Multi-line input

  Pressing ENTER on a line that does not end with a semicolon (;) moves the cursor to the next line for more commands until a statement ends with a semicolon.

  

To use interactive mode, enter the snow sql command followed by ENTER, as shown:

snow sql

The command opens a sub-shell with a > prompt where you can enter SQL commands interactively:

$ snow sql
  ╭───────────────────────────────────────────────────────────────────────────────────╮
  │ Welcome to Snowflake-CLI REPL                                                     │
  │ Type 'exit' or 'quit' to leave                                                    │
  ╰───────────────────────────────────────────────────────────────────────────────────╯
  >

You can then enter SQL commands, as shown:

> create table my_table (c1 int);

+-------------------------------------+
| status                              |
|-------------------------------------|
| Table MY_TABLE successfully created.|
+-------------------------------------+

To exit interactive mode, enter exit, quit, or CTRL-D.