Created 04-18-2018 07:12 AM
I'm trying to connect to sql server thorough oozie .
below command is the working one in sqoop shell out side oozie environment
sqoop import --connect "jdbc:sqlserver://10.1.38.21;database=sample" --username sa --password sangeeth@123 --table "usage1" --split-by RGE_IND --target-dir "/junktest"
steps i have done till now
1. Placed msjdbc connector jar file in the /user/oozie/share/lib/lib_20180410142021/sqoop inside hdfs location.
2. same driver i placed even in the /user/sqoop_oozieJob/lib where my workflow and job properties present
I modified the sqoop command inside job.proerties becasue same above working command not working inside oozie.
job.properties
nameNode=hdfs://hdn01.abc.com:8020 jobTracker=hdn04.abc.com:8050 queueName=default oozie.use.system.libpath=true ozie.libpath=hdfs://hdn01.abc.com:8020/user/oozie/share/lib/lib_20180410142021/sqoop FileLocation=sqoop_oozieJob outputDirPath =${nameNode}/user/${FileLocation}/output-data sqoop_command = import --connect jdbc:sqlserver://10.1.38.21;database=sample; --username sa --password sangeeth@123 --verbose --driver com.microsoft.jdbc.sqlserver.SQLServerDriver --table usage1 --split-by RGE_IND --target-dir ${nameNode}/user/${FileLocation}/output-data oozie.wf.application.path=${nameNode}/user/${FileLocation}/
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> <prepare> <delete path="${outputDirPath}"/> </prepare> <configuration> <property> <name>mapred.compress.map.output</name> <value>true</value> </property> </configuration> <command>${sqoop_command}</command> </sqoop> <ok to="end"/> <error to="fail"/> </action> <kill name="fail"> <message>Sqoop failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message> </kill> <end name="end"/> </workflow-app>
job log :
INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@:start:] Start action [0000018-180417152516703-oozie-oozi-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10] 2018-04-18 12:09:27,992 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@:start:] [***0000018-180417152516703-oozie-oozi-W@:start:***]Action status=DONE 2018-04-18 12:09:27,992 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@:start:] [***0000018-180417152516703-oozie-oozi-W@:start:***]Action updated in DB! 2018-04-18 12:09:28,114 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@:start:] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W@:start: 2018-04-18 12:09:28,116 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W 2018-04-18 12:09:28,127 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] Start action [0000018-180417152516703-oozie-oozi-W@sqoop-node] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10] 2018-04-18 12:09:29,380 INFO SqoopActionExecutor:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] Trying to get job [job_1523967192778_0012], attempt [1] 2018-04-18 12:09:29,399 INFO SqoopActionExecutor:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] checking action, hadoop job ID [job_1523967192778_0012] status [RUNNING] 2018-04-18 12:09:29,401 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] [***0000018-180417152516703-oozie-oozi-W@sqoop-node***]Action status=RUNNING 2018-04-18 12:09:29,402 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] [***0000018-180417152516703-oozie-oozi-W@sqoop-node***]Action updated in DB! 2018-04-18 12:09:29,528 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W@sqoop-node 2018-04-18 12:09:41,263 INFO CallbackServlet:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] callback for action [0000018-180417152516703-oozie-oozi-W@sqoop-node] 2018-04-18 12:09:41,287 INFO SqoopActionExecutor:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] Trying to get job [job_1523967192778_0012], attempt [1] 2018-04-18 12:09:41,397 INFO SqoopActionExecutor:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] action completed, external ID [job_1523967192778_0012] 2018-04-18 12:09:41,403 WARN SqoopActionExecutor:523 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1] 2018-04-18 12:09:41,448 INFO ActionEndXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] ERROR is considered as FAILED for SLA 2018-04-18 12:09:41,825 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@fail] Start action [0000018-180417152516703-oozie-oozi-W@fail] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10] 2018-04-18 12:09:41,825 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@fail] [***0000018-180417152516703-oozie-oozi-W@fail***]Action status=DONE 2018-04-18 12:09:41,825 INFO ActionStartXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@fail] [***0000018-180417152516703-oozie-oozi-W@fail***]Action updated in DB! 2018-04-18 12:09:41,906 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@fail] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W@fail 2018-04-18 12:09:41,906 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W 2018-04-18 12:09:41,906 INFO WorkflowNotificationXCommand:520 - SERVER[hdn03.abc.com] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] No Notification URL is defined. Therefore nothing to notify for job 0000018-180417152516703-oozie-oozi-W@sqoop-node
exact error i can see is
ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1] 2018-04-18 12:09:41,448 INFO ActionEndXCommand:520 - SERVER[hdn03.abc.com] USER[hdfs] GROUP[-] TOKEN[] APP[sqoop-wf] JOB[0000018-180417152516703-oozie-oozi-W] ACTION[0000018-180417152516703-oozie-oozi-W@sqoop-node] ERROR is considered as FAILED for SLA
for the same problem some suggested to place the driver in right place .i did the same but still the same problem existsand job is geeting killed everytime.
is it because of my sqoop command syntax format is not correct inside job.properties? please help me out guys.
Thank you
Created 04-19-2018 12:52 PM
Created 04-19-2018 12:52 PM
Created 04-19-2018 01:10 PM
Yes I found the issue .in yarn logs
--driver com.microsoft.jdbc.sqlserver.SQLServerDriver is the wrong representation of driver. correct one is
--driver com.microsoft.sqlserver.jdbc.SQLServerDriver