为什么删除功能在hive-shell中不起作用

Question

hive> delete from daily_case where num_casedaily=0;
FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.

提前谢谢。

Answer 1

Hive不以传统方式支持ACID事务。您将需要Hive中ACID事务的一些先决条件和不需要的限制。

你可以回顾这篇文章：

使用蜂窝酸事务插入更新和删除数据

有关Hive事务的更多信息。

先决条件配置单元事务管理器应设置为DbTxnManagerSET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

我们需要启用并发SET hive.support.concurrency=true;

一旦我们设置了上述属性，我们就应该能够将数据插入任何表中。对于更新和删除，表应该是分块的，文件格式需要是ORC或任何ACID Compliant Format。我们还需要将表属性事务设置为真正的TBLPROPERTIES ('transactional'='true');

查看属性

$ cd /etc/hive/conf
$ grep -i txn hive-site.xml
$ hive -e "SET;" | grep -i txn
$ beeline -u jdbc:hive2://localhost:10000/training_retail

作为在HIVE中创建事务表的示例

SET hive.txn.manager;
hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.support.concurrency=true;
SET hive.enforce.bucketing;
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode;
hive.exec.dynamic.partition.mode=strict
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.compactor.initiator.on;
SET hive.compactor.initiator.on=true;
-- A positive number
SET hive.compactor.worker.threads;
SET hive.compactor.worker.threads=1;
CREATE TABLE orders_transactional (
order_id INT,
order_date STRING,
order_customer_id INT,
order_status STRING
) CLUSTERED BY (order_id) INTO 8 BUCKETS
STORED AS ORC
TBLPROPERTIES("transactional"="true");
INSERT INTO orders_transactional VALUES
(1, '2013-07-25 00:00:00.0', 1000, 'COMPLETE');
INSERT INTO orders_transactional VALUES
(2, '2013-07-25 00:00:00.0', 2001, 'CLOSED'),
(3, '2013-07-25 00:00:00.0', 1500, 'PENDING'),
(4, '2013-07-25 00:00:00.0', 2041, 'PENDING'),
(5, '2013-07-25 00:00:00.0', 2031, 'COMPLETE');
UPDATE orders_transactional
SET order_status = 'COMPLETE'
WHERE order_status = 'PENDING';
DELETE FROM orders_transactional
WHERE order_status <> 'COMPLETE';
SELECT *
FROM orders_transactional;

Answer 2

正如@Chema解释的HIVE的ACID交易。您可以更改表属性以允许事务处理。

或

您可以执行以下操作。这样就不必更改表属性。

INSERT OVERWRITE INTO daily_case
SELECT * FROM daily_case WHERE num_casedaily <> 0;