Query with SAI
Query operators
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
The SAI quickstart focuses only on defining multiple indexes based on non-primary key columns (a very useful feature). A few examples are provided here to show how to query tables with SAI indexes.
The SAI examples page provides more extensive examples.
|
SAI only supports |
Vector search
This example uses the following table and index:
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 comment_ann_idx ON cycling.comments_vs(comment_vector)
USING 'StorageAttachedIndex';Simple query
To query data using Vector Search, use a SELECT query:
SELECT * FROM cycling.comments_vs
ORDER BY comment_vector ANN OF [0.15, 0.1, 0.1, 0.35, 0.55]
LIMIT 3;Results
The query must have a limit of 1,000 or fewer.
Scrolling to the right on the results shows the comments from the table that most closely matched the embeddings used for the query.
Similarity query
To obtain the similarity calculation of the best scoring node closest to the query data as part of the results, use a modified SELECT query:
SELECT comment, similarity_cosine(comment_vector, [0.2, 0.15, 0.3, 0.2, 0.05])
FROM cycling.comments_vs
ORDER BY comment_vector ANN OF [0.1, 0.15, 0.3, 0.12, 0.05]
LIMIT 3;Results
The supported functions for this type of query are:
-
similarity_dot_product
-
similarity_cosine
-
similarity_euclidean
with the parameters of (<vector_column>, <embedding_value>). Both parameters represent vectors.
Vector Search utilizes Approximate Nearest Neighbor (ANN) that in most cases yields results almost as good as the exact match. The scaling is superior to Exact Nearest Neighbor (KNN).
Least-similar searches are not supported.
Vector Search works optimally on tables with no overwrites or deletions of the comment_vector column. For an comment_vector column with changes, expect slower search results.
The embeddings were randomly generated in this example. Generally, you would run both your source documents/contents through an embeddings generator, as well as the query you were asking to match. This example is simply to show the mechanics of how to use CQL to create vector search data objects.
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
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
