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).
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 |
hive -h 127.0.0.1 -p 10000
#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;
hive -f /path_to_script/hive_script_example.hql
#hive_script_passing_variables.hql
SELECT * FROM tutorial_db.hive_script_test WHERE type='${dbtype}';
hive -hivevar var_dbtype='RDBMS' -f /path_to_script/hive_script_passing_variables.hql
#hive_script_passing_variables.hql
SELECT * FROM tutorial_db.hive_script_test WHERE type='${var_dbtype}' AND technology='${var_tech}';
hive -hivevar var_dbtype='RDBMS' -hivevar var_tech='Hive' -f /path_to_script/hive_script_passing_multi_variables.hql
hive -e 'SELECT * FROM tutorial_db.hive_script_test limit 10'
hive> set dbtype='RDBMS';
hive> SELECT * FROM tutorial_db.hive_script_test WHERE type=${hiveconf:dbtype};
source /path_to_script/hive_script_example.hql
-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: |
Example: |