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}

you can even pass the variable from command line:

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:

If you are o the hive prompt, just run

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:

then, we can bring it into hive using the following command:

Then we can use it in the query like this:

or

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}

vs

In this case we can define some default variables in a file, and rewrite them later.