Cassandra CQL 3 - Prefix Select

Question

is there a way to perform a select based on a string prefix using CQL3? For example, consider the following table:

Key | Value
------------
ABC | 0x01
ABD | 0x02
BBB | 0x03

I want to select all the keys with the prefix 'AB'. The database will be used to store spacial information, using a geohash approach.

Answer 1

That is not possible "out of the box"... However, there are some "tricks" people came up with, see these two posts:

• Cassandra (Pycassa/CQL) Return Partial Match
• Is there any query for Cassandra as same as SQL:LIKE Condition?

...another (somehow similar) approach could be to define "composite key", where you define some prefixes as "partition key", e.g.: {key1,key2}, where key1 = ABand key2 = ABC... in these situations you could query by "key1" only and get a set of rows (like you want to do), or by "key1" and "key2" (in case you want a specific entry). You can also query only by "key2" (if you add "allow filtering" to your "select" query, however this can lead to "problems" if you have too many rows). Not sure if you can do this with your data...

HTH.

Answer 2

Not built-in in C* but possible with cassandra-lucene-index C* plugin. You can create a lucene index on the column and search the text using prefix search.

UPDATE: Since v3.4 Cassandra introduced SASI indices that offer the required functionality.