Using Azure OpenAI to retrieve SQL data

hey everyone good evening good morning good afternoon wherever you're joining in from today welcome to another live session at the reactor before we get into the session please take a moment to read our code of conduct we're all here to learn so please be respectful please be kind please be considerate ask a lot of good questions the chats open throughout and we do encourage you all to participate we're back with another session with Nick today and he's going to talk all about using Azure open AI to retrieve SQL data and with that I'm gonna add him up on the screen and let him take it away good morning everyone so I'll let you take it all away let me know if you need anything I'll be behind perfect thank you rob thank you so hi everyone who's joined good morning good afternoon uh so today we're talking about using as the title says as you open AI to retrieve and interact with data hosted in SQL databases um the point the point of of these series is to be very introductory um is just to give you some nuggets of knowledge that you could use um to develop either a more complex system or complex application these talks are not designed to be complex or Advanced we don't want to go over the heads of anyone here so it's meant to be very introductory uh you will get source code to play with um to play Within this uh after the after the talk so um just bear that in mind very introductory very simple as your open AI is still you know relatively new product I guess um so this is just an example of a a useful thing that you can do uh with Azure open AI which I still think has has not really being explored properly just yet um so Rob I'm just going to go ahead and share my screen and then just let me know if you have any problems viewing that but hopefully you shouldn't so I think yeah I think my yeah my screen is up fantastic um so yes this is what we're talking about today using as you open Ai and and SQL um super powerful combination uh again just not heavily utilized right now uh when you probably hear of open AI you're probably thinking of of chat GPT or any other chat interface um really the goal of of this introductory session is to teach you how whilst that that kind of those GPT family models are very powerful is how can you get them to interact with the real world or physical world and that's really the purpose of of this webinar today just a bit of a housekeeping um if you don't know about where if you've only heard about open AI not Azure open AI uh I'd encourage you to check the azio cognitive Services page um where you can find a suite of AI modules uh not just as you open AI but a suite of AI modules that range from Vision tasks anomaly detection speech recognition text-to-speech things like that that is very useful when building an AI product from scratch in the lowest cost in the lowest cost way and the fastest time possible um so definitely encourage anyone who hasn't checked this out to check out Azure cognitive Services see what it's about see what's going on here um but with that let's let's continue to the session um again I've tried to make this as as brief and introductory as possible so one thing I would encourage is if you have any questions um please throw them in the comments chat if you even have I mean they use this session to your advantage so even if you have a a tangential product idea that you were thinking of of using then ask questions about this so I can actually give you some advice on how you would go ahead and implement this in an action fantastic so next slide Okay so I just want to use this slide to set the scene here of why this is probably important or why this topic is important consider you consider we have a person called Bob he's an accountant in a maybe a big company um and he uses Chad gbt a lot let's assume that Bob Bob does that um now Chad gbt is not really connected to any internal company data it is not connected to data Bob may have in in OneDrive in SharePoint um but Bob is an accountant and needs to be able to retrieve information very quickly so in the typical scenario if Bob were to ask you know chai GPT hey can you tell me about my profits this month or what was my revenue for this month and Bob is career trying to get information from their Finance table um then you'll probably get a the simple answer from chai GPD which is sorry I don't have access to your data blah blah blah blah um now Bob knows that the the company probably has a lot of data sitting in SQL databases either in Azure or another cloud cloud provider hopefully Asia but um this is the the typical problem scenario where how can we how can we actually get how can we help Bob in this scenario retrieve information from that company data sitting in SQL databases right how do we connect um how do we connect Bob who doesn't know how to code doesn't know any SQL language doesn't know any query language how can we get him to transform then to extract this data from SQL databases um with a bit of prompt engineering this is the key here because an answer like sorry I don't have access to your data it's not it's not acceptable for Bob Bob one's actually concrete evidence um just based on a simple query written in in natural language so the question is how do we solve this how do we allow Bob to actually get information uh from from the SQL databases and really two main things come into play the first is a bit of prompt engineering and then hooking up an Azure open AI GPT model to an SQL function or anywhere where it can actually execute SQL commands um with those two things in place then we can actually get transform Bob can also get anything in that case Bob can say hey can you tell me about my um tell me when we had a profit margin above 60 in any given month um and Bob will be able to get all of the data at his fingertips without knowing a single bit of of SQL uh SQL language um so really this is the key this is and this is if you look at a lot of Enterprises are now looking about how do we connect uh gbt to our own data uh how do we actually extract um this data and and really it's it's it is really comes down to two things it's about some clever prompt engineering and building in some executors or some functions those are the two main things from a high level from a basic level that you would need uh to be able to execute uh to kind of solve this problem and bridge that gap between GPT not having any knowledge about the outside world or your internal data um great and by the way it looks like we've got a global audience people from Argentina London and Israel love to see it so okay so let's go through let's actually show you a quick demo uh so I actually uh the source code like I said you will be get you will be given the source code to this uh after the um after the call but uh I actually wrapped around the source code in a simple web application nothing fancy um but this will demonstrate exactly how we have solved this problem like how we've built in a how we've actually slightly prompt engineered the GPT module and and build an SQL executor uh to be able to actually extract information in this example we're going to pretend to be Bob we're going to pretend to be the accountant that needs access to the company's finances or needs to query or filter the company's finances uh and this demo will show you exactly how to do that so in this demo we can write in a simple query such as in this case we are saying show me all the rows where our expenses were greater than one thousand and dollars here and you can see what's happened is we've actually been able to generate a SQL query so we've been able to generate a SQL query and what's happened is we've been able to execute that so I don't know you know Bob doesn't know how to write SQL language but from that message we're able to generate the SQL command and execute that to get the results so what that means is from plain English we're able to fetch all of this information from a financial database that matches our query and you can see the SQL crew here that is generated is I mean that is great SQL code um and that that's a you know this is a relatively simple example and we can try another example here so in this one which uh I pre-recorded we could say uh show me where we had let's say for example if I wanted to find out where we had a where we had a loss um I could just say show me where we incur negative profits again a SQL query generated here which is perfect we're looking for all all rows where the profit is less than zero that would be a loss um and we could see here just from language we're able just to query this database and so we can try a uh let me try another message we could just say show me you know for example uh show me in this case let's see what we uh I remember I I typed in here at this point uh show me when we had Revenue okay so now we want to get it to start actually doing some complex like understanding some complex formula so I've I've written in here show me when we had Revenue that exceeds 2x expenses um you could argue that that is not really a clear message um but you'll see here with a bit of prompt engineering it's able to figure it out and execute that command um so you could see for example when I press enter uh you can see here the the SQL query that was generated is perfect select everything from finances we're assuming the the table here is called finances where our revenue is two times more than expenses so it just understood that Nuance that 2x is two times and we didn't have to say explicitly say two times expenses and it's gone ahead again executed that command and given us all of the query results uh for for that command so it's great I don't need to learn SQL I can just connect directly to SQL databases and get this information so we can even ask a more complex question um to say okay show me when we had a profit margin that was greater than 60 a little bit more of a complex scenario here but again you could see here that the SQL query that's generated is perfect right that is a perfect SQL command for getting every row where the profit margin for this business was greater than 60 uh and it's gone and it's executed that beautifully we can see uh juicy profits for for any four of these months here and we could even amend that to 90 again still works perfectly well um and there we go we see two two months here where uh profit was where we had which which looks correct the profit margin here is greater than 90 for these two uh for these two dates um so that's perfect and we can even add some more complexity into this query so we can even say show me show me when we had a profit margin that was greater than 90 but or 50 in this case let's add some more complexity here but when expenses were lower than a certain figure so it adds a bit more complexity now to this first command and you can see again the SQL query that is generated is Flawless from from this from this question um and we can see exactly this is if you don't know SQL um yeah well if you do know SQL you can see that this is the perfectly correct command um and we could see in uh uh and we can actually execute this and be able to query all of the results from here as well um and there we go we can see all of the uh the rows where uh expenses were lowered in 2000 but the profit margin was greater than uh 50 uh in this case um I did see a couple of questions come through so I will want to answer them um so so we've got Joad from London who asked whether this is my sequel so I mean this can work with mySQL databases um it can pretty the good thing is that this can adapt to pretty much any uh thanks graph for highlighting that so this can adapt to pretty much any SQL database schema you have um it just requires a bit of prompt engineering which I will show you exactly how to do um and then Jawad also says uh I assume you won't be able to update this by using GPT just the read only um I'm not sure I understand the question but I'll try to I'll try to answer it as best as I can um you so you you will have to update the prompt um which is the main thing that goes into uh GPT um and there's a there's a clever way to get gbt to always output output responses in Json form uh so that you're able to take these commands and actually run them through functions uh which again are the next slide is actually going through the entire source code of of how this works which is exactly what I'll show you um and then life asks how well does this perform in more complicated scenarios what if I had multiple tables that I would need to join again this is a perfect scenario for this type of product um and this is great right because this is just a um this demo is just a foundation of what you can do with just one table Yeah you could what you could have multiple tables absolutely um and I'll show you exactly how if you had multiple tables how you need to engineer the prompt to be able to spit out a SQL command that works perfectly so uh don't worry hopefully a lot of questions will be answered in the next uh in the next slide so if we go on to uh if we go into the next slide here um this is where it's uh let's go on to let's find this yes how does this work so what I want to be able to do is um show you exactly like how does a source code work what are the components that we need to fit together to make this type of product work now what you saw there is very introductory but you can do as the the question before stated you can join multiple tables you can you can even join multiple SQL databases you can make this as complex as you can um it really is down to how you get GPT to structure that that output and what's the prompt that goes into that um and how it asks providing you have the right side presume you'll be able to update SQL tables absolutely so you would have to have uh the API rights to be able to update read and write to a SQL for to a SQL table um if you if you wanted to go ahead and and update that so question is how does this work well let's have a look at the source code right from scratch so the first thing is um this source code again I know I'm repeating myself but you will have access to this entire source code it will be on GitHub for anyone to access um there's a couple of main components here that that build out the source code the first is we have a script here called as a Asia openai dot Pi a very simple script that effectively executes um executes the completion from gpd4 and any other module actually that you could pass into here so this is just simply a utility function that takes in a system message a user message a model temperature max tokens and you pretty much pass anything through here so the first thing we do in this project in the source code is always set up an Azure open AI script uh with a bunch of with you with a utility function um the second thing which is you know not relevant if you already have existing codbs but what we spun up to to show this demo was actually spun up a few uh tables or mock tables or mock databases which we called mydatabase.db um and this is where we Define some utility functions which I won't go into too much detail such as creating a table creating a connection querying the database setting up a new table in this case a financial table that we wanted to query um getting the schema okay so this is really important so we Define a function here called getting schema representation um and this is really important because for GPT to be able to construct a prompt correctly or construct a SQL card correctly it needs to be able to understand the schema of a specific table um that is really really important um if that is if that is not done correctly that can lead to errors um so getting that schema representation is is really of of any SQL database is really key um but I've written this function so that you guys don't have to write this yourselves from from scratch uh so you can go ahead and and grab that the other thing is uh actually looking at the main uh the main prompts that we have to Define so like what is the actual main system prompt that goes into this um and it is surprisingly simple it is surprisingly simple with a few extra tricks that you may or may not know about already the first is actually getting uh defining a personality for for GPT through a system message to say you are an AI assistant that is able to convert natural language into a property properly formatted SQL query um the table you will be querying is called finances here is the schema and you notice we've put in some placeholders here for this schema so it is not specifically defined this would be a dynamic placeholder based on the schema of the table that we're querying so that's super important this is a dynamic schema based on the table that we're querying and um at the end of the day what GPT is going to do here is actually spit out a text response um and we're going to need to you and this text response is going to be a SQL command based on natural language but for us to you know if for anyone who's played with chat gbt um the output can be structured very differently in in many cases uh the output could be in you know a text format so GPT could say hey this is your SQL command and give you your SQL command um when we're passed directly passing the output from GPT into a function that doesn't work uh that will break unless you specifically specify how GPT should format the response so in this case I always find it really easy to get GPT output a response in Json form with key value pairs which I can quickly query in reference and pass as an argument into another function to execute whatever I need um and this little prompt at the end works really well when trying to get a structured output and uh here we say You must always output your answer Json format with the following key value pairs the first is query so the the SQL query that's actually generated and the second is just a you know a checker function an error so if there's an if there is an error then at least we'll know about it so that is that is really really important when you're getting when you when when you want the responses from GPT to actually come to you in a form of an argument that you can pass into another function or executor um it's really I I find it personally um one of the best methods to actually get GPT to Output the answer in Json format pretty much in 95 of of cases so now that we have our prompt set up we have our Azure open AI set up we have our SQL database that is spelled up let's go through the main function now this is the full streamlit application which is the the web application source code which I showed you um so we don't need to go through everything here in detail but I do want to show two main components our play here and the first is query database super important function this requires a query and a connection string um and these and this is essentially a function that we can use to execute SQL commands but again we need to take that output from GPT and pass it in as an argument into this function and I'll show you exactly where that that happens um but what we do initially is we create the connection object to the the SQL database we get the schema representation so remember we defined a function to actually get the representation um of a specific table a schema representation so we can actually pass that into uh the placeholder field within the system prompt um and once a user actually inputs a message uh then we format so this is the key here we format the system message here um with this schema uh so right now we're helping Bob the accountant so we only care about the finances table in this case um and so we're formatting the system message with this schema from the finances table um and uh if you if you recall uh if I just why is this okay here we've played again um once we do that then we have a fully compiled system message with the with the schema already built into the prompt and then we can run the get completion function which is the utility function we defined in the Azure openai script we could pass in the system message and the user message and the user message we've already we've already mentioned or specified in the system message that the user will be um outputting text in natural language that's that's already been defined so when we have when we run they get completion for messages we have a system message and a user message and then we have a Json response that we can pass so we use the json.loads utility function in Python uh remember the the response from GPT will be a full fully formatted Json object with two key value pairs um that's the trick here and then we could actually just load them into a Json object once we've done that we can then query get extract the query by referencing the query key remember again in the prompt we specified to GPT that there must be two key value pairs with one of them being query so at this point we now have the query we now have the SQL query ready to go and then what we can do is if you can look online if you look at code line 42 this is where we generate the results so we can actually call our function which is our executor which was query database the query database function we can pass the query through this as one argument and pass the connection object and actually execute the command and get the results and all we do in the um in the previous uh in this function here in in line 43 is actually show uh show those results so that this is how it all comes together this is how you can get how you can actually get a GPT model to interact with the real world um when when I if I'm going if I uh was to go to our previous slide here it really is all about two key components from a basic level which is a bit of prompt engineering make sure you define your schemas properly make sure that's inserted into the prompt make sure the prompt specifies as very clear on the output that is I cannot stress that that point enough um if the output is not formatted in exactly a Json object and you try to query a specific key from that Json it just won't work um so clever prompt engineering you have a SQL function or SQL function that is effectively your executor like what are you using to execute the results and you essentially pass the output from this directly into that function to get your desired results and that is how this whole thing comes together and that is how you can get a GPT module to interact with um SQL SQL databases um I really that's that's the key the key to all of this so when if you if you just recall what I showed you in the source code when we're entering a message here what we are first doing is getting the stigma of the finances table putting that into the prompt generating a query from The Prompt so the actual output from GPT in this case is the generated SQL query you see here and then we are using that SQL query to actually execute the function which is the the function I defined earlier so there and this is again just scratching the surface this is a very basic example with one SQL database one table um so what one of the things I would like for anyone listening to this talk to to get out of um or to take from this is to take this source code take this basic source code build on this try and actually get GPT to react with multiple tables maybe multiple SQL databases maybe join them together in some way um and especially if you have and we had another question about actually permissions so if you can get GPT to actually write back to these SQL tables in a way um that is compatible then that's also great so this is the main the main thing that I want everyone to to take away uh uh from this um from this webinar but um but I did want to make this really really short and sweet I did promise to try and keep this under uh 30 minutes and I know right now running up to the 30 minute Mark um again very introductory the whole inspiration for you is to take this source code and build on top of this even try if you have even if you have your own company data try and connect uh to your own SQL databases try run a few commands see what happens um in any case I would for anyone who does try this and is curious about how to expand upon this um if you'd like my Assistance or like my uh my my uh my advice on how to take this forward with your company then just connect with me on LinkedIn and and drop me a message um again trying to try and play play with the source code uh to the best of your ability um but I hope this inspires you to take this type of application and apply this to your own use case whether it be your own company's data Maybe your clients company data and how can you actually bridge the gap between someone is that there is a stakeholder internally who needs to know about something which is hidden in a database they don't know SQL query how can you help them get information in a very efficient way without them knowing coding or without them knowing how to write SQL query and that's really the main goal here and this is why everyone is so excited about this technology because it really Bridges the gap between people who are non-technical and people who are Technical and allows those people who are non-technical to be able to do things that previously they couldn't do before so on that note um I'm going to pause there um I know we've got a couple of more a couple more messages that have popped in the chat so uh from Jawad um could you show me a show again where the input from the user actually gets embedded into the prompt uh I think I may miss that of course I will so if I go ahead and reshare my screen and by the way later you will get the source code to this so um I feel like I have to keep saying that uh multiple times because people do forget they actually do get the source code uh for this um so if I go back to the place where we actually started going through the source code here um let me have a I'm gonna try and forward the video here unfortunately this is a not letting me do that um but let's just go on to okay it's not it's not fun for some reason I can't afford the video which is which is strange um but uh essentially let me actually uh try something else here okay it's not letting me forward the video so anyway let me just and I'll I can explain to you this newly you know offline if you want but essentially what happens is um we defined a schema placeholder in the system prompt uh and what we do is we just place the we we format the system message to include uh that to include that schema um so that when the when actually we are generating the SQL output GPT understands exactly how to do this based on the columns associated in the in the existing table um but again sorry for the video not being able to to forward but hopefully if you get the source code drop me a message on LinkedIn I can walk you through that no problem great uh so Jawad so appreciate the the positive Commerce you add uh I it's exactly what I wanted for you to for to spark a lot of ideas for anyone listening um yes you know uploading data into a hosting server enabling communication between the start exactly this is exactly right staff who may not be um specifically competent or or competent in SQL language or SQL querying um and and actually you you mentioned something which is key which is eliminating the need for frequent report creation exactly uh you probably have existing people who take up a lot of time generating reports based on an executive's query well actually you could actually generate you could you could even give an executive a tool to be able to get any report they want that is customized to exactly what they want so um and this is really the key here so yeah I appreciate the the positive comments and um glad to know that it's sparking a few ideas for you if you do try this internally um or do you try this on your own own company's data again drop me a message on LinkedIn I'd love to hear it um I don't know if we have any other questions uh let me know if anyone in the chat if you like to ask any more questions um give it maybe a few seconds um okay Hamilton so how safe uh how safe is it security rise which at gftp GPT shared company data as it uses chart GPT API um so chat GPT has your opening AI is structured slightly differently it does use the same underlying models but you have another level of Enterprise security which is not available through the um through the open AI apis so you can turn content moderation off which means that no data sent to Azure open AI is actually kept for Content moderation which is huge for Enterprise customers um so security wise I would always recommend for companies I recommend myself and even in production with other Enterprises who use our GPT modules uh we always use Azure open AI not the open AI apis directly the key is because as using Azure open Ai and you have the option to turn content moderation off and that prevents your data being held um are actually at open AI Zen for the next 30 or 60 days so definitely recommend you using Azure open AI if you are concerned about security great uh how would you manage authorization um it depends on you know it depends on the um it depends on like how are you it depends on what level of permissions are you willing to give to a specific application or use case in this case if let's say you have an executive that needs to be able to use this application to be able to read and write stuff to a SQL table then they need then you need to be able to encode the right variables or write permissions to be able to to allow that that tool to be able to do that so um it depends on it depends really on the use case I think maybe you're talking about if you deploy this to everyone how do you manage authorization at a user base level um you could probably leverage uh Microsoft graph API like let's say if you're deploying to a um if you're deploying to an Enterprise that is already a Microsoft license that already hosts their their um addresses on Microsoft then um then um then you could probably leverage Microsoft SSO for role-based Access that's probably another one so you could give Executives reading right access and you could give lower level stuff just read-only access that's another way you could manage authorization great so is it preferred to buy the full schema let's say into a SQL file to GPT or search for it directly in the database um so let me see if I understand this correctly is it preferred to provide the full schema let's say into a SQL file to GPT I said giving it yeah okay I would say the first is probably quicker and you do want to give the full schema to GPT um because there's the queries can be very complex and for GPT to be able to understand the nuances associated with the fields in that schema and generate the right query then you do definitely want to pass a full schema into GPT um so giving in instructions to search for it directly in the database adds an extra step which is not necessary uh so the first is is definitely quick and recommended uh so how do you deal with DB schema that don't have meaningful field names well that's a that's a challenge uh I think the way I would deal with that is tell whoever set up that SQL table in the first place to meaningfully unkname their fields properly um that is a challenge I think another way to get around that let's say for example you have a SQL table with horrible field names um or you know which don't make any sense that comes down to a bit of prompt engineering so you can actually add some additional metadata to a prompt to let GPT know that hey the what these fields you could actually write a description for each field so you may have let's let's say we had a field called I don't know gobbledygoop but actually the values in that field relate to revenue um you could you could actually attach another you could attach a description to the prompt to say by the way the gopity group field means Revenue uh and that actually that actually solves this problem so you can still have a DB schema with silly uh silly field names but they'd be able to handle that correctly uh laith do we have to be registered to this event to get the source code uh uh yeah so RAV will add this to the YouTube video later so there'll be a link to the GitHub and you'll be able to uh get the source code from there find you the model to know where maybe we can find you in the model to know the relevant field name yeah so you you could do this but I would say this is definitely definitely not recommended um in my opinion because it requires a lot of work to fine-tune a GPT model you have to pass in a lot of data and there's something that could be easily solved um with a bit of clever prompt engineering so I would say definitely not necessary great um any more questions guys okay I think that maybe pretty much it I don't see any more questions coming in rather I'm sure I'm not sure if you do but yeah no more questions um in the chat fantastic great well look guys look you know really appreciate everyone joining today uh and thanks for all the questions um please do definitely join the reactor Meetup for sure for for more interesting events uh definitely connect with me on LinkedIn rav's just put that in uh the chat as well my LinkedIn URL um if you do have any opportunities to take the source code try this in your own in your own way uh I would love to know about it so please drop me a message great thanks Nick and thanks everyone for joining we will see you next time and um we have an amazing in-person Meetup coming up if you join our Meetup you'll know that's on September 12th and obviously Nick is speaking on that as well so um feel free to scan the QR code join our Meetup and we shall see you next um but thanks Nick thanks for your time and we'll see you soon thank you guys thank you bye-bye

Channel: Microsoft Reactor

Views: 15,909

Keywords:

Id: _VKMToxWv4E

Length: 41min 46sec (2506 seconds)

Published: Thu Aug 31 2023