updating and inserting new data to mysql using sqoop

[#1]

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:

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

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:

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?

[#2 · Accepted solution]

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.

[#3]

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

[#4]

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.

[#5]

Did you find a solution to this?