How to model for querying multiple column efficiently?

user_11243491 0
Viewed 46

I am humbly coming here to look for help, as I am currently puzzled by some aspects of Cassandra modelling and its implications.

I understood that ideally, there would be a table for each column that we want to filter by. (I met some issues with secondary index not being updated).

So let's take an example:

class Pet(Model):
    pet_id = columns.UUID(partition_key=True, default=uuid.uuid4)
    name = columns.Text()
    age = colmuns.Text()

class PetByName(Model):
    pet_id = columns.UUID(default=uuid.uuid4)
    name = columns.Text(partition_key=True)
    age = colmuns.Text()

class PetByAge(Model):
    pet_id = columns.UUID(default=uuid.uuid4)
    name = columns.Text()
    age = colmuns.Text(partition_key=True)

Queries are then simple if I want to query for one attribute for example:

PetByName.filter(name="kitty")

So my first question is: How do we do if we want, for example, to query for a pet named "kitty" and aged of "25 years".

Right now, I have a quite cumbersome implementation where I do two queries, and I then loop over the results to do a manual intersection on the pet_id attribute.

But in my head, there should be an implementation/type of batch queries that would run both queries at the same time and then merge the result sets based on an operator like intersect.

Any help, opinion, piece of advice is warmly welcomed.

Thank you for reading, and have a lovely day!

denormalization cassandra model python
1 Answers

If you want to query either by name or by age your table structure should look like this:

CREATE TABLE pet_by_name (
     id uuid,
     name text,
     age integer,
     PRIMARY KEY (name, age)
);

In this case age is a clustering key. This table supports queries by name only or by name AND age.

user_14614990
Related