Hive Variable | Set Hive Variable | HiveConf,Hivevar in hive

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarANKUSH THAVALI
  • 16 Dec, 2021
  • 0 Comments
  • 1 Min Read

Hive Variable | Set Hive Variable | HiveConf,Hivevar in hive

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;