Cassandra CQL wildcard search

Question

I have a table structure like

create table file(id text primary key, fname text, mimetype text, isdir boolean, location text);
create index file_location on file (location);

and following is the content in the table:

insert into file (id, fname, mimetype, isdir, location) values('1', 'f1', 'pdf', False, 'c:/test/');
insert into file (id, fname, mimetype, isdir, location) values('2', 'f2', 'pdf', False, 'c:/test/');
insert into file (id, fname, mimetype, isdir, location) values('3', 'f3', 'pdf', False, 'c:/test/');
insert into file (id, fname, mimetype, isdir, location) values('4', 'f4', 'pdf', False, 'c:/test/a/');

I want to list out all the ids matching the following criteria:

select id from file where location like '%/test/%';

I know that like is not supported in CQL, can anyone please suggest the approach should I take for these kind of wildcard search queries. Please suggest.

Weary

Answer 1

DataStax Enterprise adds full text search to Cassandra: http://www.datastax.com/docs/datastax_enterprise3.1/solutions/search_index

Outpour

Is there an alternative to DataStax. It doesn't seem to have a version for windows. I am mainly looking for DSE Search. – Simon

Answer 2

As of Cassandra 3.4, this is possible with SASI indexes. This should work:

CREATE CUSTOM INDEX string_search_idx ON file(location) 
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'tokenization_enable_stemming': 'true',
    'tokenization_locale': 'en',
    'tokenization_skip_stop_words': 'true',
    'analyzed': 'true',
    'tokenization_normalize_lowercase': 'true'
};

This shall search for all "%abc%" queries on the column "file". More information here.

Ruthy

Please note, that SASI indexes are almost unmaintained, so in C* 4.0 they will be disabled by default, and to enable them you'll need to change cassandra.yaml... – Emitter