How can I pass a dynamic date in a hive server action as a parameter

Question

In Oozie, I have used Hive action in Hue, same action I used parameter options to supply date parameter. Here I want to provide dynamic date parameter such as yesterday date and day before yesterday. How can I generate those date? and how can I pass as parameter.

My HQL is :

CREATE TABLE IF NOT EXISTS tmp_table as 
select * from emptable 
where day>=${fromdate}  and day<=${todate}

My HiveServer Action contains: a. HQL script b. Two parameters options one for each dates like as fromdate = , todate = c. Added file option for HQL script.

What I tried: I created two separate shell scripts which returns date. One of Shell script is

#! /bin/bash
FROM_DAY=$(date +%Y-%m-%d -d " - 1 day")
echo "$FROM_DAY" 

and hive action parameter become fromdate = /user/manishs/Oozie/filter.sh

However this approach is not working and gives exceptions: Error: Error while compiling statement: FAILED: ParseException line 4:11 cannot recognize input near '/' 'user' '/' in expression specification (state=42000,code=40000)

NOTE: If I pass a date as : fromdate ='2015-08-01' , it is working and give results.

My question is how can I pass a dynamic date in a hive server action as a parameter. OR Is any way to pass dynamic filter to a query.

Answer 1

Oozie is a scheduler, right? So why not use the built-in EL functions to compute "today" and "yesterday" and pass them to the Hive action?

In the Coordinator script, assuming that you are using Los Angeles time zone, it should look like...

<coordinator-app .......>
  <action>
    <app-path>/path/to/the/worklow/definition</app-path>
    <configuration>
      <property>
        <name>today</name>
        <value>${coord:formatTime(coord:dateTzOffset(coord:nominalTime(), "America/Los_Angeles"), 'yyyy-MM-dd')}</value>
      </property>
      <property>
        <name>yesterday</name>
        <value>${coord:formatTime(coord:dateOffset(coord:dateTzOffset(coord:nominalTime(), "America/Los_Angeles"), -1, 'DAY'), 'yyyy-MM-dd')}</value>
      </property>
    </configuration>
  </action>
</coordinator-app>

Then in the Workflow script, use the <param> element to pass the properties to Hive.

Reference: Oozie Coordinator documentation

6.7.3. coord:nominalTime() EL Function

6.7.4. coord:actualTime() EL Function

6.9.1. coord:dateOffset(String baseDate, int instance, String timeUnit) EL Function

6.9.2. coord:dateTzOffset(String baseDate, String timezone) EL Function

6.9.3. coord:formatTime(String ts, String format) EL Function

OK, probably you can't do that with Hue. But the Hue editor has so many limitations that you can't do much with it anyway.