Support Questions

Find answers, ask questions, and share your expertise

How to update hive table

avatar
New Contributor

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

12 REPLIES 12

avatar
New Contributor

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

avatar
Super Collaborator

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

avatar
Rising Star

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

avatar
Super Collaborator

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.

avatar
Super Collaborator

@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

avatar
New Contributor

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

avatar
Super Collaborator

@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.

avatar
Expert Contributor

Use below syntax try it.

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

avatar
Super Collaborator

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