Why many refer to Cassandra as a Column oriented database?

You both make good points and it can be confusing. In the example where

apple -> colour  weight  price variety
         "red"   100     40    "Cox"

apple is the key value and the column is the data, which contains all 4 data items. From what was described it sounds like all 4 data items are stored together as a single object then parsed by the application to pull just the value required. Therefore from an IO perspective I need to read the entire object. IMHO this is inherently row (or object) based not column based.

Column based storage became popular for warehousing, because it offers extreme compression and reduced IO for full table scans (DW) but at the cost of increased IO for OLTP when you needed to pull every column (select *). Most queries don't need every column and due to compression the IO can be greatly reduced for full table scans for just a few columns. Let me provide an example

apple -> colour  weight  price variety
         "red"   100     40    "Cox"

grape -> colour  weight  price variety
         "red"   100     40    "Cox"

We have two different fruits, but both have a colour = red. If we store colour in a separate disk page (block) from weight, price and variety so the only thing stored is colour, then when we compress the page we can achieve extreme compression due to a lot of de-duplication. Instead of storing 100 rows (hypothetically) in a page, we can store 10,000 colour's. Now to read everything with the colour red it might be 1 IO instead of thousands of IO's which is really good for warehousing and analytics, but bad for OLTP if I need to update the entire row since the row might have hundreds of columns and a single update (or insert) could require hundreds of IO's.

Unless I'm missing something I wouldn't call this columnar based, I'd call it object based. It's still not clear on how objects are arranged on disk. Are multiple objects placed into the same disk page? Is there any way of ensuring objects with the same meta data go together? To the point that one fruit might contain different data than another fruit since its just meta data or xml or whatever you want to store in the object itself, is there a way to ensure certain matching fruit types are stored together to increase efficiency?

Larry

• If you take a look at the Readme file at Apache Cassandra git repo, it says that,

Cassandra is a partitioned row store. Rows are organized into tables with a required primary key.

Partitioning means that Cassandra can distribute your data across multiple machines in an application-transparent matter. Cassandra will automatically repartition as machines are added and removed from the cluster.

Row store means that like relational databases, Cassandra organizes data by rows and columns.

• Column oriented or columnar databases are stored on disk column wise.

  e.g: Table Bonuses table

    ID         Last    First   Bonus
    1          Doe     John    8000
    2          Smith   Jane    4000
    3          Beck    Sam     1000
  

• In a row-oriented database management system, the data would be stored like this: 1,Doe,John,8000;2,Smith,Jane,4000;3,Beck,Sam,1000;

• In a column-oriented database management system, the data would be stored like this:
  1,2,3;Doe,Smith,Beck;John,Jane,Sam;8000,4000,1000;

• Cassandra is basically a column-family store

• Cassandra would store the above data as,

     "Bonuses" : {
           row1 : { "ID":1, "Last":"Doe", "First":"John", "Bonus":8000},
           row2 : { "ID":2, "Last":"Smith", "First":"Jane", "Bonus":4000}
           ...
     }

• Also, the number of columns in each row doesn't have to be the same. One row can have 100 columns and the next row can have only 1 column.

• Read this for more details.

Yes, the "column-oriented" terminology is a bit confusing.

The model in Cassandra is that rows contain columns. To access the smallest unit of data (a column) you have to specify first the row name (key), then the column name.

So in a columnfamily called Fruit you could have a structure like the following example (with 2 rows), where the fruit types are the row keys, and the columns each have a name and value.

apple -> colour  weight  price variety
         "red"   100     40    "Cox"

orange -> colour    weight  price  origin
          "orange"  120     50     "Spain"

One difference from a table-based relational database is that one can omit columns (orange has no variety), or add arbitrary columns (orange has origin) at any time. You can still imagine the data above as a table, albeit a sparse one where many values might be empty.

However, a "column-oriented" model can also be used for lists and time series, where every column name is unique (and here we have just one row, but we could have thousands or millions of columns):

temperature ->  2012-09-01  2012-09-02  2012-09-03 ...
                40          41          39         ...

which is quite different from a relational model, where one would have to model the entries of a time series as rows not columns. This type of usage is often referred to as "wide rows".