retrieving data from cassandra database

Question

I'm working on smart parking data stored in Cassandra database and i'm trying to get the last status of each device. I'm working on self-made dataset. here's the description of the table. table description

select * from parking.meters

need help please !

Answer 1

trying to get the last status of each device

In Cassandra, you need to design your tables according to your query patterns. Building a table, filling it with data, and then trying to fulfill a query requirement is a very backward approach. The point, is that if you really need to satisfy that query, then your table should have been designed to serve that query from the beginning.

That being said, there may still be a way to make this work. You haven't mentioned which version of Cassandra you are using, but if you are on 3.6+, you can use the PER PARTITION LIMIT clause on your SELECT.

If I build your table structure and INSERT some of your rows:

aploetz@cqlsh:stackoverflow> SELECT * FROM meters ;

 parking_id | device_id | date                 | status
------------+-----------+----------------------+--------
          1 |        20 | 2017-01-12T12:14:58Z |  False
          1 |        20 | 2017-01-10T09:11:51Z |   True
          1 |        20 | 2017-01-01T13:51:50Z |  False
          1 |         7 | 2017-01-13T01:20:02Z |  False
          1 |         7 | 2016-12-02T16:50:04Z |   True
          1 |         7 | 2016-11-24T23:38:31Z |  False
          1 |        19 | 2016-12-14T11:36:26Z |   True
          1 |        19 | 2016-11-22T15:15:23Z |  False

(8 rows)

And I consider your PRIMARY KEY and CLUSTERING ORDER definitions:

  PRIMARY KEY ((parking_id, device_id), date, status)
) WITH CLUSTERING ORDER BY (date DESC, status ASC);

You are at least clustering by date (which should be an actual date type, not a text), so that will order your rows in a way that helps you here:

aploetz@cqlsh:stackoverflow> SELECT * FROM meters PER PARTITION LIMIT 1;

 parking_id | device_id | date                 | status
------------+-----------+----------------------+--------
          1 |        20 | 2017-01-12T12:14:58Z |  False
          1 |         7 | 2017-01-13T01:20:02Z |  False
          1 |        19 | 2016-12-14T11:36:26Z |   True

(3 rows)