Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Announcements
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

Sqoop export with --update-key to mssql, fails with error

Labels:
antin_leszczysz
Explorer

Created ‎03-13-2017 02:27 PM

I have a hive table that I can successfully export to a mssql table:

sqoop export --connect jdbc:sqlserver://{some.ip.address};database={somedatabase} / --username 'someuser' /
--password-file '/some/password/file' /
--table 'Sometable' /
--columns ID,value1,value2 /
--export-dir /apps/hive/warehouse/some.db/Sometable /
--input-fields-terminated-by "||" / -m 2 /
/user/oozie/share/lib/sqoop/sqljdbc4.jar

However, i wish to update on a key and run:

sqoop export --connect jdbc:sqlserver://{some.ip.address};database={somedatabase} /
--username 'someuser' /
--password-file '/some/password/file' /
--table 'Sometable' /
--columns ID,value1,value2 /
--export-dir /apps/hive/warehouse/some.db/Sometable /
--input-fields-terminated-by "||" /
--update-key ID /
--update-mode allowinsert /
-m 2 /
/user/oozie/share/lib/sqoop/sqljdbc4.jar

The logs are very unhelpful, (note: the sqoop is run through an oozie job):

...
5972 [main] INFO  org.apache.hadoop.yarn.client.api.impl.YarnClientImpl  - Submitted application application_1485423751090_3566
6016 [main] INFO  org.apache.hadoop.mapreduce.Job  - The url to track the job: http://had003.headquarters.7layer.net:8088/proxy/application_1485423751090_3566/
6016 [main] INFO  org.apache.hadoop.mapreduce.Job  - The url to track the job: http://had003.headquarters.7layer.net:8088/proxy/application_1485423751090_3566/
6017 [main] INFO  org.apache.hadoop.mapreduce.Job  - Running job: job_1485423751090_3566
6017 [main] INFO  org.apache.hadoop.mapreduce.Job  - Running job: job_1485423751090_3566
20284 [main] INFO  org.apache.hadoop.mapreduce.Job  - Job job_1485423751090_3566 running in uber mode : false
20284 [main] INFO  org.apache.hadoop.mapreduce.Job  - Job job_1485423751090_3566 running in uber mode : false
20287 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 0% reduce 0%
20287 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 0% reduce 0%
27001 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 50% reduce 0%
27001 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 50% reduce 0%
Heart beat
37117 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 100% reduce 0%
37117 [main] INFO  org.apache.hadoop.mapreduce.Job  -  map 100% reduce 0%
38139 [main] INFO  org.apache.hadoop.mapreduce.Job  - Job job_1485423751090_3566 failed with state FAILED due to: Task failed task_1485423751090_3566_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0


38139 [main] INFO  org.apache.hadoop.mapreduce.Job  - Job job_1485423751090_3566 failed with state FAILED due to: Task failed task_1485423751090_3566_m_000000
Job failed as tasks failed. failedMaps:1 failedReduces:0


38292 [main] INFO  org.apache.hadoop.mapreduce.Job  - Counters: 32
	File System Counters
		FILE: Number of bytes read=0
		FILE: Number of bytes written=338177
		FILE: Number of read operations=0
		FILE: Number of large read operations=0
		FILE: Number of write operations=0
		HDFS: Number of bytes read=166
		HDFS: Number of bytes written=0
		HDFS: Number of read operations=4
		HDFS: Number of large read operations=0
		HDFS: Number of write operations=0
	Job Counters 
		Failed map tasks=1
		Launched map tasks=2
		Other local map tasks=1
		Rack-local map tasks=1
		Total time spent by all maps in occupied slots (ms)=16369
		Total time spent by all reduces in occupied slots (ms)=0
		Total time spent by all map tasks (ms)=16369
		Total vcore-milliseconds taken by all map tasks=16369
		Total megabyte-milliseconds taken by all map tasks=25142784
	Map-Reduce Framework
		Map input records=0
		Map output records=0
		Input split bytes=156
		Spilled Records=0
		Failed Shuffles=0
		Merged Map outputs=0
		GC time elapsed (ms)=79
		CPU time spent (ms)=960
		Physical memory (bytes) snapshot=230920192
		Virtual memory (bytes) snapshot=3235606528
		Total committed heap usage (bytes)=162529280
	File Input Format Counters 
		Bytes Read=0
	File Output Format Counters 
		Bytes Written=0
38292 [main] INFO  org.apache.hadoop.mapreduce.Job  - Counters: 32
	File System Counters
		FILE: Number of bytes read=0
		FILE: Number of bytes written=338177
		FILE: Number of read operations=0
		FILE: Number of large read operations=0
		FILE: Number of write operations=0
		HDFS: Number of bytes read=166
		HDFS: Number of bytes written=0
		HDFS: Number of read operations=4
		HDFS: Number of large read operations=0
		HDFS: Number of write operations=0
	Job Counters 
		Failed map tasks=1
		Launched map tasks=2
		Other local map tasks=1
		Rack-local map tasks=1
		Total time spent by all maps in occupied slots (ms)=16369
		Total time spent by all reduces in occupied slots (ms)=0
		Total time spent by all map tasks (ms)=16369
		Total vcore-milliseconds taken by all map tasks=16369
		Total megabyte-milliseconds taken by all map tasks=25142784
	Map-Reduce Framework
		Map input records=0
		Map output records=0
		Input split bytes=156
		Spilled Records=0
		Failed Shuffles=0
		Merged Map outputs=0
		GC time elapsed (ms)=79
		CPU time spent (ms)=960
		Physical memory (bytes) snapshot=230920192
		Virtual memory (bytes) snapshot=3235606528
		Total committed heap usage (bytes)=162529280
	File Input Format Counters 
		Bytes Read=0
	File Output Format Counters 
		Bytes Written=0
38319 [main] INFO  org.apache.sqoop.mapreduce.ExportJobBase  - Transferred 166 bytes in 34.0574 seconds (4.8741 bytes/sec)
38319 [main] INFO  org.apache.sqoop.mapreduce.ExportJobBase  - Transferred 166 bytes in 34.0574 seconds (4.8741 bytes/sec)
38332 [main] INFO  org.apache.sqoop.mapreduce.ExportJobBase  - Exported 0 records.
38332 [main] INFO  org.apache.sqoop.mapreduce.ExportJobBase  - Exported 0 records.
38332 [main] ERROR org.apache.sqoop.mapreduce.ExportJobBase  - Export job failed!
38332 [main] ERROR org.apache.sqoop.mapreduce.ExportJobBase  - Export job failed!
38333 [main] ERROR org.apache.sqoop.tool.ExportTool  - Error during export: Export job failed!
38333 [main] ERROR org.apache.sqoop.tool.ExportTool  - Error during export: Export job failed!


<<< Invocation of Sqoop command completed <<<


 Hadoop Job IDs executed by Sqoop: job_1485423751090_3566


Intercepting System.exit(1)


<<< Invocation of Main class completed <<<


Failing Oozie Launcher, Main class [org.apache.oozie.action.hadoop.SqoopMain], exit code [1]


Oozie Launcher failed, finishing Hadoop job gracefully


Oozie Launcher, uploading action data to HDFS sequence file: hdfs://{Something}
38406 [main] INFO  org.apache.hadoop.io.compress.zlib.ZlibFactory  - Successfully loaded & initialized native-zlib library
38407 [main] INFO  org.apache.hadoop.io.compress.CodecPool  - Got brand-new compressor [.deflate]


Oozie Launcher ends


38538 [main] INFO  org.apache.hadoop.mapred.Task  - Task:attempt_1485423751090_3565_m_000000_0 is done. And is in the process of committing
38538 [main] INFO  org.apache.hadoop.mapred.Task  - Task:attempt_1485423751090_3565_m_000000_0 is done. And is in the process of committing
38601 [main] INFO  org.apache.hadoop.mapred.Task  - Task attempt_1485423751090_3565_m_000000_0 is allowed to commit now
38601 [main] INFO  org.apache.hadoop.mapred.Task  - Task attempt_1485423751090_3565_m_000000_0 is allowed to commit now
38641 [main] INFO  org.apache.hadoop.mapreduce.lib.output.FileOutputCommitter  - Saved output of task 'attempt_1485423751090_3565_m_000000_0' to hdfs://{Something}
38692 [main] INFO  org.apache.hadoop.mapred.Task  - Task 'attempt_1485423751090_3565_m_000000_0' done.
38692 [main] INFO  org.apache.hadoop.mapred.Task  - Task 'attempt_1485423751090_3565_m_000000_0' done.

Does anyone have an idea why I cannot update with inserts to mssql?

4,355 Views
0 Kudos
5 REPLIES 5

antin_leszczysz
Explorer

Created ‎03-21-2017 11:24 AM

In the release note: https://sqoop.apache.org/docs/1.4.6/sqoop-1.4.6.releasenotes.html

It states it is supported:

New features:

  • SQOOP-1403 Upsert export for SQL Server
1,709 Views
0 Kudos

deepesh1
Master Collaborator

Created ‎03-21-2017 05:35 PM

You can look at the application logs from the failed application. To fetch them run the following command as the user who ran the Sqoop command.

yarn logs -applicationId application_1485423751090_3566 > app_logs.txt

app_logs.txt would contain more details on the errors. If you can't figure out, please post them here.

1,709 Views

antin_leszczysz
Explorer

Created ‎03-22-2017 11:30 AM

Thanks,

the log gave me:

..

2017-03-13 14:07:47,804 ERROR [Thread-12] org.apache.sqoop.mapreduce.AsyncSqlOutputFormat: Got exception in update thread: com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to numeric.

...

Will investigate what column is coursing this issue and will try to resolve.

1,709 Views
0 Kudos

antin_leszczysz
Explorer

Created ‎03-23-2017 08:54 AM

So annoyingly, the nvarchar/numeric issue was resolved and now I receive a generic error message:

31728 [main] ERROR org.apache.sqoop.mapreduce.ExportJobBase  - Export job failed! 
31728 [main] ERROR org.apache.sqoop.mapreduce.ExportJobBase  - Export job failed! 
31728 [main] ERROR org.apache.sqoop.tool.ExportTool  - Error during export: Export job failed!
31728 [main] ERROR org.apache.sqoop.tool.ExportTool  - Error during export: Export job failed!
1,709 Views
0 Kudos

sastry11
New Contributor

Created ‎05-25-2018 07:04 AM

I have same problem so please help me to solve this problem

1,709 Views
0 Kudos
Take a Tour of the Community
Don't have an account?
Register
Your experience may be limited. Sign in to explore more.
Announcements
Product Announcements
CDP Public Cloud: May 2023 Release Summary
What's New @ Cloudera
Cloudera Operational Database (COD) provides enhancements to the --scale-type CDP CLI option
What's New @ Cloudera
Cloudera Operational Database (COD) UI supports creating a smaller cluster using a predefined Data Lake template
What's New @ Cloudera
Cloudera Operational Database (COD) supports scaling up the clusters vertically
Product Announcements
CDP Public Cloud: April 2023 Release Summary
View More Announcements