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"
To execute a file containing a SQL query, run a command similar to the following:
snow sql -f my_query.sql
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';"
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"
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 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.
For more information about storing these values in the project definition file, see Use variables in SQL.