How to Fix Hive ACID Transaction and Compaction Errors

In this tutorial, you'll learn about How to Fix Hive ACID Transaction and Compaction Errors. We cover key concepts, practical examples, and best practices.

Hive ACID operations fail when tables are created without transactional=true, use a format that does not support ACID (like TextFile), or compactions stall, preventing new transactions from proceeding.

Quick Fix

Wrong

CREATE TABLE orders (
  id INT,
  status STRING
);

INSERT INTO orders VALUES (1, 'pending');
UPDATE orders SET status = 'shipped' WHERE id = 1;

Error: FAILED: SemanticException: UPDATE/DELETE is not allowed
on non-transactional tables. Table is not transactional.

The table must be created with ACID properties.

Right

CREATE TABLE orders (
  id INT,
  status STRING
) CLUSTERED BY (id) INTO 4 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

INSERT INTO orders VALUES (1, 'pending');
UPDATE orders SET status = 'shipped' WHERE id = 1;

1       shipped

ACID tables require ORC format, bucketing, and transactional=true.

Fix stuck compactions

-- Check compaction status
SHOW COMPACTIONS;

-- Run manual compaction if stalled
ALTER TABLE orders COMPACT 'major';
ALTER TABLE orders COMPACT 'minor';

CompactionId    Table    State
1               orders   running
2               orders   ready for cleanup

Fix delta file accumulation

-- Check number of delta files
SHOW TABLE EXTENDED LIKE 'orders';

-- Set auto-compaction
ALTER TABLE orders SET TBLPROPERTIES (
  'auto.purge'='true',
  'compactor.mapreduce.map.memory.memory'='2048'
);

Prevention

• Always use STORED AS ORC and CLUSTERED BY when creating transactional tables.
• Set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager.
• Enable hive.support.concurrency for transaction support.
• Schedule regular major and minor compactions.
• Monitor delta file count with SHOW COMPACTIONS.

DodaTech Tools

Doda Browser's ACID dashboard shows delta file counts, compaction history, and transaction conflicts on Hive tables. DodaZIP archives compaction logs for trend analysis. Durga Antivirus Pro alerts when delta file counts exceed thresholds.

Common Mistakes with acid operations

1. Using return to exit a function early instead of wrapping a pure value in the monad
2. Mixing let bindings with <- bindings in do notation, producing type errors
3. Overlapping type class instances that cause GHC to reject the program with ambiguous dispatch errors

These mistakes appear frequently in real-world HIVE code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ