Sqoop - Can't parse input data

[#1]

Hi All,

I am using sqoop 1.4.6 to transfer data between hive and MySQL. The sqoop-import job was succeeded, but the sqoop-export job was failed with the error "Can't parse input data".

The main reason I think is that there are some special characters(i.e. ^@) in the HDFS plain file which was imported by sqoop-import as shown in below.

The sqoop import command I used is :

sqoop import --connect jdbc:mysql://******:3306/smart_voice_data --table sqoop_import_test --username ****** --password ****** --verbose --hive-database hive_smart_voice_data --hive-table hive_sqoop_import_test --lines-terminated-by "\n" --hive-drop-import-delims --split-by id  --null-non-string NULL --null-string NULL --fields-terminated-by "|"  --delete-target-dir --hive-overwrite

The sqoop export command I used is :

sqoop export --connect "jdbc:mysql://******:3306/smart_voice_data?useUnicode=true&characterEncoding=gbk" --username ****** --password ****** --table sqoop_hi_filog_goi_vidaa4_normalized --export-dir hdfs://cdh5node1:8020/user/hive/warehouse/hive_smart_voice_data.db/hive_sqoop_import_test/  --input-null-string "\\\\N" --input-null-non-string "\\\\N" --fields-terminated-by '|' --input-lines-terminated-by '\n'

Questions:

1 、Is there any way to escape/filter the special character ^@ (\000) when using sqoop to import data into HDFS?

2、 Is there any way to skip the special character ^@(\000) when using sqoop to export data to mysql , or is there any solution to make sqoop-export can parse the special character ^@ ?

3、Is there any way to skip the error and continue the remaining job when doing sqoop-export ?

Any help is really appreciated , thanks a lot !

Pengcheng

[#2]

@Pengcheng Fang

Please Refer to extra arguments section of Sqoop guide on how to handle control characters and skip on errors.

http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_extra_arguments_3

[#3]

Also refer to this post on how to handle this

https://community.hortonworks.com/questions/92891/sqoop-netezza-connector-syntax-for-ctrl-chars-trun...

[#4]

@Sunil Kumar Bolisetty, thanks for your reply. But it seems the '--ctrl-chars' extra arguments is only applicable for Netezza , not applicable for MySQL, right ?

[#5]

In my case, both the source database and target database are MySQL, so do you have any idea if the --ctrl-chars extra argument can also be applied to MySQL during sqoop import or sqoop export ?

[#6]

Hi,

Is this issue resolved? I am facing the similar issue while exporting to Oracle