MemSQL includes the following built-in functions for working with vectors of 32-bit floating-point numbers, represented as blobs:

• DOT_PRODUCT
• VECTOR_SUB
• EUCLIDEAN_DISTANCE
• JSON_ARRAY_PACK

Inserting Vectors as Blobs from an External Application

As a best practice when inserting binary data into a table from an external application, first convert a vector blob to its hexadecimal representation. Then in MemSQL, execute the INSERT statement using the UNHEX() built-in function to convert the hex representation back to its original binary format.

Consider the following example written in Python, which uses a few modules to generate vectors and convert them to hex:

import numpy
import struct

vector = numpy.random.random(1024)
vectorStr = "".join([struct.pack('f', elem) for elem in vector])
vectorStrEncoded = vectorStr.encode('hex')

When this code is executed, a random floating point vector is generated:

[0.09361789  0.22198475  0.36929942 ...,  0.97525847  0.98422799  0.26693578]

The vector is then converted to a binary string representation. Finally, the resulting vector is hex encoded:

bdbabf3df84f633ed014bd3ef6ca183ffc759b ... 48ca303e8aaa793f5ef67b3fcfab883e

Using the hex-encoded vector, you can connect to the database and execute a query that calls the UNHEX() built-in function to convert the vector to its original binary format during insertion:

memsqlConnection.query('INSERT INTO TABLE vectors VALUES (UNHEX("%s"))' % (vectorStrEncoded))

You can also do:

memsqlConnection.query('SELECT EUCLIDEAN_DISTANCE(UNHEX("%s"), UNHEX("%s"))' % (vectorStrEncoded, vectorStrEncoded))

Performance Note

DOT_PRODUCT(), EUCLIDEAN_DISTANCE(), and VECTOR_SUB() are high-performance functions implemented for fast vector operations, using single-instruction multiple-data (SIMD) processor instructions. Your hardware must support AVX2 instructions in order to use these functions.

Once the vectors have been stored in a table in the binary format, you do not need to use the UNHEX builtin to use the blob with the vector builtins.

-DOT_PRODUCT takes in two vector blob arguments and returns the scalar dot product of those two vectors

-VECTOR_SUB takes in two vector blob arguments and returns a vector blob which is a result of the second argument vector being subtracted from the first argument vector

-EUCLIDEAN_DISTANCE takes in two vector blob arguments and returns the scalar euclidean distance between the two vectors. EUCLIDEAN_DISTANCE(v1, v2) is equal to sqrt(DOT_PRODUCT(VECTOR_SUB(v1, v2), VECTOR_SUB(v1, v2))). However, because EUCLIDEAN_DISTANCE is more efficient with memory bandwidth, we expect the former to be faster than the latter.

For each of the three builtins, the input blob arguments must be the same length, and their binary length must be a multiple of 4. As long as those two conditions are met, the builtins can operator on vectors of any length. There is no requirement that the length of the vectors must be a power of 2, or that the length of the vectors must be divisible by 8 or 16.

If the result for either DOT_PRODUCT or EUCLIDEAN_DISTANCE is infinity, negative infinity, or NaN, null will be returned instead.

JSON_ARRAY_PACK

There is also the JSON_ARRAY_PACK builtin which can be used as a helper for the vector functions. It takes in a JSON array of floating point numbers and returns a packed binary blob suitable to be used as an argument for the vector functions. For example:

select DOT_PRODUCT(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'))

returns 5.25

Use Cases

A common use case for these functions is to work with floating-point vectors that describe images, such as images of faces or products. These vectors can be created using a neural network trained to categorize the images. Objects are inserted into a table. One field of the object is the vector that describes the image, obtained by feeding the image into the neural network. Queries using DOT_PRODUCT() or EUCLIDEAN_DISTANCE() can then be run to find closest matches. For example, the following query retrieves the 10 closest matches to the constant vector in the query, based on a cosine similarity match implemented using DOT_PRODUCT().

select t.*, dot_product(t.vector, unhex("...constant vector...")) as score
from t
order by score desc
limit 10;

Was this Article Helpful?

Thanks For Your Feedback!