0

Due to some technical hiccups of using cassandra with Talend, we are using stargate api to read and write data into DSE Cassandra. I had to say, i am completely new to the cassandra or even NoSql world.

I have few fields, status(text), status_code(text), and attemp_count(int). Now i need read data from Cassandra with below condition.

condition:

status!='PROCESSED' and status_code!=400 and attemp_count<8

Below is how my table design looks.

enter image description here

And below is the error i am getting.

Column 'status_code' has an index but does not support the operators specified in the query. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

{
    "description": **"Bad request: org.apache.cassandra.stargate.exceptions.InvalidRequestException:** Column 'status_code' has an index but does not support the operators specified in the query. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING",
    "code": 400
}

Query i am using to do a simple test:

{{url}}/v2/keyspaces/dco/mc_inbound_log?where={"status_code":{"$gt":"201"}}
Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
Romano
  • 43
  • 4
  • 1
    This looks like more of a data modeling issue rather than Stargate specific. You would get the same error using CQL since range queries on Secondary Indexes aren't supported. – dwettlaufer Oct 06 '21 at 17:15

2 Answers2

2

You can try using status!='PROCESSED' and status_code!='400' and attemp_count<8

Since status_code is a string you will need the quotes.

Patrick Callaghan
  • 76
  • 3
  • Thank you for answering, but i was looking for how the same condition can be written as where clause for stargate api, like i mentioned above. – Romano Oct 06 '21 at 17:16
  • 1
    You can try the following but I'm not sure if it will work since it looks like neither status nor attempt_count are indexed `{"status_code":{"$ne":"400"},"status":{"$ne": "PROCESSED"},"attempt_count":{"$lt": 8}}` – dwettlaufer Oct 06 '21 at 17:27
2

dwettlaufer is indeed correct in saying that this is a data modelling issue. The underlying CQL query isn't valid so Stargate.io will not be able to run it.

You are filtering on columns which are (1) not part of the primary key, and (2) not indexed. This is the reason why (3) ALLOW FILTERING is required. Negative filters (!=) is also expensive because they'll require a full table scan.

You have an analytics use case so consider using Spark or Solr. Cheers!

Erick Ramirez
  • 13,964
  • 1
  • 18
  • 23
  • Thanks for the reply. Just in case, if i need to use ALLOW FILTERING for now on the stargate api, how do do it on the below url.?? {{url}}/v2/keyspaces/dco/mc_inbound_log?where={"status_code":{"$gt":"201"}}. Appreciate any help. – Romano Oct 07 '21 at 13:12
  • The REST API does not currently support `ALLOW FILTERING`. – dwettlaufer Oct 07 '21 at 16:42
  • 1
    I wanted to echo @dwettlaufer's response. Is this for your work? If you reach out to us through the live chat on the Astra website, we'd be happy to work with you to see if we can come up with a solution. Cheers! – Erick Ramirez Oct 07 '21 at 23:41