I made my first venture into 23ai’s VECTOR feature only a few weeks ago. I used some interesting data (my Untappd Beer diary) with a generation model loaded into the Oracle Database to compute some vectors using Hugging Face and all-MiniLM-L12-v2 (Read that now.)

So that was cool, but it was all text based.

How about doing something similar with pictures?

You can’t load an LLM to Oracle that deals with pictures (yet??), so what am I do to? The database is perfectly capable of dealing with the vectors once I have them, I just need to compute them somewhere other than from the database.

Note: while you can’t generate the vectors for images wholly from the database, you can use our APIs to get them from external sources via REST APIs (Docs.) Instead of configuring that, I’m just going to do it locally on my client.

Want to play with 23ai? Try it for FREE on your own machine!
Container | Linux (OL9) | Windows | VirtualBox

Just use python!

I cobbled some code together from both StackOverflow and some AI prompts.

It takes a filename from the command line argument passed to it, and then using the Hugging Face openai/clip-vit-base-patch32 model – generates some vectors that we can then use to compare pictures to see if they’re alike, or just how alike they are.

Once I get that Vector data (it looks like the above if you print it), I then hand it over to the requests library to do a POST to an ORDS REST API.

The code – feel free to laugh, I’m only mildly aware of how it actually works, is here for your perusal. I do appreciate that Grok comments and explains the code.

Python
from PIL import Image
from transformers import CLIPProcessor, CLIPModel
import torch
import numpy as np
import http.client
import sys

# Load the CLIP model and processor
model = CLIPModel.from_pretrained("openai/clip-vit-base-patch32")
processor = CLIPProcessor.from_pretrained("openai/clip-vit-base-patch32")

# Move model to GPU if available
device = torch.device("cuda" if torch.cuda.is_available() else "cpu")
model = model.to(device)

filename = sys.argv[1]

# Load and preprocess the image
try:
    image = Image.open(filename).convert("RGB")  # Ensure image is in RGB mode
except FileNotFoundError:
    print("Error: The file not found.")
    exit(1)
except Exception as e:
    print(f"An error occurred while opening the image: {e}")
    exit(1)

inputs = processor(images=image, return_tensors="pt").to(device)

# Generate the image embedding
with torch.no_grad():  # Disable gradient calculation for efficiency
    image_features = model.get_image_features(**inputs)

# Convert to numpy array, detach from computation graph, and move to CPU
vector = image_features.detach().cpu().numpy().squeeze()  # .squeeze() removes any singleton dimensions
vector_str = '[' + ','.join(map(str, vector)) + ']'

#print("Shape of vector:", vector.shape)
#print("Vector:", vector_str)

conn = http.client.HTTPConnection("localhost:8181")
payload = vector_str

headers = {
     'Content-Type' : "text/plain",
     'file' : filename
    }


conn.request("POST", "/ords/hr/vectors/pics/", payload, headers)
res = conn.getresponse()
data = res.read()
print(data.decode("utf-8"))

So when the vector is created from processing the input file, I need to send it to my database. And I’m going to do that via a REST call. So my python app doesn’t need to import the (very awesome) Oracle python driver, nor do I need to make or sort a database connection with database credentials.

The REST API is really, really simple –

It”s a POST handler with a PL/SQL block of code. I’m not doing any input validation, nor am I doing any exception handling. I’ve demonstrated how to do that. And, I’ve shown how to properly secure this with OAuth2 clients for your python scripts.

But this is all local to my machine, and I was pressed for time, so as long as my kids don’t hop on my machine, it’s pretty safe.

The only ‘trick’ is I’m processing the body of the request as a CLOB, and I’m doing that by sending to the INSERT the bind variable, :body_text (Docs).

The PL/SQL block for the POST handler:

PLSQL
BEGIN
    insert into PICS_AND_VECTORS (file_name, victor)
       values (:fn, :body_text);
    :res := 'Vector for image file, ' || :fn || ', added!';
end;

If we scooch down to where my python code does the API call, we can observe the setup of the request headers and the content of the POST request.

What I would like to have my python app do is process the image to get the vector, upload that AND the image itself, along with the file name, in a single request…and then do that for every file in the directory I supply.

This wasn’t all that tricky, except I had to play with the output from the model.get_image_features() function call to look like a vector – that’s why you see the string concatenation call right after that, which is also used to define the POST request PAYLOAD.

💡 Using a REST API to load a VECTOR into the Oracle Database is super simple, you’re just doing an INSERT.

Calling my program

Like I said, I was running out of daylight and I wanted to finish off this post, so instead of buckling down and coding the loop and more complicated API, I just called it 4 times. And then I manually uploaded the 4 images to my table.

If I were processing 4,000 files, you bet your butt I would have taken the time to do that. I’m going to guess you didn’t come here to learn python from me, so I’m ok with that, for now.

Looking at the table data

The pictures are all of my dog, and in some of them, I’m also drinking a beer.

Comparing/finding similar pictures

In some of the pictures it’s just my dog, and in the others, it’s my dog plus some beer. To do the vector comparisons effectively we’ll want to have an INDEX.

I’m doing the same type I did before, and I’m borrowing what other great folks at Oracle have demonstrated. Read up more on Vector indexes.

SQL
CREATE VECTOR INDEX pics_hnsw_idx ON pics_and_vectors (victor) ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Then I need a PL/SQL function I can use, to make it VERY easy to pass an existing VECTOR to a SQL statement –

PLSQL
create or replace function kareem2 (input_id in integer) return vector is
 roger vector;
begin
 select victor into roger
 from pics_and_vectors
 where id = input_id;
 return roger;
end;

I pass in an ID for a row in my table, it simply returns the VECTOR in that same row.

Now I can run the query –

SQL
select file_name,
       content
  from pics_and_vectors
 where id <> 4
 order by vector_distance(
   victor,
   kareem2(4),
   cosine
)
 fetch approximate first 2 rows only;

And the winning two pictures, are…

Yes, I will use my cute dog to help sell softwares, and no I do not feel guilty about it.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment