2018年第31周-hive支持的Delete和Update的配置

配置hive-site.xml

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 = 1

以上是方便此文章布局，所以就没用xml格式。配置完后需重启hive的HiveMetaStore

建表要求

支持delete和update的表的配置必须是如下：

CREATE TABLE `jdb.t_user`(
  id BIGINT ,
  username STRING COMMENT '用户名',
  phone STRING,
  create_time TIMESTAMP COMMENT '创建时间') clustered by (id) into 2 buckets STORED AS ORC TBLPROPERTIES('transactional'='true');

必须是ORC格式、分桶和指定TBLPROPERTIES('transactional'='true')。

测试语句

create database jdb;

insert into jdb.t_user values(1,'jevoncode','18888888888','2018-06-11 13:34'),(2,'jevoncode','18888888888','2018-06-11 13:34'),(3,'jevoncode','18888888888','2018-06-11 13:34'),(4,'jevoncode','18888888888','2018-06-11 13:34'),(5,'jevoncode','18888888888','2018-06-11 13:34'),(6,'jevoncode','18888888888','2018-06-11 13:34');

delete from jdb.t_user where id=1;

TroubleShooting

1.在hive执行语句报以下错：

FAILED: LockException [Error 10280]: Error communicating with the metastore  

HIVE_IN_TEST或者HIVE_IN_TEZ_TEST.只有在测试环境中才能用delete，update操作，也可以理解，毕竟还没有开发完全。所以需要在hive-site.xml添加配置：

<property>
<name>hive.in.test</name>
<value>true</value>
</property>

jdbc配置

使用hiveserver2提供jdbc功能

可以与mybatis结合起来用，不过报以下错误:

2018-06-11 15:30:31.527 [http-nio-8762-exec-2] INFO  com.zaxxer.hikari.pool.PoolBase(516)  - HikariPool-1 - Driver does not support get/set network timeout for connections. (Method not supported)
2018-06-11 15:30:31.527 [http-nio-8762-exec-2] DEBUG com.zaxxer.hikari.pool.PoolBase(127)  - HikariPool-1 - Closing connection org.apache.hive.jdbc.HiveConnection@2549598: (Failed to create/setup connection)
2018-06-11 15:30:31.531 [http-nio-8762-exec-2] DEBUG org.apache.thrift.transport.TSaslTransport(498)  - writing data length: 83
2018-06-11 15:30:31.609 [http-nio-8762-exec-2] DEBUG org.apache.thrift.transport.TSaslTransport(459)  - CLIENT: reading data length: 40
2018-06-11 15:30:31.612 [http-nio-8762-exec-2] DEBUG com.zaxxer.hikari.pool.HikariPool(480)  - HikariPool-1 - Cannot acquire connection from data source
java.sql.SQLException: Method not supported
    at org.apache.hive.jdbc.HiveConnection.setReadOnly(HiveConnection.java:1262)
    at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:409)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:370)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:194)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:460)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:534)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)

升级hive-jdbc版本到2.0.0版本以上即可