How to use less <= and >= in materlized view table in cassandra

user_12759780 0
Viewed 72

My CQL query

CREATE MATERIALIZED VIEW mv_test AS 
SELECT *  FROM host_accounts WHERE id IS NOT NULL AND creation_ts IS NOT NULL 
PRIMARY KEY (id, creation_ts) WITH clustering ORDER BY (creation_ts DESC);

this my query but it's gave this error InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING" when i am using this query select * from mv_test where creation_ts > 1606761000 AND creation_ts < 1609353000;

cql spring-boot cassandra database java
1 Answers

This is correct behavior of Cassandra because your query is restricted only by the condition on creation_ts that is the clustering column. And because you don't have restriction on the id field, Cassandra don't know the partition key, and to fulfill the condition it will need to go through all data and apply filter. And by default, Cassandra don't allow such behavior without ALLOW FILTERING - the same as if you have such condition on the "normal" table.

If you want to perform such query in your system, you need to change approach:

  • Change data model, so data will be partitioned by date/part of timestamp, and then you need to issue multiple queries to cover all necessary time ranges
  • Use Spark or custom code that will perform correct scanning of all data. But this won't be fast because you'll need to process a lot of data.
Alex_Ott_186270
Related