Build AI sql assistant with LLM. LanghChain tutorial. Streamlit Tutorial. LLM Tutorial . ChatGPT.

Hello friends welcome to my channel so in today's video tutorial we will see how to build an llm based application which also communicates with database to extract information this application will takes users question in natural language and pass it to the underlying llm the llm will take the natural language question and convert it into an SQL query and then this SQL query is passed to the database by our application and then executed and then it Returns the final output to the user so such kind of application is specifically helpful in real business scenario for example in the reporting layer of data warehousing projects where end user usually do not have SQL expertise and can use natural language to get quick information from the database so before we develop the code or we go to the code as I just want you to show how this application will look like when we are when we will be done with our coding so yeah this is how our end application will look like so this is the title aisql assistant and this is the text input to provide a user query and we have three types here result query and ERD diagram this ERD diagram is simply to help end user to just tell how our tables or entities are connected so you can see that region have multiple Nations One Nation have multiple customers and one customer can put multiple orders as well so we are using snowflake database for this demo and we are using the data for tpch schema so if we pass a single simple query to this application let's say how many total customers I we have and run it it is running see uh I have got the total number of customers here I can see the query as well which is simply a count star from customer right so if we go to our database and run this query okay so you can see that we get the desired output but you may think that okay this is simply select count star or select star from our table uh will it be able to make joins uh on the fly yes so if I pass if I pass a complex query let's say which customer has the highest order count also gave me the customer details so we have different table where we have placed the orders and then we have a different dimension table where we are storing the customer details so if I fire this query can I go back to the results it is running okay so here you see the output so we have got the customer name customer address customer phone all the customer related details and how many orders that customer had placed so in the query you can see that on the fly it was able to identify which two tables to be used to extract the information from analytics.raw.orders and inner joint analytics dot draw Dot customer which column to be used to join them and then extract the information so that's how it can also generate the complex queries and provide the output so let us go and develop the application now so to develop this application we are going to need libraries like LinkedIn which is basically a framework to develop application on llm streamlit which we will be using to develop the front end open AI Library which we will we will be using to communicate with GPT and then because we are using snowflake database so we will be using snowflake python connector so let's start by importing some libraries import OS path list import path this class we need to evaluate our project directory from pil import image this we need to load the image of urd diagram so another file we need is from App secret so I have created a python file uh where I have kept all these secrets including the open AI API key this x SQL execution python file is basically a simple code which I am using to execute generated SQL by llm to into the database and then returning the final output in the form of a data frame and if due to any issue the query gets failed or it gets compilation error then I get the compilation error message instead of the output so let's go back the first step we will have to declare an environment variable for our open AI API key OS dot Environ we will have to keep the same name otherwise we are going to get an error we will have to evaluate the root path because we need to use directories and images so P for p in part of my current file dot parents if T Dot sorry P dot parts and the last part of equals to my project name demo [Music] and if it is fine give me the first element so if we try to print root path let us see what we get uh it shows error here it says it was not closed foreign [Music] okay here it needs to be double equal so let's just run this code python demo.py so we have got our root path we are good until now so now let's create some content for our application so create front end for creating front end we will need streamlit Library Imports stream lit as St first of all let's give some title AI SQL assistant then we will give we will take a user input in the natural language you know the import request to S3 Dot text input enter your question there in the next step I showed you that we had three tabs uh one for result second for SQL query and third for ER diagram so we will create those steps first of all we will create a list for of the titles so tab titles equals to result query third one will be ER diagram ERD diagram you can have as many tabs as you want so after that let's create those tabs so tabs equals to St dot tabs and provide the tab titles tab titles now we will have those steps now we need to load the image so let us do that now load the image ER the image equals to image dot open we need to give the path so path is our root path after that root path we need to go to images and then erd.ng images erd.png they should load our image now let us put the image in the tab so with tabs our sequence will be 0 1 and 2 so here it should come as two okay St dot image then ERD image let's quickly check whether our application is working fine until here or not to run a streamlit application you can type frame let run and then your file name okay so we can see that it is working perfectly result query and in the ERD diagram we also have the diagram image so let's go back as the next step we need to prepare our prompt pass it to llm get the response and then execute the query into the database and then print the final output so here we will create the prompt so guys I have written already a prompt into my directory prompts and then tpch prompt.uml file so I will be loading my prawn from this file why I created this file and how to load I have created a detailed video on this topic and I will share the link of the video and you can check that video for detailed information but just to give you an overview by you can see the length of my uh prompt here and these prompts can change very frequently uh to get the higher performance or uh better results so that is why we are keeping it as a separate so that in real development real application development scenario also we can maintain the versioning of prompts so if you want to understand quickly what I am passing in the prompt is I'm simply telling it what kind of application it is and in that I am also passing the ddl of my tables and I am passing some use cases and the queries for those use cases this is particular to particularly to help the llm to understand if some of the functions it has to generate on its own for example if date function it has to develop it has to use then which date function it will be using because this date functions are different in different databases and llm would not know so let us load this prompt from template equals to okay to load this prompt we will need the langteen library from LinkedIn import or LinkedIn Dot prompts import load prompt so load prompt and then we will have to give the path root path and then prompts and then these name of my file which is tpch underscore prom dot by AML I have used Gmail format you can use Json format as well now when now we have our front template next step we need to create llm and a pane so let's first import the libraries from Land chain import open AI and llm chain so let's create the instance of an llm llm equals to open AI I will keep the temperature 0 because I do not want in this case my llm to be creative I want if I pass the exact same query it should give me the exact same SQL in return and we will create one chain which is SQL generation n equals to llm thing I will pass llm instance to it and prompt as prompt template let's create verbose also true now I have the chain llm prompt ready now what I will program is that if we get the user input perform the actions so if user input first of all get the SQL query which I will get from SQL Generation chain and I will pass it my user input then I will get my I will have to execute this SQL into my database for which I will need the function so results equals to execute SF query I will pass my SQL query to it and then let us write those output in the tabs so with tabs first tab is for results St dot right result with tabs one St dot right SQL query so guys we are done with the development of the application so let's go and check now okay so nothing is broken at least on the top we are good so let us just pass a simple query how many customers my I have okay I see it has failed so okay okay so by mistake instead of passing the query we have passed the object so let us just modify it so in the query I have to pass text here and here also it will be text this is what was the keyword yes so we should be good now so let us just refresh it pause the same question how many customers I have okay this time I have got the result you can see this is the generated query as well if we pass if we pass any complex query let us say give me three dates which has maximum number of orders so let us see it is running now okay I have got my output and it says that 2017 228 had the maximum number of orders and it is 124 673 let us just go and check we will use the same query which is generated by llm if I run this query I get the same result the 2017-228 has the maximum number of orders so this is how we can develop quickly an llm based real world application which is also applicable in a real world business scenarios and can help our clients so this is all for this video guys if you find this video helpful please like it and subscribe to my channel thank you and we'll see you in the next video

Channel: TechLycan

Views: 4,944

Keywords:

Id: akW4sWuZWuc

Length: 19min 28sec (1168 seconds)

Published: Sat Aug 26 2023