Oozie shell action - Run hive query in shell script via Oozie with Kerberos environment

[#1]

Below are the steps to run hive query in a shell script using Oozie shell action

.

1. Configure job.properties

Example:

#*************************************************
#  job.properties
#*************************************************
nameNode=hdfs://<namenode-fqdn>:8020
jobTracker=<resourcemanager-host-fqdn>:8050
queueName=default
examplesRoot=examples
oozie.use.system.libpath=true
oozie.wf.application.path=${nameNode}/user/${user.name}/

.

2. Configure Workflow.xml

Example:

<workflow-app xmlns="uri:oozie:workflow:0.3" name="shell-wf">
<credentials>
  <credential name='my_auth' type='hcat'>
    <property>
      <name>hcat.metastore.uri</name>
      <value>thrift://<hive-metastore-hostname>:9083</value>
    </property>
    <property>
      <name>hcat.metastore.principal</name>
      <value>hive/_HOST@HWX.COM</value>
    </property>
  </credential>
</credentials>
    <start to="shell-node"/>
    <action name="shell-node" cred="my_auth">
        <shell xmlns="uri:oozie:shell-action:0.1">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <exec>test.sh</exec>
       <file>/user/<username>/test.sh</file>
        </shell>
        <ok to="end"/>
        <error to="fail"/>
    </action>
    <kill name="fail">
        <message>Shell action failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>
    <end name="end"/>
</workflow-app>

.

3. Write sample shell script

Example:

#!/bin/bashhive -e "SET mapreduce.job.credentials.binary=$HADOOP_TOKEN_FILE_LOCATION; select count(*) from test_hive;"

.

4. Upload workflow.xml and shell script to "oozie.wf.application.path" defined in job.properties

.

5. Follow below command to run Oozie workflow

oozie job -oozie http://<oozie-server-hostname>:11000/oozie -config /$PATH/job.properties -run

Please note - This has been successfully tested with hive.execution.engine=mr;

.

Please comment if you have any question! Happy Hadooping!! 🙂

[#2]

@Kuldeep Kulkarni Is there a way of specifying hive.execution.engine=mr in oozie just to override the global setup (where hive.execution.engine=tez)? I am having issues loading into hive because Tez cannot launch AM. I have tried it with MR as execution engine and it works fine.

[#3]

@Kuldeep Kulkarni :

Could you let me know how can I call a Hive scripts file i:e <script_name>.hql into a Ooozie shell action file. Is there a way I can achieve the same.

[#4]

@Kuldeep Kulkarni we are using hiveserver2, can we use beeline cli instead of hivecli for the same setup?

[#5]

https://community.hortonworks.com/articles/52877/oozie-shell-action-run-hivetez-query-in-shell-scri....