For now, all transactions in HQL are auto-committed without supporting BEGIN, COMMIT, and ROLLBACK, like as with relational databases. Also, the table that has a transaction feature enabled has to be a bucket table with the ORC file format. The following configuration parameters must be set appropriately in hive-site.xml or beeline connection string to turn on transaction support:
> SET hive.support.concurrency = true;
> SET hive.enforce.bucketing = true;
> SET hive.exec.dynamic.partition.mode = nonstrict;
> SET hive.txn.manager =
> org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
> SET hive.compactor.initiator.on = true;
> SET hive.compactor.worker.threads = 1;
When a transaction is enabled, each transaction-related operation, such as INSERT, UPDATE, and DELETE, stores data in delta files. At read time, the reader merges the base and delta files, applying any updates and deletes. Both base and delta directory names contain the transaction IDs. Occasionally, these changes need to be merged into the base files by compactors, which are background processes in the metastore, for better performance and smaller file size. To see a list of all tables/partitions currently being compacted or scheduled for compaction, use the SHOW COMPACTIONS statement.
Then, create a table with transactions enabled in the table properties and populate the data:
> CREATE TABLE employee_trans (
> emp_id int,
> name string,
> start_date date,
> quit_date date,
> quit_flag string
> )
> CLUSTERED BY (emp_id) INTO 2 BUCKETS STORED as ORC
> TBLPROPERTIES ('transactional'='true'); -- Also need to set this
No rows affected (2.216 seconds)
> INSERT INTO TABLE employee_trans VALUES
> (100, 'Michael', '2017-02-01', null, 'N'),
> (101, 'Will', '2017-03-01', null, 'N'),
> (102, 'Steven', '2018-01-01', null, 'N'),
> (104, 'Lucy', '2017-10-01', null, 'N');
No rows affected (48.216 seconds)
For a table with transactions enabled, we can perform UPDATE, DELETE, and MERGE operations on data.