Sqoop export issue without delimiter

Hi,

I am facing below issue while exporting the table from HDFS to MySQL. Please look into the below log and suggest me, if I am wrong:

Source Data in HDFS:

[shubhaprasadsamal@gw01 ~]$ hadoop fs -ls /apps/hive/warehouse/shitansu1_db.db/departments
Found 2 items
-rwxrwxrwx 3 shubhaprasadsamal hdfs 0 2017-01-06 13:56 /apps/hive/warehouse/shitansu1_db.db/departments/_SUCCESS
-rwxrwxrwx 3 shubhaprasadsamal hdfs 84 2017-01-06 13:56 /apps/hive/warehouse/shitansu1_db.db/departments/part-m-00000
[shubhaprasadsamal@gw01 ~]$

Destination table in MySQL:

mysql> use retail_export;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables like ‘departments_export_sss’;
±-------------------------------------------------+
| Tables_in_retail_export (departments_export_sss) |
±-------------------------------------------------+
| departments_export_sss |
±-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from departments_export_sss;
Empty set (0.00 sec)

Export command and error logs:

[shubhaprasadsamal@gw01 ~]$ sqoop export -m 2 --connect “jdbc:mysql://nn01.itversity.com:3306/retail_export” --username retail_dba --password itversity --table departments_export_sss --export-dir /apps/hive/warehouse/shitansu1_db.db/departments --outdir java_files --batch

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

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

Please suggest.

Thanks,
Shitansu

You have to go to task logs and issue is highlighted over there.

You can see the issue over there. Issue is you are trying to export data which have /u001 as delimiter with out specifying input-fields-terminated-by. With out that sqoop will try to export using , as delimiter.

Conclusion: It is not issue with sqoop export, it is issue with the script.

Thanks for looking into the issue.

I was trying to export the data from the file where data was not stored in “,” separated field and the error was on the row#6 of the file where the program was trying to export “7Fan Shop” (with /u001 - Split with space) without having “input-fields-terminated-by” in the code

When I tried exporting the data from the file where data was stored in “,” separated field, import went successfully without having “input-fields-terminated-by” in the code.

Thanks,
Shitansu.