Cassandra(Amazon keyspace) Query Error on clustered columns

Question

I am trying execute query on clustering columns on amazon keyspace, since I don't want to use ALLOW FILTERING with my native query I have created 4-5 clustering columns for better performance.

But while trying to filter it based on >= and <= with on 2 clustering columns, I am getting error with below message

message="Clustering column "start_date" cannot be restricted (preceding column "segment_id" is restricted by a non-EQ relation)"

I had also tried with multiple columns query but I am getting not supported error message="MultiColumn relation is not yet supported."

Query for the reference

select * from table_name where shard_id = 568 and division = '10' and customer_id = 568113 and (segment_id, start_date,end_date)>= (-1, '2022-05-16','2017-03-28') and flag = 1;

or

select * from table_name where shard_id = 568 and division = '10' and customer_id = 568113 and segment_id > -1 and start_date >='2022-05-16';

score 2 · Accepted Answer · answered Aug 01 '22 at 12:04

I am assuming that the your table has the following primary key:

CREATE TABLE table_name (
    ...
    PRIMARY KEY(shard_id, division, customer_id, segment_id, start_date, end_date)
)

In any case, your CQL query is invalid because you can only apply an inequality operator on the last clustering column in your query. For example, these are valid queries based on your table schema:

SELECT * FROM table_name
    WHERE shard_id = ? AND division = ?
    AND customer_id <= ?

SELECT SELECT * FROM table_name \
    WHERE shard_id = ? AND division = ? \
    AND customer_id = ? AND segment_id > ?

SELECT SELECT * FROM table_name \
    WHERE shard_id = ? AND division = ? \
    AND customer_id = ? AND segment_id = ? AND start_date >= ?

All preceding columns must be filtered by an equality operator except for the very last clustering column in your query.

If you require a complex predicate for your queries, you will need to index your Cassandra data with tools such as Elasticsearch or Apache Solr. They will allow you to run complex search parameters to retrieve data from your database. Cheers!

Thank you Eric for the answer, Since amazon keyspace doesn't support secondary index creation, can you please tell the solution for such complex queries, FYI : Im using native query cassandra using spring boot and datastax driver — Rachit, Aug 01 '22 at 16:22
I have also raised another question, can you please provide me answer of this [link](https://stackoverflow.com/questions/73197046/amazon-keyspace-cassandra-query-support-for-in-clause-secondary-indexes) — Rachit, Aug 01 '22 at 16:56

score 0 · Answer 2 · answered Nov 17 '22 at 21:27

ALLOW Filtering gets a bad rap sometimes. It all depends on how many rows you end up scanning. It's good to understand how many rows per partition will be scanned and work backwards from there. Only the last column can contain inequality statements to bound ranges. Try to order your columns to eliminate the most columns first, which reduce the number of rows 'Filtered'.

In the example below we used the index for keys up to start date and filtered on end_data, segment_id, and flag.

select * from table_name where shard_id = 568 and division = '10' and customer_id = 568113 and start_date >= '2022-05-16' and end_date > '2017-03-28') and (segment_id > -1 flag = 1;```

Cassandra(Amazon keyspace) Query Error on clustered columns

2 Answers2