CODEWITHSUNDEEP
cassandracassandra-3.0
Karthick Jayaraman
Karthick Jayaraman

Reputation: 301

Cassandra query failed to exec - Want to know the reason

I'm working on creating a Scheduler service which require Cassandra table structure as below.

CREATE TABLE IF NOT EXISTS spc_cmd_scheduler (
id timeuuid,
router_id text,
account_id text,    
mode text,
triggered_by text,
retry_count smallint,
PRIMARY KEY ((triggered_by,retry_count),id)
)WITH CLUSTERING ORDER BY (id ASC);

When I do query with PK I'm getting below error. May I know the reason why?

select count(*) from spc_cmd_scheduler  where triggered_by = 'ROUTER_ONBOARD' and retry_count < 3;

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"

I understand "ALLOW FILTERING" will solve my problem here but wanted to know what is wrong with the table structure. What is the optimal way to design this table that suits my requirement.

Just to give background of my requirement, I need to run a scheduler to scan this table and issue a command and delete the entry once its successful. If command fails, I need to retry for 3 times. So this table requires SELECT, UPDATE and DELETE operations.

Upvotes: 0

Views: 109

Answers (1)

Alex Ott
Alex Ott

Reputation: 87359

In your case, the problem is that retry_count column is the part of the partition key, and we can use only equality operators (= or IN) for partition key column. Inequality operations (<, >, etc.) are supported only for clustering columns, and all preceding clustering columns need to be specified.

Upvotes: 1

Related Questions