Section 7: CQL Complete Guide

Master CREATE TABLE

Complete guide to creating tables in Cassandra - primary keys, clustering columns, data types, and real-world examples!

Home › Section 7: CQL Guide › Tables › CREATE TABLE

🎯 What is CREATE TABLE?

CREATE TABLE in Cassandra

The CREATE TABLE command defines the structure of your data in Cassandra. Unlike SQL, you MUST design tables based on your queries, not just your data relationships!

🎯 Key Concept:

In SQL: Design normalized tables, then write queries
In Cassandra: Design queries first, then create tables to serve those queries!

✅ What Makes Cassandra Tables Special

  • Primary Key determines data distribution
  • Partition Key controls which node stores data
  • Clustering Columns sort data within partition
  • No JOINs - denormalize everything

❌ What Cassandra Tables Don't Have

  • No Foreign Keys - no relationships
  • No Indexes by default - query by primary key
  • No ALTER to change primary key
  • No referential integrity - your job!

📖 Basic Syntax

Complete CREATE TABLE Syntax

Full Syntax
CREATE TABLE [IF NOT EXISTS] keyspace_name.table_name (
  column1 datatype [STATIC],
  column2 datatype,
  column3 datatype,
  ...
  PRIMARY KEY (partition_key [, clustering_column...])
) [WITH option1 = value1
  [AND option2 = value2 ...]];

-- Alternative syntax for composite keys:
PRIMARY KEY ((partition_col1, partition_col2), clustering_col1, clustering_col2)

Simplest Possible Table

Basic Example
-- Most basic table (single primary key)
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  username TEXT,
  email TEXT
);

-- Insert data
INSERT INTO users (user_id, username, email)
VALUES (uuid(), 'alice', 'alice@example.com');

-- Query by primary key (FAST!)
SELECT * FROM users WHERE user_id = ?;

🔑 Understanding Primary Keys

PRIMARY KEY is THE Most Important Decision

The primary key determines:
✓ Which server stores your data
✓ How data is sorted
✓ What queries you can run
✓ Performance of reads and writes
⚠️ Cannot be changed after table creation!

Primary Key Types

Simple Primary Key

One column = partition key

CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  name TEXT
);

-- All data for one user_id on same node

Composite Primary Key

Partition key + clustering columns

CREATE TABLE user_posts (
  user_id UUID,
  post_id TIMEUUID,
  content TEXT,
  PRIMARY KEY (user_id, post_id)
);

-- user_id = partition key
-- post_id = clustering column

Compound Partition Key

Multiple columns in partition key

CREATE TABLE posts_by_category (
  category TEXT,
  year INT,
  post_id UUID,
  title TEXT,
  PRIMARY KEY ((category, year), post_id)
);

-- (category, year) = partition key
-- post_id = clustering column

How to Choose Primary Key

Question Answer What to Do
How will I query? By user_id user_id = partition key
Do I need sorting? Yes, by date Add date as clustering column
Large partition? 100M records per user Add year/month to partition key
Hot partition? One user = 90% of traffic Redesign to distribute load

🔤 Common Data Types

Category Type Description Example
Text TEXT UTF-8 strings 'Hello World'
VARCHAR Same as TEXT 'Alice'
ASCII US-ASCII only 'user123'
Numbers INT 32-bit signed integer 42
BIGINT 64-bit signed integer 9876543210
FLOAT 32-bit floating point 3.14
DECIMAL Variable precision 19.99
IDs UUID Universal unique ID 550e8400-e29b-41d4-a716-446655440000
TIMEUUID Time-based UUID Used for time-ordered data
Time TIMESTAMP Date + time (milliseconds) '2025-01-15 14:30:00'
DATE Just date '2025-01-15'
Collections SET<type> Unique values {'red', 'blue', 'green'}
LIST<type> Ordered, duplicates OK ['apple', 'banana', 'apple']
MAP<k,v> Key-value pairs {'name': 'Alice', 'age': '25'}
Complete Type Example
CREATE TABLE users_complete (
  user_id UUID PRIMARY KEY,
  username TEXT,
  age INT,
  balance DECIMAL,
  is_active BOOLEAN,
  created_at TIMESTAMP,
  tags SET<TEXT>,
  login_history LIST<TIMESTAMP>,
  preferences MAP<TEXT, TEXT>
);

📊 Clustering Columns & Sorting

What are Clustering Columns?

Clustering columns determine the sort order of data within a partition. Think of it like chapters in a book - the partition key is the book, clustering columns organize the chapters!

Clustering Order Examples

-- Example 1: User posts sorted by time (newest first)
CREATE TABLE user_posts (
  user_id UUID,
  post_time TIMESTAMP,
  content TEXT,
  PRIMARY KEY (user_id, post_time)
) WITH CLUSTERING ORDER BY (post_time DESC);

-- Query: Get user's 10 most recent posts (FAST!)
SELECT * FROM user_posts
WHERE user_id = ?
LIMIT 10;

-- Example 2: Sensor readings sorted by time (oldest first)
CREATE TABLE sensor_data (
  sensor_id UUID,
  reading_time TIMESTAMP,
  temperature FLOAT,
  PRIMARY KEY (sensor_id, reading_time)
) WITH CLUSTERING ORDER BY (reading_time ASC);

-- Example 3: Multiple clustering columns
CREATE TABLE orders (
  user_id UUID,
  order_year INT,
  order_month INT,
  order_id UUID,
  total DECIMAL,
  PRIMARY KEY (user_id, order_year, order_month, order_id)
) WITH CLUSTERING ORDER BY (order_year DESC, order_month DESC, order_id ASC);

Clustering Rules

  • Query clustering columns IN ORDER (left to right)
  • Can use ranges (>, <, >=, <=) on LAST clustering column only
  • Can't skip clustering columns in WHERE clause
  • ORDER BY must match clustering order
Valid vs Invalid Queries
-- ✅ VALID: Clustering columns in order
SELECT * FROM orders
WHERE user_id = ? AND order_year = 2025 AND order_month > 6;

-- ✅ VALID: Prefix of clustering columns
SELECT * FROM orders
WHERE user_id = ? AND order_year = 2025;

-- ❌ INVALID: Skipping order_year
SELECT * FROM orders
WHERE user_id = ? AND order_month = 6; -- ERROR!

-- ❌ INVALID: Range on non-last clustering column
SELECT * FROM orders
WHERE user_id = ? AND order_year > 2020 AND order_month = 6; -- ERROR!

💻 Live CQL Console - Practice CREATE TABLE

Interactive Learning

Try creating tables below! Click example buttons to load templates, then modify and execute them. See results instantly!

🖥️ CQL Console keyspace: tutorial
cqlsh:tutorial> Type your CQL command or click an example above

Console ready. Execute a command to see results.

🔧 What Happens When You CREATE TABLE?

Behind the Scenes

When you execute CREATE TABLE, Cassandra performs several critical operations across the cluster. Let's visualize the complete workflow!

CREATE TABLE Backend Workflow 1️⃣ User Executes CQL CREATE TABLE users (...) 2️⃣ Coordinator Node Receives Parses DDL statement 3️⃣ Validate Schema ✓ Keyspace exists? ✓ Table name unique? ✓ Data types valid? 4️⃣ Store in system.schema_* 📋 system_schema.tables 🔑 system_schema.columns ⚙️ Metadata stored 5️⃣ Gossip to All Nodes 💬 Schema change event 📡 Broadcast to cluster ⚡ Near-instant propagation 6️⃣ All Nodes Update Local Schema Every node knows about the new table 7️⃣ File System Setup 📁 Create /data/keyspace/table/ 🗂️ Directories for SSTables 8️⃣ Memory Allocation 💾 Allocate Memtable space 🔍 Initialize Bloom filters 9️⃣ Table Ready ✅ Can accept INSERTs 🚀 Fully operational ✅ TABLE CREATED SUCCESSFULLY! Total Time: ~100-500ms depending on cluster size

🗂️ File System Changes

When table is created:

/var/lib/cassandra/data/
└── keyspace_name/
    └── table_name-uuid/
        ├── manifest.json
        ├── snapshots/
        └── backups/

// Ready for data!

💾 Memory Allocation

Per-table memory structures:

  • Memtable: ~64MB initially
  • Bloom Filter: ~1-5MB per SSTable
  • Index: Partition key index
  • Row Cache: If enabled

📋 System Tables Updated

Metadata stored in:

  • system_schema.tables
  • system_schema.columns
  • system_schema.types
  • system_schema.indexes

Important Timing Notes

  • Small clusters (3 nodes): ~100-200ms to create table
  • Large clusters (100+ nodes): ~500ms-1sec due to gossip propagation
  • Schema agreement: Wait for all nodes to acknowledge
  • Best practice: Check schema_version matches across nodes

🌐 Data Distribution After CREATE TABLE

Once the table is created, Cassandra is ready to distribute data across nodes using the partition key. Here's how it works:

How Partition Keys Distribute Data Partition Key: user_id Hash Function: Murmur3 Hash Token Ring (-2^63 to 2^63-1) Node 1 Token: -6000...0000 Node 2 Token: -2000...0000 Node 3 Token: 2000...0000 Node 4 Token: 6000...0000 Example Records user_id: alice hash: -5234...8901 → Node 1 user_id: bob hash: -1876...4532 → Node 2 user_id: charlie hash: 3456...7890 → Node 3 Replication RF = 3 (common setup) Each partition stored on 3 different nodes Example: alice's data ✓ Primary: Node 1 ✓ Replica: Node 2 ✓ Replica: Node 3 🎯 Why This Distribution Strategy Works ✓ Even Distribution: Hash function ensures balanced load across nodes ✓ Fast Lookups: Know exactly which node has data without searching ✓ Fault Tolerance: Replication ensures data survives node failures ✓ Linear Scaling: Add more nodes = more capacity (no resharding!)

💻 Live Console

CQLSH Connected 
Connected to cluster at 127.0.0.1:9042
cqlsh> CREATE TABLE user_timeline (
    user_id UUID,
    post_id TIMEUUID,
    content TEXT,
    image_url TEXT,
    likes_count INT,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC)
AND comment = 'Stores user posts in reverse chronological order';

cqlsh> SELECT * FROM user_timeline
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
LIMIT 20;

💡 Real-World Examples

Example 1: Social Media - User Timeline

Requirement: Show user's posts, sorted newest first, paginated

CREATE TABLE user_timeline (
  user_id UUID,
  post_id TIMEUUID,
  content TEXT,
  image_url TEXT,
  likes_count INT,
  created_at TIMESTAMP,
  PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC)
  AND comment = 'Stores user posts in reverse chronological order';

-- Query: Get 20 most recent posts
SELECT * FROM user_timeline
WHERE user_id = 550e8400-e29b-41d4-a716-446655440000
LIMIT 20;

-- Why this works:
-- ✓ All posts for one user stored together (partition key)
-- ✓ Sorted by TIMEUUID (contains timestamp)
-- ✓ No full table scan needed
-- ✓ Single partition read = FAST!

Example 2: E-commerce - Orders by User

Requirement: Find user's orders in specific date range

CREATE TABLE orders_by_user (
  user_id UUID,
  order_date DATE,
  order_id UUID,
  total_amount DECIMAL,
  status TEXT,
  items LIST<TEXT>,
  PRIMARY KEY (user_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);

-- Query: Orders from last 30 days
SELECT * FROM orders_by_user
WHERE user_id = ?
  AND order_date >= '2025-01-01'
  AND order_date <= '2025-01-30';

-- Query: All orders from specific date
SELECT * FROM orders_by_user
WHERE user_id = ?
  AND order_date = '2025-01-15';

Example 3: IoT - Sensor Readings with TTL

Requirement: Store sensor data, auto-delete after 90 days

CREATE TABLE sensor_readings (
  sensor_id UUID,
  reading_date DATE,
  reading_time TIMESTAMP,
  temperature FLOAT,
  humidity FLOAT,
  pressure FLOAT,
  PRIMARY KEY ((sensor_id, reading_date), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC)
  AND default_time_to_live = 7776000; -- 90 days in seconds

-- Why compound partition key:
-- ✓ Distributes data across multiple partitions (by date)
-- ✓ Keeps each partition size reasonable
-- ✓ Old partitions auto-deleted by TTL

-- Query: Today's readings for sensor
SELECT * FROM sensor_readings
WHERE sensor_id = ? AND reading_date = '2025-01-15';

Example 4: Messaging App - Chat Messages

Requirement: Store messages per conversation, sorted by time

CREATE TABLE messages_by_conversation (
  conversation_id UUID,
  message_id TIMEUUID,
  sender_id UUID,
  message_text TEXT,
  attachments LIST<TEXT>,
  is_read BOOLEAN,
  PRIMARY KEY (conversation_id, message_id)
) WITH CLUSTERING ORDER BY (message_id DESC)
  AND comment = 'Messages sorted newest first for chat view';

-- Query: Load last 50 messages in conversation
SELECT * FROM messages_by_conversation
WHERE conversation_id = ?
LIMIT 50;

-- Query: Messages after specific point (pagination)
SELECT * FROM messages_by_conversation
WHERE conversation_id = ?
  AND message_id < ? -- Load older messages
LIMIT 50;

Example 5: Gaming - Player Leaderboard

Requirement: Top players by score, per game, per season

CREATE TABLE game_leaderboard (
  game_id UUID,
  season INT,
  score BIGINT,
  player_id UUID,
  player_name TEXT,
  achieved_at TIMESTAMP,
  PRIMARY KEY ((game_id, season), score, player_id)
) WITH CLUSTERING ORDER BY (score DESC, player_id ASC)
  AND comment = 'Leaderboard sorted by highest score';

-- Query: Top 100 players this season
SELECT * FROM game_leaderboard
WHERE game_id = ? AND season = 2025
LIMIT 100;

-- Why this works:
-- ✓ Compound partition key (game + season)
-- ✓ Sorted by score DESC = highest first
-- ✓ player_id breaks score ties
-- ✓ Single partition read = INSTANT!

Example 6: Analytics - Page Views Tracking

Requirement: Track website page views, aggregate by day

CREATE TABLE page_views (
  page_url TEXT,
  view_date DATE,
  view_time TIMESTAMP,
  visitor_id UUID,
  referrer TEXT,
  user_agent TEXT,
  country TEXT,
  PRIMARY KEY ((page_url, view_date), view_time)
) WITH CLUSTERING ORDER BY (view_time DESC)
  AND default_time_to_live = 7776000 -- 90 days
  AND compaction = {'class': 'TimeWindowCompactionStrategy'};

-- Query: Today's views for homepage
SELECT COUNT(*) FROM page_views
WHERE page_url = '/home' AND view_date = '2025-01-15';

-- Query: Last 100 views for blog post
SELECT * FROM page_views
WHERE page_url = '/blog/cassandra-tips'
  AND view_date = '2025-01-15'
LIMIT 100;

Example 7: Social Media - Followers/Following

Requirement: Track who follows whom, bidirectional queries

-- Table 1: Find who user follows
CREATE TABLE user_following (
  user_id UUID,
  followed_at TIMESTAMP,
  following_user_id UUID,
  following_username TEXT,
  PRIMARY KEY (user_id, followed_at, following_user_id)
) WITH CLUSTERING ORDER BY (followed_at DESC);

-- Table 2: Find user's followers (denormalized!)
CREATE TABLE user_followers (
  user_id UUID,
  followed_at TIMESTAMP,
  follower_user_id UUID,
  follower_username TEXT,
  PRIMARY KEY (user_id, followed_at, follower_user_id)
) WITH CLUSTERING ORDER BY (followed_at DESC);

-- Query: Who does Alice follow?
SELECT * FROM user_following
WHERE user_id = ?;

-- Query: Who follows Alice?
SELECT * FROM user_followers
WHERE user_id = ?;

-- Important: Write to BOTH tables on follow action!

Example 8: Video Streaming - Watch History

Requirement: User watch history, continue watching feature

CREATE TABLE watch_history (
  user_id UUID,
  watched_at TIMESTAMP,
  video_id UUID,
  video_title TEXT,
  duration_seconds INT,
  progress_seconds INT,
  completed BOOLEAN,
  device_type TEXT,
  PRIMARY KEY (user_id, watched_at, video_id)
) WITH CLUSTERING ORDER BY (watched_at DESC)
  AND comment = 'User viewing history with progress tracking';

-- Query: User's watch history (recent 50)
SELECT * FROM watch_history
WHERE user_id = ?
LIMIT 50;

-- Query: Continue watching (incomplete videos)
SELECT * FROM watch_history
WHERE user_id = ?
  AND completed = false
LIMIT 10 ALLOW FILTERING; -- Use carefully!

Example 9: Ride Sharing - Trip History

Requirement: Store all rides per user, queryable by date range

CREATE TABLE user_trips (
  user_id UUID,
  trip_year INT,
  trip_month INT,
  trip_id TIMEUUID,
  pickup_location TEXT,
  dropoff_location TEXT,
  distance_km FLOAT,
  fare_amount DECIMAL,
  driver_id UUID,
  rating INT,
  PRIMARY KEY ((user_id, trip_year, trip_month), trip_id)
) WITH CLUSTERING ORDER BY (trip_id DESC);

-- Query: This month's trips
SELECT * FROM user_trips
WHERE user_id = ?
  AND trip_year = 2025
  AND trip_month = 1;

-- Query: Calculate monthly spending
SELECT SUM(fare_amount) FROM user_trips
WHERE user_id = ?
  AND trip_year = 2025
  AND trip_month = 1;

Example 10: Financial - Stock Trades

Requirement: Track all trades per user, sorted by time

CREATE TABLE user_trades (
  user_id UUID,
  trade_date DATE,
  trade_time TIMESTAMP,
  trade_id UUID,
  symbol TEXT,
  trade_type TEXT, -- BUY or SELL
  quantity INT,
  price_per_share DECIMAL,
  total_amount DECIMAL,
  commission DECIMAL,
  PRIMARY KEY ((user_id, trade_date), trade_time, trade_id)
) WITH CLUSTERING ORDER BY (trade_time DESC);

-- Query: Today's trades
SELECT * FROM user_trades
WHERE user_id = ?
  AND trade_date = '2025-01-15';

-- Query: All trades for specific stock today
SELECT * FROM user_trades
WHERE user_id = ?
  AND trade_date = '2025-01-15'
  AND symbol = 'AAPL' ALLOW FILTERING;

Pattern Recognition

Notice the common patterns across all examples:

✓ Partition Key: Always identifies the "owner" (user_id, sensor_id, etc.)
✓ Time Bucketing: Large datasets use date in partition key
✓ Clustering: Almost always includes timestamp for sorting
✓ Denormalization: Store redundant data for query performance
✓ TTL: Auto-expire for time-series/analytics data

⚙️ Table Options (WITH Clause)

Option Description Example
default_time_to_live Auto-delete data after N seconds 86400 (1 day)
comment Human-readable table description 'User activity logs'
compaction Compaction strategy SizeTieredCompactionStrategy
compression Data compression algorithm LZ4Compressor
gc_grace_seconds Tombstone grace period 864000 (10 days)
Complete Options Example
CREATE TABLE time_series_data (
  device_id UUID,
  event_time TIMESTAMP,
  metric_value FLOAT,
  PRIMARY KEY (device_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC)
  AND default_time_to_live = 2592000 -- 30 days
  AND comment = 'IoT device metrics, auto-expire after 30 days'
  AND compaction = {'class': 'TimeWindowCompactionStrategy'}
  AND compression = {'sstable_compression': 'LZ4Compressor'};

✅ Best Practices

✅ DO These Things

  • Design queries BEFORE creating tables
  • Use UUID or TIMEUUID for IDs
  • Keep partitions under 100MB
  • Use compound partition keys for large datasets
  • Add TTL for time-series data
  • Use TIMEUUID for time-ordered data
  • Document table purpose with comment
  • Use appropriate clustering order

❌ DON'T Do These

  • Don't use sequential IDs (hotspots!)
  • Don't create tables without knowing queries
  • Don't store large blobs (> 10MB)
  • Don't use collections with > 100 items
  • Don't forget to set TTL on time-series
  • Don't create "unbounded" partitions
  • Don't normalize - denormalize!
  • Don't rely on ALLOW FILTERING

Quick Decision Checklist

Question 1: How will I query this data?
→ Answer: By user_id → user_id becomes partition key

Question 2: Do I need data sorted?
→ Answer: Yes, by time → time becomes clustering column

Question 3: Will partitions grow unbounded?
→ Answer: Yes, 1M records/user → Add date to partition key

Question 4: Should old data auto-delete?
→ Answer: Yes, after 90 days → Add TTL = 7776000

Question 5: What data type for IDs?
→ Answer: Need uniqueness → UUID
→ Answer: Need time-ordering → TIMEUUID

Final Decision:
PRIMARY KEY ((user_id, date), timestamp)
WITH default_time_to_live = 7776000;

Common Mistakes to Avoid

  1. The "One Big Partition" Mistake: Storing all data in single partition (e.g., all logs for all users). Fix: Add user_id or date to partition key
  2. The "Wrong Clustering Order" Mistake: Using ASC when you need DESC. Fix: Think about how you'll query!
  3. The "Forgot TTL" Mistake: Time-series data growing forever. Fix: Always set TTL for time-series!
  4. The "Can't Query" Mistake: Creating table, then realizing you can't run needed queries. Fix: Design queries FIRST!
Previous
Tables
Advertisement

Responsive Ad