Sqoop Export job failed!




I have been trying to export the data from my hive table to MYSQL table.
Here is the code I have tried.

sqoop import --connect jdbc:mysql://nn01.itversity.com/retail_db --username retail_dba --password itversity \
--table orders --hive-import --hive-overwrite --hive-database phani_retail_db --hive-table orders --fields-terminated-by ':' --lines-terminated-by '\n'

sqoop import --connect jdbc:mysql://nn01.itversity.com/retail_db --username retail_dba --password itversity \
--table order_items --hive-import --hive-overwrite --hive-database phani_retail_db --hive-table order_items --fields-terminated-by ':' --lines-terminated-by '\n'

create table order_revenue AS
select order_date as order_date,
sum(order_item_subtotal) as order_revenue
from orders join order_items
on order_id = order_item_order_id
where order_date LIKE "2013-07%"
group by order_date;

For exporting Data into MYSQL, I have used the below script.

sqoop export --connect jdbc:mysql://nn01.itversity.com/retail_export --username retail_dba --password itversity --table phani_retail_order_revenue --export-dir hdfs://nn01.itversity.com:8020/apps/hive/warehouse/phani_retail_db.db/order_revenue --input-fields-terminated-by β€˜:’ --input-lines-terminated-by β€˜\n’

Here is the error I’m getting.

Warning: /usr/hdp/ does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
17/12/07 13:05:50 INFO sqoop.Sqoop: Running Sqoop version:
17/12/07 13:05:50 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
17/12/07 13:05:51 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/12/07 13:05:51 INFO tool.CodeGenTool: Beginning code generation
17/12/07 13:05:51 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM phani_retail_order_revenue AS t LIMIT 1
17/12/07 13:05:51 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM phani_retail_order_revenue AS t LIMIT 1
17/12/07 13:05:51 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/hdp/
Note: /tmp/sqoop-phanimandava/compile/0e979a3f13c2598d26bd0a5bdf5a982a/phani_retail_order_revenue.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/12/07 13:05:53 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-phanimandava/compile/0e979a3f13c2598d26bd0a5bdf5a982a/phani_retail_order_revenue.jar
17/12/07 13:05:53 INFO mapreduce.ExportJobBase: Beginning export of phani_retail_order_revenue
17/12/07 13:05:55 INFO impl.TimelineClientImpl: Timeline service address: http://rm01.itversity.com:8188/ws/v1/timeline/
17/12/07 13:05:55 INFO client.RMProxy: Connecting to ResourceManager at rm01.itversity.com/
17/12/07 13:05:55 INFO client.AHSProxy: Connecting to Application History server at rm01.itversity.com/
17/12/07 13:06:01 INFO input.FileInputFormat: Total input paths to process : 1
17/12/07 13:06:01 INFO input.FileInputFormat: Total input paths to process : 1
17/12/07 13:06:02 INFO mapreduce.JobSubmitter: number of splits:4
17/12/07 13:06:02 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1507687444776_21350
17/12/07 13:06:03 INFO impl.YarnClientImpl: Submitted application application_1507687444776_21350
17/12/07 13:06:03 INFO mapreduce.Job: The url to track the job: http://rm01.itversity.com:8088/proxy/application_1507687444776_21350/
17/12/07 13:06:03 INFO mapreduce.Job: Running job: job_1507687444776_21350
17/12/07 13:06:11 INFO mapreduce.Job: Job job_1507687444776_21350 running in uber mode : false
17/12/07 13:06:11 INFO mapreduce.Job: map 0% reduce 0%
17/12/07 13:06:18 INFO mapreduce.Job: map 100% reduce 0%
17/12/07 13:06:19 INFO mapreduce.Job: Job job_1507687444776_21350 failed with state FAILED due to: Task failed task_1507687444776_21350_m_000003
Job failed as tasks failed. failedMaps:1 failedReduces:0

17/12/07 13:06:19 INFO mapreduce.Job: Counters: 13
Job Counters
Failed map tasks=1
Killed map tasks=3
Launched map tasks=4
Data-local map tasks=3
Rack-local map tasks=1
Total time spent by all maps in occupied slots (ms)=38620
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=19310
Total vcore-milliseconds taken by all map tasks=19310
Total megabyte-milliseconds taken by all map tasks=39546880
Map-Reduce Framework
CPU time spent (ms)=0
Physical memory (bytes) snapshot=0
Virtual memory (bytes) snapshot=0
17/12/07 13:06:19 WARN mapreduce.Counters: Group FileSystemCounters is deprecated. Use org.apache.hadoop.mapreduce.FileSystemCounter instead
17/12/07 13:06:19 INFO mapreduce.ExportJobBase: Transferred 0 bytes in 24.5018 seconds (0 bytes/sec)
17/12/07 13:06:19 INFO mapreduce.ExportJobBase: Exported 0 records.
17/12/07 13:06:19 ERROR mapreduce.ExportJobBase: Export job failed!
17/12/07 13:06:19 ERROR tool.ExportTool: Error during export: Export job failed!

Can you please have a look at this one? @itversity @vinodnerella


Its looks like issue with delimiters. Check your table in hive using describe and use correct value for --input-fields-terminated-by

Your logs for failed map task shows issue while parsing -

Error: java.io.IOException: Can’t export data, please check failed map task logs at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:122) at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39) at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:146) at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162) Caused by: java.lang.RuntimeException: *Can’t parse input data: β€˜2013-07-31 00131878.06000000006’ at phani_retail_order_revenue.__loadFromFields(phani_retail_order_revenue.java:267) at phani_retail_order_revenue.parse(phani_retail_order_revenue.java:210) at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89) … 10 more Caused by: java.util.NoSuchElementException at java.util.ArrayList$Itr.next(ArrayList.java:854) at phani_retail_order_revenue.__loadFromFields(phani_retail_order_revenue.java:262) … 12 more


Hi @s23711,

Thanks for your reply.
I have noticed why this problem occurred. The data itself contains β€˜:’ in it and the fields are also delimited by β€˜:’.
So while parsing, it would receive too many β€˜:’ to map.
But is there any other way to export such data?