16

I know there is no update of file in Hadoop but in Hive it is possible with syntactic sugar to merge the new values with the old data in the table and then to rewrite the table with the merged output but if I have the new values in another table then I can achieve the same effect by using a left outer join.

The problem in my case is that I have to update the table by setting one value to a column with some where condition. It is known that SET is not supported.

For example, consider the below normal query:

UPDATE temp1
SET location=florida
WHERE id=206;

I tried to convert the same in Hive but I got stuck in the place of SET. If anyone let me know then it would be a great help for me.

Don Cruickshank
  • 5,641
  • 6
  • 48
  • 48
Jothi
  • 183
  • 1
  • 2
  • 6
  • UPDATE command is not supported in older version of hive. To achieve the same you can follow these steps.unmeshasreeveni.blogspot.in/2014/11/… OR use can upgrade your hive to hive-0.14.0 and do the following steps http://unmeshasreeveni.blogspot.in/2014/11/updatedeleteinsert-in-hive-0140.html – USB Nov 25 '14 at 09:13

2 Answers2

11
INSERT OVERWRITE TABLE _tableName_ PARTITION (_partitionColumn_= _partitionValue_) 
SELECT [other Things], CASE WHEN id=206 THEN 'florida' ELSE location END AS location, [other Other Things] 
FROM _tableName_ WHERE [_whereClause_];

You can have multiple partitions listed by separating them by commas. ... PARTITION (_partitionColumn_= _partitionValue1_, _partitionColumn_= _partitionValue2_, ...). I haven't done this with multiple partitions, just one at a time, so I'd check the results on a test/dev env before doing all partitions at once. I had other reasons for limiting each OVERWRITE to a single partition as well.

This page https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML has a little more on it.
This site https://cwiki.apache.org/confluence/display/Hive/LanguageManual, in general, is your best friend when working with HiveSQL.

I've developed something identical to this to migrate some data and it's worked. I haven't tried it against large datasets, only a few GB and it has worked perfectly.

To Note - This will OVERWRITE the partition. It will make previous files go bye-bye. Create backup and restore scripts/procedures. The [other Things] and [other Other Things] are the rest of the columns from the table. They need to be in the correct order. This is very important or else your data will be corrupted.

Hope this helps. :)

QuinnG
  • 6,346
  • 2
  • 39
  • 47
  • Hi Nija, It works but i didn't used the partition.Just with the help of CASE itself UPDATE can be achieved.Thanks once again! – Jothi Jun 03 '11 at 06:11
  • @Jothi: Yea - I didn't know if you had partitions or not, I use them, so I included them. Glad it helped. Don't forget to upvote and mark as answered answers that help. – QuinnG Jun 03 '11 at 06:19
  • Hi,when i referred to the link got that partition column has a corresponding input column from the select statement. so from your query is that Location a partition column.Need some more clarification on partitionColumn_= _partitionValue.Thanks! – Jothi Jun 03 '11 at 06:39
  • Hi Nija,Now i got it like if it is a partitioned table then the above query can be used.Thanks! – Jothi Jun 03 '11 at 06:46
  • @Jothi Could you please post the query you used finally to make this work? – user1452759 Mar 05 '15 at 07:36
1

This may be hacky but it's worked for somethings I've had to do at work.

    INSERT OVERWRITE TABLE tabletop PARTITION(partname = 'valueIwantToSet')
    SELECT things FROM databases.tables WHERE whereclause;

As you might expect this breaks your data up into partitions, but if the distribution of the value you want to set is proportional to "good data chunk sizes" (this is up to you to design) then your queries on that data will be better optimized

@Jothi: Could you please post the query you used?

Engineiro
  • 1,146
  • 7
  • 10