Hive 官方手册翻译 – Hive DDL(数据定义语言)
Hive 官方手册翻译 – Hive DML(数据操纵语言)
Hive 官方手册翻译 – Hive Transactions (Hive 事务)
ACID介绍
如果一个表要实现update和delete功能,该表就必须支持ACID,而支持ACID,就必须满足以下条件:
- 表的存储格式必须是ORC(STORED AS ORC);
- 表必须进行分桶(CLUSTERED BY (col_name, col_name, …) INTO num_buckets BUCKETS);
- Table property中参数transactional必须设定为True(tblproperties(‘transactional’=‘true’));
创建表
create table if not exists user_orc(
id int,
name string,
sex string,
year int
)
clustered by (id) into 4 buckets
row format delimited fields terminated by ','
stored as orc
tblproperties ('transactional'='true');
删除表
drop table user_orc;
查看表
SELECT * FROM user_orc;
报错
FAILED: SemanticException [Error 10265]: This command is not allowed on an ACID table default.user_orc with a non-ACID transaction manager. Failed command: SELECT * FROM user_orc
解决方案:
临时解决
hive> SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
hive> SET hive.support.concurrency=true;
hive> SET hive.enforce.bucketing=true;
hive> SET hive.exec.dynamic.partition.mode=nonstrict;
hive> SET hive.compactor.initiator.on=true;
hive> SET hive.compactor.worker.threads=1;
永久解决
CM修改hive配置
服务端
hive-site.xml 的 Hive 服务高级配置代码段(安全阀)
hive.compactor.initiator.on – true
hive.compactor.worker.threads – 1
hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
客户端
hive-site.xml 的 Hive 客户端高级配置代码段(安全阀)
hive.support.concurrency – true
hive.enforce.bucketing – true
hive.exec.dynamic.partition.mode – nonstrict
hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
插入语句
insert into user_orc values (1,'liuli','男','1996');
insert into user_orc values (2,'liu','男','1997');
insert into user_orc values (3,'li','女','1998');
insert into user_orc values (4,'lisi','女','1999');
更新语句
update user_orc set name = "zhangsan" where id = 4;
删除语句
DELETE FROM user_orc where id =4;
视图
CREATE VIEW st_uc as
select
e.sex,d.name
from user_orc e
INNER JOIN class_orc d
ON e.id= d.id;