Run vector search with CQL

You can use CQL to run vector search on vector data for machine learning applications. To enable vector search with CQL, you must store embeddings in a column that uses the vector data type.

Vector search is also known as similarity search or Approximate Nearest Neighbor (ANN) search. ANN queries are optimized to find vectors that are approximately nearest to the query vector. Queries to find the exact nearest neighbors (KNN) can be significantly slower, especially with large datasets, and are not supported in Cassandra-based databases.

This tutorial uses CQL statements to run a vector search. You can also use the Data API.

Quickstart for tables:

Prerequisites

An understanding of vector databases and similarity search.
An active Serverless (vector) database.

Prepare the schema

To run ANN queries, you must have a table with a vector column and an SAI index on that column.

Prepare a keyspace, table, and index for this tutorial. You can use an existing keyspace or create a new one for this tutorial.

Create a keyspace named cycling.
Connect to the CQL shell for Astra DB.
Select the keyspace that you want to use for this tutorial:
```
USE cycling;
```
Create a table named comments_vs to store text data about cycling races and embeddings.

Vector data is stored alongside related non-vector data, also known as metadata. The vector embeddings are stored in a vector column that supports vectors of the float type and of arbitrary subtypes. In this example, the vector column uses the float type and specifies the array dimension of 5 to store the embeddings. Embeddings for production use typically have much higher dimensions.
```
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);
```
To use an existing table, use ALTER TABLE to add a vector column to store vector embeddings:
```
ALTER TABLE cycling.comments_vs
  ADD comment_vector VECTOR <FLOAT, 5>;
```
Create a Storage Attached Indexing (SAI) index on the comment_vector column:
```
CREATE CUSTOM INDEX comment_ann_idx ON cycling.comments_vs(comment_vector)
  USING 'StorageAttachedIndex';
```
For more information about SAI, see the SAI documentation and Indexing for similarity search.

Load vector data

Insert data into the table:

INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'Alex','Raining too hard should have postponed','2017-02-14 12:43:20-0800',[0.45, 0.09, 0.01, 0.2, 0.11]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),e7ae5cf3-d358-4d99-b900-85902fda9bb0,'Alex','Second rest stop was out of water','2017-03-21 13:11:09.999-0800',[0.99, 0.5, 0.99, 0.1, 0.34]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),e7ae5cf3-d358-4d99-b900-85902fda9bb0,'Alex','LATE RIDERS SHOULD NOT DELAY THE START','2017-04-01 06:33:02.16-0800',[0.9, 0.54, 0.12, 0.1, 0.95]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-4207-9494-a29f9809de6f,'Amy','The gift certificate for winning was the best',totimestamp(now()),[0.13, 0.8, 0.35, 0.17, 0.03]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-7207-9494-a29f9809de6f,'Amy','The <B>gift certificate</B> for winning was the best',totimestamp(now()),[0.13, 0.8, 0.35, 0.17, 0.03]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-4207-9494-a29f9809de6f,'Amy','Glad you ran the race in the rain','2017-02-17 12:43:20.234+0400',[0.3, 0.34, 0.2, 0.78, 0.25]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-4207-9594-a29f9809de6f,'Jane','Boy, was it a drizzle out there!','2017-02-17 12:43:20.234+0400',[0.3, 0.34, 0.2, 0.78, 0.25]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(), c7fceba0-c141-3207-9494-a29f9809de6f,'Amy','THE RACE WAS FABULOUS!','2017-02-17 12:43:20.234+0400',[0.3, 0.34, 0.2, 0.78, 0.25]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-4207-9494-a29f9809de6f, 'Amy','Great snacks at all reststops','2017-03-22 5:16:59.001+0400',[0.1, 0.4, 0.1, 0.52, 0.09]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),c7fceba0-c141-4207-9494-a29f9809de6f,'Amy','Last climb was a killer','2017-04-01 17:43:08.030+0400',[0.3, 0.75, 0.2, 0.2, 0.5]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),e8ae5cf3-d358-4d99-b900-85902fda9bb0,'John','rain, rain,rain, go away!','2017-04-01 06:33:02.16-0800',[0.9, 0.54, 0.12, 0.1, 0.95]);
INSERT INTO cycling.comments_vs (record_id, id, commenter, comment, created_at, comment_vector) VALUES (now(),e8ae5df3-d358-4d99-b900-85902fda9bb0,'Jane','Rain like a monsoon','2017-04-01 06:33:02.16-0800',[0.9, 0.54, 0.12, 0.1, 0.95]);

Note the format of the vector data type. Vector data must be stored in a valid format so that it can be indexed and searched correctly. Additionally, your embeddings must all originate from the same embedding model and match the dimensionality of your vector index. If embeddings originate from different models, the vector search won’t represent an accurate comparison.

This example uses randomly generated embeddings to demonstrate the vector search functionality. In a production scenario, you would produce embeddings specifically for your data and your search query. This example is simplified to show the mechanics of how to use CQL to create vector search data objects.

Run vector search queries

These examples demonstrate how to perform a vector search with CQL.

Vector search works optimally on tables with no overwrites or deletions of the vector column. For a vector column with changes, expect slower search results.

Vector search uses an approximate nearest neighbor (ANN) algorithm that typically yields results of comparable quality to an exact nearest neighbors (KNN) algorithm. ANN speed and scaling are superior to KNN, making it a better choice for large datasets and real-time applications that prioritize speed over absolute accuracy.

Least-similar searches are not supported.

For more information, see Vector concepts: Vector search.

Simple ANN search

A SELECT with ORDER BY … ANN OF runs a vector search to find the rows most similar to the specified query vector:

SELECT * FROM cycling.comments_vs
  ORDER BY comment_vector ANN OF [0.15, 0.1, 0.1, 0.35, 0.55]
  LIMIT 3;

LIMIT cannot be greater than 1,000.

In the results, the record_id column shows the unique identifier for the records that most closely matched the query vector:

 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 | a2ad6561-2759-11ef-81d8-676f970e6feb
 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 | a2a9e2f1-2759-11ef-81d8-676f970e6feb
 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 | a2adda91-2759-11ef-81d8-676f970e6feb

(3 rows)

ANN search with similarity calculation

Use the similarity_ functions to include the similarity score for each match in the results. The supported functions are similarity_dot_product, similarity_cosine, and similarity_euclidean. The arguments for each function are the name of the vector column and the embedding for comparison.

SELECT comment, similarity_cosine(comment_vector, [0.1, 0.15, 0.3, 0.12, 0.05])
    FROM cycling.comments_vs
    ORDER BY comment_vector ANN OF [0.1, 0.15, 0.3, 0.12, 0.05]
    LIMIT 3;

 comment                                | system.similarity_cosine(comment_vector, [0.1, 0.15, 0.3, 0.12, 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)

You can also set the similarity function on the index. For more information, see SAI vector search examples.

CassIO for AI workloads

CassIO abstracts away the details of accessing your Cassandra-based database for the typical needs of generative artificial intelligence (AI) or other machine learning workloads. CassIO offers a low-boilerplate, ready-to-use set of tools for seamless integration of Cassandra-based databases in most AI-oriented applications. For more information, see CassIO.