A series on Cassandra — Part 2: Indexes and keys

In this article we are going to discuss the types of keys and indexes in Cassandra and how to apply them to real world modelling scenarios.

This is a continuation of the previous article in this series, our introduction to Cassandra. If you haven’t had the chance we do recommend you have a quick read through that first.

A bit of background

As we mentioned in the first part of this series, Cassandra is built for performance at scale, with the strong guarantee of horizontal scalability. The cheapest operation by far is considered the write, with reads following shortly after. Cassandra is well prepared to handle gigantic influxes of data and if you are after hundreds of thousands of writes per second, you’re in the right place.

The key thing to remember is the origin of performance in Cassandra, the killer idea behind it, namely the “overpowered HashMap” concept. In the previous article, we advised you to think of Cassandra as an overpowered hash-map when it comes to indexing, built to “jump-to-reference” as fast as possible, both at write and query time, with a lot of clever mechanics behind the scenes to enable for complex data modelling with what at first hand appears to be a rather limited syntax and modelling engine. In this article, we will be discussing the indexing options in more detail, what their limitations are and where they are most appropriately used to solve a real world problem.

The basic mechanism

So how does Cassandra jump to reference? It’s a very simple concept. There are a number of default data partitioners you can use, more details about that here, the default being the Murmur3 partitioner and hash algorithm. When you feed in a CQL query with a WHERE clause, in addition to the pre-checks it does to tell you about potential errors and so on, the very core of the matching is first forming a hash from the data you gave it and trying to match a data entry against that.

Reality is likely more complicated, but that’s an excellent way to envision it. On top of that, Cassandra stores information about the structure of your tables, and it’s capable of anticipating when the hash cannot be formed. It can match the columns specified WHERE .. AND .. clause in your CQL query against the schema information it stores for a table and simply check if all columns part of the PRIMARY KEY where specified. Otherwise, it’s safe to conclude there is no way to get a match and an error is returned.

Defining a key in CQL

The PRIMARY KEY in the Cassandra query language is defined together with the table, much like in SQL, except there are a few apparent game changers. In SQL, you can only have one PRIMARY KEY per table, and although at first glance you would think Cassandra is different in this sense, it isn’t, it can simply be formed by multiple columns, since, as we described above, in reality it will only need to produce a single unique hash to get a match.

Let’s have a look at a few possible CQL schema definitions, to get an idea of how the primary key is formed.

PRIMARY KEY (
(partition_key_1, partition_key_2),
clustering_key_1,
clustering_key_2
)

The above is the textbook default way of defining a PRIMARY KEY in Cassandra. The simple catch is that if you have a single PARTITION KEY you may omit the enclosing parentheses and the important thing to remember is that the first part of the PRIMARY KEY is the PARTITION KEY. The below is also a valid CQL definition, where the PRIMARY KEY is composed solely from the PARTITION KEY.

CREATE TABLE People(
  id uuid,
  name text,
  firstName text,
  PRIMARY KEY (id)
)

Storage is 2-level nested HashMap

There is one more subtlety to the HashMap concept: there are 2 levels. Level one is the association between the PARTITION KEY and the rest of the columns forming the PRIMARY KEY and the second level is the association between the PRIMARY KEY and the rest of the data. The association looks like this: PARTITION KEY -> PRIMARY KEY -> DATA. If this were a Scala Map, the type would be Map[String, Map[String, Data]]. The keys of a Map are unique and that’s a very important aspect. It means multiple rows can belong to the same PARTITION KEY but only one row can belong to the rest of primary.

Let’s try to visualise this with a very simple CQL example.

CREATE TABLE People(
  id uuid,
  country text,
  name text,
  firstName text,
  phone text,
  eyeColor text,
  PRIMARY KEY(country, id)

And the Phantom DSL equivalent:

import com.outworkers.phantom.dsl._
case class Person(
  id: UUID,
  country: String,
  name: String,
  firstName: String,
  phone: String,
  eyeColor: String
)
  
abstract class People extends Table[People, Person] {
  object country extends StringColumn with PartitionKey
  object id extends UUIDColumn with PrimaryKey
  object name extends StringColumn
  object firstName extends StringColumn
  object phone extends StringColumn
  object eyeColor extends StringColumn
}

Now that’s plenty of columns to have some fun with. In the above scenario, country is the PARTITION KEY as it’s the first part of the primary. There is a very interesting behaviour that’s possible right now. You might think you can only query by the full primary, or in other words you would need to provide both the country and an id to retrieve a record.

But Cassandra uses the PARTITION KEY to distribute data across physical data partitions, which gives you the ability to query by specifying only the country, effectively allowing the retrieval of all people from a certain country. The CQL would look like this:

SELECT * FROM People WHERE country = 'United Kingdom' LIMIT 5000;

The Phantom DSL equivalent is even more interesting, as phantom supports lazy asynchronous iterators that allow you to fold over iterators of any size. This behaviour is based on Play Async Iterators, and it looks like this:

abstract class PeopleByCountry {
  // continuation from above
  def peopleByCountry(country: String): Enumerator[Person] = {
    select.where(_.country eqs country).limit(5000).fetchEnumerator
  }
}

You can even control the chunks fetched at a given time via the Netty channels using:

// ..
  def peopleByCountry(country: String): Enumerator[Person] = {
    select.where(_.country eqs country).limit(5000).setFetchSize(1000).fetchEnumerator
  }
// ..

Back to CQL and Cassandra, the important part is that you can query by specifying only the full PARTITION KEY. How is this useful in practice? Well, it’s in most cases a very simple and straightforward way of modelling one-to-many relationships, because the same key can relate to a theoretically infinite number of rows. And you can even get very complex relationships between pairs of certain columns depending on what your application needs.

Compound Key

A Compound Key is used part of Cassandra modelling terminology, at it means the PRIMARY KEY of the table is composed of exactly 1 column in the PARTITION KEY and at least column in the rest of the PRIMARY KEY. The above People is a great example, because country is the PARTITION KEY and id is part of the PRIMARY KEY.

The CQL syntax would be: PRIMARY KEY (partition, primary1, ..), where partition and primary1 are mandatory if you want to call a key compound.

Composite Keys

A Composite Key is used part of Cassandra modelling terminology, at it means the PRIMARY KEY of the table is composed of at least 2 columns in the PARTITION KEY and at least one more column in the rest of the PRIMARY KEY.

An example of a composite key might be:

CREATE TABLE PeopleByCountryAndRegion(
  country text,
  region text,
  id UUID,
  PRIMARY KEY ((country, region), id)
)

As you probably guessed, this is used to distribute data based on the combination of 2 columns, often very useful in practice.

Conclusion

Although defining keys appears complex at first, mapping the old school SQL equivalents is actually quite simple once you understand the mechanisms that power Cassandra.

• Modelling one-to-one relationships can be done by using a single PRIMARY / PARTITION KEY.
• Modelling one-to-many relationships can be done by using a COMPOUND KEY.
• Modelling many-to-many relationships can be done by using a COMPOSITE KEY.

Secondary indexes

Implemented as a marketing decision with no technical background to warrant them, secondary indexes are a feature of CQL you should probably forget about. It’s perhaps because querying by any index is not possible that the Cassandra team received requests for such an index, but it fundamentally breaches the contract of the underlying storage model and the killer idea behind Cassandra, specifically the way it thinks about SSTables. How are they useful? They enable MongoDB style queries, where you can quickly enable querying by a column in a table without doing any of the work, such as storing data in duplicate ways and maintaining consistency at application level. This duplication approach is better described in the first post of this series.

Reminiscent of the SQL syntax, creating an INDEX, or what we refer to as a Secondary index, is done as follows:

CREATE INDEX firstName ON People (firstName);

In Phantom DSL, the equivalent schema definition looks like this:

..
object firstName extends StringColumn with Index
..

After you add the above, you can successfully use firstName in a WHERE clause without get a compilation error. This is courtesy of implicit magic that happens in Phantom.

Now, you can successfully query by firstName, but it’s really important that you understand the actual mechanism. Again, secondary indexes are unnatural given the Cassandra storage engine and the way they work is being filtering records in memory at runtime. Remember how Cassandra is smart and tries to do very little for your queries so they can be extremely fast? Now you are breaching that contract, as you are explicitly asking it to work more than any database should. It’s simply unfit for this purpose, and it even tries to tell you that by making you explicitly ALLOW FILTERING in the CQL query where a match by a Secondary index is needed. Because of this, secondary indexing has exponentially lower performance than normal querying, as there’s nothing clever about it to back performance. Anything above a few thousand records will seriously impair performance and even that might be an issue.

Just duplicate data at will, as Cassandra wants you to, and you will be very happy with query performance no matter what scale you are at. Remember, every time you use a secondary index, what you should do instead is to apply the procedure described in article 1 of this series, which is to create a separate table where your index is the primary key, and then maintain consistency at application level. Writes are extremely cheap, in memory filtering by secondary indexes is not.

Clustering order

This is another extremely powerful feature available in Cassandra, and it allows you to naturally store records in a given order based on the value of a particular column. It allows for everybody’s favourite NoSQL sentence: timeseries data. Cassandra is so good and powerful for this operation there isn’t a database in the world who will even be in the same league as Cassandra. It’s incredibly powerful, and with this apparently trivial feature of ordering records, you can do anything from storing server logs to complex Spark aggregates of real time market data, empowering your own hedge fund with very little work on your side.

Combined with the unmatched write performance and capacity, if you are doing things where the timeline is important, Cassandra is more than likely the place where you want to be. So how does it work? All you really have to do is define CLUSTERING ORDER by a given column and a direction. Clustering keys also become part of the PRIMARY KEY. It looks like this in CQL:

CREATE TABLE Stocks(
  id uuid,
  market text,
  symbol text,
  value bigdecimal,
  time timestamp
  PRIMARY KEY(id)
) WITH CLUSTERING ORDER BY (time DESC);

And the Phantom equivalent:

case class Stock(
  id: UUID,
  market: String,
  symbol: String,
  value: BigDecimal,
  time: DateTime
)
abstract class Stocks extends Table[Stocks, Stock] {
  object id extends UUIDColumn with PartitionKey
  object market extends StringColumn
  object symbol extends StringColumn
  object value extends BigDecimalColumn
  object time extends DateTimeColumn with ClusteringOrder with Descending
}

What we’ve told Cassandra is to store all stock records in naturally descending order by the time column. So every time you write to the Stocks table, Cassandra will figure out where that record is supposed to go in the physical data partitions and store the record in the order you told it to. So how’s that useful? Say hello to range queries!

abstract StocksExample {
  def getEntriesForToday: Future[Seq[Stock]] = {
    // Get the start of the day using JodaTime
    val start = new DateTime().withTimeAtStartOfDay()
    // Use the default constructor to get "now".
    val end = new DateTime()
    
    // Do a range query, effectively saying: "Give me all records where the time is greater than start and lower than end".
    select.where(_.time gte start).and(_.time lte end).fetch()
  }
}

At this point, you may be thinking that’s hardly impressive, there are numerous ways to do timeseries in a whole range of technologies. What’s truly incredible is the performance of those queries in Cassandra. Because of how it is able to naturally order records, you can virtually abuse the storage engine and it will be a long time before you will notice the slightest performance impact. Also, you can take everything “for granted”, as Cassandra does all the work for you. If you’re using Phantom, you even get auto-complete assist thanks to the internals and implicit mechanism. It’s that simple!

Want to learn more?

As official Datastax partners, Outworkers offers a comprehensive range of professional training services for Apache Cassandra and Datastax Enterprise, taking your engineering team from Cassandra newbies to full blown productivity in record time. Our example driven courses are the weapon of choice for companies of any size and if you happen to be a Scala user, we will also throw in a professional training session on using phantom at scale. All of our face-to-face training courses come with free ongoing access to our online training material.

For enquiries and bookings, please contact us by email at office@outworkers.com.