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

There is no operation supported for deletion or update of a particular record or particular set of records, and to me, this is more a sign of a poor schema.

A way to work around this limitation is to use partitions: I don't know what you id corresponds to, but if you're getting different batches of ids separately, you could redesign your table so that it is partitioned by id, and then you would be able to easily drop partitions for the ids you want to get rid of.

Answer 2

Hi,

Earlier, there was no operation supported for the deletion and updatE of a particular record in Hive. But since updating of Hive 0.14, these operations are possible to make changes in a Hive table. You can use the below command.

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