Tag: AzureSQL

Embedding text using a local model can provide significant cost advantages and flexibility over cloud-based services. In this blog post, we explore how to set up and use a local model for text embedding and how this approach can be integrated with Azure SQL databases for advanced querying capabilities.

Cost Comparison: Open AI text-embedding-ada-002 pay Model vs. Local Model Setup Cost

When choosing between a paid service and setting up a local model for text embedding, it’s crucial to consider the cost implications based on the scale of your data and the frequency of usage. Below is a detailed comparison of the costs of using a paid model versus establishing a local one.

Pay Model Cost Estimate:

Open AI text-embedding-ada-002:

Using a paid model like OpenAI’s Ada V2 for embedding 1 terabyte of OCR texts would cost around $25,000. This estimation is based on converting every 4 characters into one token, which might vary depending on the content and structure of the OCR texts.

Local Model Cost Estimate:

Setup Costs:

The initial investment for setting up a local model can range from $4,050 to $12,750, depending on the selection of components, from mid-range to high-end. This one-time cost can be amortized over many uses and datasets, potentially offering a more cost-effective solution in the long run, especially for large data volumes.

Overall Financial Implications

While the upfront cost for a local model might seem high, it becomes significantly more economical with increased data volumes and repeated use. In contrast, the cost of using a pay model like OpenAI’s text-embedding-ada-002 scales linearly with data volume, leading to potentially high ongoing expenses.

Considering these factors, the local model offers a cost advantage and greater control over data processing and security, making it an attractive option for organizations handling large quantities of sensitive data.

Why I Have Decided to Use a Local Model?

Cost and data volume considerations primarily drove the decision to use a local model for text embedding. With over 20 terabytes of data, including 1 terabyte of OCR text to embed, the estimated cost of using a commercial text-embedding model like OpenAI’s text-embedding-ada-002 would be around USD 25,000. By setting up a local model, we can process our data at a fraction of this cost, reducing expenses by 49% to 84%.

Exploring Local Models: Testing BGE-M3, MXBAI-EMBED-LARGE, NOMIC-EMBED-TEXT, and text-embedding-ada-002 from Open AI.

I encountered some intriguing results in my recent tests with local embedding models BGE-M3 and NOMIC-EMBED-TEXT. Both models showed an accuracy below 0.80 when benchmarked against OpenAI’s “Text-embedding-ada-002.” This comparison has sparked a valuable discussion about the capabilities and limitations of different embedding technologies.

How to Choose the Best Model for Your Needs?

When considering open-source embedding models like NOMIC-EMBED-TEXT, BGE-M3, and MXBAI-EMBED-LARGE, specific strengths and applications that make them suitable for various machine learning tasks should be considered.

1. NOMIC-EMBED-TEXT: This model is specifically designed for handling long-context text, making it suitable for tasks that involve processing extensive documents or content that benefits from understanding broader contexts. It achieves this by training on full Wikipedia articles and various large-scale question-answering datasets, which helps it capture long-range dependencies.

2. BGE-M3: Part of the BGE (Beijing Academy of Artificial Intelligence) series, this model is adapted for sentence similarity tasks. It’s built to handle multilingual content effectively, which makes it a versatile choice for applications requiring understanding or comparing sentences across different languages.

3. MXBAI-EMBED-LARGE: This model is noted for its feature extraction capabilities, making it particularly useful for tasks that require distilling complex data into simpler, meaningful representations. Its training involves diverse datasets, enhancing its generalization across text types and contexts.

Each model brings unique capabilities, such as handling longer texts or providing robust multilingual support. When choosing among these models, consider the specific needs of your project, such as the length of text you need to process, the importance of multilingual capabilities, and the type of machine learning tasks you aim to perform (e.g., text similarity, feature extraction). Testing them with specific data is crucial to determine which model performs best in your context.

In our analysis, we’ve compared various results and identified the best open-source model to use compared to the OpenAI’s Text-embedding-ada-002.

We executed this query using the keyword ‘Microsoft’ to search the vector table and compare the content of Wikipedia articles.

declare @v nvarchar(max)
select @v = content_vector from dbo.wikipedia_articles_embeddings where title = 'Microsoft'
select w.title, w.text from 
(select top (10) id, title, text, dot_product
from [$vector].find_similar$wikipedia_articles_embeddings$content_vector(@v, 1, 0.25) 
order by dot_product desc) w
order by w.title
go

We utilized the KMeans compute node for text similarity analysis, focusing on a single cluster search. For a detailed, step-by-step guide on creating this dataset, please refer to the article I shared at the end of this article.

Follow the results overview:

To calculate the percentage of similarity of each model with “Text-embedding-ada-002”, we’ll determine how many keywords match between “Text-embedding-ada-002” and the other models, then express this as a percentage of the total keywords in “Text-embedding-ada-002”. Here’s the updated table with the percentages:

Follow the comparison table:

1. Text-embedding-ada-002 Keywords Total: 10 (100% is based on these keywords).
2. Matching Keywords:
      – BGE-M3: Matches 7 out of 10 keywords of Text-embedding-ada-002.
      – NOMIC-EMBED-TEXT: Matches 3 out of 10 keywords of Text-embedding-ada-002.
      – MXBAI-EMBED-LARGE: Matches 1 out of 10 keywords of Text-embedding-ada-002.

This table illustrates that the BGE-M3 model is similar to “Text-embedding-ada-002,” with 70% of the keywords matching. It is followed by “NOMIC-EMBED-TEXT” at 30% and “MXBAI-EMBED-LARGE,” with the least similarity at 10%.

How does it perform when doing an approximate search with 1, 4, 8, and 16 clusters?

We execute this query within the Azure database to perform this test across each database and model we use:

create table #trab ( linha varchar( 200) null )

insert into #trab (linha) values ('Model: mxbai-embed-large')

declare @v nvarchar(max)
select @v = content_vector from dbo.wikipedia_articles_embeddings where title = 'Microsoft'

insert into #trab (linha) values ('')
insert into #trab (linha) values ('Search with 1 cluster')

insert into #trab (linha)
select w.title from 
(select top (10) id, title, text, dot_product
from [$vector].find_similar$wikipedia_articles_embeddings$content_vector(@v, 1, 0.25) 
order by dot_product desc) w
order by w.title
go

declare @v nvarchar(max)
select @v = content_vector from dbo.wikipedia_articles_embeddings where title = 'Microsoft'

insert into #trab (linha) values ('')
insert into #trab (linha) values ('Search with 4 clusters')

insert into #trab (linha)
select w.title from 
(select top (10) id, title, text, dot_product
from [$vector].find_similar$wikipedia_articles_embeddings$content_vector(@v, 4, 0.25) 
order by dot_product desc) w
order by w.title
go

declare @v nvarchar(max)
select @v = content_vector from dbo.wikipedia_articles_embeddings where title = 'Microsoft'

insert into #trab (linha) values ('')
insert into #trab (linha) values ('Search with 8 clusters')

insert into #trab (linha)
select w.title from 
(select top (10) id, title, text, dot_product
from [$vector].find_similar$wikipedia_articles_embeddings$content_vector(@v, 8, 0.25) 
order by dot_product desc) w
order by w.title
go

declare @v nvarchar(max)
select @v = content_vector from dbo.wikipedia_articles_embeddings where title = 'Microsoft'

insert into #trab (linha) values ('')
insert into #trab (linha) values ('Search with 16 clusters')

insert into #trab (linha)
select w.title from 
(select top (10) id, title, text, dot_product
from [$vector].find_similar$wikipedia_articles_embeddings$content_vector(@v, 16, 0.25) 
order by dot_product desc) w
order by w.title
go

select * from #trab

drop table #trab

Follow the results overview:

Based on the previous detailed list, here are the calculations for the percentage of similarity:

1. Total Distinct Keywords in Text-embedding-ada-002: 10 (100% based on these keywords)

2. Keywords in each Cluster Search:

   – BGE-M3: 5 keywords (Microsoft, Microsoft Office, Microsoft Windows, Microsoft Word, MSN)

   – NOMIC-EMBED-TEXT: 4 keywords (Microsoft, MSN, Nokia, Outlook.com)

   – MXBAI-EMBED-LARGE: 2 keywords (Microsoft, Nokia)

Here’s the updated table with the percentage similarity for searches with 1, 4, 8, and 16 clusters:

This table shows the similarity percentages for each model across different cluster configurations compared to the “text-embedding-ada-002” model. Each model retains a consistent similarity percentage across all cluster numbers, indicating that the cluster configuration did not affect the keywords searched for in these cases.

To execute the Python code to embed the vectors, first, you have to install Ollama

How Did You Set Up a Local Model Using Ollama?

To run an Ollama model with your GPU, you can use the official Docker image provided by Ollama. The Docker image supports Nvidia GPUs and can be installed using the NVIDIA Container Toolkit. Here are the steps to get started:

1. Install Docker: Download and install Docker Desktop or Docker Engine, depending on your operating system.
2. Select and Pull the Ollama Model: Choose a preferred model from the Ollama library, such as nomic-embed-text or mxbai-embed-large, and pull it using the following command: docker pull ollama/ollama..
3. Run the Ollama Docker Image: Execute Docker run commands to set up the Ollama container. You can configure it specifically for either CPU or Nvidia GPU environments. Run the Docker container with the following command: docker run -d --gpus=all -v ollama:/root/.ollama -p 11434:11434 --name ollama ollama/ollama.
4. You can now run the Ollama model using the following command: docker exec -it ollama ollama run nomic-embed-text  or docker exec -it ollama ollama run mxbai-embed-large .
5. Access and Use the Model: To start interacting with your model, utilize the Ollama WebUI by navigating to the local address provided (typically http://localhost:11434).

Please note that the above commands assume you have already installed Docker on your system. If you haven’t installed Docker yet, you can download it from the official Docker website.

You can also download and install Ollama on Windows:

How do you convert text into embedding using the local model using Ollama?

After setting up your local model with Ollama, you can use the following Python script to convert text into embeddings:

# Importing necessary libraries and modules
import os
import pyodbc  # SQL connection library for Microsoft databases
import requests  # For making HTTP requests
from dotenv import load_dotenv  # To load environment variables from a .env file
import numpy as np  # Library for numerical operations
from sklearn.preprocessing import normalize  # For normalizing data
import json  # For handling JSON data
from db.utils import NpEncoder  # Custom JSON encoder for numpy data types

# Load environment variables from a .env file located in the same directory
load_dotenv()

# This is the connection string for connecting to the Azure SQL database we are getting from the environment variables
#MSSQL='Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=<DATABASE NAME>;Uid=<USER>;Pwd=<PASSWOPRD>;Encrypt=No;Connection Timeout=30;'

# Retrieve the database connection string from environment variables
dbconnectstring = os.getenv('MSSQL')

# Establish a connection to the Azure SQL database using the connection string
conn = pyodbc.connect(dbconnectstring)

def get_embedding(text, model):
    # Prepare the input text by truncating it or preprocessing if needed
    truncated_text = text

    # Make an HTTP POST request to a local server API to get embeddings for the input text
    res = requests.post(url='http://localhost:11434/api/embeddings',
                        json={
                            'model': model, 
                            'prompt': truncated_text
                        }
    )
    
    # Extract the embedding from the JSON response
    embeddings = res.json()['embedding']
    
    # Convert the embedding list to a numpy array
    embeddings = np.array(embeddings)    
    
    # Normalize the embeddings array to unit length
    nc = normalize([embeddings])
        
    # Convert the numpy array back to JSON string using a custom encoder that handles numpy types
    return json.dumps(nc[0], cls=NpEncoder )

def update_database(id, title_vector, content_vector):
    # Obtain a new cursor from the database connection
    cursor = conn.cursor()

    # Convert numpy array embeddings to string representations for storing in SQL
    title_vector_str = str(title_vector)
    content_vector_str = str(content_vector)

    # SQL query to update the embeddings in the database
    cursor.execute("""
        UPDATE wikipedia_articles_embeddings
        SET title_vector = ?, content_vector = ?
        WHERE id = ?
    """, (title_vector_str, content_vector_str, id))
    conn.commit()  # Commit the transaction to the database

def embed_and_update(model):
    # Get a cursor from the database connection
    cursor = conn.cursor()
    
    # Retrieve articles from the database that need their embeddings updated
    cursor.execute("select id, title, text from wikipedia_articles_embeddings where title_vector = '' or content_vector = '' order by id desc")
    
    for row in cursor.fetchall():
        id, title, text = row
        
        # Get embeddings for title and text
        title_vector = get_embedding(title, model)
        content_vector = get_embedding(text, model)
        
        # Print the progress with length of the generated embeddings
        print(f"Embedding article {id} - {title}", "len:", len(title_vector), len(content_vector))
        
        # Update the database with new embeddings
        update_database(id, title_vector, content_vector)

# Call the function to update embeddings using the 'nomic-embed-text' model
embed_and_update('nomic-embed-text')

# To use another model, uncomment and call the function with the different model name
# embed_and_update('mxbai-embed-large')

I’ve also created a GitHub repository with these codes; you can access it at this link.

Download the pre-calculated embeddings using OpenAI’s text-embedding-ada-002

The pre-calculated embeddings with OpenAI’s text-embedding-ada-002, both for the title and the body, of a selection of Wikipedia articles, is made available by Open AI here:

https://cdn.openai.com/API/examples/data/vector_database_wikipedia_articles_embedded.zip

Once you have successfully embedded your text, I recommend exploring two of my blog posts that detail how to create a vector database for prompting and searching. These posts provide step-by-step guidance on utilizing Azure SQL alongside cosine similarity and KMeans algorithms for efficient and effective data retrieval.

Azure SQL Database now has native vector support

You can sign up for the private preview at this link.

This article, published by Davide Mauri and Pooja Kamath during this week’s Microsoft Build event, provides all the information.

Conclusion

Embedding text locally using models like Ollama presents a cost-effective, scalable solution for handling large volumes of data. By integrating these embeddings into Azure SQL databases, organizations can leverage generative AI to enhance their querying capabilities, making extracting meaningful insights from vast datasets easier. The outlined process ensures significant cost savings and enhances data security and processing efficiency.

This approach is a technical exercise and a strategic asset that can drive better decision-making and innovation across various data-intensive applications.

That’s it for today!

Sources

GitHub – Azure-Samples/azure-sql-db-vectors-kmeans: Use KMeans clustering to speed up vector search in Azure SQL DB

Vector Similarity Search with Azure SQL database and OpenAI | by Davide Mauri | Microsoft Azure | Medium

Ollama

How to Install and Run Ollama with Docker: A Beginner’s Guide – Collabnix

Leveraging KMeans Compute Node for Text Similarity Analysis through Vector Search in Azure SQL – Tech News & Insights (lawrence.eti.br)

Navigating Vector Operations in Azure SQL for Better Data Insights: A Guide How to Use Generative AI to Prompt Queries in Datasets – Tech News & Insights (lawrence.eti.br)

GitHub – LawrenceTeixeira/embedyourlocalmodel