Is there any query for Cassandra as same as SQL:LIKE Condition?

Question

The LIKE condition allows us to use wildcards in the where clause of an SQL statement. This allows us to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete. Like this

SELECT * FROM users
WHERE user_name like 'babu%';

like the same above operation any query is available for Cassandra in CLI.

Lick

sdolgy is right. Alternatively, look into something like solandra or DSE. – Kokoruda

New version of Cassandra allows this, with the proper index definition: #76464129 – Bergen

Answer 1

Simple answer: there is no equivalent of LIKE

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlSelect.html

Here is the command reference for v0.8:

http://www.datastax.com/docs/0.8/references/cql#cql-reference

If you maintain another set of rows that hold references to a username:

row: username:bab -> col:babu1, col:babar row: username:babu -> col:babur

Effectively you are cheating by pre-populating all of the results that you would normally search with in the RDBMS world. Storage is cheap in comparison to what it was years ago ... which is why this is now an accepted approach. It's less intensive on the CPU and Memory to retrieve a pre-populated list of information.

Pompous

I would not mark this answer as accepted. Actually, there is a replacement for LIKE 'something%' if the column you are filtering is a clustering key: SELECT * FROM users WHERE user_name <= 'babu' AND user_name>'babv'; See @PhilippBlum's answer – Aristarchus

Answer 2

Since Cassandra 3.4 (3.5 recommended), LIKE queries can be achieved using a SSTable Attached Secondary Index (SASI).

For example:

CREATE TABLE cycling.cyclist_name ( 
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);

Creating the SASI as follows:

CREATE CUSTOM INDEX  fn_prefix ON cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then a prefix LIKE query is working:

SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cyclist_name WHERE firstname LIKE 'Mic%';

These examples and more configuration options, like suffix queries, can be found in the documentation

A more in depth explanation about how SASI works can be found here.

Houseyhousey

Haven't looked into this question in a while. I have to add one thing to your answer. You can now do it via the secondary index, but I wouldn't rely on it, since it is not a core functionality of Cassandra. If I start using these kind of dynamic query, I would consider using duplicated data sets in another storage system. A system which is optimized for these kind of queries. – Maccabees

@Maccabees I agree. If these queries are heavily used, a system tailored to this is sure to be a better fit. I used it as part of a Proof-of-Concept research project's UI, to filter the data by name inside the Cassandra. – Houseyhousey

Can this be used with Apache Cassandra or only with Datastax Enterprise versions? The documentation is not clear about this. – Annabal

@Annabal I am very certain this is part of Apache Cassandra. If you need to heavily rely on that secondary index, you should consider using additional service like Datastax provides or maybe Solr could help. – Houseyhousey

@Houseyhousey thank you, yes we were indeed able to use it as part of Apache Cassandra. From some reason - all documentation we encountered about it was a part of Enterprise version – Annabal

Answer 3

I came across this post while I was searching for a solution to execute WHERE column_name LIKE '%keyword%' in Cassandra. The answers were promising but not quite addressing my issue.

CREATE CUSTOM INDEX idx_name ON keyspace.columnfamily (column_name) 
USING 'org.apache.cassandra.index.sasi.SASIIndex' 
WITH OPTIONS = {
'mode': 'CONTAINS', 
'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 
'case_sensitive': 'false'};

In order to make %keyword% (two %s) works, the index must have options with mode: CONTAINS along with that analyzer_class to make case_sensitive effective.

Unbalanced

I wonder why no one rated your answer. This thread has been visited over 35k until now. – Rienzi

Can this be used with Apache Cassandra or only with Datastax Enterprise versions? The documentation is not clear about this. – Annabal

It can be used with Apache Cassandra – Untwist

Answer 4

I know: It's a old question but there is a solution for this topic:

You can't use like operator in cassandra but you can use range operators and with the range operator you can solve this "like 'whatever%'"

An example: I have more than one product. Each product has his own partition key (first part of the primary key):

CREATE TABLE user(productId int, username text, PRIMARY KEY(productId, username));

Now i have some users:

INSERT INTO user(productId, username) VALUES (1, 'anna');
INSERT INTO user(productId, username) VALUES (1, 'alpha');
INSERT INTO user(productId, username) VALUES (1, 'andreas');
INSERT INTO user(productId, username) VALUES (1, 'alex');
INSERT INTO user(productId, username) VALUES (1, 'bernd');
INSERT INTO user(productId, username) VALUES (1, 'bob');

Now, i want to find all users which have an a at the beginning. In a SQL world i use LIKE 'a%' in Cassandra i use this:

SELECT * FROM user WHERE productId = 1 AND username >= 'a' AND username < 'b';

The result:

productid | username
-----------+----------
     1 |     alex
     1 |    alpha
     1 |  andreas
     1 |     anna

Maccabees

But this solution can't be used for longer strings – Lanfranc

like if I want to queru for words, how should i do it? I guess this solution is not good right? – Lanfranc

Yes, you're right. This solution is restricted in words. The question case is: WHERE user_name like 'babu%'; And you can use this solution for this cases. For querying words, you can use elastic search. – Maccabees

How do i find something like "%search-word%" ? – Venetis

@amitmah you have to scan the entire database for such searches. That's not specific to Cassandra, though. – Naseberry

Note that it should be username >= 'a' because 'a' is a match if you have username LIKE 'a%' in SQL. And of course you can match words. user_name >= 'baby' would work just fine too. I've used it many times, actually. – Naseberry

@AlexisWilke You are completely right. Sorry for the small mistake. – Maccabees

Answer 5

CQL LIKE statements are in Scylla Open Source 3.2 RC1, the release candidate for Scylla, a CQL-compatible database. We'd love feedback before release. Here's the details:

• CQL: LIKE Operation #4477

The new CQL LIKE keyword allows matching any column to a search pattern, using % as a wildcard. Note that LIKE only works with ALLOW FILTERING.

LIKE Syntax support:

'_' matches any single character

'%' matches any substring (including an empty string)

'\' escapes the next pattern character, so it matches verbatim

any other pattern character matches itself

an empty pattern matches empty text fields

For example:

INSERT INTO t (id, name) VALUES (17, ‘Mircevski’)

SELECT * FROM t where name LIKE 'Mirc%' allow filtering

Source: [RELEASE] Scylla 3.2 RC1 2

Schmid