Cassandra CQL incompatibility between cql2 and cql3

Question

it seems like adding a new column on the fly doesnt work in Cassandra CQL3; say i have this table:

cqlsh:Keyspace2> select * from users;

user_name | birth_year | gender | password | session_token | state

-----------+------------+--------+------------+---------------+-------

jsmith | 1968 | null | ch@ngem3a | null | null

jsmith2 | 1963 | null | ch@ngem3a2 | null | null

cqlsh:Keyspace2> insert into users (user_name, x) values ('jsmith',100);

>Bad Request: Unknown identifier x

Perhaps you meant to use CQL 2? Try using the -2 option when starting cqlsh.

but the warning is correct, the same thing works when i go to CQL2:

> Blockquote

cqlsh:Keyspace2> exit
[root@bdvm1 ~]# cqlsh
Connected to Test Cluster at localhost:9160.
[cqlsh 2.2.0 | Cassandra 1.1.9-dse-2.2.2-SNAPSHOT | CQL spec 2.0.0 | Thrift protocol 19.33.0]
Use HELP for help.
cqlsh> use Keyspace1;
cqlsh:Keyspace1> select * from users;
 KEY   | birth_year | gender
-------+------------+--------
  TEST |       1968 |      m
  TEST1 |       1968 |      f

cqlsh:Keyspace1> insert into users (KEY, x) values ('jsmith',100);
cqlsh:Keyspace1> select * from users;
 KEY,TEST | birth_year,1968 | gender,m
 KEY,TEST1 | birth_year,1968 | gender,f
 KEY,jsmith | x,100

Does anyone know why ? I would have thought CQL3 was backwards compatible ..

thanks, Matt

jbellis jbellis · Accepted Answer · 2013-04-17T13:28:26

You need to read http://www.datastax.com/dev/blog/cql3-for-cassandra-experts and http://www.datastax.com/dev/blog/thrift-to-cql3. The first in particular explains why CQL2 could not model many common Cassandra design patterns, and how we addressed those in CQL3.

Short answer: What CQL2 called "dynamic columns in a row" are represented as "multiple rows in a partition" in CQL3. To reduce confusion, we now refer to the raw storage engine atom of name:value as a cell, rather than a column, and a group of cells with a common key as a partition.

Cassandra CQL incompatibility between cql2 and cql3

2 Answers