在Hive上执行事务操作(增、删、改、查)出现FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.错误
已被周知的是Hive从0.14版开始支持事务操作,而CDH5.4.10总的Hive是1.1.0版本,而且该配置的也都配置了,如
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.compactor.worker.threads = 1 ; update abc1 SET empwork_name = "Raj" where empwork_key = 70;"
还有把表建成orc格式,增加bucket
create table testTableNew(id int ,name string ) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
但问题依旧没有解决
后来搜了下Cloudera的官方论坛,有个方案是在hive运行时配置事务操作,例如
hive -e "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;
解决问题,原因是Hive事务管理器( transaction manager )不支持设置为每条Query级别的,而是应该是Session级别的