Stack Exchange
spring data Cassandra LIKE query on secondary indexed cluster column
Java Version:
openjdk version "20" 2023-03-21
OpenJDK Runtime Environment (build 20+37)
OpenJDK 64-Bit Server VM (build 20+37, mixed mode, sharing)

Springboot Version: 3.0.1
Cassandra Version:
cqlsh:hipi> SHOW version;
[cqlsh 6.0.0 | Cassandra 4.0.7 | CQL spec 3.4.5 | Native protocol v5]

Table description
cqlsh:abc> DESCRIBE TABLE books;

CREATE TABLE abc.books (
    author_id text,
    name text,
    created_at timestamp,
    status boolean,
    PRIMARY KEY (author_id, name)
) WITH CLUSTERING ORDER BY (name ASC)
    AND additional_write_policy = '99p'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';

CREATE INDEX books_name_idx ON abc.books (name);

While querying with WHEN =
SELECT * FROM books WHERE name='8b481c6e-48a0-4552-a624-9e8fbad4f930';

Result
cqlsh:hipi> SELECT * FROM books WHERE name ='8b481c6e-48a0-4552-a624-9e8fbad4f930';
 author_id                            | name                                 | created_at                      | status
--------------------------------------+--------------------------------------+---------------------------------+--------
 a1c08312-d8fe-46f8-b625-bf43da2a2920 | 8b481c6e-48a0-4552-a624-9e8fbad4f930 | 2023-06-13 09:36:07.311000+0000 |   True

But while using with LIKE
SELECT * FROM books WHERE name LIKE '%8b4%';

Result
InvalidRequest: Error from server: code=2200 [Invalid query] message="LIKE restriction is only supported on properly indexed columns. name LIKE '%8b4%' is not valid."

To use LIKE filtering in CQL, you need to build your index as a SSTable Attached Secondary Index (SASI).  SASI indexes are considered to be "experimental," so they are disabled by default.  To enable them, flip this setting in the cassandra.yaml for all nodes, and restart the cluster:
sasi_indexes_enabled: true

Next, create your index like this:
CREATE CUSTOM INDEX books_name_idx ON abc.books (name)
    USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then, this should work:
> SELECT * FROM books WHERE name LIKE 'Mastering%';

 author_id | name                           | created_at                      | status
-----------+--------------------------------+---------------------------------+--------
      6866 | Mastering Apache Cassandra 3.x | 2023-06-13 12:51:12.802000+0000 |   True

(1 rows)


There are annotation like @SASI.NonTokenizingAnalyzed and @SASI.StandardAnalyzed I tried both but it does not work, the above table structure is with @SASI.NonTokenizingAnalyzed but not working, will check how to use Cassandra.yaml file in springboot.

With docker, I could not change the Cassandra.yaml file I changed and copied inside container multiple times with docker cp /opt/homebrew/var/services/git/hipi-cassandra-be-service/cassandra.yaml cassandra:\etc\cassandra\cassandra.yaml it does not work, but without docker, I could change it and get it work. path of the file in macOS is /opt/homebrew/etc/cassandra/cassandra.yaml. Thank you so much.

@PrafullaKumarSahu excellent!  Glad you got it figured out.
