How to Drop All SQL Variables in Snowflake Session?

  • Post author:
  • Post last modified:July 27, 2021
  • Post category:Snowflake
  • Reading time:6 mins read

Snowflake supports SQL session variable declaration by the user. SQL variable serves many purposes such as storing application specific environmental variables. You can also use SQL variables to create parameterized views or parameterized query. Once the variables are defined, you can explicitly use UNSET command to reset the SQL variables. In this article, we will check how to drop or reset all SQL variables in Snowflake session.

Snowflake SQL Variables

Before going into methods to reset all SQL variables, let us check how to define a SQL variable and reset it.

Following commands allow you to work with SQL variables.

Let us check these commands briefly.

Snowflake SET Command

You can initialize the SQL variables using Snowflake SQL statement SET. You can also set the variables in the connection string when you connect to Snowflake.

For example,

set my_variable=10;
set my_variable2='someValue';

Snowflake UNSET Command

You can use Snowflake UNSET command to drop or remove the session variables. You can destroy a SQL variable using UNSET command.

For example,

unset my_variable;
unset my_variable2;

Snowflake SHOW VARIABLES Command

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

For example,

show variables;

Drop All SQL Variables in Snowflake Session

You can drop individual SQL variable using UNSET command. But, the task will become tedious if you have defined multiple variables in the current session. You either have to abort the current session to drop all variables or you can use Snowflake control structures such as WHILE loop and create a simple stored procedure to drop all SQL variables in the current session.

You can use following couple of methods to destroy all Snowflake SQL variables in the current session.

Now, let us check these two methods.

Snowflake Stored Procedure to Drop All Snowflake SQL Variables

You can make use of Snowflake control structures such as a WHILE loop to UNSET all SQL variables. Using SP to reset all variables is recommended approach. It won’t abort the current session and you don’t have to re-login again.

For example, Following JavaScript Snowflake procedure will identify all SQL variables and drop them from the current session.

CREATE OR REPLACE PROCEDURE drop_sql_variables()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var stmt = snowflake.createStatement({
    sqlText: "SHOW VARIABLES",    
});
stmt.execute();
var result = stmt.execute();
while(result.next()) {
  snowflake.createStatement({
      sqlText: "UNSET (" + result.getColumnValue('name') + ");",    
  }).execute();  
}
$$;

You can execute above procedure using CALL command.

call drop_sql_variables();

Abort Snowflake Current Session

SQL variables are private to a session. When a Snowflake session is closed, all variables created during the session are dropped. You can abort the current session to drop all SQL variables. Note that, this is not recommended approach as session abort will log you out of the session. You may have to login again to use Snowflake.

For example,

SELECT SYSTEM$ABORT_SESSION(CURRENT_SESSION()::INT);

Hope this helps 🙂

Tags: , , ,