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

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

.

1. Configure job.properties

#*************************************************
# 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

#!/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!! 🙂