APACHE CASSANDRA ADVANCED CRUD OPERATIONS

 

Advanced CRUD (Create, Read, Update, Delete) With Filtering, Batch, TTL, and Lightweight Transactions

---

AIM

To perform advanced CRUD operations in Apache Cassandra including

• Batch operations

• Conditional updates (LWT)

• TTL (Time-To-Live)

• Filtering

• Secondary indexes

• Updating collections (list, set, map)

---

SOFTWARE REQUIREMENTS

• Apache Cassandra 4.x

• Java JDK 8/11

• cqlsh client

• Ubuntu/Windows environment

---

THEORY

Cassandra provides more advanced data manipulation features compared to basic CRUD:

1. TTL (Time to Live)

A feature that automatically expires data after a specified number of seconds.

2. Batch Statements

Used to perform multiple writes atomically.

3. Lightweight Transactions (LWT)

Performed using IF EXISTS or IF condition, used for conditional updates.

4. Filtering

Allows non-primary-key filtering using ALLOW FILTERING.

5. Collection Types

• List → ordered

• Set → unique values

• Map → key–value pairs

6. Secondary Index

Allows filtering on non-primary-key columns.

---

⭐ PROCEDURE & COMMANDS

---

1. Create Keyspace and Table

CREATE KEYSPACE college
WITH replication = {
   'class': 'SimpleStrategy',
   'replication_factor': 1
};

USE college;

CREATE TABLE students (
   id INT PRIMARY KEY,
   name TEXT,
   age INT,
   subjects LIST<TEXT>,
   marks MAP<TEXT, INT>,
   hobbies SET<TEXT>
);

---

2. INSERT with Collections

INSERT INTO students (id, name, age, subjects, marks, hobbies)
VALUES (1, 'Alice', 20, ['Math','Science'], {'Math':90,'Science':85}, {'reading','sports'});

INSERT INTO students (id, name, age)
VALUES (2, 'Bob', 22);

---

3. UPDATE Collection Values

Append to List

UPDATE students 
SET subjects = subjects + ['English']
WHERE id = 1;

Remove from List

UPDATE students 
SET subjects = subjects - ['Science']
WHERE id = 1;

Add to Set

UPDATE students 
SET hobbies = hobbies + {'music'}
WHERE id = 1;

Update Map Value

UPDATE students 
SET marks['Math'] = 95
WHERE id = 1;

---

⭐ 4. DELETE Operations

Delete a single column

DELETE age FROM students WHERE id = 1;

Delete collection elements

UPDATE students SET hobbies = hobbies - {'sports'} WHERE id = 1;

Delete entire row

DELETE FROM students WHERE id = 2;

---

⭐ 5. TTL (Time-to-Live)

Insert with TTL

INSERT INTO students (id, name, age)
VALUES (3, 'Charlie', 21, 1)
USING TTL 30;

(Record expires after 30 seconds)

Update with TTL

UPDATE students USING TTL 60
SET age = 25 WHERE id = 1;

---

⭐ 6. Batch Operations

BEGIN BATCH
INSERT INTO students (id, name, age) VALUES (4, 'David', 23);
UPDATE students SET age = 26 WHERE id = 1;
DELETE FROM students WHERE id = 2;
APPLY BATCH;

---

⭐ 7. Conditional Updates (Lightweight Transactions)

UPDATE students 
SET age = 24 
WHERE id = 1 
IF age = 20;

INSERT INTO students (id, name)
VALUES (5, 'Eva')
IF NOT EXISTS;

---

⭐ 8. Secondary Index & Filtering

Create Index

CREATE INDEX ON students (age);

Filter Query

SELECT * FROM students 
WHERE age = 21
ALLOW FILTERING;

---

⭐ RESULT

• Collection types were successfully inserted and modified.

• TTL automatically deleted records.

• Batch operations allowed atomic multi-statements.

• LWT prevented race conditions by verifying conditions.

• Index improved filtering on non-key columns.