You are viewing an older version of this section. View current production version.
MemSQL includes the following built-in functions for working with vectors of 32-bit floating-point numbers, represented as blobs:
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 (3.8), which uses a few modules to generate vectors and convert them to hex:
import numpy import struct vector = numpy.random.random(1024) vectorStr = "".join([str(struct.pack('f', elem)) for elem in vector]) vectorStrEncoded = vectorStr.encode("utf-8").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))
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
EUCLIDEAN_DISTANCE is infinity, negative infinity, or NaN,
null will be returned instead.
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]'))
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
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
select t.*, dot_product(t.vector, unhex("...constant vector...")) as score from t order by score desc limit 10;