Cassandra CREATE TABLE Guide in cqlsh and DbSchema

You can add a column to a Cassandra table next week without a migration. You cannot change its primary key at all: Cassandra 5.0 states that the primary key of a table cannot ever be altered, and that CLUSTERING ORDER BY is one important option that cannot be changed after creation. A CREATE TABLE statement is therefore two irreversible decisions wrapped around a column list that stays negotiable.

That asymmetry is where the effort belongs. The same page records that adding a new column is a constant time operation, and concludes that there is no need to anticipate future usage while initially creating a table.

Model the table for the queries it must serve

Cassandra reads fastest when the primary key matches the query, and the Cassandra 5.0 documentation states it as a property of the data model: data modeling that considers the querying patterns and assigns primary keys based on the queries will have the lowest latency in fetching data. Start from the queries the application runs, and accept that two query shapes over the same facts often mean two tables.

The same page is equally direct about the cost of a careless key. All rows sharing a partition key are stored on the same set of replica nodes, which is what makes a single-partition read cheap, and which also creates a hotspot for both reading and writing when one key value takes most of the traffic. The guidance on size is that partitions must be sized "just right, not too big nor too small".

To run the statements below you need a Cassandra cluster, permission to create schema objects, and either cqlsh or DbSchema connected to it. The keyspace has to exist first, and How to Create a Keyspace in Cassandra covers the replication decisions behind it.

Create a table in cqlsh

The example stores orders read back by customer and day, newest first inside each partition:

USE ecommerce;

CREATE TABLE IF NOT EXISTS orders_by_customer_day (
    customer_id uuid,
    order_day date,
    order_time timeuuid,
    order_id uuid,
    status text,
    total decimal,
    sales_rep text STATIC,
    PRIMARY KEY ((customer_id, order_day), order_time, order_id)
) WITH CLUSTERING ORDER BY (order_time DESC)
  AND comment = 'Orders grouped by customer and day'
  AND compaction = {'class': 'TimeWindowCompactionStrategy'};

A DDL statement that succeeds prints nothing in cqlsh and the prompt returns. To read the definition back, ask cqlsh to describe it:

DESCRIBE TABLE ecommerce.orders_by_customer_day;

DESCRIBE prints a description of a schema element, typically the DDL statements that would recreate it, so the table options Cassandra filled in from its defaults show up next to the ones you wrote.

The partition key and the clustering columns

A CQL primary key has two parts, and the inner parentheses decide which columns land in which part. PRIMARY KEY ((customer_id, order_day), order_time, order_id) reads as a composite partition key of two columns followed by two clustering columns.

What the partition key decides

The partition key is the first component of the primary key definition, and an extra set of parentheses lets it span several columns. A partition is the set of rows that share the same value for their partition key, so (customer_id, order_day) puts each customer's orders for one day in a partition of their own. Cassandra computes a hash from those two columns, and that hash value defines where the partition lives in the cluster.

What clustering columns decide

order_time and order_id follow the partition key, which makes them the clustering columns. For a given partition, all rows are ordered by the clustering order, and clustering columns also add uniqueness to a row. Ascending is the default for every clustering column, and WITH CLUSTERING ORDER BY (order_time DESC) reverses it for the first one.

Cassandra 5.0 lists three effects of that option. It changes the order of results for a SELECT with no ORDER BY clause. It limits ORDER BY on the table to the declared clustering order or its exact reverse, so on a table declared (a DESC, b ASC) a query asking for ORDER BY (a ASC, b ASC) will not return the expected order. And queries in reverse clustering order are slower than the default ascending order, which is why the documentation recommends declaring the descending order in the schema when the application reads newest first.

Static columns are shared by the partition

sales_rep carries STATIC, and a static column is shared by all the rows belonging to the same partition. The Cassandra 5.0 documentation shows the effect on a partition of two rows:

CREATE TABLE t (
    pk int,
    t int,
    v text,
    s text static,
    PRIMARY KEY (pk, t)
);
INSERT INTO t (pk, t, v, s) VALUES (0, 0, 'val0', 'static0');
INSERT INTO t (pk, t, v, s) VALUES (0, 1, 'val1', 'static1');
SELECT * FROM t;

Both rows report the value written second, because the second insert overwrote the one static value the partition has:

Two restrictions come with the keyword. A table without clustering columns cannot have static columns, since every partition there holds a single row and every column is inherently static. And only non-primary key columns can be static.

Table options worth setting at creation

Options follow the WITH keyword, and most of them can be altered later. These are the defaults Cassandra 5.0 documents for the ones a new table usually touches:

The last four are maps, and that shape matters the first time you change one: setting any compaction sub-option erases all previous compaction sub-options, and compression behaves the same way. How to Alter a Table in Cassandra shows what that costs.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Create a table in DbSchema

DbSchema reaches Cassandra through its own open-source JDBC driver, com.dbschema.Cassandra.JdbcDriver, described on the Cassandra connection page. The URL that driver expects carries a datacenter name, which nodetool status prints on any node.

1. In DbSchema, choose Connect to Database and pick Cassandra, and DbSchema opens the Connection Dialog for that database type.
2. Give the connection a Connection Name, enter the Server Host and Port with the Database User and Password, click Test Connection, then Connect.
3. DbSchema reads the keyspace and draws its tables, columns and keys as an interactive diagram. This step only reads.
4. Open the DbSchema SQL Editor from the Editors menu, paste the CREATE TABLE statement, and click Execute Query.

Step 4 goes to the live database, so the table exists in Cassandra the moment the statement returns. The diagram is DbSchema's own copy of the keyspace and it changes only when DbSchema reads the keyspace again, which is worth knowing before you go looking for a new table that cqlsh created a minute ago.

Common mistakes when creating a Cassandra table

Three of them end in a new table and a data copy. Picking a partition key with few distinct values concentrates traffic on one replica set, which is the hotspot the documentation warns about. Counting on a later change to CLUSTERING ORDER BY fails, because that option cannot be changed after creation. Counting on a later change to the primary key fails for the same reason, and both leave you creating a second table and backfilling it.

The fourth is quieter, and it shows up while you type rather than months later. STATIC on a table whose primary key has no clustering columns is rejected, for the reason the static column section gave.

The statements above run as written in the SQL Editor of the free DbSchema Community Edition. Download DbSchema at https://dbschema.com/download.html, connect to your cluster with the datacenter name nodetool status gives you, and run the CREATE TABLE against your own keyspace; the diagram then shows the new table with its columns. When the definition has to change later, How to Alter a Table in Cassandra covers what ALTER TABLE will and will not do.