In this example, we’ll demonstrate how we use the dot_product function (for cosine similarity) to find a matching image of a celebrity from among 7000 records in just three milliseconds!
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.
Step 1: Signup for a free SingleStoreDB trial account at https://portal.singlestore.com/
Step 2: Create a workspace (S00 is enough)
Step 3: Create a database called image_recognition in the SQL Editor
1Create database image_recognition;
Step 4: Go to ‘connect’ on your workspace in the portal and copy the workspace URL, your username and password to connect to your database using sqlalchemy.
Step 5: Import the the following libraries into your python kernel or Jupyter notebook
1!pip3 install pymysql boto3 sqlalchemy2from sqlalchemy import *3import pymysql, boto3, requests, json4import matplotlib.pyplot as plt5import ipywidgets as widgets6from botocore import UNSIGNED7from botocore.client import Config8import botocore.exceptions9import urllib.request
Step 6: Create the connection string
1UserName='<Username usually admin>'2Password='<Password for that user>'3DatabaseName='image_recognition'4URL='<Host that you copied above>:3306'5db_connection_str = "mysql+pymysql://"+UserName+":"+Password+"@"+URL+"/"+DatabaseName6db_connection = create_engine(db_connection_str)
Step 7: Create a table — named “people” — in your database
1query = 'create table people (filename varchar(255), vector blob, 2shard(filename))'3db_connection.execute(query)
Step 8: Import our sample dataset into your database. Alternatively, follow the instructions in our previous blog to learn how you can create your vectors for your own images using facet and insert them into SingleStoreDB.
Note: The notebook or python script will run thousands of commands sequentially from the SQL script stored in Github repo and might take 15-20 minutes to be fully executed.
1url = 2'https://raw.githubusercontent.com/singlestore-labs/singlestoredb-samples/m3ain/Tutorials/Face%20matching/celebrity_data.sql'4response = requests.get(url)5sql_script = response.text6new_array = sql_script.split('\n')7for i in new_array:8 if (i != ''):9 db_connection.execute(i)
Step 9: Run our image matching algorithm using just two lines of SQL. In this example, we use Adam Sandler:
1selected_name = "Adam_Sandler/Adam_Sandler_0003.jpg"2query1 = 'set @v = (select vector from people where filename = "' + 3selected_name + '");'4query2 = 'select filename, dot_product(vector, @v) as score from people 5order by score desc limit ' + str(num_matches) +';'6db_connection.execute(query1)7result = db_connection.execute(query2)8for res in result:9 print(res)
(Optional) Step 10: Use our visualizer and drop down to see this image matching in action!
1s3 = boto3.resource('s3',region_name='us-east-1', 2config=Config(signature_version=UNSIGNED))3bucket = s3.Bucket('studiotutorials')4prefix = 'face_matching/'5names=[]6for obj in bucket.objects.all():7 if (obj.key.startswith(prefix)):8 drop = obj.key[len(prefix):]9 names.append(drop)10 11 12def on_value_change(change):13 selected_name = change.new14 print(selected_name)15 num_matches = 516 #SingleStore query to find the vectors of images that match17 countquery = 'select count(*) from people where filename = "' + 18 selected_name + '";'19 countdb = db_connection.execute(countquery)20 for i in countdb:21 x = i[0]22 if (int(x) > 0):23 query1 = 'set @v = (select vector from people where filename = "' + 24 selected_name + '");'25 query2 = 'select filename, dot_product(vector, @v) as score from 26 people order by score desc limit ' + str(num_matches) +';'27 db_connection.execute(query1)28 result = db_connection.execute(query2)29 original = "/original.jpg"30 images = []31 matches = []32 try:33 bucket.download_file(prefix + selected_name, original)34 images.append(original)35 except botocore.exceptions.ClientError as e:36 if e.response['Error']['Code'] == "404":37 38 urllib.request.urlretrieve('https://i.redd.it/mn9c32es4zi21.png', original)39 else:40 raise41 cnt = 042 for res in result:43 print(res)44 temp_file = "/match" + str(cnt) + ".jpg"45 images.append(temp_file)46 matches.append(res[1])47 try:48 bucket.download_file(prefix + res[0], temp_file)49 except botocore.exceptions.ClientError as e:50 if e.response['Error']['Code'] == "404":51 urllib.request.urlretrieve('https://i.redd.it/mn9c32es4zi21.png', 52 temp_file)53 else:54 raise55 cnt += 156 fig, axes = plt.subplots(nrows=1, ncols=num_matches+1, figsize=(40, 5740))58 for i in range(num_matches+1):59 axes[i].imshow(plt.imread(images[i]))60 axes[i].set_xticks([])61 axes[i].set_yticks([])62 axes[i].set_xlabel('')63 axes[i].set_ylabel('')64 if i == 0:65 axes[i].set_title("Original Image", fontsize=14)66 else:67 axes[i].set_title("Match " + str(i) + ". Score: " + 68str(matches[i-1]), fontsize=14)69 plt.show()70 else:71 print("No match for this image as it was not inserted into the 72 People Database")73 74 75dropdown = widgets.Dropdown(76 options=names,77 description='Select an Image:',78 value=names[0]79)80dropdown.observe(on_value_change, names='value')81display(dropdown)
Look at SingleStoreLab's github repository on image recognition to follow this demo with our example python notebook!
.png?width=24&disable=upscale&auto=webp)
.png?width=24&disable=upscale&auto=webp)
