How to update hive table

[#1]

How can i update a field in hive 1.2 , hadoop 2.3.2?

[#2]

Step:1

create table test_90855 ( uid string, user_name string, start_date date, active boolean) CLUSTERED BY (start_date) into 100 buckets STORED AS ORC TBLPROPERTIES ('transactional'='true');

Step 2:

set hive.auto.convert.join.noconditionaltask.size=10000000; set hive.support.concurrency=true; set hive.enforce.bucketing=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on=true; set hive.compactor.worker.threads=1; set hive.enforce.bucketing=true;

Step 3:

update test_90855 set user_name='xxx';

i have tried above steps and its not working. Help me to fix this issue

[#3]

@Prakash M Are you seeing any errors or is it that column is not getting updated? Whats the exact issue you are facing?

[#4]

@Prakash M Configurations looks good. Can you try setting the properties, create the table and try to insert/update?

[#5]

If you are doing "set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager" at CLI it won't always work. You have to set this option in hive-site.xml (and restart) to make sure (it's a bug that's been fixed in HIVE-11716). You also have to make sure that "set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on=true; set hive.compactor.worker.threads=1;" are set for your standalone metastore process process which is required for ACID.

[#6]

@Prakash M

You can use this :

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

Please refer these as well :

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Update

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

[#7]

FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

[#8]

@Prakash M

Your configs look good.Did you restart hive service after modifying these configs?

Please perform these two quick checks :

1) From ambari, go to hive service and check that the config changes you made are reflecting correctly.

2) Restart hive service. (You might need to restart oozie if cluster has this installed)

After this try to run the update command.

[#9]

Use below syntax try it.

UPDATE employee SET name = null WHERE id <= 10; 

[#10]

In Hadoop, the construct of a update is to a huge MapReduce and then find the record(s), that need to be updated and do an insert and delete. As you can see from MapReduce perspective its an expensive operation with levels of mapReduce. With ACID turned on,

All of the above answers are correct. But you should design your Data Structures to be append only with date and time stamp and or a version reference for the latest state of your records.

Even though ACID supports Updates, i would say in order to manage performance, i would recommend to insert instead of update ( More like an Upsert function).