Sqoop Export fails

#1

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.

0 Likes

#2

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.

0 Likes

#3

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

0 Likes

#4

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.

0 Likes

#5

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

0 Likes

#6

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

0 Likes

#7

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 |

0 Likes

#8

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

0 Likes

#9

Error it shows is cant parse input data

0 Likes

#10

Hi manoj.rana,

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

0 Likes

#11

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

0 Likes

#12

@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 ‘|’

0 Likes

#13

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

0 Likes

Sqoop export using delimiters
Sqoop Export - Delimiters