what is acid property in hive and how to update data in hive

      Comments Off on what is acid property in hive and how to update data in hive

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that ensure that database transactions are processed reliably. Atomicity requires that either all the operations in a transaction are completed or none are completed. Consistency ensures that the database remains in a consistent state after a transaction. Isolation ensures that transactions are isolated from each other, so that the result of one transaction is not visible to other transactions until it is committed. Durability ensures that once a transaction is committed, its effects are permanent and will persist even in the case of system failures.

In Hive, ACID properties can be achieved using the transaction-based operations in Hive 3.0 and later versions. Hive supports update and delete operations on transactional tables, which are tables with the ACID property. By default, tables in Hive are not transactional.

Here is an example of how to create a transactional table in Hive:

-- Create a transactional table
CREATE TABLE sales_txn (
  item_id INT,
  sale_date STRING,
  sale_amount FLOAT
)
CLUSTERED BY (item_id) INTO 4 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');


In this example, we are creating a transactional table called “sales_txn” with the same structure as the “sales” table. The TBLPROPERTIES clause sets the “transactional” property to “true”, which makes the table transactional.

To update data in a transactional table, you can use the following syntax:

-- Update data in a transactional table
UPDATE sales_txn
SET sale_amount = sale_amount * 1.1
WHERE item_id = 1001;

In this example, we are updating the sale_amount column for rows with item_id equal to 1001 by multiplying it by 1.1. The update operation is performed within a transaction, ensuring that the ACID properties are maintained.