Cassandra: modeling timed data

Question

Lets assume a device that can give n signals at random times. We collect this data from m devices over some span of time. So our 'meta-schema' is

DeviceId : int
SignalId : int
SignalDateTime : DateTime (with mSec as YY-MM-DD HHMMSS.mm)
ExtraData : String

I want to put this data into cassandra and Im trying to understand the various ways. I'd want to be able to get data out by any combination of the three values( DeviceId, SignalId, SignalDateTime).

I can imagine using DeviceId as a row key, then pairing SignalDateTime : SignalId. But then what do I do with ExtraData? Maybe make it a supercolumn? Similarly I could create unique row keys with DeviceId and SignalDateTime but is this a reasonable way to use Cassandra?

When you say you need to get data out by any combination of the three values what exactly do you mean. You might have a date but no device/signal id and you need to get all data that has that specific date. Or you might just have a signal id and need to get all devices that have that signal id as well as all the data for that signal id? — nickmbailey

psanford psanford · Accepted Answer · 2012-01-09T06:47:38

With Cassandra you really want to start with the different queries you intend to make on the data and work backward from there to the appropriate column family definitions. Ideally every query that you make will select data from only one row. If you want to achieve this while still being able to query by different fields you will need to denormalize that data across multiple column families.

You could start with the following column families:

CF1
RowKey: DeviceID
ColumnNames: SignalDateTime
Value: Serialized [SignalID + ExtraData]

CF2
RowKey: SignalID
ColumnName: SignalDateTime
Value: Serialized [DeviceID + ExtraData]

The value would be some serialized form of *ID + ExtraData (using JSON, ProtocolBuffers, etc.). With this schema you could query for all data from Device1 from time t0 to t1, or all data from Signal1 from t0 to t1.

Perhaps you would like to also query for DeviceID and SignalID from t0 to t1. This is a case where it would make sense to use composite columns:

CF3
RowKey: DeviceID
ColumnName: CompositeColumn[SignalID:SignalDateTime]
Value: ExtraData

To query this column family you would fetch the row based on DeviceID and slice columns for SignalID and time within your time range. The pycassa docs explain some of the basics of composite columns.

Cassandra: modeling timed data

1 Answers