Skip to content

Latest commit

 

History

History
93 lines (67 loc) · 3.24 KB

sql-ref-syntax-ddl-declare-variable.md

File metadata and controls

93 lines (67 loc) · 3.24 KB
layout title displayTitle license
global
DECLARE VARIABLE
DECLARE VARIABLE
Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

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.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.

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 initialized to `NULL`
DECLARE some_var STRING;

Related Statements