Creating a Table
Avector 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
CREATE OR REPLACE TABLE example.vs
(
name VARCHAR(16) NOT NULL,
embedding VECTOR(3) NOT NULL
)
table_name = 'example.vs'
columns = [
['name', 'string', 'char16'],
['embedding', 'bytes', 'vector(3)']
]
gpudb.GPUdbTable(columns, table_name, db = kinetica)
table_name = 'example.vs'
vs = gpudb.GPUdbTable.from_df(df, kinetica, table_name, clear_table = True)
Vector Column with Normalization
CREATE OR REPLACE TABLE example.vs_norm
(
name VARCHAR(16) NOT NULL,
embedding VECTOR(3, NORMALIZE) NOT NULL
)
table_name = 'example.vs_norm'
columns = [
['name', 'string', 'char16'],
['embedding', 'bytes', 'vector(3)', 'normalize']
]
vs = gpudb.GPUdbTable(columns, table_name, db = kinetica)
table_name = 'example.vs_norm'
vs = gpudb.GPUdbTable.from_df(
df, kinetica, table_name, clear_table = True,
column_types = { 'embedding': [ 'vector(3)', 'normalize' ] }
)
Inserting Vector Data
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]')
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)
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)
vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)
vs.insert_df(df, batch_size = 10000)
Retrieving Vector Data
SELECT name, embedding
FROM example.vs
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]))))
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"]))
vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)
df = vs.to_df()
print(df)
Indexing Vector Data
CAGRA Vector Index
ALTER TABLE example.employee
ADD CAGRA INDEX (profile)
retobj = kinetica.alter_table(
table_name = "example.employee",
action = "create_index",
value = "profile",
options = {
"index_type": "cagra"
}
)
HNSW Vector Index
ALTER TABLE example.employee
ADD HNSW INDEX (profile)
retobj = kinetica.alter_table(
table_name = "example.employee",
action = "create_index",
value = "profile",
options = {
"index_type": "hnsw"
}
)
Searching Vector Data
Vector Search by Operator
SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance
FROM example.vs
ORDER BY distance
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
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]))
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]))
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)
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 Search by Function
SELECT TOP 5 name, L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance
FROM example.vs
ORDER BY distance
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
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]))
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]))
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)
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)