Thursday, June 20, 2013

Cassandra CQL-3 with wide rows / dynamic columns

So in the Cassandra community, people are not very happy with the move of Cassandra from its cli to CQL . Apparently 90% of existing Cassandra customers still use the cli/Thrift for their queries, and thus not very happy to have to move away from it.
One of the reasons for moving to CQL is, aside from obvious reasons like performance improvements and asynchronous calls, to compete with Mongo DB's simplicity actually.. But anyway, on to our subject of the day: my recent problem was that i wanted to insert at run time new columns on the fly in Cassandra (one of the primary reasons for using a NoSQL DB).

These new columns would come in with a Name, and a Value. So Datastax states that this is possible (http://www.datastax.com/dev/blog/does-cql-support-dynamic-columns-wide-rows ) in CQL ; however they dont state how to insert a column value along with the column name .

 Here is how:

 CREATE TABLE WideRow ( username VARCHAR, productNumber UUID, productName VARCHAR, purchaseDate TIMESTAMP, PRIMARY KEY (username, productName, productNumber) ;

 Essentially, create a composite column name with both the Name and the Value.
 These will be prepended to each of the values of the remaining (non-composite) columns. I.e., username is the row key. The other 2 columns create a composite column name, meaning they will be part of the column names, prepended to the other columns. I.e. 'matt', 'radio'_'123'_'04/01/2013', 'tv'_'235'_'05/02/2012' . This is a wide row, with 2 columns that each have different values. These 2 columns were created unique by using the Name and Value as a concatenation.