Tuesday, 18 November 2014

Update Statement In Hive For Large Tables

Hive Version used - hive-0.12.0

In Previous Blog  we have seen creating and loading data into partition table.
Now we will try to update one record using INSERT statement as hive doesnt support UPDATE command. In newer version of hive, UPDATE command will be added.

 We will see an example for updating Salary of employee id 19 to 50,000

INSERT INTO TABLE Unm_Parti PARTITION (Department = 'A') SELECT employeeid,firstname,designation, CASE WHEN employeeid=19 THEN 50000 ELSE salary END AS salary FROM Unm_Parti Where employeeid=19;
Using the above command your hive record get updated.

From hive-0.14 onwards UPDATE is available.
How to use CURD operations in hive-0.14.0


  1. Hi,

    Here you are updating partition table and in select also you are selecting same partition table.

    But can we do like below

    update hivetest1 set name = (select name from hivetest where hivetest.id = hivetest1.id) where id IN select hivetest1.id from hivetest1);

  2. Hi unmesha the command you typed is appending new column but not updating existing column in hive

  3. For latest and updated Cloudera certification dumps in PDF format contact us at completeexamcollection@gmail.com.
    Refer our blog for more details http://completeexamcollection.blogspot.in/2015/04/cloudera-hadoop-certification-dumps.html

  4. Hi Unmesha
    Very good informative article.
    Can you please help me implementing SCD type II in Hive.
    If possible can you provide the query.