Vector Search

Overview

Kinetica's vector search capability is enabled through its use as a vector store database. Once a table has been created with vector type and a set of embeddings have been loaded into the table, a variety of K-nearest neighbor searches can be performed on the data set.

Details on the vector type and its usage are found below. For more complete walkthroughs of the functionality in Jupyter notebook form, see:

Vector Type

The vector data type has been added to facilitate managing embeddings and issuing vector search queries. The vector type is effectively an array of float types and can be used as shown in the following examples.

Create Table

A vector column can optionally be configured to normalize the vector data inserted into it, giving each vector a magnitude (L2 norm) of 1. This can improve the performance of some vector operations with minimal overhead.

Vector Column without Normalization

SQL 
1
2
3
4
5

CREATE OR REPLACE TABLE example.vs
(
      name VARCHAR(16) NOT NULL,
      embedding VECTOR(3) NOT NULL
)
Python (native API) 
1
2
3
4
5
6
7

table_name = 'example.vs'
columns = [
    ['name', 'string', 'char16'],
    ['embedding', 'bytes', 'vector(3)']
]

gpudb.GPUdbTable(columns, table_name, db = kinetica)
Python (from DataFrame) 
1
2

table_name = 'example.vs'
vs = gpudb.GPUdbTable.from_df(df, kinetica, table_name, clear_table = True)

Vector Column with Normalization

SQL 
1
2
3
4
5

CREATE OR REPLACE TABLE example.vs_norm
(
      name VARCHAR(16) NOT NULL,
      embedding VECTOR(3, NORMALIZE) NOT NULL
)
Python (native API) 
1
2
3
4
5
6
7

table_name = 'example.vs_norm'
columns = [
    ['name', 'string', 'char16'],
    ['embedding', 'bytes', 'vector(3)', 'normalize']
]

vs = gpudb.GPUdbTable(columns, table_name, db = kinetica)
Python (from DataFrame) 
1
2
3
4
5
6

table_name = 'example.vs_norm'

vs = gpudb.GPUdbTable.from_df(
        df, kinetica, table_name, clear_table = True,
        column_types = { 'embedding': [ 'vector(3)', 'normalize' ] }
)

Insert Data

SQL 
1
2
3
4
5
6
7

INSERT INTO example.vs
VALUES
      ('fun','[-0.23764,0.43119,-0.72154]'),
      ('play','[-0.73571,0.19937,-0.89408]'),
      ('food','[0.47222,-0.44545,-0.51833]'),
      ('money','[0.59784,-0.057026,0.97746]'),
      ('work','[0.51359,0.19695,-0.51944]')
Python (as strings) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

embs = [
    ['fun', '[-0.23764,0.43119,-0.72154]'],
    ['play', '[-0.73571,0.19937,-0.89408]'],
    ['food', '[0.47222,-0.44545,-0.51833]'],
    ['money', '[0.59784,-0.057026,0.97746]'],
    ['work', '[0.51359,0.19695,-0.51944]']
]

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

vs.insert_records(embs)
Python (as native types) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

embs = [
    ['fun', [-0.23764,0.43119,-0.72154]],
    ['play', [-0.73571,0.19937,-0.89408]],
    ['food', [0.47222,-0.44545,-0.51833]],
    ['money', [0.59784,-0.057026,0.97746]],
    ['work', [0.51359,0.19695,-0.51944]]
]

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

vs.insert_records(embs)
Python (as DataFrame) 
1
2
3

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

vs.insert_df(df, batch_size = 10000)

Retrieve Data

SQL 
1
2

SELECT name, embedding
FROM example.vs
Python (w/o native type conversions) 
1
2
3
4
5
6
7
8
9

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

records = vs.get_records()

dim = 3

print('%-16s %s' % ('Name', 'Embedding'))
for record in records:
    print('%-16s %s' % (record[0], list(struct.unpack('%df'%dim, record[1]))))
Python (w/ native type conversions) 
1
2
3
4
5
6
7

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica, convert_special_types_on_retrieval = True)

records = vs.get_records(encoding = 'json')

print('%-16s %s' % ('Name', 'Embedding'))
for record in records:
    print('%-16s %s' % (record["name"], record["embedding"]))
Python (as DataFrame) 
1
2
3
4
5

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

df = vs.to_df()

print(df)

Vector (CAGRA) Index

The performance of some vector searches can be improved with the application of a CAGRA index.

This can be applied, in SQL, during table creation, via CREATE TABLE, as well as afterwards, as below:

SQL 
1
2
3

ALTER TABLE example.employee
ADD CAGRA INDEX (profile)
WITH OPTIONS (index_options = 'itopk_size: 128, graph_build_algo: ivf_pq')
Python 
1
2
3
4
5
6
7
8
9

retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "profile",
    options = {
            "index_type": "cagra",
            "index_options": "itopk_size: 128, graph_build_algo: ivf_pq"
    }
)

Vector Functions & Operators

Vector Column Functions

FunctionDescription
L1_NORM(v)Calculates the sum of the absolute values of the given vector's values
L2_NORM(v)Calculates the square root of the sum of squares of the given vector's values
LINF_NORM(v)Returns the maximum of the given vector's values
LP_NORM(v, p)Calculates the Lp-space norm of the given vector in the space p
NTH(v, n)Returns the given vector's value at 0-based index n
SIZE(v)Returns the given vector's number of values

Vector Search Functions

A number of K-nearest neighbor functions have been implemented to support vector searches. For examples, see Vector Function Examples.

FunctionDescription
COSINE_DISTANCE(v1, v2)1 minus the cosine similarity (equality of angle) of the given vectors
DOT_PRODUCT(v1, v2)Calculates the sum of products of the given vectors' values
EUCLIDEAN_DISTANCE(v1, v2)Alias for L2_DISTANCE
L1_DISTANCE(v1, v2)Calculates the L1-space (taxicab) distance between the given vectors
L2_DISTANCE(v1, v2)Calculates the L2-space (Euclidean) distance between the given vectors
L2_SQUAREDDISTANCE(v1, v2)Calculates the sum of squares of distances between the given vectors' values
L2_DISTSQ(v1, v2)Alias for L2_SQUAREDDISTANCE
LINF_DISTANCE(v1, v2)Calculates the maximum of the distances between pairs of values in the given vectors
LP_DISTANCE(v1, v2, p)Calculates the Lp-space distance between the given vectors in the space p

Vector Search Operators

These operators can be used as shorthand to apply vector functions to individual vector column values. For examples, see Vector Operator Examples.

Note

These operators are only available in SQL or in the native API via /execute/sql.

OperatorEquivalent Function
v1 <-> v2L2_DISTANCE(v1, v2)
v1 <=> v2COSINE_DISTANCE(v1, v2)
v1 <#> v2DOT_PRODUCT(v1, v2)

Vector Search Examples

Vector searches can be performed using either named functions or the corresponding operators for select functions.

Vector Operator Examples

SQL Literal 
1
2
3

SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance 
FROM example.vs
ORDER BY distance
SQL Lookup 
1
2
3
4
5
6

SELECT TOP 5 name, embedding <-> search_embedding as distance 
FROM
      example.vs,
      (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
WHERE name <> 'fun'
ORDER BY distance
Python Literal 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

sql = """
    SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance 
    FROM example.vs
    ORDER BY distance
"""

with gpudb.GPUdbSqlIterator(kinetica, sql) as records:
    
    print('%-16s %s' % ('Name', 'Distance'))
    for record in records:
        print('%-16s %f' % (record[0], record[1]))
Python Lookup w/ SQL 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

sql = """
    SELECT TOP 5 name, embedding <-> search_embedding as distance 
    FROM
        example.vs,
        (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
    WHERE name <> 'fun'
    ORDER BY distance
"""

with gpudb.GPUdbSqlIterator(kinetica, sql) as records:
    
    print('%-16s %s' % ('Name', 'Distance'))
    for record in records:
        print('%-16s %f' % (record[0], record[1]))
Python Literal to DataFrame 
1
2
3
4
5
6
7
8
9

sql = """
    SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance 
    FROM example.vs
    ORDER BY distance
"""

df = kinetica.to_df(sql)

print(df)
Python Lookup w/ SQL to DataFrame 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

sql = """
    SELECT TOP 5 name, embedding <-> search_embedding as distance 
    FROM
        example.vs,
        (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
    WHERE name <> 'fun'
    ORDER BY distance
"""

df = kinetica.to_df(sql)

print(df)

Vector Function Examples

SQL Literal 
1
2
3

SELECT TOP 5 name, L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance 
FROM example.vs
ORDER BY distance
SQL Lookup 
1
2
3
4
5
6

SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance 
FROM
      example.vs,
      (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
WHERE name <> 'fun'
ORDER BY distance
Python Literal 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

records = vs.get_records_by_column(
        ["name", "L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance"],
        limit = 5,
        options = {"sort_by": "distance"}
)

print('%-16s %s' % ('Name', 'Distance'))
for record in zip(records['name'], records['distance']):
    print('%-16s %f' % (record[0], record[1]))
Python Lookup w/ SQL 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

sql = """
    SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance 
    FROM
        example.vs,
        (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
    WHERE name <> 'fun'
    ORDER BY distance
"""

with gpudb.GPUdbSqlIterator(kinetica, sql) as records:
    
    print('%-16s %s' % ('Name', 'Distance'))
    for record in records:
        print('%-16s %f' % (record[0], record[1]))
Python Literal to DataFrame 
1
2
3
4
5
6
7
8
9

sql = """
    SELECT TOP 5 name, L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance
    FROM example.vs
    ORDER BY distance
"""

df = kinetica.to_df(sql)

print(df)
Python Lookup w/ SQL to DataFrame 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

sql = """
    SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance 
    FROM
        example.vs,
        (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
    WHERE name <> 'fun'
    ORDER BY distance
"""

df = kinetica.to_df(sql)

print(df)