question Re: Update and Delete are not working in Hive ? in Support Questions

Update and Delete are not working in Hive ?

syamsri — Fri, 16 Sep 2022 11:55:35 GMT

Hi all,

I am not able to delete or update in hive table .

create table testTableNew(id int ,name string ) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

insert into table testTableNew values('101','syri');

select * from testtablenew;

1	102	syam
2	101	syri
3	101	syri

delete from testTableNew where id = '101';

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

update testTableNew
set name = praveen
where id = 101;

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

I have added few properties in hive-site.xml also :

hive.support.concurrency
true

hive.enforce.bucketing
true

hive.exec.dynamic.partition.mode
nonstrict

hive.txn.manager
org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

hive.compactor.initiator.on
true

hive.compactor.worker.threads
2

hive.in.test
true

After restart the Hive service also same error i am facing.

Quick Start VM - 5.8 and Hive version - 1.1.0.

Please guide me to sort this issue.

Thanks,

Syam.

Re: Update and Delete are not working in Hive ?

saranvisa — Sun, 23 Jul 2017 20:32:47 GMT

@syamsri

Go to Yarn -> Configuration -> search for "yarn.nodemanager.resource.memory-mb". If it is 1 GB by default, increase it to 2 GB
Save it and restart YARN
(Sometime, may need to restart HUE as well, need to chk)

try again now

Re: Update and Delete are not working in Hive ?

syamsri — Tue, 25 Jul 2017 05:10:24 GMT

Thanks for the reply Saranvisa,

I already increased this configuration for some other reason,

But no work of update and delete operations.

Thanks,

Syam.

Re: Update and Delete are not working in Hive ?

saranvisa — Tue, 25 Jul 2017 13:22:00 GMT

@syamsri

how much you have increased? so i think in your case it is not sufficient, you have to increase more and try your update & delete operations

Re: Update and Delete are not working in Hive ?

syamsri — Tue, 25 Jul 2017 13:27:51 GMT

Re: Update and Delete are not working in Hive ?

UjjwalRana — Sun, 10 Sep 2017 08:25:56 GMT

Can you try uninstalling hive and re-install it again and then try the same command.

Re: Update and Delete are not working in Hive ?

syamsri — Mon, 11 Sep 2017 11:36:16 GMT

Hi Ujjwal,

Thanks for the reply.

This is quickstart Vm machine 5.8.

Uninstalling is not the solution i think.

Thanks,

Syam

Re: Update and Delete are not working in Hive ?

csguna — Mon, 11 Sep 2017 11:52:12 GMT

@syamsri

Ok Could you please let ,me know the file format that you are using for Hive ( testTableNew ) ,

Hive supports Delete Update only on ORC format starting from 0.14 .

Try creating a table with ORC format , if you want more flexibility then try Apache KUDU but it has it owns merits and demerits . Hope this helps .

CREATE TABLE Sample (
  id                int,
  name              string
)
CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC
TBLPROPERTIES ("transactional"="true",
  "compactor.mapreduce.map.memory.mb"="2048",    
  "compactorthreshold.hive.compactor.delta.num.threshold"="4",  
  "compactorthreshold.hive.compactor.delta.pct.threshold"="0.5"
);

Re: Update and Delete are not working in Hive ?

syamsri — Mon, 11 Sep 2017 12:11:09 GMT

Thanks for the reply.

I created the ORC format table only.

You can see the details in first post.

Apache Kudu is like hive ?

Thanks,

Syam.

Re: Update and Delete are not working in Hive ?

saranvisa — Mon, 11 Sep 2017 14:13:55 GMT

@syamsri

Apache Kudu is not like hive. It is like HDFS. The difference is HDFS stores data in row wise where as Kudo stores in column wise

Re: Update and Delete are not working in Hive ?

csguna — Tue, 12 Sep 2017 01:41:16 GMT

@syamsri Since you are using Cloudera manager - are you using safety valve to add those properties that needs to go in HS2 or did you manual edited the hive-site.xml ? because it looks like your default session configuration is what being used and its not picking it up those transcation properties .

Re: Update and Delete are not working in Hive ?

syamsri — Tue, 12 Sep 2017 04:47:45 GMT

Kudu is like HBase..

Re: Update and Delete are not working in Hive ?

syamsri — Tue, 12 Sep 2017 05:06:13 GMT

Please check the hive-site.xml file and Guide me.

<configuration>
<property>
<name>hive.metastore.uris</name>
<value>thrift://quickstart.cloudera:9083</value>
</property>
<property>
<name>hive.metastore.client.socket.timeout</name>
<value>300</value>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>hive.warehouse.subdir.inherit.perms</name>
<value>true</value>
</property>
<property>
<name>hive.auto.convert.join</name>
<value>true</value>
</property>
<property>
<name>hive.auto.convert.join.noconditionaltask.size</name>
<value>20971520</value>
</property>
<property>
<name>hive.optimize.bucketmapjoin.sortedmerge</name>
<value>false</value>
</property>
<property>
<name>hive.smbjoin.cache.rows</name>
<value>10000</value>
</property>
<property>
<name>hive.server2.logging.operation.enabled</name>
<value>true</value>
</property>
<property>

<property>
<name>hive.server2.logging.operation.log.location</name>
<value>/var/log/hive/operation_logs</value>
</property>
<property>
<name>mapred.reduce.tasks</name>
<value>-1</value>
</property>
<property>
<name>hive.exec.reducers.bytes.per.reducer</name>
<value>67108864</value>
</property>
<property>
<name>hive.exec.copyfile.maxsize</name>
<value>33554432</value>
</property>
<property>
<name>hive.exec.reducers.max</name>
<value>1099</value>
</property>
<property>
<name>hive.vectorized.groupby.checkinterval</name>
<value>4096</value>
</property>
<property>
<name>hive.vectorized.groupby.flush.percent</name>
<value>0.1</value>
</property>
<property>
<name>hive.compute.query.using.stats</name>
<value>false</value>
</property>
<property>
<name>hive.vectorized.execution.enabled</name>
<value>true</value>
</property>
<property>

<property>
<name>hive.vectorized.execution.reduce.enabled</name>
<value>false</value>
</property>
<property>
<name>hive.merge.mapfiles</name>
<value>true</value>
</property>
<property>
<name>hive.merge.mapredfiles</name>
<value>false</value>
</property>
<property>
<name>hive.cbo.enable</name>
<value>false</value>
</property>
<property>
<name>hive.fetch.task.conversion</name>
<value>minimal</value>
</property>
<property>
<name>hive.fetch.task.conversion.threshold</name>
<value>268435456</value>
</property>
<property>
<name>hive.limit.pushdown.memory.usage</name>
<value>0.1</value>
</property>
<property>
<name>hive.merge.sparkfiles</name>
<value>true</value>
</property>
<property>
<name>hive.merge.smallfiles.avgsize</name>
<value>16777216</value>
</property>
<property>
<name>hive.merge.size.per.task</name>
<value>268435456</value>
</property>

<property>
<name>hive.optimize.reducededuplication</name>
<value>true</value>
</property>
<property>
<name>hive.optimize.reducededuplication.min.reducer</name>
<value>4</value>
</property>
<property>
<name>hive.map.aggr</name>
<value>true</value>
</property>
<property>
<name>hive.map.aggr.hash.percentmemory</name>
<value>0.5</value>
</property>
<property>
<name>hive.optimize.sort.dynamic.partition</name>
<value>false</value>
</property>
<property>
<name>hive.execution.engine</name>
<value>mr</value>
</property>
<property>
<name>spark.executor.memory</name>
<value>52428800</value>
</property>
<property>
<name>spark.driver.memory</name>
<value>52428800</value>
</property>
<property>
<name>spark.executor.cores</name>
<value>1</value>
</property>
<property>

<property>
<name>spark.yarn.driver.memoryOverhead</name>
<value>64</value>
</property>
<property>
<name>spark.yarn.executor.memoryOverhead</name>
<value>64</value>
</property>
<property>
<name>spark.dynamicAllocation.enabled</name>
<value>true</value>
</property>
<property>
<name>spark.dynamicAllocation.initialExecutors</name>
<value>1</value>
</property>
<property>
<name>spark.dynamicAllocation.minExecutors</name>
<value>1</value>
</property>
<property>
<name>spark.dynamicAllocation.maxExecutors</name>
<value>2147483647</value>
</property>
<property>
<name>hive.metastore.execute.setugi</name>
<value>true</value>
</property>
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.zookeeper.quorum</name>
<value>quickstart.cloudera</value>
</property>
<property>

<property>
<name>hive.zookeeper.client.port</name>
<value>2181</value>
</property>
<property>
<name>hbase.zookeeper.quorum</name>
<value>quickstart.cloudera</value>
</property>
<property>
<name>hbase.zookeeper.property.clientPort</name>
<value>2181</value>
</property>
<property>
<name>hive.zookeeper.namespace</name>
<value>hive_zookeeper_namespace_hive</value>
</property>
<property>
<name>hive.cluster.delegation.token.store.class</name>
<value>org.apache.hadoop.hive.thrift.MemoryTokenStore</value>
</property>
<property>
<name>hive.server2.enable.doAs</name>
<value>true</value>
</property>
<property>
<name>hive.server2.use.SSL</name>
<value>false</value>
</property>
<property>
<name>spark.shuffle.service.enabled</name>
<value>true</value>
</property>
</configuration>

Thanks,

Syam.

Re: Update and Delete are not working in Hive ?

Suribharu — Fri, 29 Sep 2017 13:20:41 GMT

Did you get any solution to the above issue.I got the similar issue while update/delete.

Re: Update and Delete are not working in Hive ?

csguna — Sat, 30 Sep 2017 04:58:27 GMT

@Suribharu

what file format are you using in hive ?

what version of hive ?

could you share me the delete query

Re: Update and Delete are not working in Hive ?

UjjwalRana — Wed, 04 Oct 2017 04:58:38 GMT

Sorry for the late response. Nope i could not find the exact solution for those error. However i did followed all the steps mentioned on this post but that did not work. As a result i uninstalled hive and re-installed some other hive version which works for me. I spend many days for this issue to find the exact solution but could not find it out.

Re: Update and Delete are not working in Hive ?

AlexMulti — Thu, 30 Nov 2017 11:59:34 GMT

@UjjwalRana

What version of Hive did you install? Did you manage to solve the issue?

Thanks!

Re: Update and Delete are not working in Hive ?

syamsri — Fri, 01 Dec 2017 05:44:23 GMT

Hive version : Hive 1.1.0-cdh5.13.0,

Still i didnt get the solution...

Re: Update and Delete are not working in Hive ?

csguna — Sun, 03 Dec 2017 06:05:41 GMT

Hi @AlexMulti @syamsri @UjjwalRana @Suribharu

are you still looking for solution ?

what version of hive are you using ?

what file format does your table has ?

what client tool are using hiveshell or beeline ?

did you perform the pre-requsites before performing the hive delete operation?

Re: Update and Delete are not working in Hive ?

AlexMulti — Wed, 06 Dec 2017 14:03:48 GMT

Hi @csguna,

I haven't found any solutions to the problem (tried Hive CLI and HUE query editor). I will try to do a workaround...

What do you mean by "prerequisites"?

Version:

CDH 5.12.1

Hive 1.1

Thanks!