0

I am trying to migrate one on SQL from Oracle to Hive. To make to work on hive , I have to break Merge statement into UPDATE/DELETE and INSERT.

Please help me on this. I have my version of splitting , but i am not sure on it.

Note: I am not using Hive MERGE for other reason.

Merge Statement:

MERGE INTO bonuses D
 USING (SELECT employee_id, salary, department_id FROM employees
 WHERE department_id = 80) S
 ON (D.employee_id = S.employee_id)
 WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01  WHERE (S.salary > 8000)
   DELETE WHERE (S.salary >= 10000)
 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
   VALUES (S.employee_id, S.salary*.01)
   WHERE (S.salary <= 8000);

Table DDLs:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
CREATE TABLE employees ( employee_id NUMBER, salary NUMBER, department_id int);
Sanjiv
  • 1,795
  • 1
  • 29
  • 45
  • StackOverflow is not a code writing service. Post what you came up with an perhaps people will comment on it. Your question might be better asked on https://codereview.stackexchange.com/. Thanks. – Bob Jarvis - Слава Україні Aug 02 '17 at 19:08
  • is this statement your own case? – mehmet sahin Aug 02 '17 at 19:30
  • Unfortunately , I can't share original Merge statement .so i have created similar one. – Sanjiv Aug 02 '17 at 19:39
  • 1
    Use `insert overwrite table with full join`, see example here: https://stackoverflow.com/a/37744071/2700344 Write your case statements and filters. – leftjoin Aug 02 '17 at 20:07
  • As quoted in Question, I want simply breaking up MERGE into individual UPDATE/DELETE/INSERT. if don't want to use HIVE ACID , then individual UPDATE/DELETE can be converted in insert override. Hope it clarifies the question. – Sanjiv Aug 02 '17 at 20:14

0 Answers0