Update and Delete are not working in Hive ?

[#1]

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.

[#2]

@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"
);

[#3]

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.

[#4]

@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

[#5]

Kudu is  like HBase..

[#6]

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

[#7]

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.

[#8]

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

[#9]

@Suribharu

what file format are you using in hive ? 

what version of hive ? 

could you share me the delete query

[#10]

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.

[#11]

@UjjwalRana

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

Thanks!