Need Cassandra schema

HI Just started to investifate Cassandra and have a bit co开发者_Go百科nfusion. Could you suggest schema for following:

Schema: email, city, items1[], items2[]

Input: cityId, item1, item2

I need:

select email 

where city=cityId 

and item1 is NOT in items1[] 

and item2 is NOT in items2[]

Is it possible?

You may need to give a bit more detail to get a thorough answer to this one.

In Cassandra 0.7 onwards, you can use a secondary index to select rows according to column value (i.e. select rows where city=cityId). You will need to enable this in your Cassandra schema by setting "index_type: KEYS" in the column metadata.

See http://www.datastax.com/dev/blog/whats-new-cassandra-07-secondary-indexes for more details.

Cassandra does not provide negation, so your "NOT in items1[]" conditions may need to be tested by the client, not by the Cassandra nodes. How many possible values are there for your items1 and items2 (just a handful, or thousands?).

You will probably need to set up a column family purely for answering this specific type of query, i.e. a lookup table with some kind of compound key based on item1 and item2. However, this may be challenging to maintain server-side if there are many potential values of item1 and item2, and expensive to retrieve client-side!