4

I'm a newbie with hadoop & hive. I want to delete certain rows in my database - which is on hive-hadoop. I know its not supported out of the box, and that hadoop is a read only file system. I'm curious about what are the best approaches for accomplishing this. If anyone has done this before, can they share their learnings/procedures?

Thanks!

Sunny
  • 369
  • 2
  • 3
  • 11
  • possible duplicate of [How to delete and update a record in Hive](http://stackoverflow.com/questions/17810537/how-to-delete-and-update-a-record-in-hive) – Neels Mar 25 '14 at 06:43
  • You should probably check out this http://stackoverflow.com/questions/17810537/how-to-delete-and-update-a-record-in-hive – Neels Mar 25 '14 at 06:43

4 Answers4

13

In Big Data there really aren't deletes. That said, you can overwrite your table or partition if it isn't too big, or isolate your deletes to a particular partition like JamCon suggests.

For datasets which are not too huge, you can do something like 

INSERT OVERWRITE TABLE mytable
SELECT * FROM mytable
WHERE ID NOT IN ( 'delete1', 'delete2', 'delete3');
Jerome Banks
  • 1,620
  • 11
  • 15
4
INSERT OVERWRITE TABLE mytable  SELECT * FROM mytable WHERE 1=0;

The above query first deletes the data files, as OVERWRITE option is specified in the query. Next it will execute SELECT to read the records from the table, as condition fails, SELECT query will get zero records and insert zero records into the table.

Dimitri W
  • 110
  • 1
  • 3
  • 6
user6216847
  • 41
  • 1
3

The best approach is to partition your data such that the rows you want to drop are in a partition unto themselves. You can then drop the partition without impacting the rest of your table. This is a fairly sustainable model, even if your dataset grows quite large.

JamCon
  • 2,313
  • 2
  • 25
  • 34
1

Or you could insert the rest of of rows (i.e., those you want to keep) into another table and then drop the old table.

Henaras
  • 21
  • 4