Hi All,
I would like to import data from SQL server into Hive via Sqoop in Hue and start loading them incrementally, but none of the commands work. I am able to import a table via command line tho.
I am looking for solutions already for some time, but nothing i have found on a forum helped.
Via command line I am running successfully the following command:
sqoop import --connect "jdbc:sqlserver://xxx:1433;database=INFA_SOURCE" --username infa_source -P --table personen -m 1 --hive-import
The same does not work via Hue, for example I would like to list tables:
list-tables --connect jdbc:sqlserver://xxx:1433;database=INFA_SOURCE --username infa_source --password XXX --verbose
After running this command, job is successfull, but workflows fails with status KILLED.
Information about the environment:
- I have newest version cdh 5.13 running on ubuntu 16.04
- each application is saved in folder /user/hue/oozie/deployments and I placed sqljdbc4.jar driver everywhere I could locally and in hadoop (for example in /var/lib/sqoop or in hadoop /user/oozie/share/lib/lib_20171122100655/sqoop)
Each application creates job.properties file that lookes like that:
security_enabled=False
nameNode=hdfs://hadoop:8020
jobTracker=hadoop:8032
Workflow.xml:
<workflow-app name="Batch job for query-sqoop1" xmlns="uri:oozie:workflow:0.5">
<start to="sqoop-71b8"/>
<kill name="Kill">
<message>Action failed, error message[${wf:errorMessage(wf:lastErrorNode())}]</message>
</kill>
<action name="sqoop-71b8">
<sqoop xmlns="uri:oozie:sqoop-action:0.2">
<job-tracker>${jobTracker}</job-tracker>
<name-node>${nameNode}</name-node>
<command>list-tables --connect jdbc:sqlserver://xxx:1433;database=INFA_SOURCE --username infa_source --password XXX --verbose;</command>
</sqoop>
<ok to="End"/>
<error to="Kill"/>
</action>
<end name="End"/>
</workflow-app>
I tried putting jdbc in quotes "" or adding SSL information encrypt=true;trustServerCertificate=true ,but nothing works.
In a log files I can see an error:
Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]
Whole logs from a Workflow:
2017-12-28 10:58:10,948 INFO org.apache.oozie.service.JPAService: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[] No results found
2017-12-28 10:58:10,996 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@:start:] Start action [0000018-171219121312920-oozie-oozi-W@:start:] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2017-12-28 10:58:10,997 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@:start:] [***0000018-171219121312920-oozie-oozi-W@:start:***]Action status=DONE
2017-12-28 10:58:10,997 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@:start:] [***0000018-171219121312920-oozie-oozi-W@:start:***]Action updated in DB!
2017-12-28 10:58:11,070 INFO org.apache.oozie.service.JPAService: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@:start:] No results found
2017-12-28 10:58:11,092 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@:start:] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W@:start:
2017-12-28 10:58:11,092 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W
2017-12-28 10:58:11,123 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] Start action [0000018-171219121312920-oozie-oozi-W@sqoop-161f] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2017-12-28 10:58:13,362 INFO org.apache.oozie.action.hadoop.SqoopActionExecutor: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] checking action, hadoop job ID [job_1513681970898_0024] status [RUNNING]
2017-12-28 10:58:13,373 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] [***0000018-171219121312920-oozie-oozi-W@sqoop-161f***]Action status=RUNNING
2017-12-28 10:58:13,375 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] [***0000018-171219121312920-oozie-oozi-W@sqoop-161f***]Action updated in DB!
2017-12-28 10:58:13,386 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W@sqoop-161f
2017-12-28 10:58:49,949 INFO org.apache.oozie.servlet.CallbackServlet: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] callback for action [0000018-171219121312920-oozie-oozi-W@sqoop-161f]
2017-12-28 10:58:50,170 INFO org.apache.oozie.action.hadoop.SqoopActionExecutor: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] action completed, external ID [job_1513681970898_0024]
2017-12-28 10:58:50,206 WARN org.apache.oozie.action.hadoop.SqoopActionExecutor: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] Launcher ERROR, reason: Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]
2017-12-28 10:58:50,257 INFO org.apache.oozie.command.wf.ActionEndXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] ERROR is considered as FAILED for SLA
2017-12-28 10:58:50,319 INFO org.apache.oozie.service.JPAService: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] No results found
2017-12-28 10:58:50,369 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@Kill] Start action [0000018-171219121312920-oozie-oozi-W@Kill] with user-retry state : userRetryCount [0], userRetryMax [0], userRetryInterval [10]
2017-12-28 10:58:50,370 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@Kill] [***0000018-171219121312920-oozie-oozi-W@Kill***]Action status=DONE
2017-12-28 10:58:50,370 INFO org.apache.oozie.command.wf.ActionStartXCommand: SERVER[hadoop] USER[admin] GROUP[-] TOKEN[] APP[Batch job for query-sqoop1] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@Kill] [***0000018-171219121312920-oozie-oozi-W@Kill***]Action updated in DB!
2017-12-28 10:58:50,545 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@Kill] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W@Kill
2017-12-28 10:58:50,546 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W
2017-12-28 10:58:50,546 INFO org.apache.oozie.command.wf.WorkflowNotificationXCommand: SERVER[hadoop] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[0000018-171219121312920-oozie-oozi-W] ACTION[0000018-171219121312920-oozie-oozi-W@sqoop-161f] No Notification URL is defined. Therefore nothing to notify for job 0000018-171219121312920-oozie-oozi-W@sqoop-161f
Can anyone advice what else to check?
Thanks
Anna
