Azure SQL – Page 2 – 💡Tech News & Insights

Interactive Data Analysis: Chat with Your Data in Azure SQL Database Using Vanna AI

In an era where data is the new gold, the ability to effectively mine, understand, and utilize this valuable resource determines the success of businesses. Traditional data analysis methods often create a bottleneck due to their complexity and the need for specialized skills. This is where the groundbreaking integration of Vanna AI with Azure SQL Database heralds a new dawn. Inspired by the pivotal study “AI SQL Accuracy: Testing different LLMs + context strategies to maximize SQL generation accuracy,” this article explores how Vanna AI is not just an innovation but a revolution in data analytics. It simplifies complex data queries into conversational language, making data analysis accessible to all, irrespective of their technical prowess.

Understanding Vanna AI: The Next Frontier in Data Analytics

Vanna AI emerges as a pivotal innovation in the rapidly evolving landscape of artificial intelligence and data management. But what exactly is Vanna AI, and why is it becoming a game-changer in data analytics? Let’s delve into the essence of Vanna AI and its transformative impact.

What is Vanna AI?

Vanna AI is an advanced AI-driven tool designed to bridge the gap between complex data analysis and user-friendly interaction. At its core, Vanna AI is a sophisticated application of Large Language Models (LLMs) optimized for interacting with databases. It leverages the power of AI to translate natural language queries into precise SQL commands, effectively allowing users to “converse” with their databases.

Key Features and Capabilities

Natural Language Processing (NLP): Vanna AI excels at understanding and processing human language, enabling users to ask questions in plain English and receive accurate data insights.
Contextual Awareness: One of the standout features of Vanna AI is its ability to understand a specific database’s structure and nuances contextually. This includes schema definitions, documentation, and historical queries, significantly enhancing the accuracy of SQL generation.
Adaptability Across Databases: Vanna AI is not limited to a single type of database. Its versatility allows it to be integrated with various database platforms, including Azure SQL Database, enhancing its applicability across different business environments.
Ease of Use: By simplifying the process of data querying, Vanna AI democratizes data analysis, making it accessible to non-technical users, such as business analysts, marketing professionals, and decision-makers.

How Vanna works

Vanna works in two easy steps – train a RAG “model” on your data and then ask questions that will return SQL queries that can be set up to run on your database automatically.

vn.train(...): Train a RAG “model” on your data. These methods add to the reference corpus below.
vn.ask(...): Ask questions. This will use the reference corpus to generate SQL queries that can be run on your database.

Empowering SQL Generation with AI

The challenge in traditional data analysis has been the necessity of SQL expertise. Vanna AI disrupts this norm by enabling users to frame queries in plain language and translate them into SQL. This approach democratizes data access and accelerates decision-making by providing quicker insights.

The research compared the efficacy of various Large Language Models (LLMs) like Google Bison, GPT 3.5, GPT 4 turbo, and Llama 2 in generating SQL. While GPT 4 excelled overall performance, the study highlighted that other LLMs could achieve comparable accuracy with the proper context.

Presenting the Practical Application I Developed for Your Evaluation.

A testament to Vanna AI’s practical application, I created an example app that you can test yourself and understand how it works, an innovative application designed for the Microsoft Adventure Works database. Available at this URL. This application exemplifies how AI can transform data interaction. It allows users to converse with the Adventure Works database in natural language, simplifying complex data queries and making data analysis more approachable and efficient.

Exploring the AdventureWorksLT Schema: An Overview of Database Relationships and Structure

Here is a concise introduction to the Adventure Work database. This will help you better understand the database structures and tables, enabling you to make more effective inquiries in the test application I developed.

In the Dbo schema, there is an ErrorLog table designed to capture error information, with fields such as ErrorTime, UserName, and ErrorMessage. The CustomerAddress table bridges customers to addresses, suggesting a many-to-many relationship as one customer can have multiple addresses, and one address can be associated with multiple customers.

The SalesLT schema is more complex and includes several interconnected tables:

Product: Contains product details, such as name, product number, color, and size.
ProductCategory: Organizes products into hierarchical categories.
ProductModel: Defines models for products, which could include multiple products under a single model.
ProductModelProductDescription: This link between product models and their descriptions indicates a many-to-many relationship between models and descriptions facilitated by a culture identifier.
ProductDescription: Stores descriptions for products in different languages (indicated by the Culture field).
Address: Holds address information and is related to customers through the CustomerAddress table.
Customer: Holds customer information such as name, contact details, and password hashes for customer accounts.
SalesOrderHeader: Captures the header information of sales orders, including details like order date, due date, and total due amount.
SalesOrderDetail: Provides line item details for each sales order, such as quantity and price.

The schema includes primary keys (PK) to uniquely identify each entry in a table, foreign keys (FK) to establish relationships between tables, and indexes (U1, U2) to improve query performance on the database.

Explore the Source Codes of the app I developed.

To develop an app yourself using the Azure SQL database, click this li n k to access my GitHub repository containing all source codes.

Conclusion

As we stand at the cusp of a data revolution, Vanna AI’s integration with Azure SQL Database and its practical embodiment in applications like the app I created, for example, for the Microsoft Adventure Works database, represents more than technological advancement; they signify a paradigm shift in data interaction and analysis. This evolution marks the transition from data being experts’ exclusive domain to becoming a universal language understood and utilized across various business sectors. The journey of data analytics, powered by AI and made user-friendly through Vanna AI, is not just about technological transformation; it’s about empowering organizations and individuals with the tools to unlock the true potential of their data. Stay connected with the evolving world of Vanna AI and discover how this revolutionary tool can redefine your approach to data, paving the way for a more informed, efficient, and data-driven future.

That’s it for today!

Vanna.AI – Personalized AI SQL Agent

Navigating Vector Operations in Azure SQL for Better Data Insights: A Guide How to Use Generative AI to Prompt Queries in Datasets

The evolving landscape of data analytics has brought vector databases to the forefront, especially with their application in finding similarities in diverse data types such as articles, photos, and products. Azure SQL, combined with the prowess of OpenAI, offers a powerful platform for executing vector operations, simplifying the task of finding similar items and enhancing recommendation systems in applications.

What is Vector Similarity, and How do you calculate cosine similarity?

Vector similarity revolves around transforming data into numerical vectors or embeddings. These embeddings are numerical representations of various concepts converted into sequences of numbers, making it easier for computers to grasp their relationships. This method is particularly effective in comparing and finding similarities between data points, a crucial feature in applications like search engines and clustering algorithms.

Cosine similarity, a commonly used metric in vector similarity, measures the cosine of the angle between two vectors. This metric is crucial in determining the degree of similarity between the vectors, irrespective of their size. In Azure SQL, cosine similarity can be computed with a simple SQL formula involving SUM and SQRT functions applied to the vector elements, thus providing a straightforward yet powerful way to measure vector similarity.

Cosine similarity can be calculated in SQL using the following formula, given two vectors, a and b:

SELECT 
    SUM(a.value * b.value) / (  
        SQRT(SUM(a.value * a.value)) * SQRT(SUM(b.value * b.value))   
    ) AS cosine_similarity
FROM
    vectors_values

How to Querying Azure OpenAI?

Azure SQL’s integration with Azure OpenAI simplifies generating and working with embeddings. Users can obtain the vector representation of any given text by creating an Azure OpenAI resource and deploying a model like text-embedding-ada-002. This integration enables the execution of REST API calls from within the Azure SQL Database, making fetching and working with embeddings more streamlined and accessible.

SQL

-- Declare a variable to hold the response from the external REST API
declare @response nvarchar(max);

-- Declare and initialize a variable with JSON payload. 
-- The JSON object contains an 'input' key with a text value.
declare @payload nvarchar(max) = json_object('input': 'This is how to futureproof your career in an AI world');

-- Execute a stored procedure to invoke an external REST endpoint
exec sp_invoke_external_rest_endpoint
    @url = 'https://<your-app-name>.openai.azure.com/openai/deployments/embeddings/embeddings?api-version=2023-03-15-preview', -- The URL of the REST endpoint
    @credential = [https://<your-app-name>.openai.azure.com], -- Credential for accessing the REST API
    @payload = @payload, -- The JSON payload defined earlier
    @response = @response output; -- Output parameter to store the response from the API

Advantages of Implementing Vectors in Azure SQL Database

1. Ease of Access with Azure OpenAI: Azure SQL combined with Azure OpenAI offers easy access to REST services for generating embeddings using pre-trained machine learning models. This accessibility facilitates the calculation of embeddings, which is otherwise a complex task.

2. Efficient Storage with Columnstore Indexes: Azure SQL databases efficiently store vectors using column store indexes. This method is particularly beneficial since Azure SQL doesn’t have a specific vector data type. Vectors, essentially lists of numbers, can be conveniently stored in a table with one row per vector element.

3. Fast Distance Calculation: The internal optimization of column store indexes in Azure SQL, employing SIMD and AVX-512 instructions, allows for high-speed calculation of distances between vectors, which is crucial for determining similarity.

4. Integration with Azure AI Search: Azure SQL’s integration with Azure AI Search streamlines the entire process of chunking, generating, storing, and querying vectors for vector search, significantly speeding up the development of the vectorization pipeline and minimizing maintenance tasks.

5. Capability for Complex Operations: Azure SQL enables complex operations like indexing, storing, and retrieving vector embeddings from a search index, which is essential for identifying the most similar documents in a vector space.

6. Versatile Data Handling: Azure SQL’s ability to handle structured and unstructured data, along with vector data, provides more versatility compared to vector databases, which are primarily optimized for vector data storage and retrieval.

7. Ease of Querying and Relevance Determination: The integration with Azure OpenAI allows easy querying of the REST service to obtain vector representations of text, which can then be used to calculate similarity against stored vectors, identifying the most relevant data.

8. Simplified Deployment and Management: Deploying and managing an embedding model via the Azure portal is straightforward, reducing the complexity of managing vector databases.

9. Suitability for a Range of Applications: While vector databases are specialized for high-dimensional similarity searches, Azure SQL’s broader application scope makes it suitable for various types of data and applications, from financial records to customer data.

10. Support for Advanced Azure Features: Azure SQL supports advanced Azure features, such as AI and machine learning capabilities, which can be seamlessly integrated with vector similarity operations for enhanced analytics and insights.

These advantages highlight the flexibility, efficiency, and ease of use of Azure SQL databases for vector similarity operations, making them a preferable choice in scenarios where diverse data types and complex operations are involved, alongside the need for seamless integration with other Azure services.

Implementing Vectors in Azure SQL Database

Azure SQL Database provides a seamless way to store and manage vector data despite not having a specific vector data type. Column-store indexes, vectors, and essentially lists of numbers can be efficiently stored in a table. Each vector can be represented in a row with individual elements as columns or serialized arrays. This approach ensures efficient storage and retrieval, making Azure SQL suitable for large-scale vector data management.

I used the Global News Dataset from Kaggle in my experiment.

First, you must create the columns to save the vector information. In my case, I created two columns: title_vector For the news title and content_vector the news content. For this, create a small Python code, but you can also do that directly from SQL using a cursor. It's important to know that you don't need to pay for any Vector Databases by saving the vector information inside the Azure SQL.

Python

import openai
import pyodbc  # or another SQL connection library

# Set up OpenAI credentials
openai.api_type = "azure"
openai.api_key = "<YOUR AZURE OPEN AI KEY>"
openai.api_base = "https://<your-app-name>.openai.azure.com/"
openai.api_version = "2023-07-01-preview"

# Connect to your Azure SQL database
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=<SERVER>;DATABASE=<DATABASE>;'
                      'UID=<USER>;PWD=<PASSWORD>')

def get_embeddings(text):
    # Truncate the text to 8000 characters
    truncated_text = text[:8000]

    response = openai.Embedding.create(input=truncated_text, engine="embeddings")
    embeddings = response['data'][0]['embedding']
    return embeddings

def update_database(article_id, title_vector, content_vector):
    cursor = conn.cursor()

    # Convert vectors to strings
    title_vector_str = str(title_vector)
    content_vector_str = str(content_vector)

    # Update the SQL query to use the string representations
    cursor.execute("""
        UPDATE news
        SET title_vector = ?, content_vector = ?
        WHERE article_id = ?
    """, (title_vector_str, content_vector_str, article_id))
    conn.commit()

def embed_and_update():
    cursor = conn.cursor()
    cursor.execute("SELECT article_id, title, full_content FROM news where title_vector is null and full_content is not null and title is not null order by published desc")
    
    title_vector = ""
    content_vector = ""
    
    for row in cursor.fetchall():
        article_id, title, full_content = row
        
        print(f"Embedding article {article_id} - {title}")
        
        title_vector = get_embeddings(title)
        content_vector = get_embeddings(full_content)
        
        update_database(article_id, title_vector, content_vector)

embed_and_update()

These two columns will contain something like this: [-0.02232750505208969, -0.03755787014961243, -0.0066827102564275265…]

Second, you must extract these vectors line by line into a new table for each vector field. In this case, I created a procedure to do it.

SQL

-- =============================================
-- Author:      Lawrence Teixeira
-- Create Date: 11-24-2023
-- Description: This stored procedure creates vectors for news titles and contents.
-- It processes data from the 'news' table and stores the vectors in separate tables.
-- =============================================

CREATE PROCEDURE [dbo].[Create_News_Vector]
AS
BEGIN
    SET NOCOUNT ON; -- Prevents the sending of DONE_IN_PROC messages to the client.

    -- Extract and store title vectors:
    -- First, check if the 'news_titles_vector' table exists and drop it if it does.
    IF OBJECT_ID('dbo.news_titles_vector', 'U') IS NOT NULL
        DROP TABLE dbo.news_titles_vector;

    -- Using a Common Table Expression (CTE) to process title vectors.
    WITH cte AS
    (
        SELECT 
            v.article_id,    
            CAST(tv.[key] AS INT) AS vector_value_id, -- Casting 'key' as INT for vector ID.
            CAST(tv.[value] AS FLOAT) AS vector_value   -- Casting 'value' as FLOAT for vector value.
        FROM 
            dbo.news AS v 
        CROSS APPLY 
            OPENJSON(v.title_vector) tv -- Parses JSON of title_vector in the 'news' table.
    )
    -- Create 'news_titles_vector' table with processed vectors.
    SELECT
        article_id,
        vector_value_id,
        vector_value
    INTO
        dbo.news_titles_vector
    FROM
        cte;

    -- Extract and store content vectors:
    -- Check and drop 'news_contents_vector' table if it exists.
    IF OBJECT_ID('dbo.news_contents_vector', 'U') IS NOT NULL
        DROP TABLE dbo.news_contents_vector;

    -- CTE for processing content vectors.
    WITH cte AS
    (
        SELECT 
            v.article_id,    
            CAST(tv.[key] AS INT) AS vector_value_id, -- Casting 'key' as INT for vector ID.
            CAST(tv.[value] AS FLOAT) AS vector_value   -- Casting 'value' as FLOAT for vector value.
        FROM 
            dbo.news AS v 
        CROSS APPLY 
            OPENJSON(v.content_vector) tv -- Parses JSON of content_vector in the 'news' table.
    )
    -- Create 'news_contents_vector' table with processed vectors.
    SELECT
        article_id,
        vector_value_id,
        vector_value
    INTO
        dbo.news_contents_vector
    FROM
        cte;

    -- Columnstore indexes creation is advised outside the stored procedure.
    -- These indexes optimize data storage and query performance on vector tables.
    CREATE CLUSTERED COLUMNSTORE INDEX cci_news_titles_vector
    ON dbo.news_titles_vector 
	order (article_id);

    CREATE CLUSTERED COLUMNSTORE INDEX cci_news_contents_vector
    ON dbo.news_contents_vector
	order (article_id);
END

I also create a function to search directly to the dataset using the Cosine similarity formula.

SQL

-- This Azure SQL function finds news articles similar to the given content vector.
CREATE function [dbo].[SimilarNewsContentArticles](
    @vector nvarchar(max) -- Input parameter: JSON string representing a content vector.
)
returns table -- The function returns a table.
as
return with 

-- CTE for processing the input vector.
cteVector as
(
    select 
        cast([key] as int) as [vector_value_id], -- Extracts and casts the 'key' from JSON to int.
        cast([value] as float) as [vector_value] -- Extracts and casts the 'value' from JSON to float.
    from 
        openjson(@vector) -- Parses the input JSON vector.
),

-- CTE for calculating similarity scores with existing articles.
cteSimilar as
(
    select top (50)
        v2.article_id, 
        sum(v1.[vector_value] * v2.[vector_value]) as cosine_distance 
        -- Calculates cosine similarity (distance) between vectors.
    from 
        cteVector v1 -- Uses the processed input vector.
    inner join 
        dbo.news_contents_vector v2 
        on v1.vector_value_id = v2.vector_value_id -- Joins with stored article vectors.
    group by
        v2.article_id
    order by
        cosine_distance desc -- Orders by similarity score, descending.
)

-- Final selection combining article data with similarity scores.
select 
    a.*, -- Selects all columns from the news article.
    r.cosine_distance -- Includes the calculated similarity score.
from 
    cteSimilar r -- Uses the similarity scores CTE.
inner join 
    dbo.news a on r.article_id = a.article_id -- Joins with the news articles table.
GO

Finally, you can start querying your table using prompts instead of keywords. This is awesome!

Check out the app I developed, which is designed to assist you in crafting prompts and evaluating your performance using my news dataset. To explore the app, click here.

I also created this GitHub repository with everything I did.

Click on this image to open the GitHub repository.

Conclusion

While the sample in this blog is not optimized for maximum efficiency, it is an excellent starting point for understanding and implementing vector operations in Azure SQL. The process, despite its simplicity, is relatively fast. For instance, querying an eight vCore Azure SQL database can return the fifty most similar articles in just half a second, demonstrating the efficiency and utility of vector operations in Azure SQL Database for data analytics and insights. If you want to know more about this topic, don’t hesitate to get in touch with me.

That’s it for today!

Sources:

Azure-Samples/azure-sql-db-openai: Samples on how to use Azure SQL database with Azure OpenAI (github.com)