Operations on table in Cassandra

Cassandra uses CQL (Cassandra Query Language) to perform operations on tables. CQL is similar to SQL but designed for Cassandra's distributed, NoSQL architecture. The main table operations are CREATE, INSERT, UPDATE, DELETE, SELECT, ALTER, and DROP.

CREATE TABLE

CREATE TABLE sample_table (
    id INT PRIMARY KEY,
    name TEXT,
    age INT,
    email TEXT
);

INSERT Data

INSERT INTO sample_table (id, name, age, email)
VALUES (1, 'John Doe', 35, 'john@example.com');

INSERT INTO sample_table (id, name, age, email)
VALUES (2, 'Jane Doe', 30, 'jane@example.com');

INSERT INTO sample_table (id, name, age, email)
VALUES (3, 'Bob Smith', 45, 'bob@example.com');

UPDATE Data

Use UPDATE with a WHERE clause on the primary key ?

UPDATE sample_table SET age = 40 WHERE id = 2;

DELETE Data

DELETE FROM sample_table WHERE id = 3;

SELECT Data

-- Select all columns
SELECT * FROM sample_table;

-- Filter by condition
SELECT * FROM sample_table WHERE id = 1;

-- Select specific columns
SELECT name, email FROM sample_table WHERE id = 2;

Note: In Cassandra, WHERE clauses are restricted to primary key columns and indexed columns. ORDER BY requires a clustering column.

ALTER TABLE

Add or remove columns from an existing table ?

-- Add a column
ALTER TABLE sample_table ADD surname TEXT;

-- Drop a column
ALTER TABLE sample_table DROP email;

DROP TABLE

DROP TABLE sample_table;

Operations Summary

Conclusion

Cassandra's CQL provides SQL-like table operations (CREATE, INSERT, UPDATE, DELETE, SELECT, ALTER, DROP) adapted for its distributed architecture. Key differences from SQL include restrictions on WHERE clauses (must include primary key) and no support for JOINs or subqueries.