Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to update hive table

Highlighted

How to update hive table

New Contributor

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

12 REPLIES 12
Highlighted

Re: How to update hive table

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

Highlighted

Re: How to update hive table

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?

Highlighted

Re: How to update hive table

Contributor

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

Highlighted

Re: How to update hive table

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.

Highlighted

Re: How to update hive table

Expert Contributor

@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

Highlighted

Re: How to update hive table

New Contributor

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

Highlighted

Re: How to update hive table

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.

Highlighted

Re: How to update hive table

Expert Contributor

Use below syntax try it.

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

Re: How to update hive table

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

Don't have an account?
Coming from Hortonworks? Activate your account here