Hive is used for batch and interactive SQL queries. HiveServer2 supports a command shell Beeline that works with HiveServer2. It’s a JDBC client that is based on the SQLLine CLI. You can run HiveQL script file passing parameter using Beeline CLI.
Variable Substitution allows for tasks such as separating environment-specific configuration variables from code. You can substitute the values to variable that you have used in HiveQL query.
Read:
- Hive Dynamic SQL Support and Alternative
- HiveServer2 Beeline Command Line Shell Options and Examples
Run HiveQL Script File Passing Parameter using Beeline CLI
You can set the variable with the value using Beeline interactive shell options and use that variable inside your query. Below is the syntax for Beeline variable substitution:
$ beeline -u jdbc:hive2://hive_server:10000/test_db -n username -p password --hivevar var1=value -f file.sql
Below is sample HiveQL that uses variable substitution:
$ cat file.sql
select * from table where col = ${var1}
Run HiveQL Script File Passing Parameter using Beeline CLI Examples
Below is the working example to demonstrate Beeline variable substitution:
$ cat test_file.sql
select * from ds_tbl_db.var_sub_demo
where dept = ${dep_id};
Now execute the file using below beeline command:
Below example demonstrate the use of variable substitution in the Hive Beeline command line interface.
https://gist.github.com/26376f6431b7277b53d20c920e8e6d21
As you can see in the above examples, beeline substitute the variable values at run time. This feature is available in Hive as well but Beeline approach is easy to implement and use.