Cassandra: making a data model / schema

Question

(Not sure what its called... model.. schema.. super model?)

I have 'n' (uniquely id'd) sensors in 'm' (uniquely id'd) homes. Each of these fires 0 to 'k' times / day (in blocks of 1-5). This data is currently stored in MySQL with a table for each 'home' and a structure of:

time stamp
sensor id
firing count

Im having trouble wrapping my mind around a 'nosql' model of this data that would allow me to find counts of firings by home, time, or sensor.

.. Or maybe this isn't the right kind of data to push to nosql? Our current server is bogging down under the load ( hundreds of millions of rows x hundreds of homes ). Im very interested in finding a data store that allows the scalability of cassandra.

Answer 1

It depends. Think "Query first" approach:

1. identify the queries
2. model the data

So, while you might have a Column Family which is your physical model, you will also have one or more which provide the data as it is queried. And, you can further take advantage of Cassandra features, such as:

Column Names can contain data. You don't have to store a value, each of the names could be a timestamp, for example

It is well suited to store thousands of columns for each key and the columns will remain sorted and can access in forward or reverse order; so, to continue above example, can easily get list of all timestamps for a sensor

Composite data types allow you to combine multiple bits of data into keys, names, or values. e.g. combine house id and sensor id

Counter Columns provide an simple value increment, even for the initial value, so just always a write operation.

Indexes can be defined on static column names which in effect, provides a reverse Column Family with the key as the result, just be careful of bucket size (e.g. might not want values to millisec)