CODEWITHSUNDEEP
hive
Mohamed Niyaz
Mohamed Niyaz

Reputation: 218

How to do update/delete operations on non-transactional table

I am a beginner to hive and got to know that update/delete operations are not supported on non-transactional tables. I didn't get a clear picture of why those operations are not supported? Also, wanted to know if there exists a way to update the non-transactional table.

Upvotes: 3

Views: 5717

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

why those operations are not supported?

Transaction tables are special managed tables in hive which should be in orc format, bucketed(hive 2.0), dbtransacmanager enabled and concurrency enabled. These properties ensures we can do ACID operations. And every DML(DEL/UPD/MERGE/INS) creates delta file which tracks the changes. So, hive does a lot of internal operations to maintain the table and data.

wanted to know if there exists a way to update the non-transactional table.

Of course there is.
The idea is to truncate and insert whole data after changing the data. You can update a field using below code. Assume you have emp table and you want to update emp salary to 5000 for id =10. For simplicity's sake, emp table has 3 columns only - empid, empname, empsal.

Insert overwrite emp -- truncate and load emp table
Select empid, empname, empsal from emp where id <> 10 -- non changed data
union all
Select empid, empname, 5000 empsal from emp where id = 10 -- union to changed data

You can use similar sql to delete employee id 10.

Insert overwrite emp -- truncate and load emp table
Select empid, empname, empsal from emp where id <> 10 -- insert when emp id not equal to 10 which means it gets deleted from emp

You can do merge too. You can ins/upd/del data from other sources too.

Upvotes: 4

Related Questions