use 'transactional', I.e. lower case
From: <Zhu>, Jian-bing <[email protected]<mailto:[email protected]>>
Reply-To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Date: Monday, September 7, 2015 at 11:58 PM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Subject: UPDATE and DELETE fail due to Error 10122 on Hive 1.2.1
Hi,
I tried to test UPDATE and DELETE on Hive, but it always failed because of
"[Error 10122]: Bucketized tables do not support INSERT INTO".
I am using Hortonworks Sandbox HDP 2.3 which contains Hive 1.2.1.
I already configured all the required parameters according to Hive doc:
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 (for exactly one instance of the Thrift
metastore service)
hive.compactor.worker.threads - a positive number on at least one instance of
the Thrift metastore service
Hive log is attached and the followings are the output from Hive console.
Can anyone help to take a look and provide some suggestion to resolve it?
Thanks in advance!
hive> CREATE TABLE mytable (col1 int, col2 string)
> CLUSTERED BY (col1) INTO 2 BUCKETS
> STORED AS ORC TBLPROPERTIES('TRANSACTIONAL'='TRUE');
OK
Time taken: 1.324 seconds
hive> insert into mytable values (1, 'AAA');
Query ID = root_20150908014932_e21e8fb1-f93b-472a-a527-1adc29cd3359
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id
application_1440988430185
_0013)
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... SUCCEEDED 2 2 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 5.70 s
--------------------------------------------------------------------------------
Loading data to table default.mytable
Table default.mytable stats: [numFiles=2, numRows=1, totalSize=336,
rawDataSize=
91]
OK
Time taken: 16.585 seconds
hive> insert into mytable values (2, 'BBB');
Query ID = root_20150908014955_2647ae71-0687-43dd-84c5-8263e65622f9
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id
application_1440988430185
_0013)
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... SUCCEEDED 2 2 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 7.97 s
--------------------------------------------------------------------------------
Loading data to table default.mytable
Table default.mytable stats: [numFiles=4, numRows=2, totalSize=672,
rawDataSize=182]
OK
Time taken: 13.812 seconds
hive> delete from mytable;
FAILED: SemanticException [Error 10122]: Bucketized tables do not support
INSERT INTO: Table: default.mytable
hive>
hive> select * from mytable;
OK
2 BBB
1 AAA
Time taken: 0.803 seconds, Fetched: 2 row(s)
Best Regards,
Jianbing
