Blog

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;

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.