Cassandra or Hbase?

Question

I have a requirement, where I want to store the following:

• Mac Address // PKEY
• TimeStamp // PKEY
• LocationID
• ownerName
• Signal Strength

The insertion logic is as follows:

• Store the above statistics for each active device (MacAddress) once every hour at each location (LocationID)
• The entries are created at end of each hour, so the primary key will always be MAC+TimeStamp

There are no updates, only insertions

The queries which can be performed are as follows:

• Give me all the entries for last 'N' hours Where MacAddress = "...."
• Give me all the entries for last 'N' hours Where LocationID IN (locID1, locID2, ..);

Needless to say, there are billions of entries, and I want to use either HBASE or Cassandra. I've tried to explore, and it seems that Cassandra may not be correct choice.

The reasons for that is if I have the following in cassandra:

< < RowKey > MacAddress:TimeStamp > >
+ LocationID
+ OwnerName
+ Signal Strength

Both the queries will scan the whole database, right? Even if I add an index on LocationID, that is only going to help in the second query to some extent, because there is no index on timestamp (I believe that seaching on timestamp is not fast, as the MacAddress:TimeStamp composite Key would not allow us to search only on timestamp, and instead, a full scan would happen, is that correct?).

I'm stuck here big time, and any insights would really help, if we should opt HBase or Cassandra.

Answer 1

The right way to model this with Cassandra is to use a table partitioned by mac address, ordered by timestamp, and indexed on location id. See the Cassandra data model documentation, especially the section on clustering [predefined sorting]. None of your queries will require a full table scan.

Answer 2

You have to remember that NoSql instances like Cassandra allow horizontal scaling and make it a lot easier to shard the data. By developing a shard strategy (identifying shard key, etc) you could dramatically reduce the size of the data on a single instance and make queries (even when trying to query massive data sets) doable.

Answer 3

Either one would work for this query:

Give me all the entries for last 'N' hours Where MacAddress = "...."

In cassandra you would want to use an ordered partitioner so you can do easy scans. That way you would not have to scan the entire table. (I'm a little rusty on Cassandra).

In hbase it is always ordered by the rowkey so the scan becomes easy. You would just set a start and stop rowkey. Conceptually it would be:

  scan.setStartRow(mac+":"+timestamp);
  scan.setStopRow(mac+":"+endtimestamp);

And then it would only scan over the rows for the given mac address for the given time period--only a small subset of the data.

This query is much harder:

Give me all the entries for last 'N' hours Where LocationID IN (locID1, locID2, ..);

Cassandra does have secondary indexes so it seems like it would be "easy" but I don't know how much data it would scan through. I haven't looked at Cassandra since it added secondary indexes.

In hbase you'd have to scan the entire table or create a second table. I would recommend creating a second table where the rowkey would be < location:timestamp > and you'd duplicate the data. Then you'd use that table to lookup the data by location using a scan and setting the start and end keys.