Bigdata SQL: ACID Support in Apache Hive

Now lets take a look at some of the recent additions in Hadoop and Hive to support ACID and transactions. ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. ACID is what protects two people from booking the same seat in a movie theater when making an online booking.

Most NoSQL and Hadoop data stores dont support ACID. In turn, they support BASE (Basically Available Soft State and Eventual) consistency. “Basically available” means that the system guarantees availability. “Soft State” means that the state of the system may change over time, even without input that could result from the eventual consistency model.

Eventual consistency is the core concept behind BASE. Maintaining changing data in a cluster-based data-storage system that spans across data centers and is replicated across multiple locations involves latency. A change made in one data center takes a while to propagate to another data center or node. So, if two simultaneous queries are started and hit two different replicated versions of the data, they could get different answers. Eventually, though, the data will be replicated across all copies and will be consistent. This is called “eventual consistency.” This is exactly what happens when, for example, you try to sell a product on Amazon. After you have successfully submitted your request to list the product for sale, Amazons internal storage system—DynamoDB, which is an eventually consistent database—comes up with the message that it will take some time for the product to show up across different zones in Amazons data centers.

When Hadoop was initially designed, HDFS was designed to be a WORM (write once read many times) file system. Hive provided SQL support over files in HDFS, which meant it was basically a file database platform. Starting in HDFS 2.0, data could be loaded or appended, but support for incremental updates or deletes without complex ETL (Extract, Transform, Load) -like batch transformations was not possible.
However, as of now, using Hadoop doesnt mean you have to give up on ACID. Hive now supports new SQL query syntax, which supports transactions, which has been part of relational databases since the 70s.
Some of the new SQL query syntax added to Hive include the following:

INSERT INTO TABLE X
VALUES (col1, col2, …), (col1, col2, …);
UPDATE C SET col1 = value1 [, col2 = value2 …] [WHERE expression]
DELETE FROM X [WHERE expression]
SHOW TRANSACTIONS;
Shows all currently running and aborted transactions in the system
SHOW LOCKS ;

Transaction and ACID support is very essential for multiple-use cases in the big data platform. When tracking changes made on a particular row or column over time—to determine, for example, the lineage, when they were made, and by whom—ACID becomes important. If some data was inserted inaccurately, and you want to be able to correct it, the ability to update the data becomes important.
Applications that required data merges, updates, or deletes had to rely on reinstatement of the table or partitions or buckets. Support for ACID and support for incremental updates and deletes eliminates the need to develop, maintain, and manage entire sets of complex ETL/ELT code. This provides a boost to traditional database developers to engage SQL on Hadoop, using familiar syntax and semantics, and it reduces time and effort within the development and quality analysis of operational workloads.
ACID support is essential for building transactions. Support for ACID began with version 0.13, but it was incomplete. Support for ACD (Atomicity, Consistency and Durability) was only at the partition level, and support for isolation was done using Zookeeper. Hive 0.14 introduced new APIs that completed the ACID properties support for Hive. Transactions are supported at the row level—for Update, Insert, and Deletes.
Any change to row data is logged in row order in a file that resides in the same directory in HDFS, where the base table data resides. For each transaction or a batch of transactions, a new delta file is created. A separate directory in HDFS is maintained for the base files, and a separate one is maintained for the delta files. On read, the delta is merged into the base data, applying any update or deletes. The process of purging the delta files is known as compaction. In order to do this, all the deltas are merged into the original base file. The compaction process is done by a set of threads in the Hive metastore. During a minor compaction process, multiple delta files are coalesced into one delta file on a per-bucket basis, while for a major compaction process, one or more delta files are rewritten to a new base file on a per-bucket basis. Compaction is done both to reduce the load of too many files on the name node, as well as to reduce the latency time to merge on the fly while processing the query. Compactions are generally scheduled by the Thrift server, and the only way for an end user to run compactions is
by using the ALTER TABLE COMPACT command. The compactor is clever enough not to remove a delta file until all the readers have finished their reads. However, there are constraints to supporting transactions in Hive. Only ORC formats can support transactions, and tables have to be bucketed to support transactions. Transactional statements such as BEGIN , COMMIT , and ROLLBACK are not yet supported, and all operations are automatically committed. Transaction support is off by default. From an isolation perspective, only the snapshot isolation level is supported. Other isolation levels, such as read committed, repeatable read, and serializable, are not supported as of now. A reader will see consistent data for the duration of the query.
In order to provide the durability feature of transaction, all transaction-related metadata is stored in the Hive metastore. A new lock manager has been added to Hive, to support transactions. All lock metadata is stored in the Hive metastore. To ensure that no lock or transaction is left in the state of limbo, a keep alive, or heartbeat, mechanism has been implemented, whereby each lock holder and transaction communicates with the metastore to indicate liveliness