How to delete and update a record in Hive

Question

I have installed Hadoop, Hive, Hive JDBC. 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.

Thanks

hive> delete from student where id=1; Usage: delete [FILE|JAR|ARCHIVE] <value> [<value>]* Query returned non-zero code: 1, cause: null 

Answer 1

As of Hive version 0.14.0: INSERT...VALUES, UPDATE, and DELETE are now available with full ACID support.

INSERT ... VALUES Syntax:

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...] 

Where values_row is: ( value [, value ...] ) where a value is either null or any valid SQL literal

UPDATE Syntax:

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

DELETE Syntax:

DELETE FROM tablename [WHERE expression] 

Additionally, from the Hive Transactions doc:

If a table is to be used in ACID writes (insert, update, delete) then the table property "transactional" must be set on that table, starting with Hive 0.14.0. Without this value, inserts will be done in the old style; updates and deletes will be prohibited.

Hive DML reference:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
Hive Transactions reference:
https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions