SAI quick start

Follow the steps to get started quickly with Storage-Attached Indexing (SAI).

To get started with the Storage-Attached Indexing (SAI), create a keyspace and a simple table. Use a few CREATE CUSTOM INDEX .. USING 'StorageAttachedIndex' DDL statetments on DataStax Enterprise (DSE) database tables, load some data, and submit queries to try the beta features in your development environment.

Create a keyspace

In CQLSH, start by defining the keyspace to try the commands in a test environment. Example:
CREATE KEYSPACE demo WITH replication = {'class': 'NetworkTopologyStrategy' , 
  'Cassandra': '1'};

Create a database table

Create a database table in the demo keyspace. Example:
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 secondary indexes with SAI.
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'; 
Take a look at the DDL:
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;