当前位置:   article > 正文

Hive 行级更新操作 -- 实现update和delete_hive3.0 支持行级更新

hive3.0 支持行级更新

在hive执行delete和update操作时,报错
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
操作代码:

hive> select * from tablename where id = 10000000;
Query ID = root_20190109131616_5a231979-1726-4bb3-9fb8-839dd8d082d4
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1545619490751_0354, Tracking URL = http://node00:8088/proxy/application_1545619490751_0354/
Kill Command = /opt/cloudera/parcels/CDH-5.11.0-1.cdh5.11.0.p0.34/lib/hadoop/bin/hadoop job  -kill job_1545619490751_0354
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-01-09 13:16:31,446 Stage-1 map = 0%,  reduce = 0%
2019-01-09 13:16:37,624 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.23 sec
MapReduce Total cumulative CPU time: 3 seconds 230 msec
Ended Job = job_1545619490751_0354
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 3.23 sec   HDFS Read: 1284990 HDFS Write: 137 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 230 msec
OK
10000000	136****94	0	NULL	86	edfccfa84875884f158132ca1b889a15
Time taken: 17.549 seconds, Fetched: 1 row(s)
hive> update tablename set sex = 1 where id = 10000000;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
hive> delete from tablename where id = 10000000;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.
hive> 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

查询相关资料发现:
必须设置这些配置参数以打开Hive中的事务支持,见hive官网

Minimally, these configuration parameters must be set appropriately to turn on transaction support in Hive:

Client Side

hive.support.concurrency – true
hive.enforce.bucketing – true (Not required as of Hive 2.0)
hive.exec.dynamic.partition.mode – nonstrict
hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
Server Side (Metastore)

hive.compactor.initiator.on – true (See table below for more details)
hive.compactor.worker.threads – a positive number on at least one instance of the Thrift metastore service
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

修改CDH中Hive的配置文件hive-site.xml

  1. hive --> 配置 --> 搜索 hive-site
    在这里插入图片描述
  2. 服务端配置
    <property>
    	<name>hive.compactor.initiator.on</name>
    	<value>true</value>
    </property>
    <property>
    	<name>hive.compactor.worker.threads </name>
    	<value>1</value>
    </property>
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

在这里插入图片描述

  1. 客户端配置
    <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>
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

在这里插入图片描述

  1. 重启hive
    在这里插入图片描述

再次执行update和delete操作,依然报错。

hive> update ods_user set sex = 1 where id = 10000000;
FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table hf_ods.ods_user that does not use an AcidOutputFormat or is not bucketed
hive> delete from ods_user  where id = 10000000;
FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table hf_ods.ods_user that does not use an AcidOutputFormat or is not bucketed
hive> 
  • 1
  • 2
  • 3
  • 4
  • 5

如果一个表要实现update和delete功能,该表就必须支持ACID,而支持ACID,就必须满足以下条件:

  1. 表的存储格式必须是ORC(STORED AS ORC);
  2. 表必须进行分桶(CLUSTERED BY (col_name, col_name, …) INTO num_buckets BUCKETS);
  3. Table property中参数transactional必须设定为True(tblproperties(‘transactional’=‘true’));

修改建表语句:

create table if not exists tablename (
  id bigint,
  sex tinyint  COMMENT '性别',
  name String COMMENT '姓名'
) COMMENT '用户表'
partitioned by (year string)
clustered by (id) into 2 buckets
row format delimited fields terminated by '\t'
stored as orc TBLPROPERTIES('transactional'='true');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/798494
推荐阅读
相关标签
  

闽ICP备14008679号