With the number of comparators provided by Cassandra, chances are that you'll never need to write one of your own. But if there must be some column ordering that cannot be achieved or worked around using the given comparators, you can always write your own. For example, sorting with UTF8Type is case sensitive; capital letters come before small letters as seen in the following code:

[default@mastering_cassandra] list daily_hits;
RowKey: 15812 
=> (counter=CA, value=9502) 
=> (counter=MA, value=123) 
=> (counter=NY, value=31415) 
=> (counter=la, value=6023) 
=> (counter=ma, value=43)

One may want to have a case-insensitive sorting. To write a custom comparator, you need to extend org.apache.cassandra.db.marshal.AbstractType<T> and implement the abstract methods. Once you are done with your comparator and supporting classes, package them into a JAR file and copy this file to Cassandra's lib directory on all the servers you want this comparator to be on. It may be a good idea to look into the org.apache.cassandra.db.marshal package and observe how the comparators are implemented. Here is an example. The following snippet shows a custom comparator that orders the columns by the length of the column name, which behaves mostly like UTF8Type except for the ordering:

public class LengthComparator extends AbstractType<String> {

  public static final LengthComparator instance = new LengthComparator();
  
  public int compare(ByteBuffer o1, ByteBuffer o2) {
    return (getString(o1).length() - getString(o2).length());
  }

  // Rest of the methods utilize UTF8Type for operations
}

You can view the complete code online. One thing to note is if you don't declare an instance singleton, Cassandra will throw an exception at the startup. Compile this class and put the JAR or .class file in the $CASSANDRA_HOME/lib folder. You can see the JAR file listed in the Cassandra startup log in the classpath listing. Here is an example of a column family creation, insertion, and retrieval using this comparator. You will see that the columns are ordered in the increasing length of their column names:

# Create Column Family with Custom Comparator
[default@MyKeyspace] CREATE COLUMN FAMILY custom_comparator 
WITH 
KEY_VALIDATION_CLASS = LongType AND 
COMPARATOR = 'in.naishe.mc.comparator.LengthComparator' AND
DEFAULT_VALIDATION_CLASS = UTF8Type;

# Insert some data
[default@MyKeyspace] SET custom_comparator[1]['hello'] = 'world';
[default@MyKeyspace] SET custom_comparator[1]['hell'] = 'whirl';
[default@MyKeyspace] SET custom_comparator[1]['he'] = 'she';    
[default@MyKeyspace] SET custom_comparator[1]['mimosa pudica'] = 'some plant';
[default@MyKeyspace] SET custom_comparator[1]['a'] = 'smallest col name';

# Get Data, columns are ordered by column name length
[default@MyKeyspace] get custom_comparator[1];
=> (column=a, value=smallest col name, timestamp=1375673015868000)
=> (column=he, value=she, timestamp=1375672967041000)
=> (column=hell, value=whirl, timestamp=1375672959028000)
=> (column=hello, value=world, timestamp=1375672928673000)
=> (column=mimosa pudica, value=some plant, timestamp=1375672990036000)Indexes

Indexing a database is a means to improve the retrieval speed of data. Before Cassandra 0.7, there was only one type of index—the default one—which is the index on row keys.

If you are coming from the RDBMS world, you may be a bit disappointed with what Cassandra has to offer in terms of indexing. Cassandra indexing is a little inferior. It is better to think of indexes as hash keys. In this topic, we'll discuss a little on the primary index or row key index, then we'll move to use cases where we'll see a couple of handy techniques to create an index on column names (secondary index) like effect. Next is the secondary index; we'll discuss a little on it, its pros and cons, and the help that it provides in keeping the boilerplate code down.

A primary key or row key is the unique identifier of a row, in much the same way as the primary key of a table from a relational database system. It provides quick and random access to the rows. Since the rows are sharded among the servers of the ring, each server just has a subset of rows, and hence, primary keys are distributed too. Cassandra uses the partitioner (cluster-level setting) and replica placement strategy (keyspace-level setting) to locate the respective node in the ring to access a particular row. On a node, an index file and sample index is maintained locally that can be looked up via binary search followed by a short sequential read (see Chapter 1, Quick Start).

The problem with primary keys is that their location is governed by partitioners. Partitioners use a hash function to convert a row key into a unique number (called token ) and then write/read that key to/from the node that owns this token. This means that if you use a partitioner that does not use a hash that follows the key's natural ordering, chances are that you can't sequentially read the keys just by accessing the next token on the node. The following snippet shows an example of this. The row keys 1234 and 1235 should naturally fall next to each other if they are not altered (or if an order-preserving partitioner is used). However, if we take a consistent MD5 hash of these values, we can see that the two values are far away from each other. There is a good chance that they might not even live on the same machine.

ROW KEY | MD5 HASH VALUE
--------+----------------------------------
1234    | 81dc9bdb52d04dc20036dbd8313ed055
1235    | 9996535e07258a7bbfd8b132435c5962

Let's take an example of two partitioners: ByteOrderPartitioner that preserves lexical ordering by bytes, and RandomPartitioner that uses MD5 hash to generate a row key. Let's assume that we have a users_visits column family with a row key, <city>_<userId>. ByteOrderPartioner will let you iterate through rows to get more users from the same city in much the same way as a SortedMap interface. (Refer to http://docs.oracle.com/javase/6/docs/api/java/util/SortedMap.html). However, in RandomPartioner, the key being the MD5 hash value of <city>_<userId>, the two consecutive userIds from the same city may lie on two different nodes. So, we cannot just iterate and expect grouping to work, like accessing entries of HashMap.

It may be useful to keep in mind that queries, such as the range slice query, pull rows from the start row key to the end row key provided by the user. If the user is not using an order-preserving partitioner, the rows returned will not be ordered by the key; rather, they'll be orders by the partitioner that is set.

We will see partitioners in more detail in Chapter 4, Deploying a Cluster, section Partioners. But using the obviously better-looking partitioner, ByteOrderPartitioner , is assumed to be a bad practice. There are a couple of reasons for this, the major reason being an uneven row key distribution across nodes. This can potentially cause a hotspot in the ring. reasons for this, the major reason being an uneven row key distribution across nodes. This can potentially cause a hotspot in the ring.

For any real application, one necessarily needs information grouped by criteria and to be able to be searched by search criteria. Secondary indexes came into existence in Version 0.7, so the natural question is how were these types of requirements fulfilled before it? The answer is a wide-row index , aka manual index, aka alternate index. Basically, we create a column family that holds the row keys of other column families as its columns, and the row key of this column family is the value we wanted to group by. We can use this in the city-user example that we discussed in the previous section. Instead of relying on the primary key, we can create a column family that has city as the row key and user ID as the column name (and probably, username as the column value to cache it in such a manner that we do not pull data from the parent column family unless we need more details other than the username)—all done. Whenever you need to get users by their city names, you can select the row of that city.

Before we go ahead and discuss some of the patterns using a wide-row index, the one thing that should be kept in mind is that a lot of these cases can be handled by secondary indexes, and it would be worth counting the pros and cons of using a wide-row pattern versus a secondary index.

The idea is simple: you create a column family with its row keys as the name of the group (the group by predicate in SQL) and the column names as the row keys of the column family that you wanted to group.

Let's take an example of a social networking application that lets users create groups and other people can join it. You have a users column family and groups column family. You make another column family that holds a user per group and call it group_users. This will have the group's row key as the row key, and the user's row key as the column name. Does it ring a bell? It's the same thing as a join table or link table in a many-to-many relationship in the RDBMS world.

Another use case is grouping by field. Let's say your application provides hotel search facilities where you can select a city and see the hotels. Or perhaps your video-streaming website offers a tagging mechanism as a part of video metadata and you wanted users to click on the tags and see the videos with the same tags—it is the same logic. We create a column family named tag_videos where each row key is a tag and the columns are row keys of the videos columns' family. On clicking a tag, we just load the whole row from tag_videos for that tag and show it to the user (with pagination, perhaps):

Figure 3.4: An example of grouping by tag name

One improvement can be made with this mechanism to add some meaningful values to the columns. For example, it may be worth storing the video title as the column value. This will save an extra query to the videos table. Just pull the columns, and show the names. Pull more data when and if required.

Unlike row keys, columns are sorted by the comparator that you provide. If you provide a UTF8Type comparator, the columns will be sorted in a string order. In the video tag example, you may want videos to be sorted by the username of the user who uploaded the video. You may just set up the column key as <userName>:<videoId> (note that the separator can be confusing if either of the values that make a column name have a separator as a part of it) and now, you get videos for a given tag sorted by the username. You can range slice the column or further filter the videos for a given tag by a particular user. Here is a sample of the code:

#Get hold of CFs
vidCF = ColumnFamily(con, 'videos') 
tagCF = ColumnFamily(con, 'tag_videos') 

#insert in videos as well as tag index CF
vidCF.insert( 
  rowKey, #<title>:<uploader> 
  { 
  'title':title, 
  'user_name':uploader, 
  'runtime_in_sec':runtime, 
  'tags_csv': tags  #this is CSV string of tags
  }) 
  
for tag in tags.split(','): 
  tagCF.insert( 
    tag.strip().lower(), #index CF's row-key = tag
    {
     #key=<uploader>_<rowKeyOfVideosCF>, value=<title>
     uploader+ "_" + rowKey: title 
    } 
  ); 

#retrieve video details grouped by tag
tag = 'action'
movies = tagCF.get(tag.strip().lower()) 
for key, val in movies.iteritems(): 
  vidId = key.split('_')[1] 
  movieDetail = vidCF.get(vidId) 
  print ''' 
  {{ 
    user: {0}, 
    movie: {1}, 
    tags: {2} 
  }}'''.format(movieDetail['user_name'], movieDetail['title'], movieDetail['tags_csv']) 
  
#Result for tag='action' sorted by user name
    { 
      user: Kara, 
      movie: Olympus Has Fallen, 
      tags: action, thriller 
    }
    { 
      user: Kara, 
      movie: The Croods, 
      tags: animation, action, mystery 
    } 
    { 
      user: Leo Scott, 
      movie: Oblivion, 
      tags: action, mystery, sci-fi 
    } 
    { 
      user: Sally, 
      movie: G.I. Joe: Retaliation, 
      tags: action, adventure 
    }

Push all in one: In Cassandra, there is often more than one way to skin a cat. Our previous approach uses multiple rows, one for each tag name. But you can very well push all the tags in one single row. You may just have one single row, perhaps named tag_index, with column names as <tag>_<videoId> and comparator as UTF8Type. Since the columns are sorted, you can slice the row with column names starting with the desired tag.

Figure 3.5: Indexing all videos by tag in one row

There are a couple of drawbacks with this approach: one, a row is entirely stored on one machine (and the replicas too). If it's a frequently accessed index, these machines may get excessively loaded. Two, the number of columns per row is limited to approximately two billion, and if you happen to be having more videos, this may be an issue.

In the previous section, we saw a common pattern where we put column names as a concatenated string of two properties of another object—username and video ID. In an even simpler version, we just dumped all the movies into one row with the column name as a tag appended with a separator and video ID. One thing that was etched in the previous implementation is the fact that if our chosen separator is a part of either of the strings that we concatenate, it will be a nightmare splitting them to get an attribute value. What if there are more than two attributes to create a column name? Conjure a super column, a column of columns.

The idea is simple: a super column is a level two nesting. Let's work out some tagging problems using a super column. It may not seem super advantageous over what we had with the string concatenation mechanism, but we can see cleaner decoupling here.

Figure 3.6: An inverse index with a super column

The code is as follows:

# Tag insertion in super-column

for tag in tags.split(','): 
  tagCF.insert( 
   tag.strip().lower(), #row-key = tag name 
   { 
     uploader: {        #level 1 nesting = uploader name
       vidId: title     #level 2 nesting: key=videoId, value=title 
         } 
      } 
  )

# Fetch videos by tag, grouped by users
tagCF = ColumnFamily(connection, 'tag_videos_sup') 
movies = tagCF.get(tag.strip().lower())

for key, val in movies.iteritems():           #level 1 iteration 
  username = key 
  # Some string formatting  print ''' 
  {{ 
    {0}:'''.format(username)
  for k, v in val.iteritems():                #level 2 iteration 
    print '''	{{{0}=> {1}}}'''.format(k, v) 
  print ''' 
  }'''

# Result
tag: action 
    { 
      Kara: 
       {Olympus Has Fallen:Kara=> Olympus Has Fallen} 
       {The Croods:Kara=> The Croods} 
    }

    {
      Leo Scott: 
       {Oblivion:Leo Scott=> Oblivion} 
    }

    { 
      Sally: 
       {G.I. Joe: Retaliation:Sally=> G.I. Joe: Retaliation} 
       {Scary MoVie:Sally=> Scary MoVie} 
    }

This is the exact same code as we had in the previous section with two exceptions:

Let's see what we gain by using super columns. One thing that's clear is that we avoided the dangers of manually splitting strings. We have a cleaner structure now. This is a typical use case for super columns.

All good again! Let's think of the situation where we dumped all the tags in a row. Can we convert that into a super-column-based index to something like the following snippet?

tag_index:
[
  tag1:[
    {user1:[{rowKey1: videoId1},{rowKey2: videoId2},...]},
    {user2:[{rowKey3: videoId3},{rowKey4: videoId4},...]},
    ...
  ],
  tag2:[
    {user4:[{rowKey9: videoId9},{rowKey7: videoId7},...]},
    {user2:[{rowKey3: videoId3},{rowKey8: videoId8},...]},
    ...
  ],
  ...
]

Basically, we are asking for one more level of nesting, that is, subcolumns, in order to be able to have a column family or subcolumns of a subcolumn. Unfortunately, Cassandra just provides two levels of nesting. This takes us back to having some sort of ad hoc mechanism at a subcolumn level, something like having subcolumn names as <username>:<videoId>.

The super-column-based approach did give some relief, but it is not the best solution. Plus, it has its own limitation such as one-level nesting. Subcolumns are not sorted; they are just a bunch of unordered key-value pairs. Super columns, although not deprecated, are not really preferred by the Cassandra community. The general consensus is to use composite columns and avoid super columns. It is a better, cleaner, and elegant solution and it scales much better.

Composite column names : Composite column names are made of multiple components. They can be viewed as ordered tuples of multiple types. For example, this is a composite column name: (username, city, age, SSN, loginTimestamp). The components are UTF8Type, UTF8Type, IntType, UTF8Type, and DateType, in that order.

Composite types behave much in the same way as normal comparators do: columns are sorted by them, names are validated with definitions, and slice queries can be executed. Plus, you don't need to manage the ad hoc arrangement for keys with multiple components such as in string concatenation. Composite keys are deserialized as a tuple and easily used on the client side.

The sorting of composite comparators works in the same way as the SQL query order by field1, field2, ... does. Columns get sorted by the first component of the key and then within each first component, it is ordered by the second component, and so on. Validation is done for each component individually. And slice can be applied to each component in the order in which they appear in the column family definition.

Out of the box, Cassandra provides two composite types: static CompositeType, which can have a fixed number of components that are ordered, and DynamicCompositeType, which can have any number of components of all the types defined at the column family creation in any order. So let's use the static CompositeType column names to get rid of super columns and string concatenation as shown in the following code snippet:

# Tag insertion in super-column
for tag in tags.split(','): 
  tagCF.insert( 
      tag.strip().lower(), #row-key = tag name 
      { 
        uploader: {        #level 1 nesting = uploader name
          rowKey: title    #level 2 nesting:
                           # key=videoId, value=title 
         } 
      } 
  )
  
# Fetch videos by tag, grouped by users
tagCF = ColumnFamily(con, 'tag_videos_sup') 
movies = tagCF.get(tag.strip().lower()) 
for key, val in movies.iteritems(): #level 1 iteration 
  username = key 
  print ''' 
  {{ 
    {0}:'''.format(username) 
  for k, v in val.iteritems(): #level 2 iteration 
    print '''	{{{0}=> {1}}}'''.format(k, v) 

  print ''' 
  }'''

# Result
tag: action 
    { 
      Kara: 
       {Olympus Has Fallen:Kara=> Olympus Has Fallen} 
       {The Croods:Kara=> The Croods} 
    } 
    {
      Leo Scott: 
       {Oblivion:Leo Scott=> Oblivion} 
    } 
    { 
      Sally: 
       {G.I. Joe: Retaliation:Sally=> G.I. Joe: Retaliation} 
       {Scary MoVie:Sally=> Scary MoVie} 
    }

Let's stop for a moment and think about the applicability of composite columns. You can have tags on a video page. On clicking a tag, you can show a whole list of videos just by pulling a row from the tag_videos index column family. If you have a composed key, such as <title>:<length>:<username>:<rowkey>, you can get all the details shown in the list, plus you may have the thumbnail's URI as the column value to be displayed next to each list item. Time series data can be stored and sorted by timestamps that can later be pulled to view the history, and the timestamp can be pulled off the composite column name. These are just a couple of examples; composite type is a really powerful tool to come up with innovative solutions.

Working through previous examples, a relational database person would think that all this manual management of a custom index family is a little too much work. In RDBMS, one can just perform ...where user_name = 'Kara'. If the data is large, one can index it to one user_name value and improve read/search by taxing the writes. If one wanted to just get the videos that are longer than a minute's runtime, it is going to be ...where user_name = 'Kara' and runtime_in_sec> 60. Cassandra Version 0.6 and the earlier versions had no way of doing something like this without holding denormalized data in a separate column family, much like a materialized view. Similar to what we have been discussing, Version 0.7 or higher has a secondary index and it is well supported by CQL and client APIs.

The indexes that are created on columns are called secondary indexes. Secondary indexes—much the same way as a primary index—are similar to hashes and are not like a B-Tree, which is commonly used in relational databases and filesystems. This means you can run the equality operator on secondary indexes, but a range query or inequality cannot be done (actually, you can, but we'll see that case later on) on index columns.

Let's take our old video example and search for all the videos uploaded by a particular user without having to use an index column family. Later, we'll try to get all the videos by a user that are shorter than 7,500 seconds. Check the following code:

# Please note: The symbol  is used for line continuation. 
# It means that the next line is a continuation of current 
# line and treated as single
# line. They are kept in separate line due to formatting issues.

# creating index on columns
sys = SystemManager('localhost:9160') #assume default installation
sys.create_index(keyspace, 'videos', 'user_name', UTF8_TYPE) 
sys.create_index(keyspace, 'videos', 'runtime_in_sec', INT_TYPE)

# criteria: where user_name= <username>
username_criteria = 
create_index_expression('user_name', username) 

# criteria: where runtime <= <max_length>
#LTE? Less than or equals
length_criteria = 
create_index_expression('runtime_in_sec', max_length, LTE) 

# query: where user_name = 'Sally'
#just pull 3 

user_only_clause = 
create_index_clause([username_criteria], count=3)

movies_by_user = 
videoCF.get_indexed_slices(user_only_clause)

print_movie(movies_by_user)

# query: where user_name = 'Sally' and runtime < 7500
#pull all 
user_runtime_clause = 
create_index_clause([username_criteria, length_criteria])

movies_by_user_runtime = 
videoCF.get_indexed_slices(user_runtime_clause)

print '''-- movies for username: {} 
  and length <= {} --'''.format(username, max_length)

print_movie(movies_by_user_runtime)

# results
-- movies for username: Sally  -- 
{
  user: Sally, 
  title: Scary MoVie, 
  runtime: 7610 
}
{
 user: Sally, 
  title: Oz the Great and Powerful, 
  runtime: 7800 
}
{
 user: Sally, 
  title: The Place Beyond the Pines, 
  runtime: 6610 
}
-- movies for username: Sally and length <= 7500 – 
{ 
  user: Sally, 
  title: The Place Beyond the Pines, 
  runtime: 6610 
}
{
 user: Sally, 
  title: G.I. Joe: Retaliation, 
  runtime: 7410 
}

So now that we are excited by the secondary index, let's try to get all the movies that are smaller than 7,500 seconds across all the users using the following code:

# Fetch using in equality
# Intended to fail, At least on equality necessary! 
runtime_clause = 
create_index_clause([length_criteria])

movies_by_runtime = 
videoCF.get_indexed_slices(runtime_clause)

print_movie(movies_by_runtime)

# Result
InvalidRequestException(why='No indexed columns present in index clause with operator EQ')

Oops! But, we were kind of expecting that, as a secondary index is much like a hash map. What we were not expecting is how the inequality worked in the query that was running along with an equality (for example, where user_name = 'Sally' and runtime <=7500). Ideally, it should fail there as well. The secret behind this magic is in memory comparison. The result, returned from the equality operation, gets sliced in the memory on the coordinator node.

So, now that we have seen that the secondary index is good but not so great, let's demystify the secondary index further. The first thing to keep in mind is that though a secondary index seems similar to a traditional database index, it is inferior to it. In fact, the mechanism itself is different. Under the hood, indexes are stored in their own column family and cannot be accessed by users. They are synchronized with the node local data, which means the index column family will always be locally consistent.

This hidden column family has a row key as the index value and the columns are corresponding row keys of the column family whose data we are indexing. Did this ring a bell? Remember this, we have done the same thing in an earlier section, Simple groups, with tags as the row key and the video column family's row key as column names in our manual index. But there is a fine distinction between the two. Each node just indexes the data on the local machine—this means if the replication factor is RF, each node will have data on RF number of nodes; so, each node will have indexes for them. This way, each query needs to touch at least the NUMBER_OF_NODES / RF value in the secondary index. While in manually managed cases we are going to touch just one node—the one that has the row corresponding to the index value. Row and index updates are performed as a single atomic operation. But a manual index requires to be managed by application logic, while a secondary index is managed by Cassandra. Also, keeping a manual index restricts the upper bound of the number of row keys per index to be equal to the row limit, which is two billion. The automated index just needs to store the data on that node, so it's a bit more spacious than the manual index.