Hive Scripts & Variable

Hive Scripts & Variable

This tutorial will help in understanding how to create & execute Hive scripts, how to define & use variables in Hive and how to run queries directly without going to Hive console. Hive scripts are generally stored with .hql extension (Hive query language).


Below table show some of the arguments that can be used with 'Hive' command

Argument

Description

  -h

Used to specify hostname/ip of remote server

  -p

Used to specify Port number of remote server

  -f

Used to execute Hive queries from file

  -hiveconf

Should be used to define configuration properties

  -hivevar

Should be used to define user variables

  -e

Used to run queries/commands without login into Hive console

  -v

Used to echo executed hive statements to console



Connect to Hive console on remote server

hive -h 127.0.0.1 -p 10000


Hive Scripts and its Execution
Hive Scripts with Variables and its Execution
Executing queries without login into Hive console

 hive -e 'SELECT * FROM tutorial_db.hive_script_test limit 10'

Setting and using variables within Hive console

hive> set dbtype='RDBMS';
hive> SELECT * FROM tutorial_db.hive_script_test WHERE type=${hiveconf:dbtype};

Running Hive script from Hive console

source /path_to_script/hive_script_example.hql


Comparison between -hiveconf and -hivevar

  -hiveconf

  -hivevar

Should be used to define configuration properties

Should be used to define user variables

Defined as '-hiveconf conf_name = conf_value'

Defined as '-hivevar var_name = var_value'

Config value can be accessed as ${hiveconf:conf_name}

Variable value can be accessed as ${var_name}

Example:
hive --hiveconf conf_name='This is Hiveconf' -e '!echo ${hiveconf:conf_name};'

Example:
hive --hivevar var_name='This is Hivevar' -e '!echo ${var_name};'

Note: Using hiveconf for defining user variables will not throw any error and it will not break anything.