spring data Cassandra LIKE query on secondary indexed cluster column

Question

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."

Answer 1

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)