How to overwrite MySQL table when using sqoop export from Hive

Question

I need to transfer data from Hive to MySQL.

Here is my sqoop command:

jdbc:mysql://mysqlserver --username username --password password --table test --columns "member_id,answer_id,answerer_id" -m 1 --export-dir /user/hive/warehouse/utils.db/test --input-fields-terminated-by \001 --lines-terminated-by \n --update-mode allowinsert 

But, every time I run this command, data seems to be appended to the table but not overwrite the table.

So, is there any way that I can truncate MySQL table automatically when I run this sqoop command?

Answer 1

I think what you are trying to do is, complete refresh of the table each time you upload the data. usually that is something that needs to be handled in the database end. You will need to delete all records before performing the insert. The other way is use --staging-table parameter along with --clear-staging-table which will make sure that the table is cleared each time. In this scenario you --table will contains a dummy table that will be appened each time. you can have a trigger to clear the data of that table at set period everyday or when pleases. I have given the sqoop command below. I have placed "test" as staging table and "dummy" as main table.

jdbc:mysql://mysqlserver --username username --password password --table dummy --columns "member_id,answer_id,answerer_id" -m 1 --export-dir /user/hive/warehouse/utils.db/test --input-fields-terminated-by \001 --lines-terminated-by \n --update-mode allowinsert --staging-table test --clear-staging-table