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 SELECT queries, but not UPDATE or DELETE queries.

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

 id                                   | created_at                      | comment                                | comment_vector                                    | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+---------------------------------------------------+-----------+--------------------------------------
 e8ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 |              rain, rain,rain, go away! | b'?fff?\\n=q=\\xf5\\xc2\\x8f=\\xcc\\xcc\\xcd?s33' |      John | 3d42f050-37da-11ef-81ed-f92c3c7170c3
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START | b'?fff?\\n=q=\\xf5\\xc2\\x8f=\\xcc\\xcc\\xcd?s33' |      Alex | 3d3d9921-37da-11ef-81ed-f92c3c7170c3
 e8ae5df3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 |                    Rain like a monsoon | b'?fff?\\n=q=\\xf5\\xc2\\x8f=\\xcc\\xcc\\xcd?s33' |      Jane | 3d433e71-37da-11ef-81ed-f92c3c7170c3

(3 rows)

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

 comment                                | system.similarity_cosine(comment_vector, [0.2, 0.15, 0.3, 0.2, 0.05])
----------------------------------------+-----------------------------------------------------------------------
      Second rest stop was out of water |                                                              0.949701
              rain, rain,rain, go away! |                                                              0.789776
 LATE RIDERS SHOULD NOT DELAY THE START |                                                              0.789776

(3 rows)

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                                | comment_vector                                                  | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-----------------------------------------------------------------+-----------+--------------------------------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START |               b'?fff?\\n=q=\\xf5\\xc2\\x8f=\\xcc\\xcc\\xcd?s33' |      Alex | 3d3d9921-37da-11ef-81ed-f92c3c7170c3
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 |      Second rest stop was out of water | b'?}p\\xa4?\\x00\\x00\\x00?}p\\xa4=\\xcc\\xcc\\xcd>\\xae\\x14{' |      Alex | 3d3c3991-37da-11ef-81ed-f92c3c7170c3
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed |       b'>\\xe6ff=\\xb8Q\\xec<#\\xd7\\n>L\\xcc\\xcd=\\xe1G\\xae' |      Alex | 3d390540-37da-11ef-81ed-f92c3c7170c3

(3 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                                | comment_vector                                                  | commenter | record_id
--------------------------------------+---------------------------------+----------------------------------------+-----------------------------------------------------------------+-----------+--------------------------------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-04-01 14:33:02.160000+0000 | LATE RIDERS SHOULD NOT DELAY THE START |               b'?fff?\\n=q=\\xf5\\xc2\\x8f=\\xcc\\xcc\\xcd?s33' |      Alex | 3d3d9921-37da-11ef-81ed-f92c3c7170c3
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-03-21 21:11:09.999000+0000 |      Second rest stop was out of water | b'?}p\\xa4?\\x00\\x00\\x00?}p\\xa4=\\xcc\\xcc\\xcd>\\xae\\x14{' |      Alex | 3d3c3991-37da-11ef-81ed-f92c3c7170c3
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 2017-02-14 20:43:20.000000+0000 | Raining too hard should have postponed |       b'>\\xe6ff=\\xb8Q\\xec<#\\xd7\\n>L\\xcc\\xcd=\\xe1G\\xae' |      Alex | 3d390540-37da-11ef-81ed-f92c3c7170c3

(3 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

 comment               | comment_vector                                        | commenter
-----------------------+-------------------------------------------------------+-----------
 Rest stops were great | b'?}p\xa4?\x00\x00\x00?}p\xa4=\xcc\xcc\xcd>\xae\x14{' |      alex

(1 rows)

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

 comment                                | comment_vector                                        | commenter
----------------------------------------+-------------------------------------------------------+-----------
 LATE RIDERS SHOULD NOT DELAY THE START |            b'?fff?\n=q=\xf5\xc2\x8f=\xcc\xcc\xcd?s33' |      Alex
      Second rest stop was out of water | b'?}p\xa4?\x00\x00\x00?}p\xa4=\xcc\xcc\xcd>\xae\x14{' |      Alex
 Raining too hard should have postponed |      b'>\xe6ff=\xb8Q\xec<#\xd7\n>L\xcc\xcd=\xe1G\xae' |      Alex
      LATE RIDERS SHOULD NOT BE ALLOWED |            b'?fff?\n=q=\xf5\xc2\x8f=\xcc\xcc\xcd?s33' | alexander

(4 rows)

Query with SAI

Query operators

Prerequisites:

Vector search

Query vector data with CQL

Simple query

Similarity query

Single index match on a column

Single index match on a column with options

Case-sensitivity

Was this helpful?

Give Feedback