1

I want to insert simple query into hive table;

I create table using following code create table t(id int, f1 String, f2 int); and try to insert insert into t values (1, '123', 1);

A task is created, but not executed.

Query ID = hadoop_20200518194705_4ec47375-e5e8-4d33-80d8-ed183aacb0c2
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1589556481112_0007, Tracking URL = http://hadoop_hose:8088/proxy/application_1589556481112_0007/
Kill Command = /home/hadoop/hadoop-3.1.3/bin/mapred job  -kill job_1589556481112_0007

What am I doing wrong?

UPD: Listing from GUI:enter image description here

1

1 Answers1

0

You shoud review ACID and Transactions in Hive. You can follow this link:

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions

Hive Transactions Manager should be set to DbTxnManager

SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

We need to enable concurrency

SET hive.support.concurrency=true;

Once we set the above properties, we should be able to insert data into any table. For updates and deletes, table should be bucketed and file format need to be ORC or any ACID Compliant Format. We also need to set table property transactions to true

TBLPROPERTIES ('transactional'='true');

As an example

Review properties

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

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;
Chema
  • 2,748
  • 2
  • 13
  • 24