SAI quick start

Create a keyspace

CREATE KEYSPACE demo WITH replication = {'class': 'NetworkTopologyStrategy' , 
  'Cassandra': '1'};

Create a database table

CREATE TABLE demo.personnel (id int, firstname text, lastname text, age int, 
  employee_start_date date, PRIMARY KEY (id));

Create a few SAI indexes on the database table

CREATE CUSTOM INDEX lastname_sai_idx ON demo.personnel (lastname) 
USING 'StorageAttachedIndex' 
WITH OPTIONS = {'case_sensitive': false, 'normalize': true }; 

CREATE CUSTOM INDEX age_sai_idx ON demo.personnel (age) 
USING 'StorageAttachedIndex'; 

CREATE CUSTOM INDEX emp_start_date_sai_idx ON demo.personnel (employee_start_date) 
USING 'StorageAttachedIndex'; 

DESCRIBE TABLE demo.personnel;

CREATE TABLE demo.personnel (
    id int PRIMARY KEY,
    age int,
    employee_start_date date,
    firstname text,
    lastname text
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';
CREATE CUSTOM INDEX emp_start_date_sai_idx ON demo.personnel (employee_start_date) USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX lastname_sai_idx ON demo.personnel (lastname) USING 'StorageAttachedIndex' WITH OPTIONS = {'normalize': 'true', 'case_sensitive': 'false'};
CREATE CUSTOM INDEX age_sai_idx ON demo.personnel (age) USING 'StorageAttachedIndex';

Add data to your table

Use CQLSH INSERT commands to add some data to your database table.

Examples:

INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (1, 'Pat', 'Wilcox', 21, '2020-01-02'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (2, 'Ed', 'Fadel', 52, '2006-05-15'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (3, 'Lisa', 'Marino', 29, '2018-07-29'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (4, 'Felton', 'McDermott', 34, '2016-02-12'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (5, 'Leeann', 'Hirthe', 37, '2017-06-16'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (6, 'John', 'Sweet', 51, '2012-12-15'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (7, 'Perla', 'Reynolds', 24, '2012-07-22'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (8, 'William', 'Wisoky', 33, '2004-02-12'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (9, 'Alicia', 'Stoltenberg', 22, '2018-04-28'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (10, 'Neville', 'Klein', 41, '2007-05-15'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (11, 'Mikel', 'Schimmel', 44, '2019-07-31'); 
INSERT INTO demo.personnel (id, firstname, lastname, age, employee_start_date) VALUES (12, 'Gurpreet', 'Kaur', 34, '2011-02-26');

Submit queries

Use the CQLSH SELECT command to submit queries. The currently supported query operators with SAI indexes are =, <, >, AND.

Examples:

Find employees whose start date was between a date range:

SELECT * FROM demo.personnel WHERE employee_start_date > '2010-01-01' AND employee_start_date < '2015-12-31' LIMIT 10;

 id | age | employee_start_date | firstname | lastname
----+-----+---------------------+-----------+----------
  7 |  24 |          2012-07-22 |     Perla | Reynolds
  6 |  51 |          2012-12-15 |      John |    Sweet
 12 |  34 |          2011-02-26 |  Gurpreet |     Kaur

(3 rows)

Find employees whose age is less than 40:

SELECT * FROM demo.personnel WHERE age < 40;

 id | age | employee_start_date | firstname | lastname
----+-----+---------------------+-----------+-------------
  5 |  37 |          2017-06-16 |    Leeann |      Hirthe
  1 |  21 |          2020-01-02 |       Pat |      Wilcox
  8 |  33 |          2004-02-12 |   William |      Wisoky
  4 |  34 |          2016-02-12 |    Felton |   McDermott
  7 |  24 |          2012-07-22 |     Perla |    Reynolds
  9 |  22 |          2018-04-28 |    Alicia | Stoltenberg
 12 |  34 |          2011-02-26 |  Gurpreet |        Kaur
  3 |  29 |          2018-07-29 |      Lisa |      Marino

(8 rows)

Find the record for a specific employee:

SELECT * FROM demo.personnel WHERE lastname = 'Sweet';

 id | age | employee_start_date | firstname | lastname
----+-----+---------------------+-----------+----------
  6 |  51 |          2012-12-15 |      John |    Sweet
    
(1 rows)

Removing an SAI index

To remove an SAI index, use DROP INDEX.

Example:

DROP INDEX IF EXISTS demo.age_sai_idx;