docs/sql-ref-syntax-ddl-declare-variable.md
The DECLARE VARIABLE statement is used to create a temporary variable in Spark.
Temporary variables are scoped at a session level.
You can reference variables by their name everywhere constant expressions are allowed.
Unless you qualify a variable with session or system.session, a variable is only resolved after
Spark fails to resolve a name to a column or column alias.
Temporary variables cannot be referenced in persisted objects such as persisted view, column default expressions, and generated column expressions.
DECLARE [ OR REPLACE ] [ VAR | VARIABLE ]
variable_name [ data_type ] [ { DEFAULT | = } default_expr ]
OR REPLACE
If specified, a pre-existing temporary variable is replaced if it exists.
variable_name
Specifies a name for the variable to be created.
The variable name may be optionally qualified with a system.session or session.
Syntax: [ system . [ session .] ] variable_name
data_type
Optionally defines the data type of the variable. If it is not specified the type is derived from the default expression.
default_expr
An optional expression used to initialize the value of the variable after declaration.
The expression is re-evaluated whenever the variable is reset to DEFAULT using
SET VAR.
If data_type is specified default_expr must be castable to the variable type.
If data_type is not specified you must specify a default and its type will become the type of
the variable.
If no default expression is given, the variable is initialized with NULL.
-- The dense form of declaring a variable with default
DECLARE five = 5;
-- Declare a defined variable
DECLARE five = 55;
[VARIABLE_ALREADY_EXISTS] Cannot create the variable `system`.`session`.`five` because it already exists.
Choose a different name, or drop or replace the existing variable. SQLSTATE: 42723
-- Use `DECLARE OR REPLACE` to declare a defined variable
DECLARE OR REPLACE five = 55;
-- Explicitly declare the default value of a variable using the keyword `DEFAULT`
DECLARE VARIABLE size DEFAULT 6;
-- STRING variable initialized to `NULL`
DECLARE some_var STRING;