layout: global title: SET VAR displayTitle: SET VAR license: | 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
The SET VAR
command sets a temporary variable which has been previously declared in the current session.
To set a config variable or a hive variable use SET.
SET { VAR | VARIABLE } { { variable_name = { expression | DEFAULT } } [, ...] | ( variable_name [, ...] ) = ( query ) }
variable_name
Specifies an existing variable. If you specify multiple variables, there must not be any duplicates.
expression
Any expression, including scalar subqueries.
DEFAULT
If you specify DEFAULT
, the default expression of the variable is assigned, or NULL
if there is none.
query
A query that returns at most one row and as many columns as the number of specified variables. Each column must be implicitly castable to the data type of the corresponding variable. If the query returns no row NULL
values are assigned.
-- DECLARE VARIABLE var1 INT DEFAULT 7; DECLARE VARIABLE var2 STRING; -- A simple assignment SET VAR var1 = 5; SELECT var1; 5 -- A complex expression assignment SET VARIABLE var1 = (SELECT max(c1) FROM VALUES(1), (2) AS t(c1)); SELECT var1; 2 -- resetting the variable to DEFAULT SET VAR var1 = DEFAULT; SELECT var1; 7 -- A multi variable assignment SET VAR (var1, var2) = (SELECT max(c1), CAST(min(c1) AS STRING) FROM VALUES(1), (2) AS t(c1)); SELECT var1, var2; 2 1 -- Too many rows SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1)); Error: ROW_SUBQUERY_TOO_MANY_ROWS -- No rows SET VAR (var1, var2) = (SELECT c1, CAST(c1 AS STRING) FROM VALUES(1), (2) AS t(c1) WHERE 1=0); SELECT var1, var2; NULL NULL