Oozie with sqoop

[#1]

Hi,

I am able to run the Sqoop command to load data form HDFS to MySQL but when I put it inside Oozie, I get an error message

1. create a file 

[cloudera@localhost ~]$ vi bar

1, joe
2, mac

2. Put file inside HDFS

hadoop fs -put bar

3. Created a schema in MySQL table

mysql> create table foo (id int, name varchar(30));

4. Below is the sqoop command that can be used to load data from HDFS to MySQL:

$ sqoop export --connect jdbc:mysql://localhost/test --table foo -m 1 --export-dir /user/cloudera/bar

5. vi job.properties

host.localdomain:8020
jobTracker=localhost.localdomain:8021
queueName=default
examplesRoot=examples

oozie.use.system.libpath=true

oozie.wf.application.path=${nameNode}/user/${user.name}/${examplesRoot}/apps/sqoop

6. vi workflow.xml

<?xml version="1.0" encoding="UTF-8"?>
<workflow-app xmlns="uri:oozie:workflow:0.2" name="sqoop-wf">
<start to="sqoop-node"/>

<action name="sqoop-node">
<sqoop xmlns="uri:oozie:sqoop-action:0.2">
<job-tracker>${jobTracker}</job-tracker>
<name-node>${nameNode}</name-node>
<command>sqoop export --connect jdbc:mysql://localhost/test --table foo -m 1 --export-dir /user/cloudera/bar</command>
</sqoop>
<ok to="end"/>
<error to="fail"/>
</action>

<kill name="fail">
<message>Sqoop failed</message>
</kill>
<end name="end"/>
</workflow-app>

7.

I am on below directory

[cloudera@localhost ~]$ pwd
/home/cloudera

[cloudera@localhost sqoop]$ hostname
localhost.localdomain

oozie job -oozie http://localhost:11000/oozie -config examples/apps/sqoop/job.properties -run

o/p 0000007-140424064654427-oozie-oozi-W

oozie job -oozie http://localhost:11000/oozie -info 0000003-140424064654427-oozie-oozi-W

8. error message is:

[cloudera@localhost ~]$ oozie job -oozie http://localhost:11000/oozie -info 0000007-140424064654427-oozie-oozi-W
Job ID : 0000007-140424064654427-oozie-oozi-W
------------------------------------------------------------------------------------------------------------------------------------
Workflow Name : sqoop-wf
App Path : hdfs://localhost.localdomain:8020/user/cloudera/examples/apps/sqoop
Status : KILLED
Run : 0
User : cloudera
Group : -
Created : 2014-05-07 18:55 GMT
Started : 2014-05-07 18:55 GMT
Last Modified : 2014-05-07 18:56 GMT
Ended : 2014-05-07 18:56 GMT
CoordAction ID: -

Actions
------------------------------------------------------------------------------------------------------------------------------------
ID Status Ext ID Ext Status Err Code
------------------------------------------------------------------------------------------------------------------------------------
0000007-140424064654427-oozie-oozi-W@:start: OK - OK -
------------------------------------------------------------------------------------------------------------------------------------
0000007-140424064654427-oozie-oozi-W@sqoop-node ERROR job_201404240646_0013 FAILED/KILLED1
------------------------------------------------------------------------------------------------------------------------------------
0000007-140424064654427-oozie-oozi-W@fail OK - OK E0729
------------------------------------------------------------------------------------------------------------------------------------

9. Job Log

2014-05-07 11:55:47,650 INFO org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@:start:] Start action [0000007-140424064654427-oozie-oozi-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-05-07 11:55:47,651 WARN org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@:start:] [***0000007-140424064654427-oozie-oozi-W@:start:***]Action status=DONE
2014-05-07 11:55:47,651 WARN org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@:start:] [***0000007-140424064654427-oozie-oozi-W@:start:***]Action updated in DB!
2014-05-07 11:55:47,738 INFO org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] Start action [0000007-140424064654427-oozie-oozi-W@sqoop-node] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2014-05-07 11:55:48,605 WARN org.apache.oozie.action.hadoop.SqoopActionExecutor: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] credentials is null for the action
2014-05-07 11:55:49,820 INFO org.apache.oozie.action.hadoop.SqoopActionExecutor: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] checking action, external ID [job_201404240646_0013] status [RUNNING]
2014-05-07 11:55:49,922 WARN org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] [***0000007-140424064654427-oozie-oozi-W@sqoop-node***]Action status=RUNNING
2014-05-07 11:55:49,922 WARN org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] [***0000007-140424064654427-oozie-oozi-W@sqoop-node***]Action updated in DB!
2014-05-07 11:56:03,426 INFO org.apache.oozie.servlet.CallbackServlet: USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] callback for action [0000007-140424064654427-oozie-oozi-W@sqoop-node]
2014-05-07 11:56:03,504 INFO org.apache.oozie.action.hadoop.SqoopActionExecutor: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] action completed, external ID [job_201404240646_0013]
2014-05-07 11:56:03,532 WARN org.apache.oozie.action.hadoop.SqoopActionExecutor: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]
2014-05-07 11:56:03,685 INFO org.apache.oozie.command.wf.ActionEndXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@sqoop-node] ERROR is considered as FAILED for SLA
2014-05-07 11:56:03,747 INFO org.apache.oozie.command.wf.ActionStartXCommand: USER[cloudera] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000007-140424064654427-oozie-oozi-W] ACTION[0000007-140424064654427-oozie-oozi-W@fail] Start action [0000007-140424064654427-oozie-oozi-W@fail] with user-retry state : userRetryCount [0],

Any help?

Thank you!

[#2 · Accepted solution]

I created lib directory inside sqoop directory and added "mysql-connector-java-5.1.29-bin.jar" file and it resolved the issue.

Thanks

Rio

[#3]

Hi Rio,

I have similar issue when I try to import data from Oracle.  The command line works fine, but it always fail and same error message as yours when I run sqoop job in Oozie in CDH5.3.

did you add the jar in local server /var/lib/sqoop or HDFS /user/oozie/share/lib/lib_.../sqoop

Thanks in advance.

[#4]

put here:

/opt/cloudera/parcels/CDH-4.3.1-1.cdh4.3.1.p0.110/lib/sqoop/lib/mysql-connector-java-5.1.29-bin.jar

[#5]

As it works from shell directly by running sqoop command hence it should not be mysql jar issue.

Looks like oozie is not able to get sqoop jar as it is the sqqop class which is missing and not the mysql class.

I am also facing the issue.

[#6]

My scenario is little bit different. I want to import one table from MsSQL to HIVE table. So i copied sqljdbc42.jar to sqoop lib directory in HDFS. But i m stil facing same erroes. Please help me .