How to delete and update a record in Hive

Question

I have installed Hadoop, Hive, Hive JD BC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in Hive.

Answer 1

Earlier, there was no operation supported for the deletion and updation of a particular record in Hive. But since updation of Hive 0.14, these operations are possible to make changes in a Hive table.

Insertion of a single value, Deletion, Updation all are now possible, in the new version of Hive that comes with full ACID support.

Standard Syntax:

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

 

Standard Syntax:

DELETE FROM tablename [WHERE expression]

 

Important: Whenever Insertion, Deletion, and Updation operations are to be performed on Hive(with ACID support) table, starting with 0.14.0 and above, then the table property "transactional" must be set on the selected table, starting with Hive 0.14.0.

Reference: https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

You can also check out this video which will teach you HIve from basics: