Semantic Search with OpenAI Embedding Creation

In this notebook, we will demonstrate an example of conducting semantic search on SingleStoreDB with SQL! Unlike traditional keyword-based search methods, semantic search algorithms take into account the relationships between words and their meanings, enabling them to deliver more accurate and relevant results – even when search terms are vague or ambiguous.

SingleStoreDB’s built-in parallelization and Intel SIMD-based vector processing takes care of the heavy lifting involved in processing vector data. This allows your to run your ML algorithms right in your database extremely efficiently with just 2 lines of SQL!

In this example, we use Open AI embeddings API to create embeddings for our dataset and run semantic_search using dot_product vector matching function!

1. Create a workspace in your workspace group

S-00 is sufficient.

2. Create a Database named semantic_search

3. Install and import required libraries

We will use the OpenAI embeddings API and will need to import the relevant dependencies accordingly.

The hosted image already ships a recent openai SDK, so the cell below asks for a minimum version rather than an exact one. That distinction matters more than it looks: pinning an exact older release forces a downgrade of the SDK and of the HTTP libraries underneath it, leaving the kernel holding a mix of versions that no longer agree with one another. A floor pin installs the package when it is missing and otherwise does nothing at all.

4. Create an OpenAI account and get API connection details

To vectorize and embed the employee reviews and query strings, we leverage OpenAI's embeddings API. To use this API, you will need an API key, which you can get here. You'll need to add a payment method to actually get vector embeddings using the API, though the charges are minimal for a small example like we present here.

5. Create a new table in your database called reviews

The table starts out with nothing but the human-readable columns: when the review was written, the person's job title, where they were, and the review text itself. The vector column is not here yet — it gets added in step 7, once there is text to embed.

That ordering is deliberate, and it is the point of the whole notebook. The embedding does not become a separate object living somewhere else; it ends up as one more column on the same row as the text it describes. That is what makes the search in step 8 an ordinary SQL query rather than a conversation between two systems. review is a TEXT column because the reviews vary in length.

6. Import our sample data into your table

This dataset has 15 reviews left by anonymous employees of a firm.

The file is a plain list of SQL INSERT statements, so loading it means fetching it over HTTPS and running each non-empty line in turn. Fifteen rows is deliberately small: every one of them has to be sent to OpenAI to be embedded in step 7, so a bigger sample would cost more and teach exactly the same lesson.

Note that we are using the %sql magic command here to run a query against the currently selected database.

The doubled braces around {{query}} are how the magic command reads a Python variable: what actually gets executed is the text held in that variable, one INSERT statement per loop iteration.

7. Add vector embeddings for each review

First we add an embeddings column to hold the vectors, then we send the review text to OpenAI's embeddings API and store what comes back alongside each row.

Three details in the cell below are worth slowing down for:

All fifteen reviews go over in a single API call. get_embeddings accepts a list and returns a list, so this is one HTTPS round trip rather than fifteen. Batching this way is faster and cheaper, and it is the normal way to embed a column of text.

Each embedding is a list of 1536 floating point numbers that places the review in space so that distance corresponds to meaning. JSON_ARRAY_PACK converts that JSON array into the compact binary form stored in the BLOB column, which is what the distance functions read directly.

The vectors arrive already normalized to length 1. That is what allows step 8 to call DOT_PRODUCT and get cosine similarity for free, with no separate division by magnitude.

8. Run the semantic search algorithm with just one line of SQL

We will utilize SingleStoreDB's distributed architecture to efficiently compute the dot product of the input string (stored in searchstring) with each entry in the database and return the top 5 reviews with the highest dot product score. Each vector is normalized to length 1, hence the dot product function essentially computes the cosine similarity between two vectors – an appropriate nearness metric. SingleStoreDB makes this extremely fast because it compiles queries to machine code and runs dot_product using SIMD instructions.

It is worth noticing what does not happen here. The stored reviews are never pulled back into Python to be compared. The only thing embedded at query time is the search string you type. Comparing it against every stored vector, ranking the results and cutting the list down to five all happen where the data already lives, and only the five winning rows travel back over the network.

Conclusion

In nine steps you built a working semantic search engine on top of fifteen rows of free-text employee reviews. You created an ordinary table, loaded ordinary rows, called OpenAI once to turn every review into a 1536-dimensional embedding, stored each embedding as one more column on the row it belongs to, and then searched the whole set by meaning with a single SELECT.

The point of the exercise is what the last query did not need. There was no separate vector store to provision, no synchronization job keeping two copies of the same data in step, and no Python loop pulling rows back to compare them. The embedding lives beside the text it describes, so "find reviews that mean this" is expressed with the same ORDER BY ... LIMIT you would use to find the five most recent rows. Anything else you can do in SQL composes with it for free: filter by department before ranking, join to an employee table, aggregate the scores. That composability is the real payoff of keeping vectors in the database rather than next to it.

It stays fast for reasons that are worth naming:

• Queries are compiled, not interpreted. SingleStore turns a query into machine code and caches the compiled plan, so the per-row work of a DOT_PRODUCT scan is a tight loop rather than an interpreter walking a tree fifteen, or fifteen million, times.

• Vector math runs on SIMD instructions. A modern CPU can multiply and accumulate several floats per instruction, and DOT_PRODUCT is written to use that. Similarity scoring is exactly the shape of work vector units are built for.

• The scan is distributed and columnar. Rows are spread across partitions that score their own slice in parallel, and only the top matches from each are merged. Adding compute adds throughput, and the query text does not change.

• Nothing crosses the network but the answer. The comparison happens next to the stored bytes. At this scale that saves milliseconds; at millions of rows it is the difference between a query and a data export.

At fifteen rows every approach looks instant, so treat this notebook as the shape of the solution rather than a benchmark. When your corpus grows, the next step is a vector index: adding one lets SingleStore approximate the nearest neighbors instead of scoring every row, trading a small, measurable amount of recall for a much shorter scan. The SQL you wrote in step 8 stays essentially the same.

9. Clean up