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).

• Connect to Hive console remotely
• Basic Hive scripts
• Hive scripts with variables
• Executing queries without login into Hive console
• Setting and using variables within Hive console
• Running Hive script file from Hive console
• -hiveconf vs -hivevar

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

• Simple Hive script

  
  #hive_script_example.hql
  CREATE DATABASE IF NOT EXISTS tutorial_db;
  
  USE tutorial_db;
  
  CREATE TABLE IF NOT EXISTS  tutorial_db.hive_script_test
  (  id INT,
     technology String,
     type String
  )
  ROW FORMAT delimited 
  FIELDS TERMINATED BY '\t'
  STORED AS textfile;
  
  LOAD DATA LOCAL inpath '/path_to_script/hive_table_data.txt' OVERWRITE INTO TABLE tutorial_db.hive_script_test;
  

• Running Hive script

  
  hive -f /path_to_script/hive_script_example.hql 
  

• Download Hive script and data file
  • Hive File: hive_script_example.hql
  • Data File: hive_table_data.txt

➠ Hive Scripts with Variables and its Execution

• Example of script using single variables

  
  #hive_script_passing_variables.hql
  SELECT * FROM tutorial_db.hive_script_test WHERE type='${dbtype}';
  

• Running Hive script with single variable

  
  hive -hivevar var_dbtype='RDBMS' -f /path_to_script/hive_script_passing_variables.hql
  

• Download Hive script and data file
  • Hive File: hive_script_passing_variables.hql
  
  
  
• Example of script using multiple variables

  
  #hive_script_passing_variables.hql
  SELECT * FROM tutorial_db.hive_script_test WHERE type='${var_dbtype}' AND technology='${var_tech}';
  

• Running Hive script with multiple variables

  
  hive -hivevar var_dbtype='RDBMS' -hivevar var_tech='Hive' -f /path_to_script/hive_script_passing_multi_variables.hql
  

• Download Hive script
  • Hive File: hive_script_passing_multi_variables.hql

➠ 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.