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

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

Handling a PySpark TypeError: ‘unsupported operand type(s) for +: ‘int’ and ‘str’

Image
Handling a PySpark TypeError: ‘unsupported operand type(s) for +: ‘int’ and ‘str’ You’ve been working with PySpark, trying to calculate the rolling sum of a column in your DataFrame, and you’ve set everything up perfectly. However, when you run your code, you face the dreaded TypeError: ‘unsupported operand type(s) for +: ‘int’ and ‘str’ error. The Reason: The error indicates that there’s a type mismatch in your DataFrame. Specifically, you’re trying to perform arithmetic operations on columns with different data types — an integer (int) and a string (str). In this scenario, you forgot a tiny but essential step: importing the ‘ sum ’ function! Here is the sample Hospital data, from pyspark.sql.window import Window as w from pyspark.sql.functions import col df_hospital = spark.read.options(header= 'True' ,inferSchema= 'True' ).csv( 'C:/Users/hp/Python_Sums_20thfeb/pyspark/hospital.csv' ) ovr_sp = w.partitionBy( 'department' ).orderBy( 'discharge_...
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