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
>
>