Support Questions
Find answers, ask questions, and share your expertise

Sqoop import with special characters in data

Sqoop import with special characters in data

Expert Contributor

Sqoop import fails with the following error:

I guess this is because of a dot or exclamation mark in the data?

How do I fix this ? Do I need to pass some arguement to allow data with special characters?

I am importing data from MySQL to hdfs/hive. The problem remains even if I remove hive-import arg which means this is a problem with sqoop?

16/05/17 13:13:39 INFO mapreduce.Job: Task Id : attempt_1463322823215_0246_m_000005_2, Status : FAILED
Error: java.lang.RuntimeException: Can't parse input data: 'Stalk.Buy.Love!'
at sales_flat_order.__loadFromFields(sales_flat_order.java:6741)
at sales_flat_order.parse(sales_flat_order.java:5939)
at org.apache.sqoop.mapreduce.MergeTextMapper.map(MergeTextMapper.java:53)
at org.apache.sqoop.mapreduce.MergeTextMapper.map(MergeTextMapper.java:34)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:146)
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:1657)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
Caused by: java.lang.NumberFormatException: For input string: "Stalk.Buy.Love!"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:589)
at java.lang.Long.valueOf(Long.java:803)
at sales_flat_order.__loadFromFields(sales_flat_order.java:5988)
... 11 more
5 REPLIES 5

Re: Sqoop import with special characters in data

@sameer lail

It seems that error is coming becuase somewhere sqoop is expecting the number input but found string i.e "Stalk.Buy.Love!" . Can you please share the full sqoop command and source table DDL?

Caused by: java.lang.NumberFormatException: For input string: "Stalk.Buy.Love!"

Re: Sqoop import with special characters in data

Expert Contributor
sqoop job --meta-connect jdbc:hsqldb:hsql://FQDN:16000/sqoop --create sample_job -- import --driver com.mysql.jdbc.Driver --connect jdbc:mysql://IP/DB?zeroDateTimeBehavior=convertToNull --username USERNAME --password PASSWORD --table TABLE_NAME --incremental increment_field --check-column column_name --last-value 0 --m 1 --merge-key FIELD_NAME --hive-import --hive-overwrite --hive-database magentodb --hive-drop-import-delims

This is the sqoop command. @Jitendra Yadav

No actually. That should not be.

Source table DDL is really too big. Would you still want me to share it? What I can confirm is that it has no invalid fields or data since it's all coming from a live server.

Re: Sqoop import with special characters in data

@sameer lail what is the data type of columns which specified in --merge-key, --incremental and --check-column option?

Re: Sqoop import with special characters in data

Expert Contributor

--incremental is lastmodified, --merge-key is id, and --check-column is datetime. @Jitendra Yadav

Re: Sqoop import with special characters in data

I'm guessing you might have malformed values within few columns, try to use "select query " import instead of full table with couple of columns. Just to isolate the issue. @sameer lail