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
Related Articles,
- Snowsql Command Line Options and Examples
- Access Snowflake using Snowsql without Password Prompt – Snowsql Environment Variables
- Snowflake Dynamic SQL Queries and Examples
- Snowflake rowcount and QueryID of last Executed Query
Hope this helps 🙂