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