Is there anyway to use LIKE in NoSQL Command on non primary Key?

I am selecting from Cassandra database using the LIKE operator on non primary key.

select * from "TABLE_NAME" where "Column_name" LIKE '%SpO%' ALLOW FILTERING;

Error from server: code=2200 [Invalid query] message="LIKE restriction is only
supported on properly indexed columns. parameter LIKE '%SpO%' is not valid."

Solution

Simply put, "yes" there is a way to query with LIKE on a non-Primary Key component. You can do this with a SASI (Storage Attached Secondary Index) Index. Here is a quick example:

CREATE TABLE testLike (key TEXT PRIMARY KEY, value TEXT) ;

CREATE CUSTOM INDEX  valueIdx ON testLike (value)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS={'mode':'CONTAINS'};

As your query requires to match a string within a column, and not just a prefix or suffix you'll want to pass the CONTAINS option on index creation.

After writing some data, your query works for me:

> SELECT * FROM testlike WHERE value LIKE '%SpO%';

 key | value
-----+--------------
   C | CSpOblahblah
   D | DSpOblahblah

(2 rows)

WARNING!!!

This query is extremely inefficient, and will probably time out in a large cluster, unless you also filter by a partition key in your WHERE clause. It's important to understand that while this functionality works similar to how a relational database would, that Cassandra is definitely not a relational database. It is simply not designed to handle queries which incur a large amount of network time polling multiple nodes for data.