Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 30 ยท Cassandra Data Model

CQL: keyspaces, tables and basic queries

Learn CQL, the Cassandra Query Language: create keyspaces and tables, then INSERT, SELECT, UPDATE and DELETE, and see how it differs from SQL.

Cassandra's query language is called CQL, the Cassandra Query Language. It was designed to look like SQL on purpose, so that CREATE TABLE, INSERT, SELECT and WHERE feel familiar. That familiarity is both a gift and a trap. The words are the same, but the rules underneath are different: there are no joins, INSERT and UPDATE behave almost identically, and WHERE is allowed to name only columns that Cassandra can find efficiently.

In this lesson you will meet the statements you will use all the time: create a keyspace, create a table, then insert, read, update and delete rows. A real Cassandra server cannot run on this site, so the CQL appears in read-only blocks. Two Python stand-ins running in your browser model a small table: one shows the main behaviours (upserts, missing columns, rejected queries), the other shows how Cassandra settles conflicting writes with timestamps. They are teaching models, not the real engine.

Keyspaces

A keyspace is the top-level container, roughly what other systems call a database or schema. It holds tables and, importantly, the replication settings that you met in Cassandra architecture in one picture.

SQL
CREATE KEYSPACE IF NOT EXISTS shop
  WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3}
  AND durable_writes = true;

USE shop;

USE shop makes shop the default for later statements in the session. In applications it is common to write shop.users explicitly instead. SimpleStrategy is for single-datacenter experiments; production uses NetworkTopologyStrategy.

Tables and primary keys

A table in CQL looks like a SQL table, with one crucial requirement: every table has a primary key, and the primary key decides where the data lives in the cluster.

SQL
CREATE TABLE IF NOT EXISTS users (
  user_id uuid PRIMARY KEY,
  name    text,
  email   text,
  country text
);

Here user_id is the entire primary key. Cassandra hashes it to a token, which tells it which nodes store the row. In the next lesson you will see how a primary key can have two parts, a partition key and clustering columns. For now, think of user_id as the address of the row.

Columns have types. uuid, text and int are the ones you will meet first; Data types, collections and UDTs covers the rest.

INSERT, SELECT, UPDATE and DELETE

SQL
INSERT INTO users (user_id, name, email, country)
VALUES (2b6b5c1a-8d4e-4b43-9e3a-0a1f6c7d9e01, 'Ada', '[email protected]', 'UK');

SELECT * FROM users WHERE user_id = 2b6b5c1a-8d4e-4b43-9e3a-0a1f6c7d9e01;

UPDATE users SET country = 'IE'
WHERE user_id = 2b6b5c1a-8d4e-4b43-9e3a-0a1f6c7d9e01;

DELETE FROM users WHERE user_id = 2b6b5c1a-8d4e-4b43-9e3a-0a1f6c7d9e01;

This is the same set of verbs you know from SQL INSERT and its relatives, with several differences.

Writes are upserts

In SQL, inserting a row whose key already exists is an error. In Cassandra INSERT is an upsert: if the row does not exist it is created, and if it does exist the columns you list are overwritten. UPDATE is also an upsert: updating a key that does not exist creates the row. Cassandra does not read before it writes, so it does not know whether the row existed and does not care. This is one reason writes are so fast.

If you need "only insert if it is new", add IF NOT EXISTS. That triggers a more expensive coordination (a lightweight transaction, covered in Lightweight transactions, batches and counters), so use it sparingly.

Missing columns are just null

Columns you do not mention in an INSERT simply have no value, and a SELECT shows them as null. There is no storage cost for a column that was never written. Writing null explicitly is different: it creates a deletion marker (a tombstone, discussed in Deletes, TTL and tombstones). Leave columns out instead.

WHERE must use the key

You can only restrict a query on columns that Cassandra can reach efficiently, which at this stage means the primary key. A query like SELECT * FROM users WHERE country = 'UK' is rejected, because Cassandra would have to look at every row on every node. The error suggests ALLOW FILTERING, which makes it run anyway by scanning. Treat that flag as a warning label, not as a solution. The right fix is usually a different table design, the subject of Query-first data modeling.

No joins, no foreign keys

There is nothing like JOIN, and no foreign-key constraints. Related data lives together in the same table or is copied into several tables.

Row-level operations need the whole key

UPDATE and DELETE must name the full primary key in WHERE. Cassandra will not let you update "all users in the UK" with a single statement.

Stand-in 1: a toy CQL table

The class below implements a table with a single-column primary key. It models INSERT as an upsert, UPDATE as an upsert, missing columns as None, IF NOT EXISTS, DELETE, and the refusal to filter on a non-key column. The error message is worded like Cassandra's, but this is Python code written for teaching, not a CQL parser.

Python A toy CQL table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Last write wins, per column

When two clients write the same column at nearly the same time, who wins? Cassandra attaches a write timestamp (in microseconds) to every value it stores, and when it must choose between two values for the same column, the one with the newer timestamp wins. This is called last-write-wins. It works per column (per cell), not per row, so two writers updating different columns of one row both succeed and both survive.

SQL
INSERT INTO users (user_id, name) VALUES (1, 'Ada') USING TIMESTAMP 1000;
UPDATE users USING TIMESTAMP 900 SET name = 'Old name' WHERE user_id = 1;
-- The second statement arrives later but is older, so it loses.

Normally the timestamp is set automatically by the client or coordinator, which assumes the clocks of the machines are reasonably synchronized. Clock drift therefore matters in Cassandra.

Stand-in 2: cells with timestamps

This stand-in stores every column as a pair of value and timestamp. It then applies three writes out of order and shows that the newest timestamp wins per column, no matter the arrival order.

Python Last write wins, column by column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that country, written with the lowest timestamp of all, still appears: it competes only with other writes to country. The two stale writes to name and email were ignored. Two takeaways: in Cassandra the order in which messages arrive does not decide the winner, and if your application needs "one writer at a time" semantics you must request them explicitly with a lightweight transaction.

What is real and what is the stand-in

The CQL statements above are real and were written for Cassandra's CQL; they cannot be run on this site. The Python tables are stand-ins: they do not parse CQL, have no replication and no storage engine. Their purpose is to make the rules visible: upserts, null columns, key-only WHERE and per-cell timestamps.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when you INSERT a row whose primary key already exists?

    Cassandra does not check whether a row exists before writing. An INSERT creates the row or overwrites the columns you list.
  2. 2. Why is SELECT * FROM users WHERE country = 'UK' rejected by default?

    Cassandra only lets you restrict columns it can locate efficiently. ALLOW FILTERING forces a scan, which is usually the wrong fix.
  3. 3. Two clients write the same column with different timestamps. Which value is kept?

    Cassandra resolves conflicts per cell by keeping the value with the newest write timestamp.
  4. 4. What is a keyspace?

    A keyspace is the Cassandra equivalent of a database or schema, and it defines the replication strategy for its tables.

Summary

  • CQL looks like SQL but has no joins, and WHERE must use columns Cassandra can locate by key.
  • A keyspace holds tables and replication settings; every table needs a primary key that decides where rows live.
  • INSERT and UPDATE are both upserts; unmentioned columns are null and cost nothing.
  • Conflicts are settled per column by the newest write timestamp (last write wins).
  • IF NOT EXISTS and ALLOW FILTERING exist but are expensive; avoid both unless you know why you need them.

Next: Partition keys and clustering keys.