CODEWITHSUNDEEP
cassandracassandra-2.0cqlcql3
Battle_Slug
Battle_Slug

Reputation: 2105

How to make the query to work?

I have Cassandra version 2.0, and in it I am totally new in it, so the question... I have table T1, with columns with names: 1,2,3...14 (for simplicity); Partitioning key is column 1 , 2; Clustering key is column 3, 1 , 5; I need to perform following query:

SELECT 1,2,7 FROM T1 where 2='A';

Column 2 is a flag, so values are repeating. I get the following error:

Unable to execute CQL query: Partitioning column 2 cannot be restricted because the preceding column 1 is either not restricted or is restricted by a non-EQ relation

So what is the right way to do it? I really need to get the data that already filtered. Thanks.

Upvotes: 0

Views: 73

Answers (2)

Castaglia
Castaglia

Reputation: 3089

So, to make sure I understand your schema, you have defined a table T1:

CREATE TABLE T1 (
  1 INT,
  2 INT,
  3 INT,
  ...
  14 INT,
  PRIMARY ((1, 2), 3, 1, 5)
);

Correct?

If this is the case, then Cassandra cannot find the data to answer your CQL query:

SELECT 1,2,7 FROM T1 where 2 = 'A';

because your query has not provided a value for column "1", without which Cassandra cannot compute the partition key (which requires, per your composite PRIMARY KEY definition, both columns "1" and "2"), and without that, it cannot determine where to look on which nodes in the ring. By including "2" in your partition key, you are telling Cassandra that that data is required for determine where to store (and thus, where to read) that data.

For example, given your schema, this query should work:

SELECT 7 FROM T1 WHERE 1 = 'X' AND 2 = 'A';

since you are providing both values of your partition key.

@Caleb Rockcliffe has good advice, though, regarding the need for other, secondary/supplemental lookup mechanisms if the above table definition is a big part of your workload. You may need to find some way to first lookup the values for "1" and "2", then issue your query. E.g.:

CREATE TABLE T1_index (
  1 INT,
  2 INT,
  PRIMARY KEY (1, 2);
);

Given a value for "1", the above will provide all of the possible "2" values, through which you can then iterate:

SELECT 2 FROM T1_index WHERE 1 = 'X';

And then, for each "1" and "2" combination, you can then issue your query against table T1:

SELECT 7 FROM T1 WHERE 1 = 'X' AND 2 = 'A';

Hope this helps!

Upvotes: 1

Caleb Rackliffe
Caleb Rackliffe

Reputation: 575

Your WHERE clause needs to include the first element of the partition key.

Upvotes: 1

Related Questions