[RESOLVED] Spring Data Cassandra : Cannot execute this query as it might involve data filtering - DevDummy

Latest

Views | Thoughts | Concepts | Techniques

Friday, January 19, 2018

[RESOLVED] Spring Data Cassandra : Cannot execute this query as it might involve data filtering



The Issue

Following error can be noticed while using the Spring Data Cassandra, if you need to search between a range on a column where secondary index is set on it.

org.springframework.data.cassandra.CassandraInvalidQueryException: Query;  CQL [SELECT * FROM **** WHERE *** > * AND *** < *** AND **** <  **** ;];  
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;

Cause of the Issue

The reason here is, if you are searching for non equivalence, cassandra has to do a full table scan and run filtering on it to fetch the expected data as the data is sorted and stored to fetch on equivalence. So instead of performing the above action, Cassandra fails the request with warning and asks us to enforce above process if we really need to do this with the significant performance penalty.

However it is a question, that where we have to set the 'ALLOW FILTERING'. Here Spring Data Cassandra is smart enough to make the query in a repository interface without an implementation, it can't notice the requirement of data filtering. 

Fix/ Workaround

Here, we need to annotate and mark the interface method with @AllowFiltering to ping Spring Data Cassandra to send it with the query and allowing the database to perform it.

@AllowFiltering
Book findByPublishedYearGreaterThanAndPublishedYearLessThan(int startyear, int endYear)

Note

use of ALLOW FILTERING is not recommended unless that you are confident the table contains a very small set of data in it. Otherwise significant performance issues may appear due to full table scan & filtering. 

See this post for more details and optimisations on data filtering.


References:

1. https://www.datastax.com/dev/blog/allow-filtering-explained-2
2. https://pixabay.com/en/filter-mechanics-vehicle-640487/

1 comment:

  1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Project Centers in Chennai.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete