IF condition based on count in cassanda

Question

I need an Query for group by along with if condition based on count and i have working query in mysql below. select notification,subject,max(time) as recent,value,IF(COUNT(event.value) > 1, 'Abnormal', 'Normal') AS NewResult from event where time between date_add(now(), interval -10 minute) and now() group by device,value order by time;

Is there any relevant query in cassandra for this.

Answer 1

No. This would require filtering and at least a scatter gather across all token ranges (if using indexes), and likely require a full table scan. Both of which will not scale well so are barely supported at all so the more complex expressions are not allowed in the cql syntax at all.

In cassandra you design your tables around your queries, not design your queries around your data. You really probably want to consider using spark or hadoop to do more complex bulk processing but really you are not using the database for what its designed for. If its just a single run adhoc query you can write a client app that pulls all the data down, aggregating and filtering.

If you have an opportunity consider changing your data model if this is read is a requirement.