Deletion of records from hive table based on some condition

[#1]

Hi,

We have a requirement to delete records from hive table based on some condition.

and we need to make sure that in future if the data will come with same ID it should not be inserted.

For deletion one approach is to load the required data in another table delete the original one and rename the new one.

But how will we ensure that in future if the data will come with same ID it should not be inserted.

Please suggest some new and optimized approach for deletions if any.

Thanks.

[#2]

Better to maintain all the deleted id's in one staging table and while loading the data into hive tables check whether id is already existed in staging table either by using Join or Merge clause

[#3]

if you are using 2.6 version or later, you can turn on ACID and execute delete commands. you can audit your delete via your app if it is needed. Otherwise, delete, merge and update can run on Hive directly with ACID.

[#4]

I refered this link.

https://www.hortonworks.com/tutorial/using-hive-acid-transactions-to-insert-update-and-delete-data/