Cassandra: Create a partitioned table without including the partitioned column name in primary key

Question

I want to create a partitioned table without including the partitioned column name in primary key.
I am not getting any clue how to create it because most of the examples declaring the clustering column in the primary key section. I am not sure whether it is doable in Cassandra or not hence seeking some help.

CREATE TABLE crossfit_gyms_by_city (  
 country_code text,  
 state_province text,  
 city text,  
 gym_name text,
 year int,
 month int,
 day int);

My requirement is:
The table should be able to store the data as Year/Month/Day partition and by the sorting order of city.

What is your query pattern requirement? Is it to fetch data based on the Year,Month,Day,City? Also I didn't understand you want to partition the table but not mention the columns to partition on when defining the table? — Yamini
@Yamini data should be stored as a Year/Month/Day partition sorted by Bity name. My table example is sample table creation, but I do not know how to define partitioned table as per my requirement. — BlueStar
In Cassandra the table structure is built from the queries requirements... I strongly recommend to take DS220 course before continue — Alex Ott
@Alex working on some urgent deliverable hence instead of checking all the details started working :( — BlueStar
"I want to create a partitioned table without including the partitioned column name in primary key" - Yeah, Cassandra doesn't work that way. It needs the partition key in the PRIMARY KEY definition so that it knows how to partition the data in the first place. — Aaron

Yamini Yamini · Accepted Answer · 2020-01-23T09:37:00

Based on the comments, I am assuming the way you store data is how you plan to fetch it. Based on this assumption I propose the below table structure; CREATE TABLE crossfit_gyms_by_city ( year int, month int, day int, city text, country_code text, state_province text, gym_name text, PRIMARY KEY ((year, month, day), city));

So your data will be partition based on year,month,day & your clustering column would be city so the data is sorted by city

Also I would suggest going through this course which will help you out with your data modelling requirements https://academy.datastax.com/resources/ds220?path=developer

Cassandra: Create a partitioned table without including the partitioned column name in primary key

1 Answers