Query with SAI
This page demonstrates how to run queries on tables with SAI indexes.
To query with SAI, you need a keyspace with a table and one or more SAI indexes.
If you haven’t created an SAI index yet, see the SAI quickstart to learn how to create multiple indexes based on non-primary key columns.
Query operators supported for SAI
|
SAI only supports |
SAI supports the following query operators for tables with SAI indexes:
-
Numerics:
=,<,>,⇐,>=,AND -
Strings:
=,AND -
Collections:
=,CONTAINS,CONTAINS KEY
SAI does not supports the following query operators for tables with SAI indexes:
-
Strings or Numerics:
LIKE,OR,IN
Vector search with SAI
For an example and explanation of vector search with CQL, see Vector search quickstart.
Single index match on a column
This example uses the following table and indexes:
CREATE TABLE IF NOT EXISTS cycling.comments_vs (
record_id timeuuid,
id uuid,
commenter text,
comment text,
comment_vector VECTOR <FLOAT, 5>,
created_at timestamp,
PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
USING 'StorageAttachedIndex'
WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};
CREATE CUSTOM INDEX created_at_idx ON cycling.comments_vs (created_at)
USING 'StorageAttachedIndex';
CREATE CUSTOM INDEX comment_ann_idx ON cycling.comments_vs(comment_vector)
USING 'StorageAttachedIndex';The column commenter is not the partition key in this table, so an index is required to query on it.
Query for a match on that column:
SELECT * FROM cycling.comments_vs WHERE commenter = 'Alex';Results
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)Note that a failure will occur if you try this query before creating the index:
SELECT * FROM cycling.comments_vs WHERE commenter = 'Alex';Results
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)
id | created_at | comment | commenter | record_id
----+------------+---------+-----------+-----------
(0 rows)Single index match on a column with options
SAI options include case_sensitive, ascii, and normalize.
This example uses the following table and indexes:
CREATE TABLE IF NOT EXISTS cycling.comments_vs (
record_id timeuuid,
id uuid,
commenter text,
comment text,
comment_vector VECTOR <FLOAT, 5>,
created_at timestamp,
PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
CREATE CUSTOM INDEX commenter_idx ON cycling.comments_vs (commenter)
USING 'StorageAttachedIndex'
WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};Case-sensitivity
The column commenter is not the partition key in this table, so an index is required to query on it.
If we want to check commenter as a case-sensitive value, we can use the case_sensitive option set to true.
Note that no results are returned if you use an inappropriately case-sensitive value in the query:
SELECT comment,comment_vector,commenter FROM cycling.comments_vs
WHERE commenter : 'alex';Results
When we switch the case of the cyclist’s name to match the case in the index, the query succeeds:
SELECT comment,comment_vector,commenter FROM cycling.comments_vs
WHERE commenter : 'Alex';Results
See also
For more examples, see SAI examples.
