Getting error while reading data from DSE cluster with Stargate API

Question

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.

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"}}

Answer 1

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.

Answer 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!