Cassandra Table data modeling for a event system monitoring data

Question

I have been working in a cassandra database to keep the real time data of an event/monitoring system that has quite a large number of events per hour(100k+) and I have hit a wall in a specific need: I need a way to get the data by event state(open,close) for all the events.

Each event has the following fields: id(this is generated by the event system), state, start_date,end_date,node, description.

My first aproach was to have a table with state as partition key and id as clustering:

CREATE TABLE alarms_by_state (
id,state,start_date,end_date,node, 
PRIMARY KEY((state),id))

This that didn't work, as it keeps 2 states for each id.

What I need is that for example we receive event with id = 1 and state=open and the application saves it into the database, later another event with id=1 and state=closed and replaces the previous record.

I know this could all be done in the application side, but I'm trying to keep that control to a minimum due to the number of events I receive per hour.

Thank you for your time

Answer 1

that didn't work, as it keeps 2 states for each id.

I like that you tried this. For anyone who sees this post in the future, it'll underscore the point that primary keys in Cassandra are unique.

Here's how I would do it.

CREATE TABLE alarms_by_date (
    id UUID,
    state TEXT,
    start_date DATE,
    end_date DATE,
    node TEXT, 
    PRIMARY KEY((day,hour,m15),start_date,id))
WITH CLUSTERING INDEX ON (start_date DESC,id ASC);

CREATE INDEX ON alarms_by_date(state);

What this does:

• Stores your data partitioned in 15 minute intervals. With 100k/hour, that's too much to throw into a single partition. Breaking that into 4 parts should keep it from growing too large. This could be done with a single partition key, as long as the key was made up of those components (ex: 202102171045).
• Applying a clustering index of start_date and id. Not sure which of the two dates (start/end) are more important to your business case. But this way, the data will be in descending order by time. Also, I like using id columns as the last clustering key, to help ensure uniqueness.
• Applying a secondary index on status is really the way to go here. Yes, there's a lot wrong with secondary indexes in Cassandra. But, the main problem with them, is that they can result in scans of multiple nodes. By pre-empting queries using that index with the date/partition, queries will be restricted to only a single node. So I think that'd be the way to go.