Cassandra cqlsh not working with where clause for key without section
My table describes:
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 works fine.
select * from the user in which the partition key also works.
But if i put a key without clause in where where it gets below error. What could 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'}
source to share
select * from user where CustID =0 allow filtering;
In Cassandra, you need to use a query based approach. The best way to solve this problem is with a table specially designed to serve this 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)
);
This will work, it will distribute well, and it doesn't require the full table scan that accompanies ALLOW FILTERING
.
yes, I am doing
cqlsh --connect-timeout=100000000 --request-timeout=10000000000
I cannot warn you about this. These defaults exist for a reason. They protect your cluster / nodes from rollover due to failed requests. When you run into a problem and are trying to increase request timeouts, take a close look at your request and see if there is a better way to create it.
source to share
You are using allow filtering
. Be careful. Filtering this query may not be a good idea, as it can use up a lot of your computational resources and cannot return any result due to a timeout. Do not use permissive filtering in production. Read the datastax doc on using ALLOW FILTERING.
https://docs.datastax.com/en/cql/3.3/cql/cql_reference/select_r.html?hl=allow,filter
Instead of using permissive filtering, create a materialized view or index.
Check out this link about creating and using a materialized view: https://www.datastax.com/dev/blog/new-in-cassandra-3-0-materialized-views
Have a look at this link about creating and using an index: http://docs.datastax.com/en/cql/3.1/cql/cql_reference/create_index_r.html
If you do not use an index
Do not use an index in the following situations:
- High performance columns because you are querying a huge amount of records for a small number of results. See Problems Using High Cardinality Column Index below.
- In tables that use a counter column In a frequently updated or deleted column. See Index Usage Issues on a Frequently Updated or Dropped Column below.
- Find a string in a large section, unless it's a narrow query. See Problems using an index to find a string in a large section, unless narrowly specified below.
Source: http://docs.datastax.com/en/cql/3.1/cql/ddl/ddl_when_use_index_c.html
source to share