Cassandra cqlsh not working with where clause on non-partition key

Question

My table describe is :

CREATE TABLE user (
    id text,
    CustID int static,
    UpdateDate date,
    DateOfBirth date static,
    Gender text static,
    Address text static,
    City text static,
    State text static,
    Zip text static,
    Email  text static,
    Phone text static,
    OverallAssets double,
   PRIMARY KEY (id,UpdateDate)
);

select * from user is working fine.

select * from user where partition key is also working fine.

But if I am putting non partition key in where clause getting below error.What can be the reason ?

ReadFailure: Error from server: code=1300 [Replica(s) failed to execute 
read] message="Operation failed - received 0 responses and 1 failures" info=
{'failures': 1, 'received_responses': 0, 'required_responses': 1, 
'consistency': 'ONE'}

This sounds similar to your problem: http://stackoverflow.com/questions/37114455/reading-error-in-cassandra — Marko Švaljek, Apr 12 '17 at 10:58
I have increased the `tombstone_failure_threshold` value. Still its not working . — curiousguy, Apr 12 '17 at 11:01
Do you see something in the logs? It still might be the tombstones (should you have more than this). — Marko Švaljek, Apr 12 '17 at 11:02
I think we can query on non-partition key using allow filtering. I have tried that. — curiousguy, Apr 12 '17 at 11:09
Also you might want to increase the timeout of the cqlsh by using `cqlsh --request-timeout=3600` or even more ... Since you are probably going over a lot of data it takes time to respond ... a nice answer on this one here http://stackoverflow.com/questions/29437517/cassandra-timeout-cqlsh-query-largeish-amount-of-data also the node migt timeout then you need to change cassandra.yaml `range_request_timeout_in_ms` and `read_request_timeout_in_ms` — Marko Švaljek, Apr 12 '17 at 11:10
Yes I am doing `cqlsh --connect-timeout=100000000 --request-timeout=10000000000` — curiousguy, Apr 12 '17 at 11:11
Executing query with allow filtering might not be a good idea as it can use a lot of your computing resources. Don't use allow filtering in production Read the datastax doc about using ALLOW FILTERING — Ashraful Islam, Apr 12 '17 at 11:24

score 2 · Answer 1 · answered Apr 12 '17 at 13:29

2

select * from user where CustID =0 allow filtering;

In Cassandra you need to take a query-based modeling approach. The best way to solve this problem is with a table that is specifically designed to serve that query.

CREATE TABLE users_by_custid (
    id text,
    CustID int,
    UpdateDate date,
    DateOfBirth date static,
    Gender text static,
    Address text static,
    City text static,
    State text static,
    Zip text static,
    Email  text static,
    Phone text static,
    OverallAssets double,
   PRIMARY KEY (cust_id,id,UpdateDate)
);

That will work, it will distribute well, and it won't require the full table scans that accompany ALLOW FILTERING.

Yes I am doing cqlsh --connect-timeout=100000000 --request-timeout=10000000000

I can't warn you against doing this enough. Those timeout defaults exist for a reason. They protect your cluster/nodes from tipping over due to bad performing queries. When you are faced with a problem and tempted to increase the query timeouts, take a closer look at your query and see if there's a better way to build it.

answered Apr 12 '17 at 13:29

Aaron

55,518
11
116
132

So `cust_id` is my `partition key` here and `id,UpdateDate` are clustering keys right? Now I wonder If I want to query where clause on multiple others columns also , should I add them as clustering keys also or better to design multiple tables to serve the purpose. I am considering the performance here. – curiousguy Apr 12 '17 at 13:49
You will want to go the multiple table route. That's the tradeoff with Cassandra (or any distributed database, for that matter). Query flexibility is always difficult to achieve. It comes down to the question of how many tables do you want to have to keep in-sync before it makes more sense to use a different data store. – Aaron Apr 12 '17 at 13:55
@curiousguy Another thought: If you're on Cassandra 3.x, you may be able to mitigate some of the query table overhead by using a materialized view or (in certain scenarios) a SASI index. Those tools can help with query flexibility, if you're willing to take a little hit in read performance. – Aaron Apr 12 '17 at 14:30
yes looking at this https://www.datastax.com/dev/blog/new-in-cassandra-3-0-materialized-views as suggested by -Ashraful – curiousguy Apr 12 '17 at 14:34

score 1 · Answer 2 · answered Apr 12 '17 at 11:29

You are using allow filtering. Be careful. Executing this 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

Instead of using allow filtering create materialized view or index.

Check this link about creating and using materialized view : https://www.datastax.com/dev/blog/new-in-cassandra-3-0-materialized-views

Check this link about creating and using index : http://docs.datastax.com/en/cql/3.1/cql/cql_reference/create_index_r.html

When not to use an index
Do not use an index in these situations:

On high-cardinality columns because you then query a huge volume of records for a small number of results. See Problems using a high-cardinality column index below.
In tables that use a counter column On a frequently updated or deleted column. See Problems using an index on a frequently updated or deleted column below.
To look for a row in a large partition unless narrowly queried. See Problems using an index to look for a row in a large partition unless narrowly queried below.

Source : http://docs.datastax.com/en/cql/3.1/cql/ddl/ddl_when_use_index_c.html

Cassandra cqlsh not working with where clause on non-partition key

2 Answers2

Linked