Azure AI Search RAG and Vector Search for SQL

When dealing with searching for semantic meaning through data in your apps, Azure AI Search can come to the rescue. The idea is to be able to look at a source of data and then vectorise it with the help of an Embedding model (typically from AI model providers such as OpenAI). By vectorising, the data is being observed and then there is an array of numbers or vectors that numerically describe the data and therefore semantically understand what the data source means. We end up with a vector Index which we are able to efficiently query from, and hence get back the result that we can translate back to as an object of our original data.

Ultimately, apps can eventually realise an architecture that is representative of the following, where a user interacts with some kind of question and 'smart answer' system that is able to use the query and return results based on semantic understanding of the existing data automatically to then act on it in a generatively should we choose, unlike traditional 'keyword matching' search. This is what we call Retrieval Augmented Generation (although there is a further technique that combine both approaches called Reciprocal Rank Fusion but is not discussed here):

Let's proceed to see how we can start using a SQL Data table. Some pre-requisites:

• Azure OpenAI Service instance in Azure (preferably in East US 2)
• Azure AI Search instance in Azure
• An OpenAI developer key from OpenAI
• An Azure SQL Database instance in Azure (configured accessible to other Azure resources) with one data table (table and data example provided below)

Create Azure AI Search Index for SQL Data

Let's imagine we have an Azure SQL Database as our source data such as this that I have nicely generated for this post about bikers/cyclist in a lapped race, their kit, and their training regimen:

Which ends up ends up looking like this. This will serve as a our primary data in this post:

Next, create both an "Azure AI Search" resource AND an "Azure Open AI" resource preferably in East US 2 where OpenAI embedding models are available (I know!!! 😐, DO NOT USE 'Azure AI Service' for this demo).

The Azure Open AI resource overview will have a 'Go to Azure AI Foundry' link at the top of it which we will go to to open up Azure AI Foundry to explore the Azure Open AI resource further.

From Azure AI Foundry, search for and find an Embedding model by OpenAI called 'text-embedding-3-small' and Deploy this by clicking Deploy, as we will need the deployment name later:

Next, from our Azure AI Search resource, we can import our SQL data using the guides from the wizard under [Azure AI Search Instance>Import and Vectorize Data]:

Next, configure and sign in into your Azure SQL Database to read the table and columns

Next, choose a column to vectorise and thereby semantically understand. Your Azure OpenAI Service here must have an embedding model already deployed in Azure AI Foundry already:

Click create and then keep note of the vector Index name given and also the vectorised column/field that it will create (which is called "text_vector" in this case):

Using Vector search in Azure AI Search

So now, let's imagine a spectator in the crowd downloads our AI app to find out more about the cyclists in the race. We can allow them to just type almost anything they like that they find interesting about the cyclists in Search and we are able to augment our data retrieval with semantic understanding that carries a confidence score between 0 an 1.

We can perform a vector search query in code and get to use this in our apps, but in order to do so we must convert our search query into a large dimension vector (usually 1536 dimensions). To do this, we can make an API call to the OpenAI embedding model by passing in the search query:

using Azure;
using Microsoft.Extensions.AI;
using OpenAI;
using OpenAI.Embeddings;
using System.Text.Json;

//the name used here is the name shown at the top right of Azure AI Foundry 
//and also is your Azure OpenAI Service instance name 
var endpoint = new Uri("https://[azure_openai_service_instance_name].openai.azure.com/");

//the key used here MUST be your dev api Key direct from OpenAI's console 
var credential = new AzureKeyCredential("[OPEN_AI_DEV_KEY]");
var deploymentName = "[your_embedding_model_deployment_name_inAIFoundry]";

var client = new EmbeddingClient(deploymentName,credential);
//use an english search term here of your choice related to querying your data
OpenAIEmbeddingCollection response = client.GenerateEmbeddings(
    new List<string> { "bikers that have exercises that stretch their legs" }
);

var result = "";
foreach (var embedding in response)
{
    ReadOnlyMemory<float> vector = embedding.ToFloats();
    int length = vector.Length;
    for (int i = 0; i < length; i++)
    {
        result += vector.Span[i] + "f,";
    }
}

System.Console.Write(result);

For the above term ("bikers that have exercises that stretch their legs"), I have provided the full array here if you need to use:

Next using the very large array of floats we got above, we can query Azure AI Search using vector search, where we have chosen to perform the vector search on the vectorised column that Azure created for us called "text_vector":

Note that nuget packages may be in Preview/Pre-Release mode:

using Azure;
using Azure.Search.Documents;
using Azure.Search.Documents.Models;
using System;

namespace VectorDataSearch
{
    class Program
    {
        static async Task Main(string[] args)
        {
            string endpoint = "https://[your_AzureAISearch_Instance].search.windows.net";
            string indexName = "[your_vector_name_from_portal]";
            string apiKey = "[AzureAISearch_Instance_Key]"; 

            var client = new AzureVectorSearchClient(endpoint, indexName, apiKey);

            // Your embedding vector (from your embedding model)
            float[] queryVector = [array we got from above from embedding model call]

            // Perform vector search
            var searchResults = await client.VectorSearchAsync(
                queryVector,
                "text_vector", // The name of your vector field in the index
                5); // Get top 5 results

            // Process results
            await foreach (var result in searchResults.GetResultsAsync())
            {
                Console.WriteLine($"Score: {result.Score}");
                Console.WriteLine($"Document: {result.Document}");
                Console.WriteLine();
            }
        }
    }
}

The AzureVectorSearchClient helper class can be written as the following:

using Azure;
using Azure.Search.Documents;
using Azure.Search.Documents.Models;
using Azure.Identity;

public class AzureVectorSearchClient
{
    private readonly SearchClient _searchClient;

    public AzureVectorSearchClient(string endpoint, string indexName, string apiKey = null)
    {
       _searchClient = new SearchClient(
          new Uri(endpoint),
          indexName,
          new AzureKeyCredential(apiKey));
    }

    public async Task<SearchResults<SearchDocument>> VectorSearchAsync(
       ReadOnlyMemory<float> vectorQuery,
        string vectorFieldName,
        int k = 3,
        double minimumScore = 0.0)
    {
        // Create vector query
        var result = new VectorizedQuery(vectorQuery)
        {
            KNearestNeighborsCount = k,
            Fields = { vectorFieldName },
            Exhaustive = true
        };

        // Create search options
        var searchOptions = new SearchOptions
        {
            VectorSearch = new VectorSearchOptions
            {
                Queries = { result }
            },
            Size = k
        };

        // Execute the search
        return await _searchClient.SearchAsync<SearchDocument>("", searchOptions);
    }
}

Azure AI Search Vector Results 🚀

From the Search term "bikers that have exercises that stretch their legs" the Azure AI Search vector search returned a good 62% confidence score (as the top score result out of 5), where the cyclist for that top record is described in the 'Comment' column in our data as "Loves long endurance rides to build stamina" which is semantically and contextually relevant and reasonably accurate!!

Again, with fuzzy mock generative data we have, the results could could have been better but this would be a good confidence score.