How to insert and Update simultaneously to PostgreSQL with sqoop command

Question

I am trying to insert into postgreSQL DB with sqoop command.

sqoop export --connect jdbc:postgresql://10.11.12.13:1234/db --table table1 --username user1 --password pass1--export-dir /hivetables/table/   --fields-terminated-by '|' --lines-terminated-by '\n'  -- --schema schema

It is working fine if there is not primary key constrain. I want to insert new records and update old records simultaneously.

I have tried

• --update-key primary_key This updates only those primary keys present in both DB(hive and postgreSQL. No insertion)

• --update-mode allowinsert - This only does the insert

• --update-key primary_key --update-mode allowinsert - This gives error

ERROR tool.ExportTool: Error during export: Mixed update/insert is not supported against the target database yet

Can anyone help me to write sqoop command which insert and update the data to postgreSQL ?

Answer 1

According to my internet search, it is not possible to perform both insert and update directly to postgreSQL DB. Instead you can create a storedProc/function in postgreSQL and you can send data there..

sqoop export --connect <url> --call <upsert proc> --export-dir /results/bar_data

Stored proc/function should perform both Update and Insert.

Link 1 - https://issues.apache.org/jira/browse/SQOOP-1270

Link 2 - PLPGSQL-UPSERT-EXAMPLE