Executing SQL statements¶

Snowflake CLI lets you execute SQL queries, ad-hoc queries or files containing SQL queries, by using the snow sql command.

To execute an ad-hoc query, run a command similar to the following:

snow sql -q "SELECT * FROM FOO"

Copy

To execute a file containing a SQL query, run a command similar to the following:

snow sql -f my_query.sql

Copy

The snow sql command can execute multiple statements, in which case multiple result sets are returned. For example running:

snow sql  -q "select 'a', 'b'; select 'c', 'd';"

Copy

results in the following output:

select 'a', 'b';
+-----------+
| 'A' | 'B' |
|-----+-----|
| a   | b   |
+-----------+

select 'c', 'd';
+-----------+
| 'C' | 'D' |
|-----+-----|
| c   | d   |
+-----------+

For more information, see the snow sql command.

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.

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"

Copy

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"

Copy

This example generates the following SQL query:

grant usage on database dev to eng_rl

Copy