Creating Keyspaces and Tables in Cassandra

Keyspaces: The Top-Level Container

In Cassandra, a keyspace is the equivalent of a database in relational systems. It defines data replication across the cluster. Every table belongs to a keyspace, and the replication strategy you choose determines how many copies of your data exist and where they are stored.

Creating a Keyspace

Cassandra offers two replication strategies: SimpleStrategy for single data center deployments and NetworkTopologyStrategy for multi-data center setups.

-- Simple strategy (development / single DC)
CREATE KEYSPACE my_app
WITH replication = {
  'class': 'SimpleStrategy',
  'replication_factor': 3
};

-- Network topology strategy (production / multi-DC)
CREATE KEYSPACE my_app
WITH replication = {
  'class': 'NetworkTopologyStrategy',
  'dc_east': 3,
  'dc_west': 2
};

-- Create only if it doesn't exist
CREATE KEYSPACE IF NOT EXISTS my_app
WITH replication = {
  'class': 'SimpleStrategy',
  'replication_factor': 1
};

Using a Keyspace

Before creating tables, switch to the target keyspace with the USE command, or prefix table names with the keyspace name.

-- Switch to keyspace
USE my_app;

-- Or reference tables with keyspace prefix
SELECT * FROM my_app.users;

Creating Tables

Tables in Cassandra must have a primary key defined at creation time. The primary key consists of a partition key (which determines data distribution) and optional clustering columns (which determine sort order within a partition).

-- Simple table with single partition key
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  name TEXT,
  email TEXT,
  created_at TIMESTAMP
);

-- Table with composite primary key
CREATE TABLE orders (
  customer_id TEXT,
  order_date TIMESTAMP,
  order_id UUID,
  total DECIMAL,
  status TEXT,
  PRIMARY KEY (customer_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);

-- Create only if it doesn't exist
CREATE TABLE IF NOT EXISTS products (
  product_id TEXT PRIMARY KEY,
  name TEXT,
  price DECIMAL,
  category TEXT
);

Table Options

Cassandra tables support various options for tuning compaction, compression, caching, and more.

CREATE TABLE events (
  event_id UUID,
  event_time TIMESTAMP,
  event_type TEXT,
  payload TEXT,
  PRIMARY KEY (event_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC)
  AND default_time_to_live = 2592000
  AND compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'DAYS',
    'compaction_window_size': 1
  }
  AND compression = {
    'sstable_compression': 'LZ4Compressor'
  };

Altering and Dropping

-- Add a column
ALTER TABLE users ADD phone TEXT;

-- Drop a column
ALTER TABLE users DROP phone;

-- Drop a table
DROP TABLE IF EXISTS products;

-- Drop a keyspace (and all its tables)
DROP KEYSPACE IF EXISTS my_app;

Key Takeaways

• Keyspaces define replication -- use NetworkTopologyStrategy for production multi-DC deployments.
• Every table must have a primary key consisting of at least a partition key.
• Use CLUSTERING ORDER BY to define how rows are sorted within a partition.
• Table options like compaction strategy and default TTL can be set at creation time.