SQL Variables

This topic describes how to define and use SQL variables in sessions in Snowflake.

In this Topic:

Overview

Snowflake supports SQL variables declared by the user. They have many uses, such as storing application-specific environment settings.

Variable Identifiers

SQL variables are globally identified using case-insensitive names.

Variable DDL

Snowflake provides the following DDL commands for using SQL variables:

Initializing Variables

Using SQL to Initialize Variables in a Session

Variables can be initialized in SQL using the SET command. The data type of the variable is derived from the data type of the result of the evaluated expression.

SET MY_VARIABLE=10;
SET MY_VARIABLE='example';

Multiple variables can be initialized in the same statement, thereby reducing the number of round-trip communications with the server.

SET (VAR1, VAR2, VAR3)=(10, 20, 30);

Setting Variables on Connection

In addition to using SET to set variables within a session, variables can be passed as arguments in the connection string used to initialize a session in Snowflake. This is especially useful when using tools where the specification of the connection string is the only customization possible.

For example, using the Snowflake JDBC driver, you can set additional connection properties that will be interpreted as parameters. Note that the JDBC API requires SQL variables to be strings.

// build connection properties
Properties properties = new Properties();

// Required connection properties
properties.put("user"    ,  "toto"      );
properties.put("password",  "mypassword");
properties.put("account" ,  "testacount");

// Set some additional variables.
properties.put("$variable_1", "some example");
properties.put("$variable_2", "1"           );

// create a new connection
String connectStr = "jdbc:snowflake://localhost:8080";

// Open a connection under the snowflake account and enable variable support
Connection con = DriverManager.getConnection(connectStr, properties);

Using Variables in SQL

Variables can be used in Snowflake anywhere a literal constant is allowed. To distinguish them from bind values and column names, all variables must be prefixed with a $ sign.

For example:

SET (MIN, MAX)=(40, 70);

SELECT $MIN;

SELECT AVG(SALARY) FROM EMP WHERE AGE BETWEEN $MIN AND $MAX;

Note

Because the $ sign is the prefix used to identify variables in SQL statements, it is treated as a special character when used in identifiers. Identifiers (database names, table names, column names, etc.) cannot start with special characters unless the entire name is enclosed in double quotes. For more information, see Identifiers.

Viewing Variables for the Session

To see all the variables defined in the current session, use the SHOW VARIABLES command:

SET (MIN, MAX)=(40, 70);

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

SHOW VARIABLES;

+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+
|  session_id | created_on                      | updated_on                      | name | value | type  | comment |
|-------------+---------------------------------+---------------------------------+------+-------+-------+---------|
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MAX  | 70    | fixed |         |
| 34359992326 | Fri, 21 Apr 2017 11:20:32 -0700 | Fri, 21 Apr 2017 11:20:32 -0700 | MIN  | 40    | fixed |         |
+-------------+---------------------------------+---------------------------------+------+-------+-------+---------+

Session Variable Functions

The following convenience functions are provided for manipulating session variables to support compatibility with other database systems and to issue SQL through tools that do not support the @ syntax for accessing variables. Note that all these functions accept and return session variable values as strings:

  • SYS_CONTEXT and SET_SYS_CONTEXT
  • SESSION_CONTEXT and SET_SESSION_CONTEXT
  • GETVARIABLE and SETVARIABLE

Dropping/Removing Variables

SQL variables are private to a session. When a Snowflake session is closed, all variables created during the session are dropped. This means that no one can access user-defined variables that have been set in another session, and when the session is closed, these variables expire.

In addition, variables always can be explicitly destroyed using the UNSET command.

For example:

UNSET MY_VARIABLE;