在pk环境下部署了一套原生hadoop环境,hive版本1.2.1,开发在用代码对hive表做update操作时报错:
Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: Attempt to do update or delete using transaction manager that does not support these operations.
at org.apache.hadoop.hive.ql.parse.UpdateDeleteSemanticAnalyzer.analyzeInternal(UpdateDeleteSemanticAnalyzer.java:65)
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:424)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:308)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1122)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1116)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
Hive从0.14版本开始支持事务和行级更新,但缺省是不支持的,需要一些附加的配置。要想支持行级insert、update、delete,需要配置Hive支持事务。
修改hive-site.xml,增加如下配置参数:
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
</property>
停止hiveserver2,kill掉进程
重启hiveserver2
hiveserver2
或者nohup hive --service hiveserver2 &
能看到10000端口正在监听代表正常
Transaction有几个依赖条件:
1、只支持ORCFile
2、默认关闭,需要自己开启
3、表必须分桶
4、0.14开始支持insert、update和delete
5、必须加入一大堆配置(下文)
开始测试是否成功:
1、建立测试表
create table t1(id int, name string)
clustered by (id) into 8 buckets
stored as orc TBLPROPERTIES ('transactional'='true');
2、插入数据
insert into t1 values (1,'aaa');
insert into t1 values (2,'bbb');
2、测试update、delete
update t1 set name='ccc' where id=1;
delete from t1 where id=2;
update成功将id为1的name改为ccc
查看分桶情况:
说明:不能修改bucket列的值,否则会报以下错误:
FAILED: SemanticException [Error 10302]: Updating values of bucketing columns is not supported. Column id.
遇到的问题:
1、连接metastore失败
select * from t1;
FAILED: LockException [Error 10280]: Error communicating with the metastore
重启metastore
Error rolling back: Can't call rollback when autocommit=true
不要加这个配置!!!!
<property>
<name>hive.in.test</name>
<value>true</value>
</property>
create table t1_new like t1; //复制表结构
insert into t1_new select * from t1; //复制表数据
ALTER TABLE t1 RENAME TO t2; //重命名表