# Vector Functions

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, 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;
``````