Support Questions
Find answers, ask questions, and share your expertise

Sqoop workflow error in Oozie

New Contributor

I created a Sqoop workflow in Oozie to sqoop data from MySQL into Hive table.

If I run Sqoop job in terminal, it run fine and data were successfully inserted into Hive table, but if I put the job inside Oozie and run it, it gives me an error:

Could not load org.apache.hadoop.hive.conf.HiveConf. Make sure HIVE_CONF_DIR is set correctly.

Encountered IOException running import job: java.io.IOException: java.lang.ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

I check the log and notice that Sqoop had successfully extract out the data from MySQL, but when want to dump inside Hive, this error comes out.

Can anyone help me with this?

I'm using HDP 2.5 here and I install it though Ambari Auto Install.

I run it as non-root user.

Below is my properties file for Oozie:

nameNode=hdfs://master.nodes:8020
jobTracker=master.nodes:8050
queueName=default
examplesRoot=jas-oozie

oozie.use.system.libpath=true
oozie.libpath=${nameNode}/user/oozie/share/lib
oozie.action.sharelib.for.pig=hive,pig,hcatalog
oozie.action.sharelib.for.hive=pig,hcatalog,atlas,hive

oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/jas-oozie-workflow.xml
outputDir=jas

and below is the xml file for Oozie:

<?xml version="1.0" encoding="UTF-8"?>

<workflow-app xmlns="uri:oozie:workflow:0.2" name="jas-import-wf">
    <start to="sqoop-import-air-quality-node"/>

    <action name="sqoop-import-air-quality-node">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <prepare>
                <delete path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data"/>
                <!-- <mkdir path="${nameNode}/user/${wf:user()}/${examplesRoot}/output-data"/> -->
            </prepare>
            <configuration>
                <property>
                    <name>mapred.job.queue.name</name>
                    <value>${queueName}</value>
                </property>
            </configuration>
            <arg>import</arg>
            <arg>--connect</arg>
            <arg>jdbc:mysql://xx.xx.xx.xx:3306/xxxx?dontTrackOpenResources=true&defaultFetchSize=1000&useCursorFetch=true&zeroDateTimeBehavior=convertToNull</arg>
            <arg>--driver</arg>
            <arg>com.mysql.jdbc.Driver</arg>
            <arg>--username</arg>
            <arg>xx</arg>
            <arg>--password</arg>
            <arg>xx</arg>
            <arg>--query</arg>
            <arg>
                select <fields> from <table> where $CONDITIONS
            </arg>
            <arg>--hive-import</arg>
            <arg>--hive-drop-import-delims</arg>
            <arg>--hive-overwrite</arg>
            <arg>--hive-table</arg>
            <arg>table</arg>
            <arg>--target-dir</arg>
            <arg>/user/${wf:user()}/${examplesRoot}/output-data/sqoop-import</arg>
            <arg>-m</arg>
            <arg>1</arg>
        </sqoop>
        <ok to="end"/>
        <error to="import-air-quality-fail"/>
    </action>

    <kill name="import-air-quality-fail">
        <message>Sqoop from ICP failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
    </kill>

    <end name="end"/>
</workflow-app>

Thank you.

3 REPLIES 3

Re: Sqoop workflow error in Oozie

Super Guru
@Tay Eng Soon

It looks like hive-common jar is missing in oozie sharelib under sqoop directory.

Can you please execute below commands and let me know how it goes.

#Command 1 - Copy hive client jars to sqoop directory under Oozie sharelib.

Note - Please replace target directory as per your sharelib location.

sudo -u hdfs hadoop fs -put `ls -lrt /usr/hdp/current/hive-client/lib/hive-*.jar|grep -v ^l|awk '{print $8}'` /user/oozie/share/lib/lib_<timestamp>/sqoop/

#Command 2 - Update sharelib

Note - Please run below command on Oozie host as 'oozie' user.

oozie admin -oozie http://localhost:11000/oozie -sharelibupdate

Re: Sqoop workflow error in Oozie

Explorer
@Tay Eng Soon

Can you check the following hive jars are available in the oozie share lib?

-rw-r--r-- 3 oozie hdfs 337967 2016-06-24 12:46 /user/oozie/share/lib/lib_xxx/sqoop/hive-common-xxx.jar

-rw-r--r-- 3 oozie hdfs 21488978 2016-06-24 12:45 /user/oozie/share/lib/lib_xxx/sqoop/hive-exec-xxx.jar

-rw-r--r-- 3 oozie hdfs 54917 2016-06-24 13:42 /user/oozie/share/lib/lib_xxx/sqoop/hive-hcatalog-server-extensions-xxx.jar

-rw-r--r-- 3 oozie hdfs 5998831 2016-06-24 13:53 /user/oozie/share/lib/lib_xxx/sqoop/hive-metastore-xxx.jar

-rw-r--r-- 3 oozie hdfs 919215 2016-06-24 12:45 /user/oozie/share/lib/lib_xxx/sqoop/hive-serde-xxx.jar

-rw-r--r-- 3 oozie hdfs 10497 2016-06-24 12:46 /user/oozie/share/lib/lib_xxx/sqoop/hive-shims-xxx.jar

-rw-r--r-- 3 oozie hdfs 20245 2016-06-24 06:42 /user/oozie/share/lib/lib_xxx/sqoop/hive-site.xml

-rw-r--r-- 3 oozie hdfs 892150 2016-06-24 13:57 /user/oozie/share/lib/lib_xxx/sqoop/tez-api-xxx.jar

-rw-r--r-- 3 oozie hdfs 275448 2016-06-24 14:05 /user/oozie/share/lib/lib_xxx/sqoop/tez-mapreduce-xxx.jar

-rw-r--r-- 3 oozie hdfs 6652 2016-06-24 15:53 /user/oozie/share/lib/lib_xxx/sqoop/tez-site.xml

-rw-r--r-- 3 oozie hdfs 22116 2016-06-24 15:59 /user/oozie/share/lib/lib_xxx/sqoop/tez-yarn-timeline-history-with-fs-xxx.jar

Re: Sqoop workflow error in Oozie

Rising Star

hive-import depends on hive client to be available. Generally you don't want to pollute the share lib of an action with other action libraries.

Specifying this in your job properties as you are doing earlier and you can try to use the hcatalog-table option in the hive-table

  1. oozie.action.sharelib.for.sqoop=sqoop,hive,hcatalog