Am facing bit issue while running Hive Action from Oozie from Second Workflow Job Run. On the 1st Run of the Worfklow Jobs is running fine with Pig & Hive Action , but whenever the Job is going to run on its second instance, on scheduled time the same job getting failed with Hive exception saying not able to access the metastore derby. {not Sure which Jar the same is checking for Derby, where am using Oracle and same is updated in hive-site.xml, hive-default.xml}

If the job started freshly after killing the previous instance the same is getting completed as expected.

Using Hive0.9 and Oozie 3.3.0, metastore using Oracle Have updated the hive-site.xml,hive-default.xml, oozie-user-hive-default.xml in HDFS along with in Local and Shared Location of DFS Oozie, but still the same not working.

My work flow as below :

<action name="hiveForUploadData">
<hive xmlns="uri:oozie:hive-action:0.2">
<job-tracker>${jobTracker}</job-tracker>
<name-node>${nameNode}</name-node>
<configuration>
<property>
<name>oozie.launcher.mapred.job.queue.name</name>
<value>default</value>
</property>
<property>
<name>oozie.hive.defaults</name>
<value>${namenode}/user/hive-default/hive-default.xml</value> -- {have tried with hive-site.xml & oozie-user-hive-default.xml as well }
</property>
</configuration>
<script>UploadScripts.q</script>
<param>inputfile_name=/user/input/table/filename</param>
</hive>
<ok to="end"/>
<error to="error"/>
</action>
<kill name="error">
<message>Hive Action Failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
</kill>

1st Run got success, working fine Oracle Meta Store getting updated as well 2nd run Getting failed with : not able to access metastore {derby} 1836 [main] INFO hive.ql.parse.ParseDriver - Parse Completed 1959 [main] INFO org.apache.hadoop.hive.metastore.HiveMetaStore - 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore 1985 [main] INFO org.apache.hadoop.hive.metastore.ObjectStore - ObjectStore, initialize called 2278 [main] ERROR DataNucleus.Plugin - Bundle "org.eclipse.jdt.core" requires "org.eclipse.core.resources" but it cannot be resolved. 2278 [main] ERROR DataNucleus.Plugin - Bundle "org.eclipse.jdt.core" requires "org.eclipse.core.runtime" but it cannot be resolved. 2278 [main] ERROR DataNucleus.Plugin - Bundle "org.eclipse.jdt.core" requires "org.eclipse.text" but it cannot be resolved. 2420 [main] INFO DataNucleus.Persistence - Property datanucleus.cache.level2 unknown - will be ignored 2421 [main] INFO DataNucleus.Persistence - Property javax.jdo.option.NonTransactionalRead unknown - will be ignored 2421 [main] INFO DataNucleus.Persistence - ================= Persistence Configuration =============== 2426 [main] INFO DataNucleus.Persistence - DataNucleus Persistence Factory - Vendor: "DataNucleus" Version: "2.0.3" 2426 [main] INFO DataNucleus.Persistence - DataNucleus Persistence Factory initialised for datastore URL="jdbc:derby:;databaseName=metastore_db;create=true" driver="org.apache.derby.jdbc.EmbeddedDriver" userName="APP" 2426 [main] INFO DataNucleus.Persistence - =========================================================== 8704 [main] INFO DataNucleus.Datastore.Schema - Initialising Catalog "", Schema "APP" using "None" auto-start option 8704 [main] INFO DataNucleus.Datastore.Schema - Catalog "", Schema "APP" initialised - managing 0 classes 8795 [main] INFO org.apache.hadoop.hive.metastore.ObjectStore - Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order" 8832 [main] INFO DataNucleus.MetaData - Registering listener for metadata initialisation 8858 [main] INFO org.apache.hadoop.hive.metastore.ObjectStore - Initialized ObjectStore

Am not sure why the Oozie is not reading the User defined "hive-default.xml" and the same is reading from Some Jar, not sure which Jar its being used. Also why/how every 1st Run of the fresh Oozie job getting success where from the second run onwards the job is not able to read the same. Could you guys help me find out where am missing or what need to be update

You need to insert just above the <configuration> tag:

<job-xml>[HIVE SETTINGS FILE]</job-xml>

where [HIVE SETTINGS FILE] is the file most commonly called hive-site.xml. This will enable your Hive script to connect to the metastore.