SQL LIKE operation in Cassandra, is possible in v3.4+

For a long time it has not been possible to do a SELECT * FROM table WHERE firstname like ‘t%’; in Cassandra like you could in eg.. MySQL or any other Relation Database for that matter.

In Cassandra v3.4 this is now possible, BUT it requires some extra to do it right, and that is why I created this blog post cause I had trouble finding it.

The solution is to create a separate index, and not the secondary indexes that Cassandra came with, but a different index, called a SASI index.

This is what I have

And the content of it looks like this

And now I would like to search for all the rows that has a first name that starts with a ‘t’

In SQL that would have been :

SELECT * FROM bth.employee WHERE firstname LIKE ‘t%’;

In fact we could have done that on any column …. but in Cassandra it would result in something like this:

In Cassandra we first has to decide on which columns this should be possible, by creating an index like this:

And so you can now do the following

But what if you decide that I would like to know all the employees that ends with an ‘s’ in their name, so something like this:

So to be able to search for something that contains we have to change the index like this instead:

And now you can run that query again:

You can read more about the SASI index here https://docs.datastax.com/en/cql/3.3/cql/cql_reference/refCreateSASIIndex.html

Enjoy!

-Tobias

3 thoughts on “SQL LIKE operation in Cassandra, is possible in v3.4+

Leave a Reply

Your email address will not be published. Required fields are marked *