Set variable for hive script
When we run hive scripts, such as Load data into Hive table, we often need to pass parameters to the hive scripts by defining our own variables.
Here are some examples to show how to pass parameters or user defined variables to hive.
Use hiveconf for variable subsititution
For example, you can define a variable DATE
, then use it as ${hiveconf:DATE}
1 2 3 |
hive> set DATE='20150405'; hive> select * from mytable where day >= '${hiveconf:DATE}' |
you can even pass the variable from command line:
1 2 3 |
% hive -hiveconf DATE='20150405' -f my_hive.hql |
Use env and system variables
You can also use env and system variables like this ${env:USER}
You can run the following command to see all the available variables:
1 2 3 |
% hive -e 'set;' |
If you are o the hive prompt, just run
1 2 |
hive> set; |
Update: I’ve started to use hivevar variables as well, putting them into hql snippets I can include from hive CLI using the source
command (or pass as -i option from command line). The benefit here is that the variable can then be used with or without the hivevar prefix, and allow something akin to global vs local use.
Use hivevar variable
We can put hivevar variable into a hql file, then we can use source file.hql
to bring it into hive:
Suppose, we have some setup.hql which sets a DATE variable:
1 2 |
set hivevar:DATE='20150506'; |
then, we can bring it into hive using the following command:
1 2 |
hive> source /path..../setup.hql; |
Then we can use it in the query like this:
1 2 |
hive> select * from my_table where date >= ${DATE} |
or
1 2 |
hive> select * from my_table where date >= ${hivevar:DATE} |
Rewrite the hivevar by defining a local variable
We could also set a “local” variable DATE,
which will affect the use of ${DATE}
, but not ${hivevar:DATE}
1 2 3 |
hive> set DATE='20150507'; hive> select * from my_table where date >= ${DATE} -- uses '20150507' |
vs
1 2 |
hive> select * from my_table where date >= ${hivevar:DATE} -- still uses the original '20150506' |
In this case we can define some default variables in a file, and rewrite them later.
-
P Grim