spring-data-cassandra: InvalidQueryException: Cannot execute this query ... use ALLOW FILTERING

Question

I have the following code

  @Indexed
  @PrimaryKeyColumn(name = "x", ordinal = 1, type = PrimaryKeyType.PARTITIONED)
  @Column(value="x")
  private String x;

  @Indexed
  @PrimaryKeyColumn(name = "code", ordinal = 2, type = PrimaryKeyType.PARTITIONED)
  @Column(value="code")
  private String code;

@Query(value = "select * from customers where code = ?0")
Optional<Customer> findByCode(String code);

When this is executed, I get Caused by: com.datastax.driver.core.exceptions.InvalidQueryException: 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.

Is there a way to avoid this just from spring-data-cassandra? I do not want to add ALLOW FILTERING in my query. I tried creating a separate index on the code column but this haven't solved the issue. I think it stops in the spring data configuration. If I execute the same query in cqlsh, it works.

Ashraful Islam · Accepted Answer · 2017-10-04 15:29:29Z

3

You must specify partition key on your query, unless you create index or use ALLOW FILTERING

Executing query with allow filtering might not be a good idea as it can use a lot of your computing resources and Might not return any result because of timeout. Don't use allow filtering in production Read the datastax doc about using ALLOW FILTERING

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/select_r.html?hl=allow,filter

answered Oct 4, 2017 at 15:29

Ashraful Islam

12.9k4 gold badges35 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

davey Over a year ago

what does it mean in practice "specify partition key on your query" ? how to do that in the code?

Ashraful Islam Over a year ago

For simple primary key, first field is the partition key. More on here stackoverflow.com/a/24953331/2320144

Omtara · Accepted Answer · 2020-02-05 07:41:01Z

When using a no-sql database, you need to properly design your data to avoid filtering. You can add a secondary index to optimize retrieval by a specific field. More details here: https://docs.datastax.com/en/archived/cql/3.3/cql/cql_using/useSecondaryIndex.html

If you are sure that the query is what you need, you can use the allowFiltering parameter on the @Query annotation to explicitly indicate that ALLOW FILTERING be used.

@Query(value = "select * from customers where code = ?0", allowFiltering = true)
Optional<Customer> findOneByCode(String code);

Collectives™ on Stack Overflow

spring-data-cassandra: InvalidQueryException: Cannot execute this query ... use ALLOW FILTERING

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related