Coming in 1.2: Collections support in CQL3

Apache Cassandra is a distributed store. When you distribute data over many machines, doing joins at read time is expensive in the general case (compared to what can be done on a single host), as you might have to join over data that is not stored on the same physical host. That is why Cassandra has always encouraged denormalization instead of joins, i.e. physical collocation of the data that is queried together. In particular, the Cassandra Query Language does not offer JOIN in the language.

Not using joins has however the drawback of making some simple patterns less elegant. Consider the case where you want to allow users to have multiple email addresses. In a relational database, the canonical way to do that would be a create an email_addresses table with a many-to-one relationship to users ... which implies a join. So in Cassandra, you would traditionally denormalize that as muliple columns email1, email2, etc. While this is usually fine from a performance standpoint (because both adding new columns and having columns without values is virtually free in Cassandra), this is tedious to use, not very natural, and have a few drawback like forcing you to do a read before adding a new email address (to know which column name to use). Enter the CQL3 supports for collections.

Sets

Cassandra 1.2 (slated for release towards the end of October) will provide a much powerful and elegant alternative to the "multiple email addresses problem" above. It introduces 3 new CQL3 types: map, set and list. Concretely, you will be able to define the users table as:

cqlsh> CREATE TABLE users (
           user_id text PRIMARY KEY,
           first_name text,
           last_name text,
           emails set<text>
       );

The operations allowed on that set include setting the set to specific value using a set literal:

cqlsh> INSERT INTO users (user_id, first_name, last_name, emails)
       VALUES('frodo', 'Frodo', 'Baggins', {'f@baggins.com', 'baggins@gmail.com'});

as well as adding new elements:

cqlsh> UPDATE users
       SET emails = emails + {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';

which, upon query, will return:

cqlsh> SELECT user_id, emails FROM users WHERE user_id = 'frodo';
 user_id | emails
---------+-------------------------------------------------------------------
 frodo   | {"baggins@caramail.com","f@baggins.com","fb@friendsofmordor.org"}

Note that the elements of the set are returned in sorted order (i.e. sets are really sorted sets). If a different order (like the insertion order) is important, you might prefer using a list instead.

You can also remove an email:

cqlsh> UPDATE users
       SET emails = emails - {'fb@friendsofmordor.org'} WHERE user_id = 'frodo'

or remove all the emails, which can be done by either one of the two following queries (that are equivalent):

cqlsh> UPDATE users SET emails = {} WHERE user_id = 'frodo';
cqlsh> DELETE emails FROM users WHERE user_id = 'frodo';

What this means is that a set (or a list or map for that matter) only exists when it has one element. Or in other words, the empty set is not distinguishable of the null value:

cqlsh> SELECT user_id, emails FROM users WHERE user_id = 'frodo';
 user_id | emails
---------+-------------------------------------------------------------------
 frodo   | null

Note that if the set (or list/map) is empty (i.e. null), the two following queries will yield the same result:

cqlsh> UPDATE users SET emails = {'a@b.com'} WHERE user_id = 'frodo';
cqlsh> UPDATE users SET emails = emails + {'a@b.com'} WHERE user_id = 'frodo';

but if emails is not empty, the first query will replace the existing set by the set containing a@b.com as sole element, while the second query will add the value to the existing set.

Using a set has the following advantages over the email1, email2, ... solution hinted above:

• It's undeniably cleaner and more natural to use.
• Adding a new email don't require a read (and in particular, no reads are done internally). And because it is a set, you do not have to care about adding an email multiple times.

Lists

The list type is also provided for the cases where the order of elements matters (and is not the natural order induced by the type of the elements) or when the same value should be allowing multiple times. Suppose for instance that each users can specify its top 5 list of preferred places. You could build on the previous example with:

cqlsh> ALTER TABLE users ADD top_places list<text>;

and then:

cqlsh> UPDATE users
       SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id = 'frodo';

You can prepend elements:

cqlsh> UPDATE users
       SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';

as well as append ones:

cqlsh> UPDATE users
       SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo';

Note that all of these operations are implemented internally without any read-before-write, and appending/prepending a new element only writes this new element, it neither read anything nor imply rewriting the whole list.

Lists also support setting (and removing) an element by its index:

cqlsh> UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo';
cqlsh> DELETE top_places[3] FROM users WHERE user_id = 'frodo';

However, one must be aware that these two operations do require a read of the whole list internally (only the updated element is written however after that read, not the whole list) and so they will have a greater latency than appending or prepending for instance.

At that point, you will have:

cqlsh> SELECT user_id, top_places FROM users WHERE user_id = 'frodo';
 user_id | top_places
---------+----------------------------------------
 frodo   | ["the shire","rivendell","riddermark"]

The last supported operation on lists is removing elements by values:

cqlsh> UPDATE users
       SET top_places = top_places - ['riddermark'] WHERE user_id = 'frodo';

will remove all the occurrences of 'riddermark' from the list. As with index based operations, this requires a read internally. Note that one could want to "emulate" this operation client-side by reading the whole list, finding the indexes that contain the value to remove and then issue remove of those indexes. This however wouldn't be "thread-safe", in the sense that if another thread/client prepend elements to the list between the read and the write, the wrong elements will be removed. This native operation does not suffer from that problem.

Maps

The last provided collection type is the map. Say you want to store in each user profile a very basic reminder/todo list, that associates to a timestamp something that the user should remember before that time. You can add:

cqlsh> ALTER TABLE users ADD todo map<timestamp, text>;

Then the map can be wholly set/replaced using a map literal:

cqlsh> UPDATE users
       SET todo = { '2012-9-24' : 'enter mordor',
                    '2012-10-2 12:00' : 'throw ring into mount doom' }
       WHERE user_id = 'frodo';

or you can update/set/delete a specific element using:

cqlsh> DELETE todo['2012-9-24'] FROM users WHERE user_id = 'frodo';
cqlsh> UPDATE users SET todo['2012-10-2 12:00'] = 'throw my precious into mount doom'
       WHERE user_id = 'frodo';
cqlsh> UPDATE users SET todo['2012-10-2 12:10'] = 'die' WHERE user_id = 'frodo';

Note that similarly to sets, maps are really ordered maps (the order is the one induced by the type of the keys):

cqlsh> SELECT user_id, todo FROM users WHERE user_id = 'frodo';
 user_id | todo
---------+-------------------------------------------------------------------
 frodo   | {"2012-10-02 12:00:00+0200":"throw my precious into mount doom",
            "2012-10-02 12:10:00+0200":"die"}

Note that each element of the map is internally stored as one Cassandra column (this is true of sets and lists too), meaning that each element can have an individual TTL for instance. If you want elements of the todo list to expire the day of their timestamp, you can compute the correct ttl and do:

cqlsh> UPDATE users USING TTL <computed_ttl>
       SET todo['2012-10-1'] = 'find water' WHERE user_id = 'frodo';

Things to know

• You can only retrieve a collection in its entirety. And while we may (or may not) relax that rule a bit in the future, this still means that collections are not meant to be excessively large. They are not a replacement for a proper modelisation into tables.
• Collections are typed but cannot currently be nested. You can have a list<text> or a list<int>, but you cannot define a list<list<int>>. It might be possible to support such nesting in the future if that is deemed useful, but it will require additional work.
• There is no support for secondary indexes on collections yet. Concretely, you could associate a set of tags to a user, but you cannot automatically index users by their tags yet. Adding that support is definitively on the roadmap but remains to be implemented.

Conclusion

Collection support has already been added to the Cassandra trunk so you can already play with them if you are curious. They will be part of the final release of CQL3 that will be part of Cassandra 1.2.

We believe collections in CQL3 will be a great help for data modelisation and are a very natural fit for the language. Furthermore, given that any decent programming language has good support for lists, sets and maps, the integration with higher level API will be straightforward.