Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท UDFs and Transactions

ACID Transactions in Hive

Understand how Hive supports INSERT, UPDATE and DELETE with ACID guarantees: transactional tables, base and delta files, snapshot isolation, required settings and limits.

Hive began as a batch system for data that is written once and read many times, so for years it could only append data or overwrite a whole table or partition. That is awkward for real work: fixing one wrong row meant rewriting a partition, and readers could see half-written output.

Since Hive 0.14, and fully usable in Hive 3, ACID tables let you run UPDATE, DELETE and MERGE, with readers always seeing a consistent snapshot. This lesson explains what is guaranteed, how it works on top of immutable files, and how to set it up.

Transactional behavior needs a real Hive metastore and ORC files, so the Hive statements are read-only. The editors model the ideas with SQLite transactions and a small JavaScript simulation.

What ACID means here

Property In Hive
Atomic A statement either commits all its changes or none
Consistent Constraints such as NOT NULL and (informational) keys are checked on write
Isolated Readers get snapshot isolation: they see the data as of the moment the query began, even while writers commit
Durable Committed changes live in HDFS files and metastore records

Limits to know up front: transactions are autocommit, one statement each (no BEGIN ... COMMIT blocks spanning statements), and the isolation level is snapshot, not serializable.

Which tables are transactional

In Hive 3 and later, managed tables are transactional by default, while external tables are not. Two kinds exist:

  • Full ACID tables: ORC format, support INSERT, UPDATE, DELETE and MERGE.
  • Insert-only tables: any file format (text, Parquet, Avro...), support INSERT and SELECT with snapshot isolation, but not UPDATE or DELETE.
SQL
-- Full ACID
CREATE TABLE customers (
  id    BIGINT,
  name  STRING,
  email STRING
)
STORED AS ORC
TBLPROPERTIES ('transactional' = 'true');

-- Insert-only, any format
CREATE TABLE raw_events (line STRING)
STORED AS TEXTFILE
TBLPROPERTIES ('transactional' = 'true', 'transactional_properties' = 'insert_only');

-- A non-transactional managed table (for compatibility)
CREATE TABLE legacy (id INT) TBLPROPERTIES ('transactional' = 'false');

Bucketing is no longer required for ACID tables (it was in Hive 1 and 2).

Required settings

Transactions need the transaction manager on the server side, and the compactor running somewhere in the cluster (the metastore hosts it):

SQL
-- hive-site.xml / session
SET hive.support.concurrency = true;
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

-- metastore side (hive-site.xml)
-- hive.compactor.initiator.on = true
-- hive.compactor.worker.threads = 2   (must be > 0)

The metastore database stores transaction ids, open transactions and locks, so a production metastore needs a robust RDBMS (MySQL, PostgreSQL), not the embedded Derby.

How it works on immutable files

HDFS files cannot be modified in place. ACID tables therefore never change a file; they add new files and let readers combine them. The directory of a table (or partition) looks like this:

TEXT
customers/
  base_0000005/            <- compacted snapshot as of transaction 5
    bucket_00000
  delta_0000006_0000006/   <- rows inserted by transaction 6
    bucket_00000
  delete_delta_0000007_0000007/   <- row ids removed by transaction 7
    bucket_00000
  delta_0000007_0000007/   <- new versions of updated rows
    bucket_00000
  • An INSERT writes a delta directory.
  • A DELETE writes a delete_delta directory that lists the row ids to hide.
  • An UPDATE is a delete plus an insert: a delete_delta for the old row and a delta for the new row.
  • Each row carries a hidden ACID row id (originalWriteId, bucket, rowId), visible through SELECT ROW__ID, * FROM customers.

A read takes a snapshot of valid transaction ids from the metastore, then merges the base, the delta files and the delete_delta files, ignoring anything from transactions that are open or aborted.

flowchart LR
    B["base_5: rows 1,2,3"] --> M["Reader merges files"]
    D1["delta_6: insert row 4"] --> M
    DD["delete_delta_7: remove row 2"] --> M
    D2["delta_7: new version of row 3"] --> M
    M --> R["Result: 1, 3 (new), 4"]

Updates and deletes are new files; the reader applies them at read time. Compaction later folds them into a new base.

Simulating the merge

This JavaScript model (a teaching stand-in, not Hive code) applies a base and deltas the way an ACID reader does.

JS Merging base, delta and delete_delta

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Row 2 is gone, row 3 has its new name, row 4 is new: three statements, three small files, one consistent result.

Atomicity with transactions in SQL

The principle of atomic changes can be seen in any SQL database. This stand-in uses a SQLite transaction. (Hive itself would run each of these statements as its own transaction, so it offers atomicity per statement, not across several.)

SQL Commit and rollback (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Locks and concurrency

Hive's DbTxnManager takes locks recorded in the metastore: shared locks for reads, exclusive or semi-shared locks for writes, at table or partition level. Because readers use snapshots, they normally do not wait for writers. Two writers conflicting on the same partition, though, may block or abort one of them (write-write conflict detection is on for updates and deletes). Inspect them with:

SQL
SHOW TRANSACTIONS;
SHOW LOCKS;
SHOW LOCKS customers EXTENDED;
ABORT TRANSACTIONS 12345;   -- kill a stuck transaction

Long-running or abandoned transactions are a real operational risk: the cleaner cannot remove old files while any reader might still need them, and open transactions slow every read because the snapshot list grows. Hive times out transactions that stop sending heartbeats (hive.txn.timeout, by default 300 seconds).

When to use ACID tables

Good fits: slowly changing dimension tables, GDPR delete requests, corrections of individual rows, streaming ingest (the Hive Streaming API writes micro-batches as deltas), and keeping a table in step with a source database through MERGE. Poor fits: huge fact tables with constant row-level updates, since every update creates more files that readers must merge and the compactor must clean up. For frequent upserts at scale, table formats such as Iceberg (supported from Hive 4) offer an alternative.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does Hive implement UPDATE on an ACID table?

    HDFS files are immutable, so an update is a delete_delta entry plus a new delta row, merged by readers.
  2. 2. Which tables support UPDATE and DELETE?

    Full ACID needs ORC; insert-only transactional tables allow only INSERT and snapshot reads.
  3. 3. What isolation do readers get?

    Readers use the list of valid transaction ids at query start, so they ignore later or uncommitted commits.
  4. 4. Can you run BEGIN ... COMMIT around several statements in Hive?

    Hive supports single-statement transactions; multi-statement transactions are not available.

Summary

  • ACID tables give atomic, snapshot-isolated reads and row-level UPDATE, DELETE, MERGE on ORC managed tables; insert-only tables work with any format.
  • Changes are written as new delta and delete_delta files; readers merge them with the base.
  • You need DbTxnManager, concurrency support and a running compactor.
  • Each statement is its own transaction; there are no multi-statement transactions.

Next: MERGE, UPDATE, DELETE and Compaction