Execute SQL Script File using Snowflake Snowsql Variable Substitution

Snowflake allows variable substitution using -D or –variable options. The variable substitution is one of the nice feature that allows you to pass values to the script dynamically. For example, pass month value to the SQL script. Many modern day databases support the variable substitution. In this article, we will check how to a execute SQL script file using Snowflake Snowsql variable substitution with an example.

Snowflake Snowsql Variable Substitution

Snowsql supports the variable substitution with the help of -D or –-variable option. You can use that defined variable inside your SQL script by using &{var} option.

Following steps helps if you want to use variable substitution.

Enable Variable Substitution
Refer variable inside your SQL script using &{var}
Execute Snowsql using -D your_variable=your_value option

Enable Variable Substitution in Config File

This is one of the prerequisites to use variable substitution in your Snowflake queries.

Open snowsql config file > add variable_substitution=true in options

For example, consider below content of snowsql config file in which we have copied variable_substitution=true.

[options]
# If set to false auto-completion will not occur interactive mode.
auto_completion = True
# Enables Variable Substitution
variable_substitution=true

Note that, variable substitution will not work without adding this variable to the config file.

Refer Variable inside your SQL script using &{var}

The second step is to refer the variable inside your script. For instance, let us consider we will pass the id value to SQL script dynamically.

For example, consider below SQL script content.

select * from S_STUDENTS 
where id = &{id};

Execute Snowsql by Defining Variable

The final step is to define the variable that you have already referred in your SQL script.

For example, consider below snowsql command with variable substitution.

Example for snowsql -D option

In the following , we have defined the variable using -D option.

D:\Snowflake\Test_scripts>snowsql -f test.sql -D id=1;
* SnowSQL * v1.2.1
Type SQL statements or !help
+----+------+--------+
| ID | NAME | CITY   |
|----+------+--------|
|  1 | AAA  | London |
+----+------+--------+
1 Row(s) produced. Time Elapsed: 0.406s

Example for snowsql -D option

In the following example, we have defined the variable using –variable option.

D:\Snowflake\Test_scripts>snowsql -f test.sql --variable id=1;
* SnowSQL * v1.2.1
Type SQL statements or !help
+----+------+--------+
| ID | NAME | CITY   |
|----+------+--------|
|  1 | AAA  | London |
+----+------+--------+
1 Row(s) produced. Time Elapsed: 0.341s