Created on 07-13-2017 11:14 AM - edited 09-16-2022 04:55 AM
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';
update testTableNew
set name = praveen
where id = 101;
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.
Created 07-23-2017 01:32 PM
try again now
Created 07-24-2017 10:10 PM
Thanks for the reply Saranvisa,
I already increased this configuration for some other reason,
But no work of update and delete operations.
Thanks,
Syam.
Created 07-25-2017 06:22 AM
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
Created 07-25-2017 06:27 AM
Created on 09-10-2017 01:22 AM - edited 09-10-2017 01:25 AM
Can you try uninstalling hive and re-install it again and then try the same command.
Created 09-11-2017 04:36 AM
Hi Ujjwal,
Thanks for the reply.
This is quickstart Vm machine 5.8.
Uninstalling is not the solution i think.
Thanks,
Syam
Created 12-10-2017 10:51 PM
Oh ok syam i got. I did the installation part manually.
Created 01-09-2018 09:29 AM
The CDH distribution of Hive does not support transactions (HIVE-5317). Currently, transaction support in Hive is an experimental feature that only works with the ORC file format. Cloudera recommends using the Parquet file format, which works across many tools. Merge updates in Hive tables using existing functionality, including statements such as INSERT, INSERT OVERWRITE, and CREATE TABLE AS SELECT.
If you require these features, please inquire about Apache Kudu.
Kudu is storage for fast analytics on fast data—providing a combination of fast inserts and updates alongside efficient columnar scans to enable multiple real-time analytic workloads across a single storage layer.
https://www.cloudera.com/products/open-source/apache-hadoop/apache-kudu.html
Created 09-11-2018 06:51 PM
Please check the link https://hortonworks.com/blog/update-hive-tables-easy-way/ hope this helps.
Created on 09-11-2017 04:49 AM - edited 09-11-2017 04:52 AM
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" );
Created 09-11-2017 05:11 AM
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.
Created 09-11-2017 07:13 AM
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
Created 09-11-2017 09:47 PM
Kudu is like HBase..
Created 09-11-2017 06:41 PM
@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 .
Created 09-11-2017 10:06 PM
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.
Created 09-29-2017 06:20 AM
Created 09-29-2017 09:58 PM
what file format are you using in hive ?
what version of hive ?
could you share me the delete query
Created 10-03-2017 09:58 PM
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.
Created 11-30-2017 03:59 AM
Created 11-30-2017 09:44 PM
Hive version : Hive 1.1.0-cdh5.13.0,
Still i didnt get the solution...