Oozie workflow not running properly

Question

I have created a new Oozie workflow in Hue UI based on the below hql query.

things.hql

drop table output_table;
create table output_table like things;
insert overwrite table output_table select t.* from things_dup td right outer join things t on (td.item_id = t.item_id) where td.item_id is null;
insert overwrite table things_dup select * from things;

The tables are,

things table

item_id product
1         soap
2         chocklate 

things_dup

item_id product
1         soap

when i run the hql seperately

hadoop dfs -f things.hql

its working fine. things_dup table have updated properly.

But when i run the workflow, things_dup table have not updated. insert overwrite table things_dup select * from things;

Can any one know why? Please help me to fix this issue.

Workflow.xml

<workflow-app name="Things_workflow" xmlns="uri:oozie:workflow:0.4">
    <start to="Things_workflow"/>
    <action name="Things_workflow">
        <hive xmlns="uri:oozie:hive-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
              <job-xml>/user/cloudera/hive-site.xml</job-xml>
            <script>things.hql</script>
            <file>/user/cloudera/hive-site.xml#hive-site.xml</file>
        </hive>
        <ok to="end"/>
        <error to="kill"/>
    </action>
    <kill name="kill">
        <message>Action failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

action

<hive xmlns="uri:oozie:hive-action:0.2">
  <job-tracker>localhost.localdomain:8021</job-tracker>
  <name-node>hdfs://localhost.localdomain:8020</name-node>
  <job-xml>/user/cloudera/hive-site.xml</job-xml>
  <script>things.hql</script>
  <file>/user/cloudera/hive-site.xml#hive-site.xml</file>
</hive>

Thanks, manimekalai

Answer 1

I also got issue with hive oozie but finally solved.

Please match you workflow.xml with this one.

Please must put this job-xml line before configuration node.

<?xml version="1.0" encoding="UTF-8"?>
<workflow-app xmlns="uri:oozie:workflow:0.2" name="pig-hive-wf">

<start to="hive-node" />

    <action name="hive-node">
    <hive xmlns="uri:oozie:hive-action:0.2">
        <job-tracker>${jobTracker}</job-tracker>
        <name-node>${nameNode}</name-node>

        <job-xml>hive-site.xml</job-xml>

        <configuration>
        <property>
            <name>mapred.job.queue.name</name>
            <value>${queueName}</value>
        </property>
            <property>
                    <name>oozie.hive.defaults</name>
                    <value>/user/cloudera/oozie/pig_hive_data_WF/hive-site.xml</value>
                </property>
        </configuration>
        <script>/user/cloudera/oozie/pig_hive_data_WF/load_data.q</script>
        <param>LOCATION=/user/${wf:user()}/oozie/pig_hive_data_WF/output/pig_loaded_data</param>
        <file>hive-conf.xml#hive-conf.xml</file>
    </hive>
    <ok to="end"/>
    <error to="fail"/>
    </action>

    <kill name="fail">
    <message>Pig Hive job is  failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end" />
</workflow-app> 

you can copy hive-site.xml file from /etc/hive/conf/hive-site.xml and put within workflow dir.