5

I have a tab-separated textfile in HDFS, and want to export this into a MySQL table.

Since the rows in the textfile do not have numerical ids, how do I export into a table with an ID automatically set during the SQL INSERT (autoincrement)?

If I try to export (id being the last defined attribute in the table), I get 

java.util.NoSuchElementException
  at java.util.AbstractList$Itr.next(AbstractList.java:350)
  at entity.__loadFromFields(entity.java:996)

If I take the autogenerated class and modify it to exclude the id-attribute, I get 

java.io.IOException: java.sql.SQLException: No value specified for parameter 27

where parameter 27 is 'id'.

Version is Sqoop 1.3.0-cdh3u3

thomers
  • 2,603
  • 4
  • 29
  • 50

3 Answers3

3

In Sqoop 1.4.1, writing a "null" in the text file field position corresponding to the autoincrement field worked for me. After exported to mySQL you will see an incremented and automatically asigned ID.

Jorge González Lorenzo
  • 1,722
  • 1
  • 19
  • 28
  • I cannot test this, but assuming that it's working, this is the more elegant solution. – thomers Aug 29 '13 at 14:08
  • FYI: Literally, quotes around null in the query. I was trying to export from Hadoop to a Rails MySQL table. Since the Rails table had an auto-incrementing ID field, I tried adding: ..."null" as id,... as the first field in the Hive query. Worked great!! TY Jorge! – Ed Birm Mar 28 '14 at 21:03
0

As somebody on the Sqoop mailinglist suggested:

  • Create a temporary table without the ID
  • Sqoop-export into this table
  • Copy the rows of this table into the final table (that has the autoincrement ID)
thomers
  • 2,603
  • 4
  • 29
  • 50
0

My source table is in HIVE. What works for me is that I add a column called id int, and populate the column as NULL. After sqoop, the mysql will receive insert (id, X, Y) values (null, "x_value, "y_value"). Then mysql knows to populate the id as auto-increment.

user1309549
  • 1