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 Indexes

There are two types of indexes available to improve the performance of vector searches:

CAGRA Vector Index

The performance of some vector searches can be improved with the application of a CAGRA index, which must be manually refreshed to account for updates to the data in the corresponding table.

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

SQL
Python

SQL
1
2
ALTER TABLE example.employee
ADD CAGRA INDEX (profile)
Python
1
2
3
4
5
6
7
8
retobj = kinetica.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "profile",
    options = {
            "index_type": "cagra"
    }
)

HNSW Vector Index

The performance of some vector searches can be improved with the application of an HNSW index, which is automatically updated as the data in the corresponding table changes.

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

SQL
Python

SQL
1
2
ALTER TABLE example.employee
ADD HNSW INDEX (profile)
Python
1
2
3
4
5
6
7
8
retobj = kinetica.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "profile",
    options = {
            "index_type": "hnsw"
    }
)

Vector Functions & Operators

Vector Column Functions

Function	Description
`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.

Function	Description
`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.

Operator	Equivalent Function
`v1 <-> v2`	`L2_DISTANCE(v1, v2)`
`v1 <=> v2`	`COSINE_DISTANCE(v1, v2)`
`v1 <#> v2`	`DOT_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)