How do I get INSERT, UPDATE, DELETE, the Table Data Editor and Import to work with Hive?
Response
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 ‘https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions’
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:
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
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))
CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES(“transactional”=”true”)
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.