Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

updating and inserting new data to mysql using sqoop

Solved Go to solution

updating and inserting new data to mysql using sqoop

Rising Star

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

Accepted Solutions

Re: updating and inserting new data to mysql using sqoop

Expert Contributor

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.

4 REPLIES 4

Re: updating and inserting new data to mysql using sqoop

Expert Contributor

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.

Re: updating and inserting new data to mysql using sqoop

Rising Star

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

Re: updating and inserting new data to mysql using sqoop

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.

Re: updating and inserting new data to mysql using sqoop

New Contributor

Did you find a solution to this?

Don't have an account?
Coming from Hortonworks? Activate your account here