Introduction

Leveraging OpenAI for semantic searches on structured databases like Azure SQL enhances search accuracy and context-awareness, providing more relevant and insightful results.

This example showcases the use of Azure SQL for storing and searching data with AI. By implementing the RAG pattern with Azure SQL, the sample efficiently searches for the most relevant code and comprehensive end-to-end examples based on user queries. The AI then generates results that include the most pertinent documents along with explanations of their relevance.

The final result is a web application, mobile friendly, which allows you to search for Samples stored in the database, by leveraging natural language to query Azure SQL. Hosted with Azure Static Web Apps, the application includes also Authentication with Entra ID and role assignments, which grant permissions to call authorized api hosted with Data API Builder.

The final demo of the solution is available here: https://ai.awesome.azuresql.dev/.

Architecture overview

The application described in this blog post is available on GitHub at the following address: https://github.com/yorek/azure-sql-db-ai-samples-search. You can fork the repository and follow the instructions to quickly run it on your own local environment, by leveraging swa cli, or you can deploy it in your Azure subscription. The example is leveraging “Azure SQL Samples” but you can easily modify the record structure to store any data that better fit your personal use case.

The components leveraged for this application are 5:

• React v18 with Typescript and Microsoft FluentUI components library
• Azure Static Web Apps for hosting
• Microsoft Data API Builder for exposing the REST endpoints
• Azure SQL Database
• Azure OpenAI

Cloud deployment

The following diagram illustrates how the application is deployed in Azure:

The workflow of Azure Static Web Apps is tailored to a developer’s daily workflow. Apps are built and deployed based on code changes.

When you create a static web app, Azure interacts directly with GitHub or Azure DevOps to monitor a branch of your choice. Every time you push commits or accept pull requests into the watched branch, a build automatically runs and your app and API deploys to Azure.

Within the application we have created an SQL Database project which setup an Azure SQL database, including mock data and configures the connectivity between Azure SQL instance and Azure Open AI HTTP endpoint.

Components interactions

The React application is composed by multiple Components. Each component has one or more Actions, configured with Redux store. An Action is used to call a REST endpoint, exposed by Data API Builder and the result is stored in the Redux state, which triggers a render of the User Interface.

For example, when the user enter a text in the search bar and press the button “Search”, the React component will call a Redux Action “Search results”. The Redux Action will call asynchronously the HTTP endpoint “http:// … data-api/search/” and retrieve a JSON object which represents the result. The results are then stored in the Redux State and the user interface is updated.

The RAG pattern is fully implemented in the Database. The model GPT-4o is used to generate the results with a well-defined structured format, so the results can be easily joined back with the samples data.

(Hybrid) RAG Pattern explained

In order to answer questions that could have either a precise (“return all the last 5 samples”) or a semantic (“return samples using a insurance use case”) answer, the usual RAG pattern is not enough as it will fail short for precise answers. In order to have both precise and semantic answers, we implemented a hybrid RAG pattern that uses two different approaches to generate the results.

The first step is to use an LLM to understand if the asked question can be answered using the available database schema and just the SQL (or any other) language. If the answer is precise, then the SQL query is generated and executed against the database. If the question cannot be answered using just the database schema and SQL, then the RAG pattern is used to find the semantically closest results.

In either case the output is then sent back to the LLM to generate a final answer. Answer that is asked to be returned as a well-defined JSON object, so it can be easily parsed and turned into a table and then joined back with the data in the database, to provide the final, structured, complete answer to the user.

I’m calling this process Hybrid RAG as it uses both the database schema and the RAG pattern to generate the results.

The hybrid RAG pattern is implemented in the database, using a stored procedure that is called by Data API builder. The stored procedure generates the SQL query and the RAG query, executes them and then generates the final answer using the LLM.

Clean, easy and efficient.