6

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Fizzy Chan
  • 141
  • 2
  • 5
  • 4
    I believe , there is no direct way to do this . But you can still achieve this as below. sqoop eval --connect 'connection string' --quey 'TRUNCATE TABLE TABLE_NAME' sqoop export --connect 'connection string' --export-dir 'HDFS_PATH' --table TABLE_NAME – Arunakiran Nulu Oct 11 '16 at 14:59

2 Answers2

4

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
BalaramRaju
  • 439
  • 2
  • 8
0

Use below command to existing records and insert new records if any .

sqoop import --connect 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-key --update-mode allowinsert

Note that above command will not apply deletes .

If you really want to truncate the data and load whole data again use below command. it is useful only when source(hdfs) has deleted records.

  1. sqoop eval --connect jdbc:mysql://mysqlserver --username username --password password --query 'TRUNCATE TABLE TABLE_NAME'
  2. sqoop export --connect jdbc:mysql://mysqlserver --username username --password password --export-dir 'HDFS_PATH' --table TABLE_NAME
Lavanya varma
  • 75
  • 1
  • 9