How do I get INSERT/UPDATE/DELETE, Table Data Editor and Import to work with Hive?

by Jan 9, 2015

How do I get INSERT, UPDATE, DELETE, the Table Data Editor and Import to work with Hive?


Jonathan Powers over 8 years ago
With Aqua Data Studio v15.0.11-9 and above, tables now require ACID to do INSERT UPDATE and DELETE transactions. This is described here ‘’

Aqua Data Studio queries the server to see if the following parameters are set correctly before it allows the above transactions. From the Apache Hive documentation:

These configuration parameters must be set appropriately to turn on transaction support in Hive: – 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
hive.compactor.worker.threads – 0
If the driver minor version is .14 and the above parameters are set correctly, Aqua Data Studio v15.0.11-9 and above will allow INSERTs, UPDATEs and DELETEs. Users can use Table Data Editor, Query Analyzer (INSERTs, UPDATEs, DELETEs) and Imports (Tools > Import Data ) with Hive. If all of these functions are grayed out, it means that the driver is not version .14 and/or the server parameters are not set correctly. Incorrect parameters settings are logged (in Help > View Log) .

Here is a working example when creating a table.

CREATE TABLE students5 (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))

INSERT INTO TABLE students5 VALUES (‘fred flintstone’, 35, 1.28)
INSERT INTO TABLE students5 VALUES (‘barney rubble’, 35, 4.28)

UPDATE students5 set name = ‘wilma flintstone’ where name = ‘fred flintstone’

SELECT * FROM students5

DELETE from students5 where name = ‘barney rubble’

We found that in order to do UPDATEs and DELETEs, the table must be bucketed. This is not the case with INSERTs. Also, table properties TBLPROPERTIES(“transactional”=”true”) has to be set. The error messages from Hive are not always informative, so if someone is having problems, it’s a good idea to see how you created your tables.