Hive - Variables

https://hive.apache.org/docs/latest/language/languagemanual-variablesubstitution/

 

Using Variables

There are three namespaces for variables – hiveconf, system, and env. (Custom variables can also be created in a separate namespace with the define or hivevar option in Hive 0.8.0 and later releases.)

The hiveconf variables are set as normal:

set x=myvalue

However they are retrieved using:

${hiveconf:x}

Annotated examples of usage from the test case ql/src/test/queries/clientpositive/set_processor_namespaces.q:

set zzz=5;
--  sets zzz=5
set zzz;

set system:xxx=5;
set system:xxx;
-- sets a system property xxx to 5

set system:yyy=${system:xxx};
set system:yyy;
-- sets yyy with value of xxx

set go=${hiveconf:zzz};
set go;
-- sets go base on value on zzz

set hive.variable.substitute=false;
set raw=${hiveconf:zzz};
set raw;
-- disable substitution set a value to the literal

set hive.variable.substitute=true;

EXPLAIN SELECT * FROM src where key=${hiveconf:zzz};
SELECT * FROM src where key=${hiveconf:zzz};
--use a variable in a query

set a=1;
set b=a;
set c=${hiveconf:${hiveconf:b}};
set c;
--uses nested variables. 

set jar=../lib/derby.jar;

add file ${hiveconf:jar};
list file;
delete file ${hiveconf:jar};
list file;

Substitution During Query Construction

Hive substitutes the value for a variable when a query is constructed with the variable.

• If you run two different Hive sessions, variable values will not be mixed across sessions.
• If you set variables with the same name in the same Hive session, a query uses the last set value.

Disabling Variable Substitution

Variable substitution is on by default (hive.variable.substitute=true). If this causes an issue with an already existing script, disable it using the following command:

set hive.variable.substitute=false;