Build Chatbot with a LLM and Azure SQL Database to Answer Questions (2024)

By: Hristo Hristov |Updated: 2024-06-14 |Comments | Related: More > Artificial Intelligence

Problem

You have tons of text data stored in your company's SQL database. You alreadyhave access to Azure OpenAI. How do you enable a large language model (LLM) to answerquestions based on your data stored in Azure SQL Database? How do you package thisin a custom application, such as a chatbot, for user-friendly interaction?

Solution

The retrieval augmented generation (RAG)pattern is a powerful technique designed to ground LLMs in your knowledge resources.This grounding enables the model to answer questions about your data. This tip willfocus on developing an example solution using Azure OpenAI, existing vectorizeddata from SQL Server, and Azure Bot Framework under Python. While getting into theintricacies of RAG itself is beyond the scope of this tip, check out thistutorial as a primer.

Prerequisites

In your Azure tenant, you need to have access to:

• Azure OpenAI.
• An Azure AI Search index populated with vectorized data from an SQL datasource. Please refer to theprevious article on MSSQLTips.com, where I explained in detail how to setup a vectorized SQL data index.
• Access to a Python environment.

Solution Namespace

Let's begin by creating a project folder and opening it in VS Code. Then,navigate to theofficial Microsoft repo containing bot framework samples. As an example, wewill go with the template for 03.Welcome bot. Either clonethe whole repo or copy the files needed from the samples/python/03.welcome-userdirectory. Below is what the project namespace should look like after you copy orclone the files:

Create a Virtual Environment

Open the already created requirements.txt fileand add the following two lines:

python-dotenvpython-dotenvopenai

So, now it looks like this:

Next, create the virtual environment. Hit Ctrl+Shift+P,select Python: Create environment, selectvenv, then your global Python interpreter. Check therequirements file for installing the required packages:

Wait until your environment is created and selected.

Configuration

Next, create a subfolder named cfg. Inside it,create a file called .env. Populate the file withthe following lines:

SCM_DO_BUILD_DURING_DEPLOYMENT=trueAZURE_OPENAI_ENDPOINT=OPENAI_API_KEY=DEPLOYMENT_ID=API_V=SEARCH_ENDPOINT=SEARCH_KEY=SEARCH_INDEX_NAME=MicrosoftAppId=MicrosoftAppPassword=

The values for these variables are available in your Azure Open AI and AI Searchservices. Here is what my env file looks like:

The values for MicrosoftAppId and MicrosoftAppPassword can be left blank fornow unless you have an existing app registration and want to deploy this bot.

Next, move the existing config.py into the samefolder. Modify the file like this:

01: import os02: from dotenv import load_dotenv03: 04: load_dotenv()05: 06: class DefaultConfig:07: """ Bot Configuration """08: 09: PORT = 397810: APP_ID = os.environ.get("MicrosoftAppId", "")11: APP_PASSWORD = os.environ.get("MicrosoftAppPassword", "")12: 13: class OpenAIServiceConfig:14: """ Open AI Service Configuration """15: 16: API_KEY = os.environ.get('OPENAI_API_KEY')17: SEARCH_KEY = os.environ.get('SEARCH_KEY')18: SEARCH_ENDPOINT = os.environ.get('SEARCH_ENDPOINT')19: SEARCH_INDEX_NAME = os.environ.get('SEARCH_INDEX_NAME')20: AZURE_OPENAI_ENDPOINT = os.environ.get('AZURE_OPENAI_ENDPOINT')21: DEPLOYMENT_ID = os.environ.get('DEPLOYMENT_ID') 22: API_V = os.environ.get('API_V')

This is the result:

App File Update

Next, we need to modify the main application file,app.py,because we moved the config file. Open app.py andchange line 21:

Previous version

Updated version

This modification ensures the import of the config class reflects its new locationin the namespace.

Bot Framework Emulator

Finally, make sure to have the Bot Framework Emulator installed so you can testthe bot. You can find the most recent releasehere.

Azure Bot Framework OpenAI Integration

Now, we need to 1) add the AI functionality to the bot and 2) adapt the coreapp file.

Azure Open AI (AOAI) Integration

In the solution namespace, create a folder named azrand add to it the file azropenaisvc.py. This filewill contain the class for interacting with AOAI:

01: import json02: from openai import AsyncAzureOpenAI03: from cfg.config import OpenAIServiceConfig04: 05: CONFIG = OpenAIServiceConfig()06: 07: class OpenAIServiceResponder():08: 09: @staticmethod10: async def get_completion(usr_msg: list[dict[str:str]]) -> tuple[str, list[tuple[int,str,str]]]:11: 12: rag_client = AsyncAzureOpenAI(13: azure_endpoint=CONFIG.AZURE_OPENAI_ENDPOINT,14: api_key=CONFIG.API_KEY,15: api_version=CONFIG.API_V16: )17: 18: completion = await rag_client.chat.completions.create(19: model=CONFIG.DEPLOYMENT_ID,20: messages=usr_msg,21: extra_body={22: 'data_sources': [23: {24: 'type': 'AzureCognitiveSearch',25: 'parameters': {26: 'endpoint': CONFIG.SEARCH_ENDPOINT,27: 'authentication': {28: 'type': 'api_key',29: 'key':CONFIG.SEARCH_KEY30: },31: 'embedding_dependency':{32: 'type':'deployment_name',33: 'deployment_name': 'embeddings'34: },35: 'index_name': CONFIG.SEARCH_INDEX_NAME,36: 'semantic_configuration': 'my-semantic-config', # name of the semantic config37: 'query_type': 'vector_simple_hybrid', # "simple" in case of non-vectorized extraction38: 'fields_mapping': {}, # If you are using your own index, you will be prompted in the Azure OpenAI Studio to define which fields you want to map for answering questions 39: 'role_information': 'You are a knowledgeable QnA bot.',40: 'filter': None, # https://learn.microsoft.com/en-us/azure/search/search-filters41: 'strictness': 2, # Determines the system's aggressiveness in filtering search documents based on their similarity scores. 42: 'top_n_documents': 2, # how many documents to show43: 'in_scope': True, # limit responses from the model to the grounding data content44: }45: }46: ]47: },48: temperature= 0.3, 49: top_p= 1,50: max_tokens= 400,51: stop= None52: )53: answer = json.loads(completion.model_dump_json(indent=2))['choices'][0]['message']['content']54: document_refs = json.loads(completion.model_dump_json(indent=2))['choices'][0]['message']['context']['citations']55: doc_data = [(nr+1, citation['title'], citation['content']) 56: for nr, citation57: in enumerate(document_refs)]58: return (answer, doc_data)

Let's break it down:

• 05: Make CONFIG variable (instance of the configclass) to reference the config values we configured earlier.
• 12 – 16: Instantiate a RAG client using the AsyncAzureOpenAI class using parameters provided by the config.
• 18 – 54: Get a completion, which is the model response. Several importantconfigurations enable this function:
  • 20: The input user message, which will be a list of dictionaries (we willsee it later).
  • 21: In the extra body, we specify how the completion will work. In this case,it is a RAG, so we need to configure the data sources.
  • 24 – 30: Configure the search service authentication.
  • 31 – 34: Configure the embeddings dependency.
  • 35 – 45: The specific configurations for the vectorized source data.Most notably:
    • 35: Index name.
    • 36: The semantic configuration if it exists (alsorefer to the previous article on SQL data vectorization).
    • 37: The query type. Ideally, you want to use the hybrid combination of vectorand semantic. Here, we use the simple hybrid type because of the lower service tierof the search service.
    • 38: Empty, as we use the default mapping.
    • 39: Role information for the service.
    • 40: The filter configuration can be used to filter out documents based onRBAC or other rules. More information is availablehere.
    • 41: Determines the system's aggressiveness in filtering search documentsbased on their similarity scores.
    • 42: How many documents to show as references when answering.
    • 43: Limit responses from the model to the grounding data content.
  • 48: A low temperature for more deterministic responses.
  • 45: Max four hundred tokens per response.
  • 53: Assign the answer to a variable. Internally, the answer is a generatorobject that is populated word-by-word as the model generates the answer. TheAsynAzureOpenAI class provided a handy methodmodel_dump_json to easily access the answer as ajson object.
  • 54: Using the internal json data structure, we can access the referencesto the grounding data that the model will generate.
  • 55 – 57: For the references, I want to output a list of tuples, whereI have the index, the title, and the source URL.

This is the result:

Update the Bot App

Make a Private Method. Next, open thefile welcome_user_bot.py. At line 20, import the modulewe created for interacting with AOAI:

from azr.azropenaisvc import OpenAIServiceResponder

At line 115, insert a new line and a new private method. This is the definition:

115: async def __get_gpt_response(self, q: list[dict[str:str]]) -> HeroCard:116: 117: gpt = OpenAIServiceResponder()118: response = await gpt.get_completion(q)119: 120: card = HeroCard(121: title='Answer: ',122: text=response[0],123: buttons=None124: )125: 126: bullet_points = "\n".join([f"{index}. {title}: {content}" for index, title, content in response[1]])127: 128: card.text += "\n\n" + bullet_points129: return card

Here, we instantiate an object of type OpenAIServiceResponder –our custom class that takes care of connecting to AOAI.

• 118: We get the response using the user query as parameter.
• 120 – 124: Using the built-in HeroCardcard type, we assign the first element of the response tuple to the text ofthe card.
• 126: Remember those references to grounding data we extracted from the jsonresponse? Here, we make them in a nice bullet-point list.
• 128: Add the bullet points to the answer.

Update the User Text Input Flow. Next, delete lines 108 through113:

Substitute with:

108: query = [{"role": "user",109: "content": text}]110: card = await self.__get_gpt_response(q=query)111: await turn_context.send_activity(MessageFactory.attachment(112: CardFactory.hero_card(card))113: ) 

This code formats the user prompt and passes it to the custom private method.Using the send_activity method, we serve the resultin the bot app.

Update the Rest of the Bot App. The template we used containscode with some example messages. We do not really need them. Let's adapt thatcode a bit. In the __init__ method, adapt line 33with a welcome message and delete everything from line 35 up to 49:

Note: The line number will no longer correspond to the original count.

Next, in the on_members_added_activity method,delete lines 57 up to 64:

Next, in the on_message_activity method, adaptline 70 with an introductory message you want to see and delete lines 73 up to 77:

Finally, save the changes to the file.

Running the Bot App

Open a terminal in VS code. Navigate to the .venv/Scriptsfolder and activate your environment:

Type cd ../.. to navigate back to the root folderof the project.

Then, type python app.py to start the bot app:

Open the Bot Framework Emulator application, click Open Bot, and input the BotURL http://localhost:3978/api/messages

At this point, you should be able to interact with your data using the GPT modelconfigured earlier in the config file. In my case, I am using GPT 4.0:

If you recall, the Alien Truth is one of Paul Graham's essays that we vectorizedand indexed in AI Searchhere. The answer develops further, including two references to index data chunks:

Conclusion

Using the Azure Bot Framework SDK for Python, Azure Open AI, and AI Search, wehave built a chatbot solution to answer questions about your SQL Server data. Thisis a basic solution demonstrating the core principle. Further improvements may includeautomatic recurring indexing of new data, deploying the chatbot to different channels(web, MS Teams, etc.), adding authentication to the bot, and monitoring the solutiontelemetry.

Next Steps

• BotFramework SDK for Python
• Azure AI Search Data Source
• AOAI Service Overview
• Large Language Models with Azure AI Search and Python for OpenAI RAG
• OpenAI API Introduction
• LangChain Prompt Template with OpenAI LLMs

About the author

Article Last Updated: 2024-06-14