Vector functions in SingleStoreDB make it possible to solve AI problems, including face matching, product photo matching, object recognition, text similarity matching and sentiment analysis.
In this article, we’ll demonstrate how we use the dot_product function (for cosine similarity) to find a matching image of a celebrity from among 16 million records in just 5 milliseconds! And it's easy – SingleStoreDB does the heavy lifting of parallelization and SIMD-based vector processing for you so you can worry about your application, not your data infrastructure.
Other vector functions supported in SingleStoreDB include euclidean distance calculation, transforming JSON arrays to binary vectors, vector math and vector manipulation.
Want to see our YouTube video on this topic instead of reading about it? Check it out here.
Interest in vector similarity search has increased a lot over time as the AI community has made tremendous strides in representing images, words, documents and much more, with vectors derived from deep neural networks. Witness the increasing interest in the term "cosine similarity" indicated by Google Trends over the last ten years, as shown in Figure 1.
Vector Matching in SQL with SingleStoreDB
In SingleStoreDB, you can match images in SQL with vector similarity search. SingleStoreDB is a real-time, distributed SQL database that's great for unified transactions and analytics. It runs in the cloud, or self-hosted. Our vector similarity matching is incredibly fast because it's based on Intel SIMD instructions, plus it benefits from our native parallel execution architecture.
Why use a database to do vector similarity testing, rather than doing it in your application? So you can worry about your application logic and let someone else worry about how to run query logic in a fast, parallel way.
SingleStoreDB applies filters and joins before vector matching operations if it makes sense. And, you can express the filters in SQL. That's not easy for an application developer to do themselves.
Example of Face Matching with SQL
We loaded 16,784,377 rows into this table:
1create table people(2 id bigint not null primary key,3 filename varchar(255),4 vector blob5);
Each row represents one image of a celebrity, and contains a unique ID number, the file name where the image is stored and a 128-element floating point vector representing the meaning of the face. This vector was obtained using facenet, a pre-trained neural network for creating vector embeddings from a face image.
Don't worry, you don't need to understand the AI to use this kind of approach – you just need to use somebody else's pre-trained neural network, or any tool that can provide you summary vectors for an object.
Now, we query this table using:
1select vector2into @v3from people4where filename = "Emma_Thompson/Emma_Thompson_0001.jpg";5 6select filename, dot_product(vector, @v) as score7from people where score > 0.18order by score desc9limit 5;
The first query gets a query vector @v for the image Emma_Thompson_0001.jpg. The second query finds the top five closest matches:
Emma_Thompson_0001.jpg is a perfect match for itself, so the score is close to 1. But interestingly, the next closest match is Emma_Thompson_0002.jpg.
Moreover, the search speed we obtained was truly incredible. The 2nd query took only 0.005 seconds on a 16 vcpu machine. And it processed all 16M vectors. This is a rate of over 3.3 billion vector matches per second. That's the beauty of our use of parallelism and Intel SIMD instructions to do the dot_product.
How to Test Out Face Matching For Yourself in SingleStoreDB
This demo is intriguing. But how do you get vectors to represent objects and store them in a SingleStoreDB database, so you can do something similar in a real application? The vectors need to come from application code, and must be inserted into a blob field of a table so you can use them with our dot_product() and euclidean_distance() functions.
We used facenet to get the images used in the database and queries we described. In your application environment you may be able to use facenet, or other vector-generation technologies from open source, third-party companies, or your own organization's AI team. One example of many is the article by Filip Saina on how to do this for a large collection of images using AWS tools.
Once you have a vector, you can insert it into SingleStoreDB — either directly as binary data (as packed 32-bit floating point when working with dot_product(), euclidean_distance()) or using our json_array_pack() function:
1set @x = json_array_pack('[0,0,1,0]');2select length(@x); 3 ⇒ 164select json_array_unpack(@x);5 ⇒ [0,0,1,0]
Keep reading to dive into the details of how to get vectors for images using facenet, and load or insert them into SingleStoreDB.
Environment Setup
First, create a machine with Ubuntu 22.04 installed. You can also use a virtual machine running this Ubuntu version, using Docker.
On the Ubuntu machine, follow these steps to install the required software:
Install Python 3.9
1sudo apt update2sudo apt install software-properties-common3sudo add-apt-repository ppa:deadsnakes/ppa4sudo apt install python3.95python3.9 --version
Install pip for Python 3.9
1curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py2sudo apt install python3.9-distutils3python3.9 get-pip.py
Install Tensorflow
1python3.9 -m pip install tensorflow
Install Scipy
1python3.9 -m pip install scipy
Install Facenet
1python3.9 -m pip install facenet
Install imageio
1python3.9 -m pip install imageio
Update facenet.py file
1vi /home/ubuntu/.local/lib/python3.9/site-packages/facenet/src/facenet.py
Replace misc.imread with imageio.imread
Add the following lines to the file in the import section (at the beginning of script):
import tensorflow.compat.v1 as tf
tf.disable_v2_behavior()
import imageio
Download the contents of this publicly-accessible Google Drive folder to:
1/home/ubuntu/Face_matching/
Run the following command to generate vectors of the dataset:
1python3.9 2/home/ubuntu/Face_matching/facenet/util/new_faces_to_vectors.py 3--inpath /home/ubuntu/Face_matching/images/lfw/ --outpath 4/home/ubuntu/Face_matching/images/lfw-out/lfw-out.log --mdlpath5/home/ubuntu/Face_matching/models/20170512-110547
A JSON file will be generated in outpath:
1/home/ubuntu/Face_matching/images/lfw-out/lfw-out.log
You can convert this JSON file data into txt file format using the following commands. These commands contain the input file paths in the source code:
1python3.9 /home/ubuntu/Face_matching/facenet/util/json_parse.py2python3.9 /home/ubuntu/Face_matching/facenet/util/merge.py
celebrity_data.txt would be generated in the home directory which contains all data with : as field separator.
Create a table in a SingleStoreDB database to load image file names and vectors:
1create table celebrity_face_vector(name text, vector text);
Load the data into the SingleStoreDB table:
1LOAD DATA LOCAL INFILE "path to celebrity_data.txt" INTO TABLE2celebrity_face_vector COLUMNS TERMINATED BY ':';
Run the following script to get a similarity score, and verify you have meaningful vectors in the file:
1select vector2into @json_text_v3from celebrity_face_vector4where name = "Emma_Thompson/Emma_Thompson_0001.jpg";5 6 7select name,8dot_product(JSON_ARRAY_PACK(@json_text_v), JSON_ARRAY_PACK(celebrity_face_vector.vector)) AS similarity9from celebrity_face_vector10order by similarity desc11limit 5
To get high-performance matching, you'll need to convert the JSON string-based format of the vectors into a binary format, and store the binary format in a table. This is the approach used in the examples with the "people" table in the first part of this blog. You can use JSON_ARRAY_PACK to convert text (JSON array) vectors to binary, then insert the result in a table:
1create table celebrity_face_vector2(name text, vector blob); 2 3insert into celebrity_face_vector24select name, json_array_pack(vector)5from celebrity_face_vector;
You can modify the input data presented to the code in this section to get vectors for face images of your own. To explore further, see the Facenet code repo from Sandberg.
A Shortcut Using Pre-made Vectors
If you'd like to take a shortcut and query data for the celebrity face image files using pre-created vectors — so you don't have to download the necessary software and build them using the procedure described in the preceding section — download this file: celebrity_data.sql. It contains premade vectors for the celebrity photos, in convenient INSERT statements.
Then, at the singlestore command line, create this table:
1create database facematch;2use facematch;3create table people (filename varchar(255), vector blob, shard(filename));
Now, source the file to create the rows. There are around 7,161 rows in the file.
1source celebrity_data.sql;
Now, query the table to find close matches to a query image:
1/* Find the 5 closest picture matches to the second picture of 2 Emma Thompson. */3 4set @v = (select vector 5 from people 6 where filename = "Emma_Thompson/Emma_Thompson_0002.jpg");7 8select filename, dot_product(vector, @v) as score9from people10order by score desc11limit 5;
.png?width=24&disable=upscale&auto=webp)
