CODEWITHSUNDEEP
cassandracassandra-3.0
Rimander
Rimander

Reputation: 131

Cassandra has two primary key and use the second

I created the next table in Cassandra

create table yyy (
 index1 int,
 index2 int,
 name text,
 primary key ((index1, index2), name)
)

when i search only by index1, perfect. Its fine!

select * from yyy where index1 ...

Buy i cant search by index2

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

How can I search by index2?

Upvotes: 3

Views: 2353

Answers (1)

Justin Cameron
Justin Cameron

Reputation: 611

Cassandra is much less flexible on how data can be queried than a relational database.

What you have called index1 and index2 are actually together called a composite partition key in Cassandra. In order to use a WHERE clause, you must always provide the entire partition key.

For example, these queries will succeed:

SELECT * FROM yyy WHERE index1 = 1 AND index2 = 2;

SELECT * FROM yyy WHERE index1 = 1 AND index2 = 2 AND name = 'fred';

But these queries will all fail:

SELECT * FROM yyy WHERE index1 = 1;

SELECT * FROM yyy WHERE index2 = 2;

SELECT * FROM yyy WHERE index1 = 1 AND name = 'fred';

This is because the partition key is used by Cassandra to determine which node the partition will be stored on. If you only supply half of the key, Cassandra cannot tell which node contains the data.

When data modelling for Cassandra you generally denormalize tables if certain queries are required that cannot be served by a single table. For example, if you needed to query on index2 alone, you might make a second table with this schema:

create table zzz (
 index1 int,
 index2 int,
 name text,
 primary key ((index2), index1, name)
)

Now you can execute SELECT * FROM zzz WHERE index2 = 2;

Note that in this case you'll also need to ensure that your index2 partitions will not get too large.

There are also some other feature to allow for more flexible querying, although you should fully understand their limitations before using them, as they can lead to significant performance or operational issues when not used as intended:

I highly suggest you read up on Cassandra data modelling best practices if you're new to Cassandra, as it's not as straightforward as it is with relational databases. There are a lot of good blogs and resources on this out there - google is your friend!

Upvotes: 8

Related Questions