Enabling SASI index

I came across an error while trying to use the like statement in cassandra:

InvalidRequest: Error from server: code=2200 [Invalid query] message="LIKE restriction is only supported on properly indexed columns. dialogue LIKE '%m' is not valid."
InvalidRequest: Error from server: code=2200 [Invalid query] message="SASI indexes are disabled. Enable in cassandra.yaml to use."

It basically says to use the like statement we need to enable the SASI index in the cassandra.yaml file.

So I had to download Apache cassandra. To install:

1. java installation:

sudo apt install openjdk-8-jdk -y

2. installation verification:

java -version

3. installing required dependencies:

sudo apt install apt-transport-https gnupg2 -y

4. Download cassandra GPG key:

sudo wget -q -O - https://www.apache.org/dist/cassandra/KEYS | sudo apt-key add -

5. adding downloaded repository:

sudo sh -c 'echo "deb http://www.apache.org/dist/cassandra/debian 311x main" > /etc/apt/sources.list.d/cassandra.list'

6. install apache cassandra:

sudo apt update
sudo apt install cassandra -y

7. verify status of cassandra:

sudo systemctl status cassandra

if cassandra status is exited, use this command:

service cassandra restart

8. login to cassandra:

cqlsh

if you encounter this error:

Traceback (most recent call last):
  File "/usr/bin/cqlsh.py", line 159, in <module>
    from cqlshlib import cql3handling, cqlhandling, pylexotron, sslhandling, cqlshhandling
ModuleNotFoundError: No module named 'cqlshlib'

just simply:

pip install cqlsh

9. To modify the casssandra.yaml:

sudo nano /etc/cassandra/cassandra.yaml

scroll down to the end of the file and enable the sasi index and materialized views:

then save and exit.

10. Clear the system cache:

nodetool flush system

11. then restart cassandra:

sudo systemctl restart cassandra
cqlsh

To know how to create a keyspace, table and add records to the table, you can refer to my article here:

https://medium.com/featurepreneur/cassandra-db-114a6f474d05

Then to use the like statement, create a custom index:

CREATE CUSTOM INDEX vmeme_dialogue_idx ON meme.vmeme (dialogue) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};
SELECT * from meme.vmeme where movie_name like '%s';

OUTPUT:

Thank you for reading!