Sqoop Export fails

Hi Durga,

I am trying to export a file from hdfs into a mysql table but it fails with the exception:cant parse input data
below is the hdfs file data:

1|A:2|B:-1|nvl:

The data is feilds terminated by | and lines terminated by :

Could you please assist.

Can you paste the complete sqoop export script here?

Error means that there is data type mismatch between data in HDFS and data types in column names.

sqoop export
–connect “jdbc:mysql://quickstart.cloudera:3306/retail_db”
–username retail_dba
–password cloudera
–table departments_ls
–export-dir /user/cloudera/sqoop_import/departments_ls
–input-fields-terminated-by | \

departments_ls table has structure as:
department_id integer
department_name varchar(30)

and data i am trying to export is 1|A:2|B:-1|nvl:

i had imported this data into hdfs from this department_ls table intially as | and : delimited and now i am trying to export it back to department_ls using above command.Please note:I truncated the table before this export

I do not understand the issue clearly.

  • what is the field terminator?
  • what is the line terminator?
  • What is the error you are getting?

Please attach the data you are trying to import and also screen shot of the error.

Feild terminator is | and line terminator is :

Data trying to export :

1|A:2|B:-1|nvl:

departments_ls table has structure as:
department_id integer
department_name varchar(30)

Error i am getting is
Status : FAILED
Error: java.io.IOException: Can’t export data, please check failed map task logs
Caused by: java.lang.RuntimeException: Can’t parse input data

that was a typo:

this is the scoop command i am using:

Sqoop command:

sqoop export
–connect “jdbc:mysql://quickstart.cloudera:3306/retail_db”
–username retail_dba
–password cloudera
–table departments_ls
–export-dir /user/cloudera/sqoop_import/departments_ls
–input-lines-terminated-by :
–input-fields-terminated-by |

Sqoop export command is incorrect. You need to use both input-fields-terminated-by as well as --input-lines-terminated by

Have you checked the map task logs? What is the error it is throwing?

Error it shows is cant parse input data

Hi manoj.rana,

Have you solved the issue? Just let me know to suggest…

No i havent.Its very wierd.

I think i am missing something…Would request Durga to post a vedio on this.

1.Import the data from a mysql table into hdfs file as Feild terminator | and line terminator is :
2.Export the same data back to the table into mysql

Hi Manoj,

I tried the same.
Input file is:
1|Jeeva:2|Durai:

Export sqoop command is:
sqoop export --connect jdbc:mysql://ip-172-31-13-154/retail_db --username sqoopuser --password NHkkP876rp --table jee_test2 --input-fields-terminated-by ‘|’ --input-lines-terminated-by ‘:’ --export-dir /user/ramXXXXX/jee_test1;

But it exports only one record.

The output in mysql is:
±-----±------+
| id | name |
±-----±------+
| 1 | Jeeva |
±-----±------+
1 row in set.

And I found that, Sqoop has issue when we use custom line delimiter.
http://grokbase.com/t/sqoop/user/125hn62efq/sqoop-export-using-non-new-line-row-delimiter

@manoj.rana

In your query there should be space after table name, currently table name & \ is continues.
(e.g)–table departments_ls \

When I try with lines terminated as ‘:’ it copied 12 rows from mysql to hdfs during import, For export it copied back only 2 rows. But its working fine with \n though.

sqoop import
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username retail_dba
–password itversity
–table departments
–target-dir /apps/hive/warehouse/gnanaprakasam.db/sqoop_import/departments_ls
–lines-terminated-by ‘\n’
–fields-terminated-by ‘|’

In mysql
CREATE TABLE departments_ls AS SELECT * FROM retail_db.departments WHERE 1=2;

sqoop export
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username retail_dba
–password itversity
–table departments_ls
–export-dir /apps/hive/warehouse/gnanaprakasam.db/sqoop_import/departments_ls/*
–input-lines-terminated-by ‘\n’
–input-fields-terminated-by ‘|’