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   |
+-----------+

You can also execute scripting blocks in Snowflake CLI with a caveat relating to the $$ delimiter.

For example:

EXECUTE IMMEDIATE $$
-- Snowflake Scripting code
DECLARE
  radius_of_circle FLOAT;
  area_of_circle FLOAT;
BEGIN
  radius_of_circle := 3;
  area_of_circle := pi() * radius_of_circle * radius_of_circle;
  RETURN area_of_circle;
END;
$$
;
Copy

Some operating systems interpret $$, such as a process ID (PID), instead of recognizing it as a scripting block delimiter. To address this limitation, you can use the following alternatives:

  • If you still want to specify the scripting block on the command line, you can escape the $$ delimiters, as in \$\$.

  • You can also put the scripting block with the default $$ delimiters into a separate file and call it with the snow sql -f <filename> command.

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.

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"
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

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 %>"
Copy

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.

Executing SQL in local files or URLs

You can use the !source query command in your SQL query to execute SQL in local files or a URL-based file. For example, the following command executes all SQL commands in a local file named my_sql_code.sql:

snow sql -q '!source my_sql_code.sql'
Copy

You can also nest !source commands in the SQL files, such as:

select emp_id FROM employees;
!source code_file_2.sql
Copy

In this example, the command executes the SELECT query and then executes the SQL commands in the code_file_2.sql file. Before executing !source queries, Snowflake CLI does the following:

  • Evaluates variable substitutions and templates.

  • Reads the contents of all nested files to ensure that no recursion occurs.

When the variables and templates are resolved and no recursion is detected, the command sends the code to Snowflake for execution.

Note

If you use double quotes ("") instead of single quotes ('') around a !source query, you might need to escape the ! (\!) depending on which shell you use.

The following examples illustrate different ways you can execute source files.

  • Execute code in a local file.

    This example assumes you have a simple query in a local SQL file.

    cat code_to_execute.sql
    Copy
    select 73;

    To execute the code in the file, enter the following command:

    snow sql -q '!source code_to_execute.sql'
    Copy
    select 73;
+----+
| 73 |
|----|
| 73 |
+----+

  • Execute code in a URL-based file.

    This example assumes you have the same simple query in a SQL file at a URL.

    To execute the code in the file, enter the following command:

    snow sql -q '!source https://trusted-host/trusted-content.sql'
    Copy
    select 73;
+----+
| 73 |
|----|
| 73 |
+----+

  • Execute code that uses variable substitution and templating.

    This example assumes you have a query in a local SQL file that uses a template variable.

    cat code_with_variable.sql
    Copy
    select '<% ctx.env.Message %>';

    To execute the code in the file, enter the following command that defines the variable value:

    snow sql -q '!source code_&value.sql;' -D value=with_variable --env Message='Welcome !'
    Copy
    select 'Welcome !';
+-------------+
| 'WELCOME !' |
|-------------|
| Welcome !   |
+-------------+

Note

The !source command supports the legacy !load alias.

Language: English