How to create Cassandra primary key in correct way

user_11564329 0
Viewed 148

I have the following table structure:

CREATE TABLE test_keyspace.persons (
    id uuid,
    country text,
    city text,
    address text,
    phone_number text,
    PRIMARY KEY (id, country, address)
);

My main scenario is to get person by id. But sometimes I want to get all cities inside country and all persons inside city as well.

I know that Cassandra must have at least one partition key and zero or more clustering keys, but I don't understand how to organize it to work most effectively (and generally work).

Can anybody give me advice?

nosql cassandra
1 Answers

So it sounds like you want to be able to query by both id and country. Typically in Cassandra, the way to build your data models is a "one table == one query" approach. In that case, you would have two tables, just keyed differently:

CREATE TABLE test_keyspace.persons_by_id (
    id uuid,
    country text,
    city text,
    address text,
    phone_number text,
    PRIMARY KEY (id));

TBH, you don't really to cluster on country and address, unless a person can have multiple addresses. But a single PK is a completely legit approach.

For the second table:

CREATE TABLE test_keyspace.persons_by_country (
    id uuid,
    country text,
    city text,
    address text,
    phone_number text,
    PRIMARY KEY (country,city,id));

This will allow you to query by country, with persons grouped/sorted by city and sorted by id. In theory, you could also serve the query by id approach here, as long as you also had the country and city. But that might not be possible in your scenario.

Duplicating data in Cassandra (NoSQL) to help queries perform better is ok. The trick becomes keeping the tables in-sync, but you can use the BATCH functionality to apply writes to both tables atomically.

In case you haven't already, you might benefit from DataStax's (free) course on data modeling - Data Modeling with Apache Cassandra and DataStax Enterprise.

user_10545580
Related