Support Questions

Find answers, ask questions, and share your expertise
Check out our newest addition to the community, the Cloudera Data Analytics (CDA) group hub.

How to update hive table

New Contributor

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


New Contributor


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

Expert Contributor

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


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

Expert Contributor

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.

Expert Contributor

@Prakash M

You can use this :

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

Please refer these as well :

New Contributor

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

Expert Contributor

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

Expert Contributor

Use below syntax try it.

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

Expert Contributor

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

@Prakash M

All good advices above. Could you confirm that ACID is turned on globally? Also Tez.

Expert Contributor

Where can I check property is Turned on and How?

New Contributor

Any updates as of now on update ? I have a ORC table and looking for updating few records

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.