Search code examples
hadoophivehadoop-partitioning

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

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!

Solution

  • 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