Variables

Applies to: Databricks Runtime check marked yes 14.1 and above

Variables are typed and schema qualified objects which store values that are private to a session. In Databricks variables are temporary and declared within a session using the DECLARE VARIABLE statement.

The terms temporary variable and session variable are interchangeable.

The schema in which temporary variables reside is system.session.

A variable is dropped implicitly at the end of the session that defines it. But you can explicitly drop it earlier using the DROP VARIABLE statement.

When a variable is defined its value is NULL or the result of the optionally specified default expression. You can modify the value of any number of variables with the SET VARIABLE statement at any time during the session.

When referenced within a query variable names share their namespace with column names, column aliases, and function parameter names. Variables are last in the order of resolution in case of name conflicts.

Variables differ semantically from parameter markers in three ways:

  • Parameter markers only exist within a single statement. The invoking API must provide the value and type. Variables exist for the duration of a session, allowing them to be referenced in multiple statements without the need to pass a value for every statement.

  • Variables can be set without leaving the context of SQL.

  • Variables can be referenced in the bodies of temporary views and SQL functions. When you reference a temporary view or temporary SQL function, the current value of any variable in its body will be used.

The IDENTIFIER clause accepts variables as arguments. This allows you to parameterize identifiers using variables and results of queries used to set those variables.

Examples 

-- A verbose definition of a temporary variable
> DECLARE OR REPLACE VARIABLE myvar INT DEFAULT 17;

-- A dense definition, including derivation of the type from the default expression
> DECLARE address = named_struct('street', 'Grimmauld Place', 'number', 12);

-- Referencing a variable
> SELECT myvar, session.address.number;
 17  12

-- Setting a single variable
> SET VAR myvar = (SELECT max(c1) FROM VALUES (1), (2) AS t(c1));
> SELECT myvar;
  2

-- Setting multiple variables
> SET VAR (myvar, address) = (SELECT address.number, named_struct('street', address.street, 'number', 10));
> SELECT myvar, address;
  12    {"street":"Grimmauld Place","number":10}

-- Drop a variable
> DROP TEMPORARY VARIABLE myvar;
> DROP TEMPORARY VARIABLE IF EXISTS address;

-- Use the IDENTIFIER clause with a variable
> DECLARE view = 'tempv';
> CREATE OR REPLACE TEMPORARY VIEW IDENTIFIER(view) (c1) AS SELECT 1;
> SELECT * FROM IDENTIFIER(view);
  1