Hive is used for batch and interactive SQL queries. Variable Substitution allows for tasks such as separating environment-specific configuration variables from code. The variable substitution is very important when you are calling the HQL scripts from shell or Python. You can pass the values to query that you are calling.
In this article, we will see how to run Hive script file passing parameter to it. We also see the working examples.
Run Hive Script File Passing Parameter
You can use the set and use that variable within the script. Below is the sample syntax that you can use to run Hive script file passing parameter.
For example:
$ hive --hiveconf var=value -e 'set var; set hiveconf:var; select * from table where col = ${hiveconf:var}'
Above statement will set the dept variable to 100.
Run Hive Script File Passing Parameter Example
Below is the working example to run Hive script file passing parameter:
$ hive --hiveconf dept=100 -e 'set dept; set hiveconf:dept; select * from test where dep_id = ${hiveconf:dept}' Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j.properties dept=100 hiveconf:dept=100 OK 1 abc 100 Time taken: 7.872 seconds, Fetched: 1 row(s)
You can use the any scripting program to dynamically pass the parameter to the hive HQL script.
Read:
- Cloudera Impala Generate Sequence Numbers without UDF
- Netezza ROWNUM Pseudo Column Alternative
- Run Impala SQL Script File Passing argument and Working Example
- An Introduction to Hadoop Cloudera Impala Architecture
- Commonly used Hadoop Hive Commands
- Hadoop Hive Dynamic Partition and Examples
- Hive String Functions and Examples
- Hive Join Types and Examples