Resources
CQL Cheatsheet
Complete Cassandra Query Language reference - all commands, syntax, and examples in one place!
🔑 Keyspace Operations
CREATE, ALTER, DROP, DESCRIBE
📊 Table Operations
CREATE, ALTER, DROP, TRUNCATE
✍️ Data Manipulation
INSERT, SELECT, UPDATE, DELETE
📦 Advanced
BATCH, UDT, INDEX, MV
🔑 Keyspace Operations
CREATE KEYSPACE
-- Simple (Single DC)
CREATE KEYSPACE my_keyspace
WITH REPLICATION = {
'class': 'SimpleStrategy',
'replication_factor': 3
};
-- Production (Multi-DC)
CREATE KEYSPACE my_keyspace
WITH REPLICATION = {
'class': 'NetworkTopologyStrategy',
'datacenter1': 3,
'datacenter2': 2
} AND durable_writes = true;
-- With Options
CREATE KEYSPACE IF NOT EXISTS my_keyspace
WITH REPLICATION = {
'class': 'NetworkTopologyStrategy',
'us-east': 3,
'us-west': 2
};
ALTER KEYSPACE
-- Change replication
ALTER KEYSPACE my_keyspace
WITH REPLICATION = {
'class': 'NetworkTopologyStrategy',
'datacenter1': 5
};
-- Disable durable writes (NOT recommended!)
ALTER KEYSPACE my_keyspace
WITH durable_writes = false;
DROP KEYSPACE
DROP KEYSPACE my_keyspace;
DROP KEYSPACE IF EXISTS my_keyspace;
DESCRIBE KEYSPACE
DESCRIBE KEYSPACES; -- List all
DESCRIBE KEYSPACE my_keyspace; -- Show DDL
DESC my_keyspace; -- Shorthand
📊 Table Operations
CREATE TABLE
-- Simple Table
CREATE TABLE users (
user_id uuid PRIMARY KEY,
username text,
email text,
created_at timestamp
);
-- Composite Partition Key
CREATE TABLE time_series (
sensor_id text,
date text,
timestamp timeuuid,
value double,
PRIMARY KEY ((sensor_id, date), timestamp)
);
-- With Clustering Order
CREATE TABLE messages (
thread_id uuid,
message_time timestamp,
sender text,
content text,
PRIMARY KEY (thread_id, message_time)
) WITH CLUSTERING ORDER BY (message_time DESC);
-- With Table Properties
CREATE TABLE products (
product_id uuid PRIMARY KEY,
name text,
price decimal
) WITH
comment = 'Product catalog' AND
compaction = {'class': 'LeveledCompactionStrategy'} AND
compression = {'enabled': true} AND
gc_grace_seconds = 864000 AND
read_repair_chance = 0.1;
ALTER TABLE
-- Add column
ALTER TABLE users ADD phone text;
ALTER TABLE users ADD (age int, city text);
-- Drop column
ALTER TABLE users DROP phone;
-- Rename column
ALTER TABLE users RENAME username TO user_name;
-- Change table properties
ALTER TABLE users WITH
gc_grace_seconds = 691200 AND
compaction = {'class': 'SizeTieredCompactionStrategy'};
DROP & TRUNCATE
DROP TABLE users;
DROP TABLE IF EXISTS users;
TRUNCATE TABLE users; -- Delete all data
➕ INSERT Statements
Basic INSERT
-- Simple insert
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'alice', 'alice@example.com');
-- With all columns
INSERT INTO users (user_id, username, email, created_at)
VALUES (
uuid(),
'bob',
'bob@example.com',
toTimestamp(now())
);
INSERT with Options
-- With TTL (Time To Live)
INSERT INTO sessions (session_id, user_id, data)
VALUES (uuid(), uuid(), 'session_data')
USING TTL 3600; -- Expires in 1 hour
-- With Timestamp
INSERT INTO events (event_id, data)
VALUES (uuid(), 'event_data')
USING TIMESTAMP 1640000000000; -- Microseconds
-- With both TTL and Timestamp
INSERT INTO cache (key, value)
VALUES ('key1', 'value1')
USING TTL 300 AND TIMESTAMP 1640000000000;
INSERT IF NOT EXISTS (LWT)
-- Lightweight transaction (atomic)
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'charlie', 'charlie@example.com')
IF NOT EXISTS;
-- Check result: [applied] = true/false
🔍 SELECT Queries
Basic SELECT
-- Select all
SELECT * FROM users;
-- Select specific columns
SELECT user_id, username, email FROM users;
-- With WHERE (must include partition key!)
SELECT * FROM users
WHERE user_id = '123e4567-e89b-12d3-a456-426614174000';
WHERE Clause Patterns
-- Partition key only
SELECT * FROM messages
WHERE thread_id = uuid();
-- Partition key + clustering column range
SELECT * FROM messages
WHERE thread_id = uuid()
AND message_time > '2024-01-01'
AND message_time < '2024-12-31';
-- With IN clause
SELECT * FROM users
WHERE user_id IN (uuid1, uuid2, uuid3);
-- Composite partition key
SELECT * FROM time_series
WHERE sensor_id = 'sensor_1'
AND date = '2024-01-15';
LIMIT & ORDER BY
-- Limit results
SELECT * FROM users LIMIT 10;
-- Per-partition limit
SELECT * FROM messages
WHERE thread_id = uuid()
PER PARTITION LIMIT 5;
-- Order by clustering column
SELECT * FROM messages
WHERE thread_id = uuid()
ORDER BY message_time DESC;
Aggregates & Functions
-- Count
SELECT COUNT(*) FROM users;
-- Min/Max/Avg/Sum
SELECT MAX(price), MIN(price), AVG(price)
FROM products;
-- DISTINCT
SELECT DISTINCT category FROM products;
-- TTL and WRITETIME
SELECT key, value, TTL(value), WRITETIME(value)
FROM cache;
ALLOW FILTERING (Use Carefully!)
-- Query non-key column (SLOW! Full table scan)
SELECT * FROM users
WHERE email = 'alice@example.com'
ALLOW FILTERING;
-- ⚠️ WARNING: Use only on small tables or for admin queries
✏️ UPDATE Statements
Basic UPDATE
-- Simple update
UPDATE users
SET email = 'newemail@example.com'
WHERE user_id = uuid();
-- Update multiple columns
UPDATE users
SET email = 'alice@new.com',
phone = '+1-555-0123',
updated_at = toTimestamp(now())
WHERE user_id = uuid();
UPDATE with Options
-- With TTL
UPDATE sessions USING TTL 3600
SET data = 'session_data'
WHERE session_id = uuid();
-- With Timestamp
UPDATE events USING TIMESTAMP 1640000000000
SET status = 'completed'
WHERE event_id = uuid();
Counter UPDATE
-- Increment counter
UPDATE page_views
SET views = views + 1
WHERE page_id = 'homepage';
-- Decrement counter
UPDATE inventory
SET stock = stock - 1
WHERE product_id = uuid();
Collection UPDATE
-- List: Append
UPDATE users
SET tags = tags + ['new_tag']
WHERE user_id = uuid();
-- Set: Add elements
UPDATE users
SET interests = interests + {'music', 'sports'}
WHERE user_id = uuid();
-- Map: Add/Update entry
UPDATE users
SET settings['theme'] = 'dark'
WHERE user_id = uuid();
UPDATE IF (LWT)
-- Conditional update
UPDATE accounts
SET balance = balance - 50
WHERE user_id = uuid()
IF balance >= 50;
-- Multiple conditions
UPDATE orders
SET status = 'shipped'
WHERE order_id = uuid()
IF status = 'paid' AND inventory > 0;
🗑️ DELETE Statements
Basic DELETE
-- Delete entire row
DELETE FROM users
WHERE user_id = uuid();
-- Delete specific columns
DELETE email, phone FROM users
WHERE user_id = uuid();
-- Delete with clustering key
DELETE FROM messages
WHERE thread_id = uuid()
AND message_time = '2024-01-15 10:30:00';
DELETE with Options
-- Delete with timestamp
DELETE FROM events USING TIMESTAMP 1640000000000
WHERE event_id = uuid();
DELETE IF (LWT)
-- Conditional delete
DELETE FROM locks
WHERE resource = 'file_123'
IF owner = 'process_A';
Collection DELETE
-- Remove from list
UPDATE users
SET tags = tags - ['old_tag']
WHERE user_id = uuid();
-- Remove from set
UPDATE users
SET interests = interests - {'old_interest'}
WHERE user_id = uuid();
-- Delete map entry
DELETE settings['old_setting'] FROM users
WHERE user_id = uuid();
📦 BATCH Statements
BATCH Operations
-- Logged batch (atomic within partition)
BEGIN BATCH
INSERT INTO users (user_id, username)
VALUES (uuid(), 'alice');
UPDATE user_stats
SET total_users = total_users + 1
WHERE stat_id = 'global';
APPLY BATCH;
-- Unlogged batch (better performance, not atomic)
BEGIN UNLOGGED BATCH
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (...) VALUES (...);
APPLY BATCH;
-- Batch with timestamp
BEGIN BATCH USING TIMESTAMP 1640000000000
UPDATE table1 SET col = 'val' WHERE id = 1;
DELETE FROM table2 WHERE id = 2;
APPLY BATCH;
BATCH Best Practices
✅ Use BATCH for: Writing to multiple tables with same partition key
❌ DON'T use BATCH for: Bulk loading (use concurrent INSERT instead)
⚠️ Keep batches small (< 10 statements) to avoid performance issues
🔤 Data Types
Text Types
text, varchar
ascii
Numeric Types
int, bigint, smallint, tinyint
float, double
decimal
varint
UUID/Time
uuid
timeuuid
timestamp
date, time
duration
Other Types
boolean
blob
inet
counter
Common Type Examples
CREATE TABLE examples (
id uuid PRIMARY KEY,
-- Text
name text,
description varchar,
code ascii,
-- Numbers
age int,
balance decimal,
big_number varint,
rating double,
-- UUID/Time
uuid_col uuid,
time_uuid timeuuid,
created_at timestamp,
birth_date date,
login_time time,
-- Other
is_active boolean,
ip_address inet,
binary_data blob
);
📚 Collections
Collection Types
CREATE TABLE users_with_collections (
user_id uuid PRIMARY KEY,
-- List (ordered, duplicates allowed)
tags list,
-- Set (unordered, unique values)
interests set,
-- Map (key-value pairs)
settings map,
phone_numbers map
);
Working with Collections
-- INSERT with collections
INSERT INTO users_with_collections (user_id, tags, interests, settings)
VALUES (
uuid(),
['premium', 'verified'],
{'music', 'sports'},
{'theme': 'dark', 'lang': 'en'}
);
-- Add to list
UPDATE users_with_collections
SET tags = tags + ['new_tag']
WHERE user_id = uuid();
-- Add to set
UPDATE users_with_collections
SET interests = interests + {'reading'}
WHERE user_id = uuid();
-- Update map entry
UPDATE users_with_collections
SET settings['notifications'] = 'enabled'
WHERE user_id = uuid();
📑 Secondary Indexes
-- Create index
CREATE INDEX users_email_idx ON users (email);
CREATE INDEX ON users (city); -- Auto-named
-- Create index on collection
CREATE INDEX ON users (interests); -- Set/List
CREATE INDEX ON users (KEYS(settings)); -- Map keys
CREATE INDEX ON users (VALUES(settings)); -- Map values
-- Drop index
DROP INDEX users_email_idx;
DROP INDEX IF EXISTS users_email_idx;
🎭 User-Defined Types (UDT)
-- Create UDT
CREATE TYPE address (
street text,
city text,
state text,
zip text
);
-- Use UDT in table
CREATE TABLE users_with_address (
user_id uuid PRIMARY KEY,
name text,
home_address frozen,
work_address frozen
);
-- Insert with UDT
INSERT INTO users_with_address (user_id, name, home_address)
VALUES (
uuid(),
'Alice',
{street: '123 Main St', city: 'NYC', state: 'NY', zip: '10001'}
);
-- Drop UDT
DROP TYPE address;
⚙️ Built-in Functions
-- UUID functions
uuid() -- Generate random UUID
now() -- Current timeuuid
-- Time functions
toTimestamp(now()) -- Convert to timestamp
toDate(now()) -- Convert to date
toUnixTimestamp(now()) -- Unix timestamp
-- Type conversion
blobAsInt(blob_col) -- Blob to int
intAsBlob(123) -- Int to blob
textAsBlob('text') -- Text to blob
-- Collection functions
token(partition_key) -- Get token value
Quick Tips
✅ DO:
- Always include partition key in WHERE
- Use prepared statements
- Set appropriate TTL
- Use NetworkTopologyStrategy
❌ DON'T:
- Use ALLOW FILTERING in production
- Make huge batches (>10 statements)
- Use SELECT * on large tables
- Ignore consistency levels
Advertisement
Responsive Ad