Comparing Cassandra structure with Relational Databases

We have to look at more complex example to see the differences :)

For start:

• column family term was used in older Thrift API
• in newer CQL API, the term table is used

Table is defined as "two-dimensional view of a multi-dimensional column family".

The term "wide-rows" was related mainly to the Thrift API. In cql it is defined a bit differently, but underneath looks the same.

Comparing SQL and CQL. In SQL table is a set of rows. In simple example it looks like in CQL it is the same, but it is not. CQL table is a set of partitions, where each partition can be just a single row (e.g. when you don't have a clustering key) or multiple rows. Partition containing multiple rows is in Thrift therminology named "wide-row". To see how it is stored underneath, please read e.g. part about composite-keys from here.

There are more differences:

• CQL can have static columns which are stored on partition level - it seems that every row in partition have a common value, but really it is a single value stored on upper level. It can be used also to model 1:N relations
• In CQL you can have collection type columns - set, list, map
• Column can contain a user defined type (you can define e.g. address as type, and reuse this type in many places), or collection can be a collection of user defined types
• But also CQL does not support JOINs which are available in SQL, and you have to structure your tables very carefully, since they have to be strictly query oriented (in cassandra you can't query data by any column value, secondary indexes also have many limitations). It is usually said that in relational model you model tables clearly basing on data, when in cassandra you model basing on queries.

I hope I was able to make it a bit more clear for you. I recommend watching some vidoes (or reading slides) from Datastax Core Concepts Course as solid introduction to Cassandra.

In my experience CQL misleads a lot of people. First of all you would never want to do:

SELECT * FROM a_table_here; 

On a production Cassandra cluster, since you are putting a huge load on your Coordinator node to aggregate all of the data from all of the other nodes. Also by default, you will be given back a maximum of 10000 "rows".

To understand how Cassandra stores your data, we need to establish a few terms first:

There's the Primary Key, in your case lastname, this is hashed to determine which node in the cluster owns this range, and it's stored there (plus any replica nodes).

Next there's Cluster Columns, I don't know if you have any in your example, but you define them like PRIMARY KEY ((lastname),age, city). In that example you are clustering by age first then city, this is ORDERED.

Now for a simplistic high-level view of Cassandra for your use case, it stores the data as a Map to an ordered Multimap:

Doe -> 36:Beverly Hills -> [email protected]

Where 'Doe' is the Primary Key, which tells you which node(s) have that row of data. And 36:Beverly Hills is the Ordered Clustering Keys (part of the ordered multimap key). Lastly [email protected] is the final value (can be multiple mind you) for the Map to a Multimap.

There's a lot of nuisances that I left out to make the example simple, for a more in-depth I would highly suggest reading: http://www.planetcassandra.org/making-the-change-from-thrift-to-cql/