Write, Run & Share Cassandra CQL queries online using OneCompiler's Cassandra online editor for free. It's one of the robust, feature-rich online editors for Cassandra. Getting started with the OneCompiler's Cassandra editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'Cassandra' and start writing queries to learn and test online without worrying about tedious process of installation.
Apache Cassandra is a free and open-source, distributed, wide-column store, NoSQL database management system designed to handle large amounts of data with high availability and no single point of failure. It uses CQL (Cassandra Query Language), which is similar to SQL.
Creates a new table with specified columns and data types. Every table must have a PRIMARY KEY which uniquely identifies each row and determines how data is distributed across the cluster. The primary key design is crucial in Cassandra - it affects both data distribution and query performance. Choose your primary key carefully based on your most common query patterns.
CREATE TABLE EMPLOYEE (
empId INT PRIMARY KEY,
name TEXT,
dept TEXT
);
Adds new rows to a table. In Cassandra, INSERT also acts as an upsert operation - if a row with the same primary key already exists, it will be updated with the new values. This behavior is different from traditional SQL databases where INSERT would fail on duplicate keys. Column names are optional if values are provided in the exact order of table columns.
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (3, 'Ava', 'Sales');
Retrieves data from a table. Queries that filter on primary key columns are highly efficient as Cassandra can directly locate the data. For queries on non-primary key columns, you must add ALLOW FILTERING which performs a full table scan - use this carefully in production as it can be slow on large tables. Design your data model so that common queries can use the primary key.
-- Select all
SELECT * FROM EMPLOYEE;
-- Select with condition (requires ALLOW FILTERING for non-primary key columns)
SELECT * FROM EMPLOYEE WHERE dept = 'Sales' ALLOW FILTERING;
-- Select by primary key (no ALLOW FILTERING needed)
SELECT * FROM EMPLOYEE WHERE empId = 1;
Modifies existing data in a table. The WHERE clause must include the complete primary key to identify which row to update. An interesting behavior in Cassandra is that UPDATE will create the row if it doesn't exist, making it functionally similar to INSERT. This upsert behavior is consistent across write operations and simplifies application logic in many cases.
UPDATE EMPLOYEE SET dept = 'Marketing' WHERE empId = 1;
Removes rows or specific columns from a table. You can delete an entire row by specifying just the primary key, or delete specific column values while keeping the row. The WHERE clause must include the primary key to identify which rows to delete. Cassandra uses tombstones to mark deleted data, which are cleaned up during compaction.
-- Delete a row
DELETE FROM EMPLOYEE WHERE empId = 1;
-- Delete a specific column
DELETE dept FROM EMPLOYEE WHERE empId = 2;
Cassandra supports three powerful collection types for storing multiple values in a single column. SET stores unique unordered values, LIST stores ordered values that can have duplicates, and MAP stores key-value pairs. Collections are useful for one-to-many relationships without needing separate tables. You can add or remove elements from collections without reading the entire collection first.
CREATE TABLE user_profile (
user_id INT PRIMARY KEY,
tags SET<TEXT>,
scores LIST<INT>,
attributes MAP<TEXT, TEXT>
);
INSERT INTO user_profile (user_id, tags, scores, attributes)
VALUES (1, {'developer', 'blogger'}, [95, 88, 92], {'city': 'NYC', 'role': 'admin'});
-- Add to set
UPDATE user_profile SET tags = tags + {'writer'} WHERE user_id = 1;
-- Append to list
UPDATE user_profile SET scores = scores + [100] WHERE user_id = 1;
-- Add to map
UPDATE user_profile SET attributes['department'] = 'Engineering' WHERE user_id = 1;
SELECT * FROM user_profile;
Composite primary keys consist of a partition key and one or more clustering columns. The partition key determines which node in the cluster stores the data, while clustering columns define the sort order of rows within that partition. This design enables efficient range queries within a partition - you can quickly retrieve all rows for a user sorted by timestamp, for example.
CREATE TABLE posts (
user_id INT,
post_id INT,
title TEXT,
content TEXT,
PRIMARY KEY (user_id, post_id)
);
INSERT INTO posts (user_id, post_id, title, content) VALUES (1, 1, 'First Post', 'Hello World');
INSERT INTO posts (user_id, post_id, title, content) VALUES (1, 2, 'Second Post', 'More content');
-- Query by partition key
SELECT * FROM posts WHERE user_id = 1;
TTL automatically expires and removes data after a specified number of seconds. This is extremely useful for temporary data like user sessions, cache entries, verification tokens, or any data that should be automatically cleaned up after a certain period. Using TTL is more efficient than manual deletes as Cassandra handles the cleanup automatically during compaction.
-- Insert with TTL (expires after 3600 seconds)
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (99, 'Temp', 'Contract') USING TTL 3600;
-- Update with TTL
UPDATE EMPLOYEE USING TTL 7200 SET dept = 'Intern' WHERE empId = 99;
Batches group multiple statements to be executed together atomically. They ensure all-or-nothing execution - either all statements succeed or none do, which is important for maintaining data consistency across related tables. However, avoid large batches as they can impact cluster performance. Batches are best used for keeping denormalized data in sync across a few related tables.
BEGIN BATCH
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (4, 'Bob', 'Engineering');
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (5, 'Eve', 'HR');
UPDATE EMPLOYEE SET dept = 'Finance' WHERE empId = 2;
APPLY BATCH;
Cassandra provides built-in functions for generating unique identifiers and working with timestamps. The uuid() function generates random UUIDs which are useful for primary keys when you don't have a natural identifier. The now() function generates time-based UUIDs (TimeUUID) that include a timestamp component. Use toTimestamp() to convert TimeUUIDs to human-readable timestamps for display or comparison.
-- Generate UUID
SELECT uuid();
-- Current timestamp
SELECT toTimestamp(now());
Modify table structure by adding or removing columns using ALTER TABLE commands. Adding columns is a lightweight operation that doesn't affect existing data. DROP TABLE removes the table and all its data permanently. TRUNCATE is useful when you want to remove all data but keep the table structure intact for future use - it's faster than deleting all rows individually.
-- Add column
ALTER TABLE EMPLOYEE ADD email TEXT;
-- Drop column
ALTER TABLE EMPLOYEE DROP email;
-- Truncate table
TRUNCATE EMPLOYEE;
-- Drop table
DROP TABLE EMPLOYEE;
