Cassandra CQL CRUD Commands Diagram — SELECT INSERT UPDATE DELETE Flow

SELECT, INSERT, UPDATE, and DELETE in Cassandra (CQL Commands Explained)

Overview

Cassandra Query Language (CQL) provides a familiar SQL-like syntax for performing data operations such as SELECT, INSERT, UPDATE, and DELETE. However, since Apache Cassandra is a distributed, NoSQL database, these operations behave differently from those in traditional relational systems. Understanding how these commands work helps developers write efficient queries that align with Cassandra’s partition-based architecture.

1. INSERT Command in Cassandra

The INSERT statement in CQL adds new data into a table. If a record with the same primary key already exists, Cassandra updates it automatically (similar to an UPSERT).

Syntax

INSERT INTO keyspace_name.table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

INSERT INTO orientalguru.users (user_id, username, email, signup_date)
VALUES (uuid(), 'shubham', 'shubham@orientalguru.co.in', toTimestamp(now()));

Key Notes:

• No explicit “update” flag is needed — Cassandra automatically overwrites existing rows with the same primary key.

• All writes are distributed across the cluster for high availability.

2. SELECT Command in Cassandra

The SELECT statement retrieves data from one or more partitions in a table. Cassandra’s query model requires that you use the partition key in your WHERE clause for efficient retrieval.

Syntax

SELECT [columns] FROM keyspace_name.table_name WHERE condition;

Example

SELECT user_id, username, email
FROM orientalguru.users
WHERE username = 'shubham';

Key Notes:

• Always include the partition key in your query.

• You can filter on clustering columns if needed.

• Avoid queries without WHERE — they cause full table scans, reducing performance.

3. UPDATE Command in Cassandra

The UPDATE statement modifies one or more columns in existing rows. If the row doesn’t exist, Cassandra will create it automatically.

Syntax

UPDATE keyspace_name.table_name
SET column1 = value1, column2 = value2
WHERE primary_key_condition;

Example

UPDATE orientalguru.users
SET email = 'newmail@orientalguru.co.in'
WHERE user_id = 55b940b0-2e43-11ef-9d2b-8b123456abcd;

Key Notes:

• Updates require the full primary key (partition + clustering keys).

• Cassandra doesn’t support WHERE filters like SQL (WHERE email = ...) unless it’s indexed.

4. DELETE Command in Cassandra

The DELETE command removes rows or specific columns from a table. In Cassandra, deletion doesn’t immediately remove data — it writes a tombstone marker, which is cleaned later during compaction.

Syntax

DELETE [columns] FROM keyspace_name.table_name WHERE condition;

Example

DELETE FROM orientalguru.users
WHERE user_id = 55b940b0-2e43-11ef-9d2b-8b123456abcd;

Key Notes:

• You must specify the partition key in WHERE.

• Tombstones are normal but can affect performance if overused.

Additional Examples

Insert and Query Multiple Rows

INSERT INTO orientalguru.users (user_id, username, email) VALUES (uuid(), 'ravi', 'ravi@orientalguru.co.in');
INSERT INTO orientalguru.users (user_id, username, email) VALUES (uuid(), 'aarti', 'aarti@orientalguru.co.in');

SELECT * FROM orientalguru.users;

Delete Specific Column

DELETE email FROM orientalguru.users WHERE user_id = <uuid>;

Batch Example

BEGIN BATCH
INSERT INTO orientalguru.users (user_id, username, email) VALUES (uuid(), 'raj', 'raj@orientalguru.co.in');
UPDATE orientalguru.users SET email = 'raj_updated@orientalguru.co.in' WHERE username = 'raj';
APPLY BATCH;

Best Practices

Conclusion

CQL’s core data manipulation commands — SELECT, INSERT, UPDATE, and DELETE — make it easy to work with Cassandra’s distributed data model. By following partition-based query patterns and efficient key design, developers can achieve fast, scalable, and reliable data access in any Cassandra cluster.