Hive ACID Transactions- Part I

Know more about ACID Transactions and how you can benefit from them

Dinesh Rajput

Published in

Clairvoyant Blog

6 min readMar 2, 2021

*Table Type along with File Format for ACID Operations*

Clairvoyant utilizes the Hive ACID transaction property to manage transactional data (Insert/Update/Delete). Hive ACID tables manage data in base and delta files which increase the performance of the job. It helps our clients lower the cost of the cluster while running jobs. Tapping into Clairvoyant's expertise in Hive ACID Transactional Tables, this blog discusses Hive ACID table basic design, Transaction/Lock Manager, the configuration of Db Lock Manager, limitations, and directory structure in Hive ACID table.

What is ACID?

ACID refers to four key properties of transaction:

1. Atomicity: It is the transaction or operation that either succeeds or fails completely, and does not leave any partial state.

2. Consistency: If any transaction is performed, the results of that transaction are visible to every subsequent query/transaction.

3. Isolation: If any incomplete transaction is performed then the other query/transaction does not cause unexpected side effects for other users, meaning that all transactions are separated from each other.

4. Durability: If the transaction is complete then it will be preserved even in the face of a machine or system failure.

Up until Hive 0.13, the ACD feature was supported at the partition level, but Isolation, which is used for the locking mechanism was absent. From 0.13 version on, the ACID feature became to be fully supported.

Use cases that require Transactions with ACID properties in Hive:

1. Streaming data: When we write streaming data into Hive partition, it creates lots of small files every few seconds which degrade the Hive performance. With the help of ACID, we can insert/update/delete on the same Hive partition without affecting the performance of the table.

2. Data correcting: Sometimes, the data stored in the data lake can be incorrect or businesses might want some changes based on a policy change or business requirement. In such situations, the challenge is to correct the data that can be achieved by insert/update/delete operations.

3. Bulk updates using SQL merge statement: With the bulk merge, we can merge small files into a single file without affecting the read performance.

Basic Design

HDFS does not support changes in the existing files. It also does not support read consistency if the writes operation appends data to a file being read by another user. To support ACID features, Hive stores table data in a set of base files and all the insert, update, and delete operation data in delta files. At read time, the reader merges both the base file and delta files to present the latest data. Example:

/user/hive/warehouse/acid_db.db/employee/base_0000022/bucket_00000

/user/hive/warehouse/acid_db.db/employee/delta_0000023_0000023_0000/bucket_00000

Transaction/Lock Manager

Lock Manager is responsible to manage locks on the database, table, and partition. It is described by property “hive.lock.manager” and the default value is “hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager”.

DummyTxnManager is a default lock manager like the older versions of Hive (<0.13) which had no locks for ACID operation. DbTxnManager manages all the locks in Hive’s metastore. To avoid clients' connections from dropping and leaving the transaction or locks dangling, a heartbeat is sent from lock holders and transaction initiators to the metastore on a regular basis. If a heartbeat is not received in the configured amount of time, the lock or transaction will be aborted. DbTxnManager will acquire locks on all tables, even those without “transactional=true” property. By default, Insert operation into a non-transactional table will acquire an exclusive lock and thus block other inserts and reads.

Configuration of DbLockManger:

Limitations

1. Like RDBMS systems, the BEGIN, COMMIT, ROLLBACK commands are not yet supported and currently, all transactions are auto-committed in the Hive ACID.

2. At present, only the ORC file format is supported.

3. Tables must be managed and bucketed. External tables can not be ACID since the changes on the external table are not in control of the compactor.

4. Hive transaction manager must be set to “org.apache.hadoop.hive.ql.lockmgr.DbTxnManager” to work with ACID tables, where non-acid sessions(like spark) are not able to access the ACID tables.

5. “LOAD DATA” statement is not supported with transactional tables since it works on the file level.

Directory Structure in an ACID table:

In an ACID transaction table, for each transaction 1 delta directory will be created. We will execute queries in the following sequence and see how the delta directory looks:

Insert
Insert
Update
Delete
Merge

Create Table -

1)Running First Insert query –

We can see the “delta_0000001_0000001_0000” directory is created for the insert query. There is a data file “bucket_00000” in the ORC format.

2)Running Second Insert Query –

We can see the “delta_0000002_0000002_0000” directory is created for the second insert query. There is a data file “bucket_00000” in the ORC format.

3)Running Update Query –

We can see “delta_0000003_0000003_0000” and “delete_delta_0000003_0000003_0000” directories are created for the update query (as update = delete + insert). We can also see that there is one delta directory for delete and one delta directory for insert as the update operation is equivalent to insert operation + delete operation.

4)Running Delete Query –

We can see the “delete_delta_0000004_0000004_0000” directory is created for the delete query.

5)Running Merge Query –

We can see “delta_0000005_0000005_0000” ,” delta_0000005_0000005_0001” and “delete_delta_0000005_0000005_0001” directories are created for the merge query.