Set date function as variable and use in beeline and hql file (hive)

Question

Could anyone please explain to me how to solve this issue.

I want to use from_unixtime(unix_timestamp() - 86400, 'yyyyMMdd) as the value for a variable and use it in a query's where clause that is stored in an hql file. I have tried:

beeline --hiveconf date=from_unixtime(unix_timestamp(), 'yyyyMMdd) -f path/file.hql (in .hql file: WHERE date <= '${hiveconf:date}';)

It does not work because of the date function. Is there any way to first get the date value in some script and then use it together with the hql file? I have only seen examples with hive cli but not beeline and I have tried some different ways but can't get it to work. Would really appreciate some help. The query works with hardcoded dates.

Thanks!

instead of using the hive udfs for generating the date , use the unix date functions so as to generate the date value for the hive variable — K S Nidhin
@KSNidhin Could you please show me with an example, I'm new to this, would be of great help! — smastika

K S Nidhin K S Nidhin · Accepted Answer · 2016-02-11T17:43:53

By using the unix date functions you could convert the date to the required format and then pass to the hive variable. Below is a sample command you could use :

cur_date=`date +%Y%m%d`
beeline --hiveconf date=${cur_date} -f path/file.hql

Then in your hive query just use ${date} where ever required.

Hope this helps

Set date function as variable and use in beeline and hql file (hive)

1 Answers