How to delete and update a record in Hive

by

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

More Related Contents:

  1. What is the difference between partitioning and bucketing a table in Hive ?
  2. Difference between Hive internal tables and external tables?
  3. How to transpose/pivot data in hive?
  4. Sqoop import : composite primary key and textual primary key
  5. Hive unable to manually set number of reducers
  6. How to update table in Hive 0.13?
  7. how many mappers and reduces will get created for a partitoned table in hive
  8. Hive: Add partitions for existing folder structure
  9. How to load data to hive from HDFS without removing the source file?
  10. Loading Data from a .txt file to Table Stored as ORC in Hive
  11. Create HIVE Table with multi character delimiter
  12. Job queue for Hive action in oozie
  13. Difference between Pig and Hive? Why have both? [closed]
  14. What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask
  15. Can Hive recursively descend into subdirectories without partitions or editing hive-site.xml?
  16. Hive Data Retrieval Queries: Difference between CLUSTER BY, ORDER BY, and SORT BY
  17. How does Hadoop process records split across block boundaries?
  18. merge output files after reduce phase
  19. Setting the number of map tasks and reduce tasks
  20. Name node is in safe mode. Not able to leave
  21. How to fix corrupt HDFS FIles
  22. Is it better to use the mapred or the mapreduce package to create a Hadoop Job?
  23. Hadoop speculative task execution
  24. How to Access Hive via Python?
  25. Easiest way to install Python dependencies on Spark executor nodes?
  26. Spark iterate HDFS directory
  27. Hadoop: …be replicated to 0 nodes instead of minReplication (=1). There are 1 datanode(s) running and no node(s) are excluded in this operation
  28. Pig Latin: Load multiple files from a date range (part of the directory structure)
  29. Cannot Read a file from HDFS using Spark
  30. Hadoop WordCount example stuck at map 100% reduce 0%

Leave a Comment