ACID Tables in Hive:

 

ACID Tables in Hive:

In Hive, starting from version 0.14, it supports Online Transactional Processing (OLTP). Before that, Hive only supported Online Analytical Processing (OLAP

What is OLTP?

OLTP systems are designed to handle transaction-oriented workloads, where multiple users are performing concurrent transactions.

In OLTP, we can perform operations like Insert, Update, and Delete. In OLAP, tables cannot be modified.

Now what is ACID:

· A — Atomicity — they are either executed completely or not at all. There are no partial transactions.

· C — Consistency — The database remains in a consistent state even in the presence of failures.

· I — Isolation — the intermediate states of one transaction are not visible to other transactions until the transaction is completed.

· D — Duration — Once a transaction is committed, its changes are permanent and survive system failures.

These are tables that allow insert/update or delete operations. 

Here are certain rules to be followed for ACID Tables:

Internal Table: Tables should be internal tables.

Bucketed: For a table to be ACID, it should be bucketed.

File Format: The table should be stored in ORC Format.

Transactional Properties: In Table properties, Transactional = ‘true’ should be enabled.


Example code to make a table ACID:

Set hive.support.concurrency = true;
Set hive.enforce.bucketing = true;
Set hive.exec.dynamic.partition.mode = nonstrict;
Set hive.txn.manager = org.apache.jadoop.hive.ql.lockmgr.DbTxnManager;
Set hive.compactor.initiator.on = true;
Set hive.compactor.worker.threads = 1;

CREATE TABLE acid_table ( sno int, city string)     clustered by (city) into 3 buckets row format delimited fields     terminated by ‘,’ lines terminated by ‘\n’ stored as ORC     TBLPROPERTIES( ‘transactional’ = ‘true’);

Below operations can be performed only in ACID Tables:

INSERT INTO acid_table(sno,city) VALUES(1,’Chennai’)
UPDATE acid_table SET city = ‘Coimbatore’ WHERE sno = 1;
DELETE FROM acid_table WHERE sno = 1;

CONCLUSION:


In conclusion, understanding the relationship between OLTP and ACID tables is essential for designing and managing databases effectively. OLTP systems are optimized for transactional workloads, with a focus on high throughput and low response times. ACID tables, on the other hand, adhere to a set of properties (Atomicity, Consistency, Isolation, Durability) that ensure reliable and consistent transaction processing, crucial for maintaining data integrity.

Comments

Post a Comment

Popular posts from this blog

Understanding Union() and UnionByName() in PySpark

🚀 Union() vs UnionByName() in PySpark   In PySpark, when working with DataFrames, union() and unionByName() are two methods used for merging data from multiple DataFrames.   Purpose of the two functions: o    union() in PySpark is used to merge two tables with the same column count irrespective of the column names. It simply appends the rows of the second table below the rows of the first table. o    unionByName() on the other hand is used to merge data from two tables based on their column names. It ensures that the columns are matched and appended accordingly.   Differences : o    Example 1: §   union() simply merges two tables without considering column names or data types. §   unionByName() merges tables based on column names, without considering the order of columns.   Example Code: spark = SparkSession.builder.appName("Union_Example").getOrCreate()   # Creating DataFrames m...
Read more

Troubleshooting PySparkTypeError: [NOT_ITERABLE]

Image
Troubleshooting PySparkTypeError:  [NOT_ITERABLE]   Ever encountered a confusing error message like 'Column is not iterable' while working with PySpark? Here's a relatable scenario: You're trying to find the highest salary from a list of employees using PySpark . You've got your DataFrame set up, but when you run your code, The error pops up, leaving you scratching your head. The reason? You forgot a tiny but essential step: importing the ' max ' function! Here's the fix, in plain language: # Don't forget to import the ' max ' function! from pyspark.sql.functions import max # Now, let's find that maximum salary: data = [("John", 2500.0), ("Alice", 3000.0), ("Bob", 2200.0)] usr_data = spark.createDataFrame(data,["name","salary"]) +-----+----------+ | name|salary    | +-----+----------+ | John| 2500.0   | |Alice | 3000.0  | | Bob | 2200.0   | +-----+----------+ max_sal = usr_data.agg(max(us...
Read more

Departmental Salary Insights: with SQL and PySpark

Image
Departmental Salary Insights: with SQL and PySpark   Description:  This article dives into the SQL and PySpark methodologies to identify employees who earn more than the average salary within their respective departments. INPUT: SQL QUERY: SELECT Name, Salary FROM ( SELECT Name, Salary, AVG (Salary) OVER ( PARTITION BY Department) AS avg_sal FROM Employee ) AS department_salaries WHERE Salary > avg_sal; PYSPARK QUERY: from pyspark.sql import SparkSession from pyspark.sql.window import Window as w from pyspark.sql.functions import avg, col from pyspark.sql.types import StructType, StructField, StringType, DoubleType # Define the schema for the DataFrame schema = StructType([ StructField( "EmpID" , StringType(), True ), StructField( "Name" , StringType(), True ), StructField( "Department" , StringType(), True ), StructField( "Salary" , DoubleType(), True ) ]) # Initialize a Spark session spark = SparkSessio...
Read more