Problem with sqoop : exporting pig file to Mysql

nlejeune · ‎09-17-2017

Hi,

I'm trying to export file from an azure datalake to a mysql database.

Here is a sample of my source file :

2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-02,1CF,D,,7.4,-0.22,2008-10-02,931
2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-03,1CF,D,,7.4,-0.22,2008-10-03,931
2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-04,1CF,D,,7.4,-0.22,2008-10-04,931
2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-05,1CF,D,,7.4,-0.22,2008-10-05,931
2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-06,1CF,D,,7.4,-0.22,2008-10-06,931
2008-10-03,TCPL - St. Clair,NGX Phys, FP (US/MM),2008-10-07,1CF,D,,7.4,-0.22,2008-10-07,931

my sqoop command :

sqoop export \
--connect jdbc:mysql://mydb.mysql.database.azure.com:3306/table_name \
--connection-manager org.apache.sqoop.manager.MySQLManager \
--password pass \
--username user \
--export-dir 'adl://datalake.azuredatalakestore.net/path/to/the/pig/generated/files/' \
--table my_destination_table \
--input-fields-terminated-by ',' \
--input-lines-terminated-by '\n' \
--batch

When I execute sqoop, here is the error :

2017-09-16 00:19:05,998 ERROR [main] org.apache.sqoop.mapreduce.TextExportMapper: Exception:
java.lang.RuntimeException: Can't parse input data: ' FP'
        at gas.__loadFromFields(gas.java:708)
        at gas.parse(gas.java:606)
        at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:89)
        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:170)
        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:1866)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.lang.IllegalArgumentException
        at java.sql.Date.valueOf(Date.java:143)
        at gas.__loadFromFields(gas.java:670)
        ... 12 more

I tried everything and I do not understand what append, please advice !

Thank you.

riyer · ‎10-06-2017

Can you send a sample file. I will try it on my machine.

Shelton · ‎10-06-2017

@Nicolas Le Jeune

The problem is the FP (US/MM) sqoop is failing to interpret the Brackets and Slash / after the fourth field.