How Does Composite Column PartitionKey Work in Cassandra -

i trying figure out advantages compound partition key can provide. @ famous weather station example below.

create table temperature ( state text, city text, event_time timestamp, temperature text, primary key ((state, city),event_time) );

now, of time query 1 single state on set of cities , range of dates. query like

select * temperature state = 'ny' , city in ('mahattan', 'brooklyn','queens') , event_time > '2016-01-01'.

assuming have large data set, in sense have few states (# < 1000) each state have many many cities ( # > 100m). replicate data , distribute them different nodes.

question: can compare differences using

primary key (**(state, city)**,event_time)  primary key (**(city, state)**,event_time)  primary key (state, city,event_time)  primary key (zipcode, event_time)  

thank you!

composite key

primary key (**(state, city)**,event_time) primary key (**(city, state)**,event_time) 

are functionally equivalent. composite partition key combined values of city , state. unable specify partition without both portions. within partition cells ordered event_time. have #state * #city partitions

[city, state] -> [event_time_0, event_time_1, event_time_2, event_time_3, ...] 

you able write queries

select * table city = x , state = y , event_time (><=) somevalue 

compound keys

primary key (state, city,event_time) 

one partition made every state. bad since there on order of 100x state/provinces means have small number of partitions. data laid out within partition city , event_time.

[illinois] --> [chicago, 0], [chicago, 1], [peoria, 0], [peoria, 1] 

queries have restrict city if restricting event time.

primary key (zipcode, event_time)  

you have 10k partitions, each have single cell each event time.