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

UDF/User Defined Functions in Cassandra 3.x

I was just playing around with Cassandra WRITETIME and thought it was somewhat difficult to figure out the date / timestamp of a number like this (microseconds since EPOC) 1470645914253000.

So in my example it looked like this

So I figured why not create a UDF that would solve this for me

That turned out to be a little bit of a challenge …

I thought that I could do like this

BUT NO, YOU CAN NOT!!!

There are several WRONGS in here it turns out

  1. First off you have to turn on
    enable_user_defined_functions: true
    in the conf/cassandra.yaml file
  2. All classes has to be fully qualified, so Date would be java.util.Date, and so on…
  3. The division operator ‘/’ can not be used !!! however +,- and * works fine. surely this must be a bug … this called for some thinking…

The error I got when trying to use the code above without fully qualified names was

And the reason, if I got it right, is that you can not do imports.

The error I got when trying to use the division ‘/’ operator was this:

The code that works looks like this, using java.math.BigDecimal to solve it was perhaps a so-so solution, but it works:

So now my output in cqlsh.sh looks like this now

That is a lot better !