DECLARE VARIABLE
Description
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.
Syntax
DECLARE [ OR REPLACE ] [ VAR | VARIABLE ]
variable_name [ data_type ] [ { DEFAULT | = } default_expr ]
Parameters
-
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.sessionorsession.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
DEFAULTusing SET VAR. Ifdata_typeis specifieddefault_exprmust be castable to the variable type. Ifdata_typeis 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 withNULL.
Examples
-- 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 initialialized to `NULL`
DECLARE some_var STRING;