Working with Vector Search

Create vector keyspace

cycling as the keyspace name:

  1. CREATE KEYSPACE IF NOT EXISTS cycling   WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : '1' };

Use vector keyspace

cycling as the keyspace name:

  1. USE cycling;

Create vector table

comments_vector column for vector. The code below creates a vector with five values:

  1. 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);

Optionally, you can alter an existing table to add a vector column:

  1. ALTER TABLE cycling.comments_vs   ADD comment_vector VECTOR <FLOAT, 5>(1)

Create vector index

Create the custom index with Storage Attached Indexing (SAI):

  1. CREATE INDEX IF NOT EXISTS ann_index  ON cycling.comments_vs(comment_vector) USING 'sai';

Storage Attached Indexing documentation.

Load vector data into your database

Insert data into the table using the new type:

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

Query vector data with CQL

SELECT query:

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

SELECT query:

  1. 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 1;

The supported functions for this type of query are:

  • similarity_dot_product
  • similarity_cosine
  • similarity_euclidean with the parameters of (, ). Both parameters represent vectors.