Column-Family Databases

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Development keyspace (single datacenter, low replication)
CREATE KEYSPACE IF NOT EXISTS dev_app
WITH REPLICATION = {
    'class': 'SimpleStrategy',
    'replication_factor': 1
};
 
-- Production keyspace (multi-datacenter, high availability)
CREATE KEYSPACE IF NOT EXISTS prod_app
WITH REPLICATION = {
    'class': 'NetworkTopologyStrategy',
    'us-east-1': 3,      -- 3 replicas in US East
    'eu-west-1': 3,      -- 3 replicas in EU West
    'ap-south-1': 2      -- 2 replicas in Asia Pacific
}
AND DURABLE_WRITES = true;  -- Commit log enabled (default)
 
-- Switch to keyspace
USE prod_app;
 
-- View keyspace details
DESCRIBE KEYSPACE prod_app;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Simple primary key: single column partition key
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email TEXT,
    name TEXT,
    created_at TIMESTAMP
);
 
-- Compound primary key: partition key + clustering column
-- Partition key: user_id
-- Clustering column: post_time (orders data within partition)
CREATE TABLE user_posts (
    user_id UUID,
    post_time TIMESTAMP,
    post_id UUID,
    content TEXT,
    PRIMARY KEY (user_id, post_time)
) WITH CLUSTERING ORDER BY (post_time DESC);  -- Most recent first
 
-- Composite partition key: multiple columns form partition key
-- Partition key: (tenant_id, user_id) -- both required for lookups
-- Useful for multi-tenant applications
CREATE TABLE tenant_user_events (
    tenant_id UUID,
    user_id UUID,
    event_time TIMESTAMP,
    event_type TEXT,
    event_data TEXT,
    PRIMARY KEY ((tenant_id, user_id), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
 
-- Multiple clustering columns: hierarchical sorting
CREATE TABLE sensor_readings (
    sensor_id UUID,
    year INT,
    month INT,
    day INT,
    reading_time TIMESTAMP,
    value DECIMAL,
    PRIMARY KEY (sensor_id, year, month, day, reading_time)
) WITH CLUSTERING ORDER BY (year DESC, month DESC, day DESC, reading_time DESC);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- LIST: Ordered collection, allows duplicates
-- Use for: Order history, comment threads
CREATE TABLE articles (
    article_id UUID PRIMARY KEY,
    title TEXT,
    tags LIST<TEXT>,              -- ['tech', 'database', 'cassandra']
    related_ids LIST<UUID>
);
 
-- Update list
UPDATE articles SET tags = tags + ['nosql'] WHERE article_id = ?;
UPDATE articles SET tags = ['new_tag'] + tags WHERE article_id = ?;  -- Prepend
UPDATE articles SET tags[0] = 'updated' WHERE article_id = ?;        -- By index
 
-- SET: Unordered collection, no duplicates
-- Use for: Unique tags, roles, permissions
CREATE TABLE user_settings (
    user_id UUID PRIMARY KEY,
    roles SET<TEXT>,              -- {'admin', 'user', 'moderator'}
    favorite_categories SET<TEXT>
);
 
-- Update set
UPDATE user_settings SET roles = roles + {'editor'} WHERE user_id = ?;
UPDATE user_settings SET roles = roles - {'user'} WHERE user_id = ?;
 
-- MAP: Key-value pairs
-- Use for: Attributes, metadata, preferences
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    attributes MAP<TEXT, TEXT>,   -- {'color': 'blue', 'size': 'XL'}
    prices MAP<TEXT, DECIMAL>     -- {'USD': 99.99, 'EUR': 89.99}
);
 
-- Update map
UPDATE products SET attributes['weight'] = '500g' WHERE product_id = ?;
UPDATE products SET attributes = attributes + {'material': 'cotton'} WHERE product_id = ?;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Create UDT for address
CREATE TYPE address (
    street TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    country TEXT
);
 
-- Create UDT for contact info
CREATE TYPE contact_info (
    email TEXT,
    phone TEXT,
    address FROZEN<address>  -- Nested UDT must be frozen
);
 
-- Use UDT in table
CREATE TABLE customers (
    customer_id UUID PRIMARY KEY,
    name TEXT,
    billing_address FROZEN<address>,
    shipping_address FROZEN<address>,
    contact FROZEN<contact_info>
);
 
-- Insert with UDT
INSERT INTO customers (customer_id, name, billing_address)
VALUES (
    uuid(),
    'John Doe',
    {
        street: '123 Main St',
        city: 'New York',
        state: 'NY',
        zip_code: '10001',
        country: 'USA'
    }
);
 
-- Query UDT fields
SELECT name, billing_address.city FROM customers WHERE customer_id = ?;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Table for examples
CREATE TABLE orders (
    customer_id UUID,
    order_date DATE,
    order_time TIMESTAMP,
    order_id UUID,
    total DECIMAL,
    status TEXT,
    PRIMARY KEY (customer_id, order_date, order_time)
) WITH CLUSTERING ORDER BY (order_date DESC, order_time DESC);
 
-- ✅ EFFICIENT: Full partition key with equality
SELECT * FROM orders WHERE customer_id = ?;
 
-- ✅ EFFICIENT: Partition key + clustering column range
SELECT * FROM orders 
WHERE customer_id = ?
AND order_date >= '2024-01-01' 
AND order_date <= '2024-01-31';
 
-- ✅ EFFICIENT: Partition key + prefix of clustering columns
SELECT * FROM orders
WHERE customer_id = ?
AND order_date = '2024-01-15';
 
-- ❌ INEFFICIENT: Missing partition key (full cluster scan)
SELECT * FROM orders WHERE order_date = '2024-01-15';
-- Error: Cannot execute this query... ALLOW FILTERING
 
-- ❌ INEFFICIENT: Skipping clustering column
SELECT * FROM orders
WHERE customer_id = ?
AND order_time > '2024-01-15T10:00:00';  -- Skipped order_date!
-- Error: PRIMARY KEY column "order_date" cannot be restricted
 
-- ⚠️ DANGEROUS: Non-key column filter
SELECT * FROM orders
WHERE customer_id = ?
AND status = 'pending'
ALLOW FILTERING;  -- Scans all rows for this customer!
 
-- ✅ EFFICIENT: Ordering matches clustering order
SELECT * FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC, order_time DESC;
 
-- ❌ INVALID: Ordering conflicts with clustering
SELECT * FROM orders
WHERE customer_id = ?
ORDER BY order_date ASC;  -- Table ordered DESC!
-- Error: Order by currently only supports the ordering specified at table definition

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Problem: IoT sensor data with millions of readings
-- Query: Get recent readings for a sensor
 
-- Bad design: All readings in one partition
CREATE TABLE sensor_data_bad (
    sensor_id UUID,
    reading_time TIMESTAMP,
    value DECIMAL,
    PRIMARY KEY (sensor_id, reading_time)
);
-- Problem: Partition grows unbounded!
 
-- Good design: Time-bucketed partitions
CREATE TABLE sensor_data (
    sensor_id UUID,
    day DATE,                      -- Bucket by day
    reading_time TIMESTAMP,
    value DECIMAL,
    PRIMARY KEY ((sensor_id, day), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);
 
-- Query today's readings for a sensor
SELECT * FROM sensor_data
WHERE sensor_id = ? AND day = toDate(now())
LIMIT 100;
 
-- Query last 7 days (requires 7 queries, one per partition)
-- Application-side: Loop over date range
-- Or use: IN clause (limited to small sets)
SELECT * FROM sensor_data
WHERE sensor_id = ?
AND day IN ('2024-01-15', '2024-01-14', '2024-01-13', ...)
LIMIT 100;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

-- Q1: Get user profile by user_id
-- Simple key-value lookup
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username TEXT,
    email TEXT,
    bio TEXT,
    profile_image_url TEXT,
    created_at TIMESTAMP
);
 
-- Q2: Get all posts by a user, ordered by time
-- Partition by user, cluster by time
CREATE TABLE posts_by_user (
    user_id UUID,
    post_id TIMEUUID,
    content TEXT,
    media_urls SET<TEXT>,
    like_count COUNTER,     -- Counter for likes
    PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);
 
-- Q3: Get all comments on a post, ordered by time
-- Partition by post, cluster by time
CREATE TABLE comments_by_post (
    post_id TIMEUUID,
    comment_id TIMEUUID,
    user_id UUID,
    content TEXT,
    PRIMARY KEY (post_id, comment_id)
) WITH CLUSTERING ORDER BY (comment_id ASC);  -- Oldest first
 
-- Q4: Get user's timeline
-- Denormalized timeline per user (fan-out on write)
CREATE TABLE timeline_by_user (
    user_id UUID,
    post_id TIMEUUID,
    author_id UUID,
    author_username TEXT,        -- Denormalized! Avoids join
    content_preview TEXT,        -- First 200 chars
    PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC)
AND default_time_to_live = 2592000;  -- 30 days TTL
 
-- Q5: Get trending posts in a category
-- Bucketed by time window + category
CREATE TABLE trending_posts (
    time_bucket TEXT,            -- '2024-01-15T10' (hourly bucket)
    category TEXT,
    score DECIMAL,               -- Trending score
    post_id TIMEUUID,
    author_username TEXT,
    content_preview TEXT,
    PRIMARY KEY ((time_bucket, category), score, post_id)
) WITH CLUSTERING ORDER BY (score DESC, post_id DESC);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Logged batch: Atomic within same partition
-- Use when: Updates affect same partition
BEGIN BATCH
    INSERT INTO user_posts (user_id, post_id, content) 
    VALUES (?, ?, ?);
    
    UPDATE users SET post_count = post_count + 1 
    WHERE user_id = ?;
APPLY BATCH;
 
-- UNLOGGED batch: No atomicity, slightly faster
-- Use when: Updates affect different partitions but 
-- you want them sent together (reduces round trips)
BEGIN UNLOGGED BATCH
    INSERT INTO posts_by_user (user_id, post_id, content)
    VALUES (?, ?, ?);
    
    INSERT INTO posts_by_category (category, post_id, user_id, content)
    VALUES (?, ?, ?, ?);
APPLY BATCH;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- IF NOT EXISTS: Insert only if row doesn't exist
-- Use for: User registration (unique email)
INSERT INTO users (user_id, email, username)
VALUES (uuid(), 'alice@example.com', 'alice')
IF NOT EXISTS;
 
-- Returns: [applied] = true/false
 
-- Result handling:
-- [applied] | user_id   | email              | username
-- true      | null      | null               | null       <- Success
-- false     | abc-123   | alice@example.com  | alice      <- Already exists
 
-- IF condition: Update only if condition matches
-- Use for: Optimistic locking, state transitions
UPDATE orders
SET status = 'shipped'
WHERE order_id = ?
IF status = 'processing';  -- Only if currently 'processing'
 
-- IF EXISTS: Update only if row exists
UPDATE users
SET email = 'newemail@example.com'
WHERE user_id = ?
IF EXISTS;
 
-- Multiple conditions
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = ?
IF quantity > 0  -- Don't go negative
AND reserved = false;  -- Not reserved
 
-- Delete with condition
DELETE FROM sessions
WHERE user_id = ?
IF session_token = ?;  -- Validate token before delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- Create secondary index
CREATE INDEX users_email_idx ON users (email);
 
-- Now this query works without ALLOW FILTERING
SELECT * FROM users WHERE email = 'alice@example.com';
 
-- Index on collection column
CREATE INDEX articles_tags_idx ON articles (tags);
 
-- Query collection index
SELECT * FROM articles WHERE tags CONTAINS 'cassandra';
 
-- Index on map keys or values
CREATE INDEX product_attrs_keys_idx ON products (KEYS(attributes));
CREATE INDEX product_attrs_values_idx ON products (VALUES(attributes));
CREATE INDEX product_attrs_entries_idx ON products (ENTRIES(attributes));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Primary table
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email TEXT,
    username TEXT
);
 
-- Lookup table: email → user_id
CREATE TABLE users_by_email (
    email TEXT PRIMARY KEY,
    user_id UUID
);
 
-- Insert requires both tables (batch or async)
BEGIN BATCH
    INSERT INTO users (user_id, email, username) 
    VALUES (?, ?, ?);
    INSERT INTO users_by_email (email, user_id) 
    VALUES (?, ?);
APPLY BATCH;
 
-- Query: Find user by email
-- Step 1: Get user_id from lookup table
SELECT user_id FROM users_by_email WHERE email = ?;
-- Step 2: Get full user record
SELECT * FROM users WHERE user_id = ?;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Create table with specific compaction strategy
CREATE TABLE metrics (
    sensor_id UUID,
    hour TIMESTAMP,
    value DECIMAL,
    PRIMARY KEY (sensor_id, hour)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'HOURS',
    'compaction_window_size': 1
}
AND default_time_to_live = 604800;  -- 7 days TTL
 
-- Change compaction strategy (online migration)
ALTER TABLE users WITH compaction = {
    'class': 'LeveledCompactionStrategy',
    'sstable_size_in_mb': 160
};
 
-- Check compaction status
SELECT * FROM system.compaction_history;