How can I pass a dynamic date in a hive server act

问题:

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.

回答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.

回答2:

try this,

fromdate = $(sh /user/manishs/Oozie/filter.sh)

filter.sh should be at given place