You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by Thomas Schweitzer <th...@gmail.com> on 2012/04/11 15:51:33 UTC

How to use autoincrement-IDs in Sqoop export

Hi,

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

Any suggestions? Thanks,

Thomas



PS: I also put this on
http://stackoverflow.com/questions/10104260/how-to-use-autoincrement-ids-in-sqoop-export

Re: How to use autoincrement-IDs in Sqoop export

Posted by Thomas Schweitzer <th...@gmail.com>.
Cheolsoo,

thanks for that - depending on the size of the import, I guess that's
feasable.

Anyway, in the meantime I figured I can use 2 columns as a composite key
and do not absolutely need a numerical ID.

Best,
Thomas



On Thu, Apr 12, 2012 at 12:22 AM, Cheolsoo Park <ch...@cloudera.com>wrote:

> Hi Thomas,
>
> I am not sure if this is a feasible option for you, but can't you do it in
> two steps?
>
> 1) export data into a staging table that has no auto incremental id column.
> 2) copy the staging table into the target table that has the auto
> incremental id column.
>
> In my quick experiment, it can be done with a single command:
>
> mysql> select * from table1;
> +-------+------+
> | name  | job  |
> +-------+------+
> | name1 | job1 |
> | name2 | job2 |
> | name3 | job3 |
> +-------+------+
> 3 rows in set (0.00 sec)
>
> mysql> INSERT INTO table2 (name, job) SELECT * FROM table1;
> Query OK, 3 rows affected (0.00 sec)
> Records: 3  Duplicates: 0  Warnings: 0
>
> mysql> select * from table2;
> +----+-------+------+
> | id | name  | job  |
> +----+-------+------+
> |  1 | name1 | job1 |
> |  2 | name2 | job2 |
> |  3 | name3 | job3 |
> +----+-------+------+
>
>
> Thanks,
> Cheolsoo
>
>
> On Wed, Apr 11, 2012 at 6:51 AM, Thomas Schweitzer <th...@gmail.com>wrote:
>
>> Hi,
>>
>> 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
>>
>> Any suggestions? Thanks,
>>
>> Thomas
>>
>>
>>
>> PS: I also put this on
>> http://stackoverflow.com/questions/10104260/how-to-use-autoincrement-ids-in-sqoop-export
>>
>>
>
>

Re: How to use autoincrement-IDs in Sqoop export

Posted by Cheolsoo Park <ch...@cloudera.com>.
Hi Thomas,

I am not sure if this is a feasible option for you, but can't you do it in
two steps?

1) export data into a staging table that has no auto incremental id column.
2) copy the staging table into the target table that has the auto
incremental id column.

In my quick experiment, it can be done with a single command:

mysql> select * from table1;
+-------+------+
| name  | job  |
+-------+------+
| name1 | job1 |
| name2 | job2 |
| name3 | job3 |
+-------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO table2 (name, job) SELECT * FROM table1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from table2;
+----+-------+------+
| id | name  | job  |
+----+-------+------+
|  1 | name1 | job1 |
|  2 | name2 | job2 |
|  3 | name3 | job3 |
+----+-------+------+


Thanks,
Cheolsoo

On Wed, Apr 11, 2012 at 6:51 AM, Thomas Schweitzer <th...@gmail.com>wrote:

> Hi,
>
> 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
>
> Any suggestions? Thanks,
>
> Thomas
>
>
>
> PS: I also put this on
> http://stackoverflow.com/questions/10104260/how-to-use-autoincrement-ids-in-sqoop-export
>
>