How to decide for clustering columns in Cassandra primary key?

user_2444661 0
Viewed 127

I am following book 'Definitive Cassandra'. A hotel application is used as an example in it. THere is a table available_rooms_by_hotel_date. It is to support use case when user wants to know about room availability in a given hotel from a given date. Data model is defined as:

hotel_id
date
room_number
is_available

hotel_id is partition key, while date and room_number are clustering columns.

Looking at the table, one can say that it supports use case when user wants to know availability of room from a given date for a given hotel.

I understand that order of clustering columns is also critical in Cassandra. So if I change order for date and room_number, how would it impact? Functionality wise I think use case is still supported. But does it impact performance or any other aspects like storage, node allocation etc?

cassandra
1 Answers

In Cassandra you can query a table by:

  1. the full primary key (hotel_id, date, room_number) - in this case you fetch only one row
  2. the partition key (hotel_id) - in that case you get all rows inside given partition - it's a minimal requirement for SELECT query;
  3. a partial primary key - partition key + some of the clustering columns, from left to right (any preceding clustering columns defined in the primary key should be specified). In the given example, you can specify only hotel_id and date, and it will return all rows for given date (or dates, if you do date IN (...). Another useful feature of the clustering column is that you can do a range query on it (but only on the last specified clustering column!). For example, if I want to find all the rooms in a given date range, I can do ... WHERE hotel_id = ... AND date >= '2020-04-05' AND date <= '2020-04-10'.

If you change the order of the room_number and date, then you could only ask for availability of the specific room(s) on date (or overall), not all rooms on specific dates - because you need to specify all preceding clustering columns, but you have only hotel_id and date, but not room_number...

Alex_Ott_186270
Related