Sqoop export issue without delimiter

Big Data Apache Sqoop
#1

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

#2

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.

#3

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.