Blog
Learnomate Technologies > Blog > Oracle DBA > Hive Variable | Set Hive Variable | HiveConf,Hivevar in hive
Hive Variable | Set Hive Variable | HiveConf,Hivevar in hive
- December 16, 2021
- Posted by: Ankush Thavali
- Category: Oracle DBA
No Comments

Hive Variable for Hive Scripts

Hive stores variables in four different namespaces, namespace is a way to separate variables.
- hiveconf
- hivevar
- system, and
- env.
hiveconf
select region ,city from temperature where region='Africa' and city='Lusaka' limit 10; set regionname; regionname='Africa' set cityname; cityname='Lusaka' set table; table=temperature select region ,city from ${hiveconf:table} where region=${hiveconf:regionname} and city=${hiveconf:cityname} limit 10;
hivevar
SET hivevar:table=temperature; select * from ${hivevar:table} limit 10; or SELECT * FROM ${table}
Hive Environment Variables (env)
SELECT "${env:HIVE_HOME}"; OK /home/hdoop/apache-hive-3.1.1-bin Time taken: 2.469 seconds, Fetched: 1 row(s)
Hive System Variables (system)
SELECT "${system:user.name}"; OK hdoop Time taken: 0.193 seconds, Fetched: 1 row(s)
Set hive command line variable
hdoop@hadoop:~$ hive --hivevar table=temperature hive> > select * from bucket.${table} limit 10; OK Zambia Lusaka 1 1 1995 73 Africa Zambia Lusaka 1 2 1995 70 Africa Zambia Lusaka 1 3 1995 72 Africa Zambia Lusaka 1 4 1995 76 Africa Zambia Lusaka 1 5 1995 76 Africa Zambia Lusaka 1 6 1995 74 Africa Zambia Lusaka 1 7 1995 76 Africa Zambia Lusaka 1 8 1995 78 Africa Zambia Lusaka 1 9 1995 77 Africa Zambia Lusaka 1 10 1995 77 Africa Time taken: 1.998 seconds, Fetched: 10 row(s)
Pass variable to hive script
hdoop@hadoop:~$ hive --hivevar table=temperature -f demo.hql Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Time taken: 2.955 seconds, Fetched: 10 row(s)
hql scripts
hdoop@hadoop:~$ cat demo.hql set regionname='Africa'; set cityname='Lusaka'; use bucket; select region ,city from ${table} where region=${hiveconf:regionname} and city=${hiveconf:cityname} limit 10;
Hive Variable for Hive Scripts

Hive stores variables in four different namespaces, namespace is a way to separate variables.
- hiveconf
- hivevar
- system, and
- env.
hiveconf
select region ,city from temperature where region='Africa' and city='Lusaka' limit 10; set regionname; regionname='Africa' set cityname; cityname='Lusaka' set table; table=temperature select region ,city from ${hiveconf:table} where region=${hiveconf:regionname} and city=${hiveconf:cityname} limit 10;
hivevar
SET hivevar:table=temperature; select * from ${hivevar:table} limit 10; or SELECT * FROM ${table}
Hive Environment Variables (env)
SELECT "${env:HIVE_HOME}"; OK /home/hdoop/apache-hive-3.1.1-bin Time taken: 2.469 seconds, Fetched: 1 row(s)
Hive System Variables (system)
SELECT "${system:user.name}"; OK hdoop Time taken: 0.193 seconds, Fetched: 1 row(s)
Set hive command line variable
hdoop@hadoop:~$ hive --hivevar table=temperature hive> > select * from bucket.${table} limit 10; OK Zambia Lusaka 1 1 1995 73 Africa Zambia Lusaka 1 2 1995 70 Africa Zambia Lusaka 1 3 1995 72 Africa Zambia Lusaka 1 4 1995 76 Africa Zambia Lusaka 1 5 1995 76 Africa Zambia Lusaka 1 6 1995 74 Africa Zambia Lusaka 1 7 1995 76 Africa Zambia Lusaka 1 8 1995 78 Africa Zambia Lusaka 1 9 1995 77 Africa Zambia Lusaka 1 10 1995 77 Africa Time taken: 1.998 seconds, Fetched: 10 row(s)
Pass variable to hive script
hdoop@hadoop:~$ hive --hivevar table=temperature -f demo.hql Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Africa Lusaka Time taken: 2.955 seconds, Fetched: 10 row(s)
hql scripts
hdoop@hadoop:~$ cat demo.hql set regionname='Africa'; set cityname='Lusaka'; use bucket; select region ,city from ${table} where region=${hiveconf:regionname} and city=${hiveconf:cityname} limit 10;