Cassandra add column after particular column

Question

I need to alter the table to add a new column after a particular column or as last column, I have been through the document but no luck.

Answer 1

Let's say I'm starting with a table that has this definition:

CREATE TABLE mykeyspace.letterstable (
    column_n TEXT,
    column_b TEXT,
    column_c TEXT,
    column_z TEXT,
    PRIMARY KEY (column_n));

1- Adding a column is a simple matter.

ALTER TABLE mykeyspace.letterstable ADD column_j TEXT;

2- After adding the new column, my table definition will look like this:

desc table mykeyspace.letterstable;

CREATE TABLE mykeyspace.letterstable (
    column_n TEXT,
    column_b TEXT,
    column_c TEXT,
    column_j TEXT,
    column_z TEXT,
    PRIMARY KEY (column_n));

This is because columns in Cassandra are stored by ASCII-betical order, after the keys (so column_n will always be first, because it is the only key). I can't tell Cassandra that I want my new column_j to go after column_z. It's going to put it between column_c and column_z on its own.

Answer 2

Cassandra will store table data based on partition & clustering key.

Standard CQL for adding column:

ALTER TABLE keyspace.table ADD COLUMN column1 columnType;

Running DESC table for a given table via CQLSH does not portray how the data is stored. It will always list the partition key & clustering key first; then the remaining columns in alphabetical order.

https://docs.datastax.com/en/cql/3.1/cql/cql_reference/alter_table_r.html

Cassandra create table won't keep column order