Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

updating and inserting new data to mysql using sqoop

avatar
Expert Contributor

Hello everyone,

I'll try to be very pointed with my question for easy understanding. So, guys, the thing is that I'm preparing my exam for HDPCD so I'm playing a lot with sqoop. In this case, I have a sample txt file to load data in Mysql. I have done the first load and then I would like to try the properties --update-key and --update-mode allowinsert to join and update my table.

I performed a first load data to mysql table and that looks like:

15849-hdfs.png

so after the first load, I have added new rows and change one row for my column called season in id <10> to <winter> so my file looks now like

15848-hdfs1.png

I executed:

sqoop export --connect jdbc:mysql://master/poc --username root --table cours --export-dir /user/hdfs/mysql/export.txt --update-key id --update-mode allowinsert --fields-terminated-by '\t' -m 1

My new table output looks like:

15850-hdfs2.png

That's correct cause the new courses were included and the season change on row id 10 also was updated but instead of update the same row sqoop has added a new column so i think that it should better just really update and not duplicate my id cause what if it's a primary key so guys. do you know how perform the update without duplicate non necessary data?

1 ACCEPTED SOLUTION

avatar
Super Collaborator

I suspect that your id column is not specified as the primary key on the table. Try making id the primary key, and see if you get different results.

View solution in original post

4 REPLIES 4

avatar
Super Collaborator

I suspect that your id column is not specified as the primary key on the table. Try making id the primary key, and see if you get different results.

avatar
Expert Contributor

You right buddy , actually I have tested creating the primary key in the same table and is just stored one record so this is what happen when we dont count with the primary key. thanks buddy

avatar
New Contributor

I am trying to export data from HDFS to Mysql using Sqoop 1.

My job is working fine and pushs data in mysql table. But when i try to update data by key and insert new rows in mysql table using sqoop commands: --update-key user --update-mode allowinsert i am getting this error :

ERROR tool.ExportTool: Error during export:

Mixed update/insert is not supported against the target database yet

After some internet searchs, i find that mysql-connector.jar does not support upsert (the connector that i used is mysql-connector-java-5.1.46-bin.jar).

Is there another version that supports upsert?

Thanks in advance.

avatar
New Contributor

Did you find a solution to this?