Is there a way to reset all SQL variables in a Snowflake session using Snowflake-SQL?

user_3336323 0
Viewed 312

I'm a big fan of Session variables when I'm creating Snowflake SQL scripts, but I'm missing the functionality to reset all my variables. Is there a way to reset all Session variables with one command? (something like UNSET ALL SESSION VARIABLES)

The underlying problem is that I find myself making many variable declaration errors when creating Snowflake scripts. Say that I'm developing two scripts. In both scripts, I make use of a common variable "var", but I forgot to declare it in one of my scripts. I'm currently unable to detect this mistake easily in my own session.

According to the documentation documentation, Snowflake allows me to show all variables and allows me to unset multiple variables if I know their identifiers. A combination of the two is what I'm looking for.

Any advise would be greatly appreciated.

snowflake-cloud-data-platform session-variables
2 Answers

You can use a stored procedure for this.

I wrote one for you:

CREATE OR REPLACE PROCEDURE unset_all()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var stmt = snowflake.createStatement({
    sqlText: "SHOW VARIABLES",    
});
stmt.execute();
var env_vars=[];
var result1 = stmt.execute();
while(result1.next()) {
  env_vars.push(result1.getColumnValue('name'));
}
if (env_vars.length > 0) {
  snowflake.createStatement({
      sqlText: "UNSET (" + env_vars + ");",    
  }).execute();
}
$$;

Test:

SET a = 'a';
SET b = 'b';
SHOW VARIABLES;
CALL unset_all();
SHOW VARIABLES;

Note that EXECUTE AS CALLER is needed for this to work.

user_1324380

One a bit "ugly" way is to cancel current session:

SET v = 1;
SET i = 'a';

SHOW VARIABLES;
-- v ...
-- i ...

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

SHOW VARIABLES;
-- empty

Remarks:

  • when using Classic UI you will be immediately logged out, it will work with PreviewApp
  • objects that depends on session will be lost(uncommitted transactions/temporary tables/session parameters and so on) - use with caution!!!
user_50708790
Related