Difficulty with date info in Hive query with workflow/coordinator

[#1]

I'm having trouble getting date information into my workflow from a coordinator to schedule a Hive query periodically.  I'm using the GUI.

For Hive:

My destination_table has a partition on the 'dt' column of type DATE.

The query, which I've of course tested separately without the parameterization:

insert into table destination_table
partition(dt='${dt}')  # note dt is string
select * from source
where day=${d}  # note day, month, year are integers
and month=${m}
and year=${y}

In my worflow, I created four parameters:

d=${DAY}
m=${MONTH}
y=${YEAR}
dt=${YEAR}-${MONTH}-${DAY}

In my coordinator, under Workflow Parameters, I select DAY, MONTH and YEAR in the picklists, and then have respectively for each:

${coord:formatTime(coord:nominalTime(), 'dd')}
${coord:formatTime(coord:nominalTime(), 'MM')}
${coord:formatTime(coord:nominalTime(), 'yyyy')}

I think something's wrong with my workflow/coordinator parameters as the query runs without error, but doesn't result in any inserts.

Thanks.

[#2 · Accepted solution]

${coord:formatTime(coord:nominalTime(), 'yyyy-MM-dd')}

the above thing works for me. Try it and let me know.

Also you can check stdout and stderr logs in HUE. check and paste the same for further analysis. 

[#3]

Thanks, but are you saying I should be using that statement in place of one of my assignments, and if so, then which one? I'm not quite clear on what you're saying I should change in what I'm doing. Thanks!

[#4]

 <action>
        <workflow>
            <app-path>${nameNode}${workflows}workflow.xml</app-path>
            <configuration>
                <property>
                    <name>currdate</name>
                    <value>${coord:formatTime(coord:nominalTime(), 'yyyy-MM-dd')}</value>
                </property>
            </configuration>
        </workflow>
</action>

is my action in the coordinator, which is starting the work flow and the workflow could read the currdate value just fine.

may be if you can paste your coordinator I could guide you more.

[#5]

I figured this out.   Above code does work, however the database state was not as I expected so that's why it was failing.  Hopefully above could be useful to someone else doing the same thing, as I found almost nothing online concerning using Hue with Oozie.