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

7 comments:

  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);

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

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

    ReplyDelete
  4. Simeon Panda :- Simeon Panda is a true role model for anyone who wishes to achieve success in the field of bodybuilding.

    Faye Chrisley :- Faye Chrisley is an American reality TV star. She is well-known for playing Nanny in the American TV series Chrisley Knows Best.

    Christi Pirro :- Christi Pirro is a lawyer and a law clerk. She is well-known as Jeanine Pirro’s daughter. Jeannie, her mother, is a TV broadcaster and writer.

    Pokimane :- Pokimane is a famous Canadian twitch streamer and YouTuber. However, she is famous for her streaming on games. So, she mostly played two games

    ReplyDelete
  5. You finished certain solid focuses there. I did a pursuit regarding the matter and discovered almost all people will concur with your blog.

    ReplyDelete
  6. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete