Fast-track RAG: Chat with SQL Databases using Few-Shot Learning and Gemini | Streamlit | LangChain

hello how's it going welcome back to my YouTube channel and in today's video we will be discussing on how to create an application capable to interact with your SQL databases with any of your SQL databases you can choose this is a very useful project because you are basically empowering users to create databases regardless of their SQL knowledge so you can finally say goodbye to back and forth emails with some of your Cod col in which they are requesting you data and then you can start focusing on doing what truly matters which is doing some data analysis rather than coding all the time complex SQL queries also this application increases the speed to make informed decision and even managers who don't feel comfortable on using SQL quties they will be able to extract valuable insights from this rack application you just basically need to type some text and then you will execute queries which is extremely useful for this rag application I used SQL database in which we will be extracting data I perform few short learning in which we quickly adapt the large language model to our specific use Case by providing some samples of SQL queries so the large language model can understand what we would like to obtain from it and we used stream light for developing the front end of this application also we used L chain to integrate all the steps from the workflow for this app let me quickly show you how this works in this case we have a movie SQL data base that is composed by three tables one is about movies and the ratings another one is about the earnings and the third one is about the jaras of the movies and I'm going to show you later on how these tables look Alik and explain you in more details how everything in this application was built but let's say the that you would like to know how many movies are in this database so we can just ask here how many titles are in this database and if we click on Gemini Pro output which basically we'll use a Gemini Pro to obtain how many titles are in this database we can see that it says that that we have 100 17 titles and if we go directly to the SQL database and we count the amount of titles that we have we can validate that we really have 117 titles so we can we're obtaining the actual result and now let let's go to python so I can show you the whole workflow of this application also I'm going to be leaving in the description the link of these repository so you can follow step by step everything here if you're enjoying this content so far don't forget to subscribe to the channel by clicking on this button because it helps me to create more content smash the like button below and turn on the notification Bell so you never miss a new video here we have our SQL Guru which navigates SQL databases with is and let's say that the user ask which move movie has the highest rating so the first step will be to connect to the SQL database after you connect with the SQL database I perform p short learning and the reason why I did this was because when I tried by using the base large language model I was not obtaining accurate SQL queries for instance I was sometimes obtaining columns on the SQL query that were not even in the SQL database so the more samples of queries that you provide while you do Fu short learning the more accurate query rated by the large language model will be also when I was doing F shot learning I explicitly specified to the large language model to use columns from the specific schema of this database to avoid this problem after we performed the future learning which is basically providing an input question that the user ask let's say which movie has the highest rating then you provide a SEL query for this specific question but later I'm going to show you some specific examples of the fusal learning samples that we use in this case then we use a large language model to generate the SQL query in this case we use Gemini Pro from Google then we execute the query that will will provide the response in SQL format so in this case will be for example 117 and then we will use the large language model just to provide the response in a better format we will provide this answer to the user so this is the workflow of this application we could skip a couple of steps if we want for instance in production it might be better also to rather them making the fal learning every time a user ask a question we can also create an embedding and save them in a vector store database I could also make a video for this so if you interested to know how we could do this please leave it in the comment section below so I can know if I should make another video about this topic if we keep checking the Rhythm file here we can find the link of the database which is a sequel light database let me show you know the SQL database how it looks like three tables it has a table related to the movie ratings and another one about the earnings and then the third one about the genre so if we want to check this database we can see here that it has an identifier so in this case is the movie ID which is the same movie ID in the other tables so the first one has the movie ID then the title the rating total quotes budget and so on second table has information related to the earnings whether they are domestic or worldwide and then the thir one about the JRA as you can see we have here three tables and when I was performing FAL learning I was trying to use the three of them allowing the large language model to understand how to make connections between the tables for this case I was using basically joints between the tables if you want to install this application the first step will be to clone the repository I'm giving you here all the steps then you go to the folder you create your visual environment to isolate the python dependencies of the libraries that you have here with the version and then you install all the requirements which is basically all the libraries and the versions in order to use this application you will need to create an environment file which will contain the credentials to use the Gemini Pro so you just need to basically copy this environment file which is an example that I provided uh we can look it up here it has random values here you just need to copy paste your Google API credentials here you can find a link on how to get your API credentials so here you just need to click on create API key and then after you substitute the Google API key in the EMV file you can run the application by typing Streamlight run source app.py and now let's go to a notebook so I can show you in details all the code that I used in order to build this application so let's create ayon notebook SQL Feer test so as we saw earlier the first step will be to connect to the SQL database once the user ask a question and for this case we'll be using line chain they have a function called SQL database that will allow us to do this then let's connect to the SQL database in this case I have the database in this location so let's go there okay so let's add the path now so the data is in this location movie. DB okay it works as expected and let's say we would like to print the tables that this database has we can type get usful table names let's see so we can see here that this SQL database base has three tables which are imtb earning and general as we saw earlier when I was showing you the SQL database another important step will be the schema of the data set because with this information you can provide to the large language model which tables we have and which columns we have and therefore you wouldn't need to explicitly specify manually all the columns that the table has especially in this case that we have so many columns so let's call this let's say database schema okay then the following step let's import the credentials so we can start using the G Pro let's use import Google generative AI as then let's get here the API key then AI API key gen I configure so we will configure the API in this case okay perfect The Next Step will be to load the model so you can see the results that the Gemini Pro provides when we ask a question and then you will see the importance of Performing F short learning here in this case generative model and let's specify here that we want to use gini Pro so let's try first by asking a question and see the SQL query provided by the model let's see let's just ask a simple question which is what's the aage right well let's go with a more simple question how many titles do we have in this data how many titles do we have in the database it's a very simple question I was going to use how what's the average that's a bit more advanced or well it's not Advanced but it's a bit more complicated than than just counting the titles in this database we can try later on the other one which is the average so let's do model generate content which is function that this model has and let's use user question let's type here the question that the user profile let's see the response let's check the SQL query so it says that we don't have permission so let's see so here I forgot to load environment variables that's a reason let's load M so we can have the value of the Google API key which was basically missing that's why I had that error let's see another the result let's check the SQL query provided so as you can see here it says well at least it says that it doesn't have access to the database rather than just providing a random answer I have a couple of examples of the usual learning that I already generated I'm just going to paste them here so you can see them so here we have basically four examples of input which are in the question and the SQL query here we have how many titles are present so we basically here do a select count of titles which are the movies in this database which is in the table of IMDb also list the titles with the 10 highest ratings so as you can see here I'm just working with simple squl queries as I said earlier if we want to have an engine able to produce more comp complex SQL queries you need to also do in the few short prompting some examples of this kind of SQL queries so in this case I'm joining two tables so here we have list the genre of the highest worldwide earning title so in this case I'm using the table of genre but also the table of ear and I'm basically making it join by movie ID here we have another one in which we are listing the title the budget and domestic earnings from the highest rating title as you can see the queries are getting more and more complex and then we will see the impact of this on the result let's run this and then let's create a prompt there are multiple ways to create prompts when we perform F shot learning uh in this case I decided to go with this one in which we basically specify that the large language model is an expert to transform in English questions to Cle query we specify here that we have three tables in our database and this is the schema that I showed earlier in which we specify the tables and also The Columns of each tables you can order the results by column to return the most interesting examples in the database also we specify here we never qu for all the columns from a specific table just for the relevant columns which is basically here instead of getting all columns I explicitly specify the columns that I'm interested to see also we specify that we don't want to insert update delete or drop and information of the database and also again specify that if you don't know the answer just say that you don't know rather than making up a random answer here we do some formatting that we remove this back steps and that's it here I append the SQL example the inputs which are this questions and also the queries which are the secret queries yeah as I said there are multiple ways in this case I prefer to use this one because it's very simple and you can still get really good results out of it so maybe we can ask the same question again which is how many titles do we have in the data Bas but now using few shots from that we just performed so let's get response with few shots let's use now the model that we loaded earlier and provide the formatted R and the question let's see the results as you can see here select count from IMDb we obtain what we expected let's say that now we ask other question that I was Shing earlier question related to average this one it's more difficult for the large language model since in the future learning we didn't provide any equ with an aggregation rout like average or sum and so on so let's see if the model is able to provide an accurate response what's the average rating of the titles response with related to average related to average let's check again and let's see the response of the model here we can see that is well in this case it selected select average rating fromb well it's working perfect even though we didn't specify any aggregation the model was able to generate correctly the SQL quy so after we generate the SQL quy The Next Step will be to here we can obtain a more summarized results so the next step will be to execute the SQL query and for this case go back to the SQL database and then we will run basically this SQL query let's say we want to run this one and as you can see we can also execute SQL queries from python so this is basically the the result which is 7.87 the average rting of movies on this database then the next step will be to use large language model to interpret the results obtained so we'll be using L chain Google vertex Ai and we will import vertex AI let's use again the Gemini Pro as a model to provide formatted responses which are intuitive for the end user Gemini Pro here we are just loading the model and then we can basically test it out now to see if everything works well let's say that base on the SQL response write a natural an intuitive well let's write an intuitive answer and then here we can have the SQL response that will be basically this file here we can provide SQL response and then after we provide this we can see that the large language model is able to provide a way more intuitive response so our end users rather than just seeing the 3 the 7.9 that we saw earlier they could have a better context with this sentence in which specifies that the average rating or the specify category is approximately 7.9 but let's make this Now using another functionality from lunching which is the prompt template so for this case let's load the function called No called prom template we'll be using the this function and here we will specify a template well we can use let a similar template that we used earlier we have this template then we can use prom template and specify this template and then the input variables of this template is basically the SQL response which is this value and now let's use a chain to connect a template with a large language model so let's use l chain chains import llm chain call it ready llm chain let's here provide the large language model that we utilize in this case and also the template from template and here let's say by and we want to run this with thisal respond as we can see here the average of the value in the column is 7.87 so as you could see here this will be the whole workflow we can see now in details what's the output of all the functions that will be used in this workflow so now it's time to build a util file with all the functions that will be us in the application so we could have our app.py as clean as possible which will allow users to understand with a less degree of difficulty the code so for that reason let's create python package called SRC which will contain our source code then let's create another one with our utils so here we can just use the code that we used earlier to create functions as a first First Step let's initialize the database connection so let's initialize thep connection let's create a function for this purpose let's call the database let's use the same code or we can make this faster and then let's return the database so basically here we are just connecting to the SQL database then let's get the schema get schema from the database which we will feed in the F shot front for this purpose we just need to provide the database and then use the function get table info so let's use it in this case turn schema let's return schema let's call this schema and theb is the database of Interest schema of the DP which is the database now let's create a function to build the prompt the fot prompting that we did in the youy notebook build SQL prom let's call it build VI shots prom and this is a more appropriate name so we will get the schema as a first step so we can get schema from the database by using this function that we created earlier we will need to provide a database so let's here say that we want to get a schema The Next Step will be to provide some examples of the input questions and secret queries after this let's use this prompt template that we have here to incorporate the scheme of the database and also some example of secet queries and then let's return the format Pro with this function we basically build a template for the few shorts promt next let's generate the SQL query and for this we will be using the large language model and also the prompt so let's have here the prompt and the user question of course and for this case let's use model let's bring the large language model that's imported now so let's use well we will also need to use the credentials again so let's have this information here so we'll be using Gemini Pro as we did earlier and the SQL query generated will be model. generate content and here we will have to specify the prompt but also the user question then the output that we want to obtain from this function is the SQL query then after we get the SQL query we want to execute that SQL query in the SQL database and then we want to provide an intuitive response after executing the SQ query so now let's create a function to execute the SQL query so we will use WR query that will execute the SEC query in database and also this is the SEC query that we want to execute so we need to provide this values and then let's return database run the SQL query now let's use the large language model that we will use to provide the intuitive responses let's use model name just in case we would like to use a different like large language model let's call it vertex AI vertex AI in this case well in this case we'll be using Gemini Pro Gemini Pro so we can remove this and let's return here the large language model large language model run SQL query generate SQL query model to generate let's create the chain between the large language model and the SQL response so let's have here SQL response this chain will basically provide intuitive responses for the user let's use as a template the one that we had earlier here let's provide this template here then let's PR template that we use here we have the templates The Next Step will be to chain the template in the large language model so let's use the chain llm chain that we saw ear earlier and we will have to provide here the large language model that interest us on this case also the prom template here let's get the answer which will basically be the result from the chain so here we have SQL response let's return this answer this is all we need in order to build the application these are all the functions so now we can proceed and build the front end of this app so let's create this app.py so the first step will be to import our Streamlight as SD because we will use Streamlight for the front end of this application now let's create this application the main column and now let's configure the settings of this page let's do this as a first step configure settings of the page which is which is basically the title of the page and so on so let's do it now St set page config and then let's specify the page title which is chat well let's use same one which is this one here at the top chat with SQL databases let's have it here we have this ice cube as an icon so we can also use it for this case we'll need to use page icon then let's have this one and then let's use the layout which I'm using which is white for the web page then let's specify the hether on this page which is basically this information chat with SQL databases using Gemini Pro let's use the same one so let's use SD header and then let's copy paste this there the next widget that we will incorporate is this one filling box in which the user can ask a question from the database and based on this question the mod will be able to generate a SQL query provide an intuitive response so widget to provide questions basically let's call it user question s the text input ask let's have the same one ask question from the database then let's create a bottom and with this botton we will basically trigger the workflow the whole pipeline specify Gemini Pro so we explicitly specify here the model that we're using so the user could know which model we're using so this is a message that will appear while the model is generating the response so we can know that the procedure is working so as we saw earlier here in this figure we can see that the first step is to do the connction with the database so let's load the function that we created in the U file to initialize the database connection let's import this The Next Step will be to generate few shots prom which will basically we're using this function that we created earlier function is called build FS prompt let's use it again we just need to specify the database that we're using which is here already when we use this fot prompt we space specify the input which are some questions and the SQL queries and also we specify the scheme of the database and now let's generate the SQL query which is one of the most important steps this is the query that will be executed so let's create here SQL query which is generate generate SQL query okay and the parameters that we have in this function are the prompt here we will specify the F shots prompt but also the user equ which is this one here then let's X execute the SQL query generated execute SQL query query well this is the result of the query so let's use the function that we created here to run the query here we specify the database and also the secal query let's get the large language model that we will be using just for intuitiveness purposes call model name well I didn't specify the model name because we're using J in this case so let's just call get vertex l let's import this function and now let's obtain the final answer will be very intuitive for the user yeah for this case we'll use the chain query function that we created earlier here we'll specify the large language model with the SQL response which is this one okay after we get the answer let's write the results of the answer and this is basically ready let's just have something to inform that the response is already generated and the application is ready after this let's just deploy this stream light app so let's type stream light run s c and app Y and then here we can see the stream light application that we just built and I showed you earlier so here we can see mod okay let me see what was the error module pH okay SRC so we need to remove SRC from here okay and now it should work as expected let's load again the application and let's see if it works okay perfect let's ask a question to test if the application is working well now such as well let's ask one of the questions that we have asked before how many titles do we have let's start with this one okay so as you can see here I have a problem while connecting to the database so let me see where is exactly the issue this one so I just modifi the location of the database now so now everything should be working all right let's ask again how many titles do we have in this database earlier it was 117 so we're expected to get the same response so here we have the Z respon is 1 17 indicates that the count of row returned by the qu is 117 perfect let's ask question which is a bit more complex such as what's the rating of the movie with the highest World Wide earnings let's see if the model is able to provide this response in this case as you can see we have the rating which is in the imtp table we also have the earnings which is in another table so we're basically joining two tables here we're using two tables at the same time as you can see the model was able to provide a response out of it we could double check this response now if you want we can generate the SQL query let's quickly do that just's do double check so the question it's the rating and the worldwide ear so let's check the rating rating from imgp a left join the other table is called earning B let's perform the left join using as an identifier movie ID equal to movie ID for an order buy to get the title with the highest earning so it will be B let me check the tables okay so the columns called worldwide worldwide desk limit one okay this should be the response 8.1 perfect let's also check the title of this movie title so it's Star Wars let's check if we could also get the title here what's the title and rating of the movie with the highest worldwide earnings let's see if we can get the same results so well perfect so as you can see here well it even gives you a bit more information here we have that is Star Wars it was relist in 2015 and this was the earnings the worldwide earnings of this movie and here we have the rating which is amazing as you could see we were able to build a rag application which is capable to interact with any SQL database we also saw that when we perform few shots prompt the large language model is more capable comprehends better question and is able to provide accurate responses when we explicitly specify to the large language model in the prompt to just use columns that it can find in the schema of the database the more complex the queries that we specify in the few shots PRS the better it is because the model will be able to generate more complex SQL queries for instance I could ask a more complicated question here related to the three tables that we have but the model might not be able to respond accurately because while we were doing the few shot front we just use joints between the tables which were the earnings and the titles but if I ask question related to the J back might have some difficulties and that's the main reason when we're using fot prom to provide as many samples as possible so thank you very much for watching this video I hope it has helped to clarify many of your doubts and if you still have some please leave them in the comment section below don't forget to subscribe to the channel hit the like button below and turn on the notification Bell because I'm going to keep uploading videos related to large language model rag applications and also other data science related topics see you the next time

Channel: Eduardo Vasquez

Views: 2,516

Keywords: LLM, SQLDatabase, FewShotLearning, LangChain, Streamlit, SQLQueryGeneration, AI, GenerativeAI, Python, data, database, sql, chatgpt, chat gpt, mysql, sql for beginners, sql tutorial, open ai, artificial intelligence, machine learning, openai chatbot, chat gpt explained technical, word embeddings, advanced tutorial chatgpt, GPT 4, SQL Data with ChatGPT, Chat with SQL using ChatGPT, langchain sql, langchain mysql, chat with database, chat with your database, chat application with database

Id: ZEtLyH6u4XE

Length: 29min 51sec (1791 seconds)

Published: Wed May 08 2024