Hive Internal, External and ACID Tables

Apache Hive is a powerful data warehousing tool in the Hadoop ecosystem that provides an SQL-like interface for querying and managing large datasets. One of the most critical aspects of Hive is understanding the difference between Internal (Managed) Tables, External Tables, and ACID Tables. Let's dive deep!

---

Understanding Hive Tables

Hive organizes data into tables similar to traditional databases, but the way these tables store and manage data is crucial for optimizing performance and managing storage efficiently.

1. Internal (Managed) Tables

An Internal Table is managed entirely by Hive. When you create an internal table and load data into it, Hive stores the data within its warehouse directory (by default, at /user/hive/warehouse/). If you drop an internal table, both the metadata and the actual data are deleted.

Key Characteristics:

• Hive controls the lifecycle of the data.

• Data is stored inside Hive's warehouse directory.

• Dropping the table deletes both metadata and data.

• Best for temporary or derived datasets.

Example:

CREATE TABLE internal_table (
    id INT,
    name STRING
) STORED AS PARQUET;

LOAD DATA INPATH '/user/data/sample_data.csv' INTO TABLE internal_table;

2. External Tables

An External Table allows Hive to query data stored outside its warehouse directory. This means Hive only manages metadata, and dropping the table does not delete the actual data.

Key Characteristics:

• Data resides outside Hive's default warehouse.

• Hive only manages metadata, not the actual data.

• Dropping the table only removes metadata, not the data itself.

• Ideal for sharing data between multiple applications.

Example:

CREATE EXTERNAL TABLE external_table (
    id INT,
    name STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/external_data/';

---

3. ACID Tables (Transactional Tables)

With the introduction of Hive ACID (Atomicity, Consistency, Isolation, Durability) Tables, Hive supports transactional operations such as INSERT, UPDATE, DELETE, and MERGE. These tables allow for efficient record-level modifications and ensure data consistency.

Key Characteristics:

• Supports INSERT, UPDATE, DELETE, and MERGE operations.

• Uses compaction to optimize storage and performance.

• Requires transactional properties enabled in Hive.

• Best suited for incremental data processing and OLTP-style operations.

Example:

SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.support.concurrency=true;

CREATE TABLE acid_table (
    id INT,
    name STRING
) STORED AS ORC
TBLPROPERTIES ('transactional'='true');

INSERT INTO acid_table VALUES (1, 'John Doe');
UPDATE acid_table SET name = 'Jane Doe' WHERE id = 1;
DELETE FROM acid_table WHERE id = 1;

---

Comparison of Hive Table Types

---

Best Practices & Use Cases

✅ Use Internal Tables When:

• You need full control over the data lifecycle.

• The data is temporary or meant for transformations.

• The dataset is not shared across multiple tools.

✅ Use External Tables When:

• You want to retain data even after dropping the table.

• You need to share data with other applications.

• Your data is managed in external storage like HDFS, S3, or Azure Blob Storage.

✅ Use ACID Tables When:

• You need to perform INSERT, UPDATE, DELETE, and MERGE operations.

• The data must maintain transactional integrity.

• Your workload requires incremental changes.

---

Conclusion

Understanding the differences between internal, external, and ACID tables is crucial for optimizing Hive storage and query performance. Whether you need Hive to manage your data entirely, query external sources, or enable transactional operations, choosing the right table type ensures better scalability and efficiency.