Connecting Azure SQL DB to Azure OpenAI Service using Cognitive Search | Create Microsoft Teams Bot

hey everyone welcome back in this video we are going to explore the end-to-end implementation of how to connect your database to Azure cognitive search and then connect Azure cognitive search to Azure open AI services this was one of the video that we used to connect The Blob storage where you placed your documents so now instead of blob storage we'll be connecting it to the Azure SQL Server so that it directly connects to your data okay let's start okay the first thing first will be starting to create Azure SQL database let's also create a SQL Server all right so let me just rename this okay this will be a development server and I'm going to configure it based on the details and I'll select the basic pricing basic service tire then review and create by the time this is uh getting deployed let's also prepare the data that we need to feed to our SEO SQL database so what I'll do I'll ask chat GPT to create a SQL query for me so that I can just copy paste and run it into the SQL Server so I just gave a sample SQL prompt which I am asking it to create a table with name quantum and add three columns ID question and answer also insert records through the table with 50 question and answer pairs so I'm going to give a theme Quantum computing the reason I asked it to generate 50 question and answer pairs just to give you how powerful can this implementation be if you have a huge amount of data so this our query is ready let's copy it and run it into Azure SQL database so I'll just go here to the query editor into my SQL DB the first login will always fail because I need to configure the firewall so that it allows my IP address to be passed through okay and also along with that the public access is denied so what I'll do I'll just go to overview set server firewall selected Networks and also let me allow Azure services so that I don't have to worry about connecting this SQL DB to other CR services and I'm going to add my client appears so that the location the IP I am accessing this DB should be allowed okay so now let's go to the query editor again now it is logged in create new query paste it okay so this time I'm going to directly run this one okay so incorrect syntax near Auto increment okay and also let me just copy this error message I am not that good in SQL syntax okay perfect so this is a new query with the updated syntax let me just copy this and paste it here okay this time let's run it fail to execute string or binary data would be truncated in table column answer truncated value interesting okay truncated entanglement is a phenomena where two or more qubits become correlated to each other okay okay maybe there seems to be something okay let me just get this error message length of the answer column in the table is not enough to store the entire okay in that case let me just make this as one thousand okay now we have all the 65 rows present in our table now it's time to just query it so that we can see all the data okay here it is okay that's fine now what we'll do we'll just create cognitive search and connect our SQL DB to that let me go to the okay let me just create a new tab and in that new tab let me create a cognitive search the reason I am selecting the basic tile because the semantic search is not yet supported in the free one it is supported but not for some of the data sources so in order to avoid the later issues so I am just selecting the basic tire at the start itself okay it's perfect now let's click on import data existing data source let's select Azure SQL database and choose an existing connection I'm going to choose the current DB that we just created since uh we have not configured the managed identity so it will be none and here let's give it a name and also the password of your DB test connection remember if you have not enable that server firewall option where your excluding or except giving that exception of connecting through Azure resources it will not succeed you will it will throw an error that you're not cognitive search is not able to connect to your database in that case you need to go to your server firewall and configure that setting and here choose the table that you want to connect to here also there are many services that you can perform or I can say some enhancement you can perform on your data such as extract people name location key phrases detect language for our requirement will not just will not do anything over here we'll just go next here this is the important piece let me just first change the index name okay and also let me copy so that I can give the same name for the indexer this is a very important piece where you select the fields that you want to retrieve filter sort Festival searchable okay now I'm going to retrieve all the fields while I query this cognitive search you might want to disregard some of the fields but yeah in my case I have only these three columns so I want to retrieve all of them okay so I'm not going to perform these operations but you might want to consider selecting them if you want to filter them with certain fields for example if I have a field called department and if your users are from that department you might want to filter them and show data based on the department that is for this is used similarly here you have other operations as well so I'm not going to consider them as of now searchable searchable is what will be used to look into your data So currently I have my data into these columns so I will send my users query and it will search in both the columns question and answer edit will get me the best answer possible okay if you want to only search in the answer you can choose that but I also want to check based on the question and the answer okay let's go next this is the indexer indexer is what use what is used to connect to your data sources and schedule if your data is constantly increasing or modified then you can maintain a schedule so that indexer will connect to your data source and get you the updated data there was one more of advanced feature available here below the schedule for the soft delete if a data is deleted in the SQL DB and you don't want to index that to cognitive search the given option to you for the soft delete solve delete means if you have a column into your SQL database which named as is deleted and it's a Boolean value true or false then cognitive search will automatically see that column and delete that data from your cognitive search index but I'm not sure why that feature is not available here but sometimes we do get an option here okay let's submit this okay perfect so before going to the Azure open AI let's first click on search Explorer and test our index as of now I'm not going to put any query that means I am just retrieving all the data available in my index so let's click on search here you can see it is retrieving ID question and the answer and my search query is just star basically everything okay so it gets me everything if at all you have a data which is greater than some 50 rows you might be getting this property skip that means at one query response you will only get 50 records for the next set of Records you can query this one next link this is the URL where it queries the next set of data okay so this is how you can query all your data at one shot okay now let me just give a sample query what is Quantum computing okay so it gave me some responses and this is the response which is having the highest score so it's not that good response because currently it is doing key keyword based search it is matching your keywords and it is just searching based on the keyword okay so what I am going to do I'm going to implement the semantic search for this one currently I am not getting that feature here in order to enable the semantic search what you're going to do click on semantic search select the plan go back to the search Explorer now you will see this query option here click on that enable the semantic search also if you want to enable the spell correction so that before passing this to the semantic search it will correct the spellings and give the better response currently I do not have a configuration let's create a new one so I'm going to name this as we write semantic config okay this title field should be a concise description of the document so what I'm going to do I'm going to only select the ID as the title field if you have a title field you can select that here it asks you to select the content fields in my case first priority I am giving to the answer the second priority I am giving it to the question so it is based on the priority if you look at the definition content field should contain text in natural language form common examples of content.text of a document the description of a product or other free form text make sure to list content fields in order of priority because lower priority Fields may get truncated or ignored so the first priority I am giving to the answer and the second priority I am giving it to the question now if at all you have some keyword Fields such as those comma separated Fields you can select them as of now I do not have any I am going to leave this as blank so save it and select your configuration here and now let me ask the same question again foreign the good part about semantic search is that it gives you the scoring out of 0 between 0 to 1 and you can analyze that okay this is the best answer and show it to the user okay see here it is giving me a response from the row number 66 basically this is the key of my table primary key the ID column and the score is 99 percent even this is also 99 both of them are just slightly uh both of them are same score yeah that's good so I can pick any of them so let's see what is the 66th question and also what is the first question let me just go to the 66th one yeah 66 is voltage Quantum Computing that's the exact match so you must have seen now the comparison between both the keyword based search and the semantic search so now you can easily compare which is the best better option yes it's semantic search is the better option it's very accurate again let's see what is the first question what is quantum Computing basically both of them are the same question but yeah you need to if you are generating some question and answer pairs using the open AI services or chat GPT you need to make sure that you remove the duplicates that it might generate this is one of the case where it is generating duplicate responses but that's fine our response is very much accurate what Azure open AI will do when we connect this cognitive search it will take the first three responses and it will formulate a better response to show it to the user currently the text which is here is very limited yeah open Azure open AI will formulate this into a better user facing response okay now now it's time for us to connect this cognitive search index to our Azure open AI let's see if I have a resource ready with me let's quickly check okay uh I have a resource oh by the way I got the GPT 4 access uh in Japan East region but for this demo I'm not going to use that because most of our viewers might not have gpt4 access so I'm just going to use a normal way all right so first thing first let's create a new deployment of 3.5 Turbo select the model and let's select the GPT 3.5 Turbo and create okay now let's go to the chat option add your data add data source select the cognitive search select the resource and also the index that we just created yeah go next here this will again ask that same question uh remember it will only show you the columns which you have selected while creating the index okay so here also it only shows you the column which you have selected and here it's also let me just select answer and the question both based on the priority let's see if that priority is still applicable here okay it's not that applicable okay that's fine okay file name is the one that will be shown below your responses if it is using if it is getting used citations citations so this file name will be the let me just quickly test it option is original file name of each document okay yeah that's the correct so it will be the name of your citation reference which will be shown so I'm going to use the question for my file name and also the title will be the title of your citation and the URL will be the source URL currently we don't have any so I'm just giving the answer so just to show you how the citations will look like and how we can make use of that okay let's go next this is the place where we will be selecting the semantic search configuration since if you have not created a semantic search configuration it will not come here and your responses might be somewhat not that accurate so the reason I'm selecting is to make my responses more accurate save and close okay now my data is now perfectly connected let's say hi to the bot okay let me ask the same question yeah okay Quantum Computing is an area of computing that's fine so here you can see this is the citation title that I was selecting it there if I click on that this is the title oh sorry this is the file name this is the title this is a URL okay now let me ask something else what is qubit let's see what is it is referring to what is the significance of the qubit that's my file name this is my title this is my source URL I am just referring it as Source URL since it is not a source URL but I am referring that because if you have any and if you have selected that it will be coming up here The Source URL I have selected is that it's the content the answer the title I am selected as the question the file name I have selected as a question again okay perfect so now let's ask the question we always ask just to check that it's only referring to our database and not to any other sources okay perfect perfect response the requested information is not available in the retrieve data please try another query or topic so now we have confirmed that it's only focusing on our document or not any other document so let's see what is a computer okay we do have some responses here what is quantum computing so that's fine okay we have we have our chatbot ready but it's only available on the is your open AI Studio we might want to connect this to our custom application that is our main use case and we will be connecting this to a Microsoft teams chatbot using Microsoft bot framework okay so just stay tuned here you have Json this Json will contain all the messages you have asked in the current chat this is useful for you to analyze the syntax how you want it to implement in your custom application okay all right okay let's go to our visual studio and start creating if you want to learn about this Microsoft bot framework I have many of them I have many of the videos in my previous implementation so you can look at it and if you want to learn how we got this templates here it's a bot framework templates available for visual studio you can also look at our previous videos to learn more about it okay let's start with DB cognitive search AI bot okay let's create perfect let's go to our Echo bot class let me show you what this current bot does and then we can build on top of this let's start it we can test in the bot framework emulator that's a local application desktop application where you can test your Bot locally connect the use case for this particular bot is very simple whatever you say it will reply back with an echo okay now let's continue developing we have these two methods available the main ones I'm not going to look at the working of the whole source code I'm just going to focus on only this method on message activity so let me tell you what these two method does this one on members whenever you start a conversation this will show a welcome message to the user this is where it sends the welcome message so initially we got this message hello and welcome another method we have is on message activity this mess third this method will be invoked only when oh there is a message from the user and it will just append the echo keyword to the user message and it will send back the response yeah so let me just get rid of these two and just write down the user query that we received and also let's write it as GPT response and I'm going to pass on the user query as a parameter okay let's create a method for this okay that's fine I have my method ready let's make it as a sink let's get rid of this piece and go back to our chat playground we might want to get the custom URL as well as the body parameters from the network tab okay this we have already done in our last video so let me just say Quantum Computing just to get the response back okay this should be the response I guess yeah this is the response this is My URL request URL let me show you in the postman so that I can directly copy paste the code yeah this is the place that we did it last time and let me copy paste again because our this URL is changed okay and let me add the payload click on view source let me just clear the chat and ask us new questions so that my request payload will be limited okay so I have now a limited request payload I'll click on this view source just copy the Json body as it is go to postman and paste it let's click on beautify perfect now I have my question here what is quantum computing okay next thing that we need is the API key let me just get that okay so as you can see we have a streaming response in order to change that I just need to change this property from True to false and I just send a new request and this time I'll get the response without streaming and this is my answer this is my citations okay perfect now it's time for me to copy this code and just paste it in my chat bot over here I am just paste I'll just paste this one okay and also let me get rid of this Json body the reason being I'll be just formatting it automatically by the visual studio feature yeah so I'm just going to copy this complete Json body come here and paste it again this time it is slightly formatted compared to that uh no it's the same thing but that's fine okay okay I had to just use interpolation okay now it's perfectly formatted based on the interpolation now let me just replace this question with the user query perfect okay you can also get all this configuration from the app settings.json but for the demonstration purpose and for the quick implementation I'm just putting it as it is okay then I don't want to print on the console foreign Json response as the string now I need to convert that to a Json object and just get this response okay so I need to go to choices um return string okay before that let me just convert that to result object equals to J object dot parse result okay now I can use here result object okay the first is choices first element under choices next is messages first second element in the messages under messages we have the content okay perfect now I can replace the return type with the task screen perfect now I have the GPT response and let me send that to the chat window okay let's first test in the emulator let me ask what is quantum computing you can replace this doc one with an empty string if at all you have multiple references you can use regex to replace all the references with the empty string so that you get a perfect response without any citations if you're tall you need citation you can also include another response to include the citation here the citation is here content that will be on the first element of messages inside content okay we have this ready now it's time for us to deploy this implementation okay so to deploy it to Microsoft teams as a chatbot we need to create Azure bot resource so let's go ahead and create it by the time this is getting ready let me just give the multi-tenant that is the resource type that we have okay this seems to be completed let me just copy directly from here a multi-tenant is my bot type and this is my app ID you can click on manage password create a new client Secret copy it and paste it here perfect now you can easily deploy this solution for multi-tenant tenant ID is not required let me publish this to azure selecting the windows let's choose the resource Group that we just created and hosting plan new hosting plan okay let me choose a free tire okay create by the time this resources are getting created let me just enable the Microsoft teams channel in my Azure bot this is perfect you can see the list of channels your Bot is connected to by default direct line and web chat will be automatically added for Microsoft teams we just added now okay this resource is created now our published profile is also created we can directly publish now perfect it will take just a minute for this to get completed currently I am not following any best practices because my configuration is currently hard coded into my app settings.json for prediction environment these need not be here either they need to be on the app service configuration or in the key vault but as of now I just put them into one local configuration for the quick demonstration and also I'm not creating any manifest but that is the recommended way if you are connecting your chatbot to Microsoft teams I already have many videos on that in our JD Bots Channel you can always look at the latest Series where we have demonstrated how you can create manifest and upload that manifest to Microsoft team App Store uh this is the one learn how to upload teams app Manifest this is a video that will help you okay now let me just go and directly open the Microsoft teams link from here let me just go to channels and open in teams or before that this will not work the reason being I need to put the endpoint URL so I just copied this configuration paste it API slash messages okay before testing on Microsoft teams let me show you how it will work in the web chat this is test in web chat so it allows you to test whether your deployment is completed or not whether your deployment is not having any issues if it works here it will work anywhere perfect now we can go to channels open in teams let me open the web browser okay initially I'll get a welcome message if it is in manifest I can automatically get a welcome message but when you open directly you might get or you might not get so this is kind of unless you initiate a conversation you'll not get a welcome message okay now you have the welcome message let me ask my question perfect now we have successfully connected our Azure open AI with the SQL DB using cognitive search and also created a chatbot that is connected to the Microsoft teams if you want to follow the best practices I would recommend you watch my multi-cloud Series where we followed the best practices for the Microsoft teams chatbot okay perfect thanks everyone have a great day

Channel: Dewiride Technologies

Views: 3,401

Keywords: Azure, Open AI, Search Demo, ChatGPT, enterprise data, chatbot, AI, machine learning, NLP, natural language processing, artificial intelligence, tutorial, custom knowledge, Azure services, Microsoft, azure search, openai, bot, train own data chatgpt, chatgpt train custom data, use own data in chatgpt, enterprise data in chatgpt, cognitive search, vector db, train custom data, chatgpt train own data, azure open ai train data, open ai, api, chatgpt api, semantic search, teams bot

Id: mtXxtSZsIpI

Length: 46min 6sec (2766 seconds)

Published: Sun Jul 30 2023