SQL Research Assistant

Key Takeaways

welcome to another YouTube video where we're going to build something really cool this is going to build off of a previous template it's going to build off of a uh it's going to build off the research assistant template but it's going to be a modified version specifically we're going to get it to work over SQL databases so what that means is we're going to create a research assistant that does its research by executing SQL queries and and asking questions of SQL databases and I think this is interesting for two reasons um probably many more but two reasons to highlight uh first a lot of data is in structured format and so being able to not only ask questions of it but also write research reports over it unlocks a massive amount of use cases secondly what we're going to be doing is actually using a subchain a SQL chain as the thing that's doing the research so this is hooking up the research assistant to another chain which is itself you know using an llm to generate SQL queries and then returning that so I think that's really interesting because it shows off this idea of having multiple llms with different responsibilities communicating and and delegating and so we're going to combine the uh the research assistant with a a SQL question answering template one we'll pick one that exists um already and uh yeah I think this will be a pretty cool combin of of two interesting topics so we're going to be working out of the Lang chain templates um repo this is it pulled up here the research assistant template is right here there's a few SQL templates we're going to use SQL AMA I really like AMA it's a great way to run uh llama as well as other models locally um and yeah we're going to dive into it we'll we'll be setting some environment variables but I'm going to do everything from scratch so you guys will be able to see uh exactly what I'm setting and and what I'm having to do I'll probably pause my screen at various points um so I can use environment variables and things like that and not leak those but other than that this will be completely one take from scratch so this is my copy of the linkchain repo um I'm going to initialize my virtual environment um I already have one set up um and then I'm going to go into the templates repo and I'm going to create a new uh a new template so I created a new template called Lang chain or sorry I created a new template using the Lang chain template new and then it's called SQL research assistant um I can now move into it and there we go I've got that set up if we take a look at what is inside it we can see that we have a basic skeleton um and the chain file which is here is uh yeah pretty basic it has this has this like Talk Like a Pirate example so what we want to do is we're going to be working off the research assistant and so let's [Music] grab let's grab this and let's move this in here let's call this SQL research assistant and let's overwrite everything then I need to fix Imports probably so I need to add SQL there SQL there where else is SQL used not there do we use any SQL okay so I've copied over the research assistant I've renamed it to SQL research assistant that seems like a good start if you haven't watched the research assistant YouTube video you definitely should um this will be this will be doable not having watched that but that would provide a lot more context the research assistant basically has two components it has a research um chain and then it has a writer chain the writer chain can be relatively untouched um if we look at that um it is is oh actually this I think no this this is good um it has a bunch of prompts that say to write a research report um it uses open AI we can keep on using open AI um and so the writer chain is good what we want to change is the research chain so previously we looked things up on the Internet we want to change this to now use SQL we can see the chain down here um and so looking at this in more detail the first component generates a bunch of smaller search queries um and then basically we look up we do a search on each of those uh sub questions answer them and join the responses and then and then we pass that as the final research report which then gets Rewritten into an essay so um what we basically want to do we still want to generate sub questions so this is going to be the same we'll maybe change the prompt a little bit but that'll be the same what we want to do is replace this part if we look at where this is defined this uh basic basically gets a bunch of links scrapes them summarizes them that we want to replace this with a SQL chain so we have a bunch of sub questions and we want each sub question to go to a SQL chain and then respond so let's take a look at SQL oama to see what's going on in here so this is the SQL chain um it actually does more than we want so it has a conversational memory we don't need that because remember we just want this one particular chain but let's copy all of this and let's put it in a new file here I guess um let's call this sequel let's put that there we also need this so so this um this example is doing question answering over uh uh sqlite database that has uh information about NBA rosters so we'll end up asking it's a pretty simple database so the questions that we're going to end up asking in this video are probably going to be pretty simple but that's fine the point of this is to show how you can heavily modify the research assistant template to do research over any source so the fact that this particular source is a pretty lame and uninteresting MBA roster database doesn't matter too much so we'll paste that there then um we're going to modify this chain slightly um this is unnecessary um so so this you can see is is doing some saving of memory we don't want to be saving any memory we don't want a concept of memory if we're just doing this we really just need the SQL chain the SQL chain we can also remove this history bit yeah we can remove this history bit and then that'll be good we can use that so we'll repl the SQL response memory with just a SQL chain um cool that seems relatively fine we might have to change some typing around the outputs but let's work with that let's call this uh SQL answer chain and then what we're going to do in here from Lan chain nope from SQL research assistant search SQL import that and then we are going to use that right there so we're taking this chain which comes from the SQL template and is designed to answer a single question and we're basically dropping it in here the map is going to mean that it's going to apply for all the questions that are generated from the sub Generation Um let's add let's add a little test case just to try this out before we spin it up um chain. invoke question um who is typically older point guards or centers um so this might be answerable with a a single SQL query but it's a good uh it's a good example of it's a it's a working functional example um okay so before running we need to make sure that ama is running AMA is what we're using to uh use a local llm and actually let's take a look at which so we're using Zephyr Zephyr is actually a little bit too big for my backbook so I'm switch to llama 2 um if you haven't set up a llama definitely check it out it's super cool repo makes it really easy to interact with language models in order to make sure that I have it running I'm going to do this it's taking a little bit okay hi awesome cool so I can see it running that's fantastic um I'm using open AI at various points throughout here so I'm going to set my open AI API key I'm going to pause the video and do that all right that key is set um and then the next thing I'm going to do is I'm going to set my Lang Smith key so Lang Smith is going to be super handy for debugging everything that goes on because I don't think this is going to work on the first try it never does and so we're going to need to iterate on it a bunch Lang Smith is going to help a bunch with that I can set it up by basically exporting these variables um if you don't have access to link Smith shoot me a DM on Twitter or LinkedIn and we can get you access there um I am going to go back to the terminal and insert those um I'm going to pause the video while I do that to not leak any Keys all right set those variables so we should be good to go um let's try it out actually so so before we do that I do have to install the package that I'm working on so I'm going to pip install d e this is in the SQL research assistant it's going to install um as an editable dependency the files so that way when I modify the files I don't have to reinstall it super handy um I can now run this file where I put this little example um python SQL research assistant chain probably should have added some print statements around it but I think it's fine because this is going to eror anyways okay cool um so I see that I have a key history that's because I've removed the history um from this chain but I haven't removed it from this prompt so let's remove it there and I'm just removing history because the sequel example that I copied was a conversational thing so it had this history key in there that's missing um now because I don't care about that so I deleted that I forgot to delete it from The Prompt now it's good okay so we get some error I was expecting this because I don't think that llama is great at writing squl queries and so I probably need to give it some more explicit instructions so let's take a look at what is going on under the hood there's a lot going on because this is a more complicated thing than usual um this is the first LM call that's being made this is where I'm asking the llm to generate a Persona and so I'm going to be writing a report in the style of a sports analyst agent that seems good so far now here's the next part um where I'm asking uh the the LM to generate some sub questions um it's generating average okay we can maybe return to this and refine this um a little bit later on but I think that works um this is one thing that is generating that kind of looks fine um this is another thing that is generating that also looks fine and we can see here that I think this is where the output error occurs and that is because there is this okay so it's being over lever Bose so it's not I'm telling it to um so this is the prompt that's this is the prompt that's being used to write the SQL quy based on the table schema below write a SQL query that would answer the user's question um no Preamble it's adding things on what I'm going to do is I'm going to add another output parser um that basically um splits on double new lines and just takes the first so there shouldn't really I can't think of a good reason why there would be double new lines in a SQL query so when it seees something like this it's going to split it's going to split here it's going to take the first thing it's going to be that that should probably work um all right let's run it again and see what happens I'm I'm expecting using llama 2 that we're going to have to iterate it on this um quite a bunch which is good because that'll give us a chance to show off lang Smith show off some of how we think about doing prompt engineering bits all of that good fun stuff okay cool so so this actually seems to work relatively well so far um it's actually getting down to line 180 in here where we're trying to join the response of the SQL answer chain together we expect to see um we expect to see strings but instead we're getting an i message M that is because um that's because this we can add a stir output parser that will add in um that'll that'll convert it from an AI message to a string so that's good and then what I also want to do though is I want to change things up a little bit because I don't just want to return the um I don't just want to return the answer so let's go answer there but instead what I want to do is I want to combine it with the original question and have like a nicely formatted kind of like response I can show what that looks like but basically I'm going to do Lambda X then do F string we can do this this uh like that okay right so now this um so this is doing several things um first it's well it's making this return a string and it's making this string be a question and answer the reason that that's useful is that then when that's passed the LM it can can see what the question it asked to get that answer was rather than just seeing all the answers there there'll be a little bit of waiting here because llama's a little bit slow locally we can actually probably see it running if we go here and we can see it in the action and it finishes okay so oh wow so so this is a much longer report than probably necessary for a simple question like this um but if we take a look at it we can see all right so it gives some introduction um okay so the average age of centers is 26.7 years old uh um there's some more it's no data for point guards okay so it's getting some information it's it's like you know I would expect it to have point guard Age center age and then that's kind of it it's not getting there let's let's see what's going on if we look at the final prompt that's inserted um Okay so we see that this is why it can't calculate that it can get that and then it can't get the age distribution of point guards versus centers okay so this is like fine that's like a more complicated SQL query and not a very good question but this is weird why can't it answer this um that's the okay that's let's take a look here um that's Center it has an explanation but that's fine because we slice that out what is here oh that's this is Center that gets the SQL query this is looking up point guard um I am probably [Music] guessing that yeah okay so I'm guessing it's like PG instead of the word point guard well we can what that means this is probably better handled in a SQL um tutorial but what that basically means is we want we need to get more information about the table um so if we look at what is in here this is yeah okay so the SQL database this is a database wrapper that we have um and when we initialize it we're passing in Sample rows and table info which I believe if yeah so so this um so okay so backing up what EX L is going in to the uh llm what's going into the llm look in the playground we can maybe see it is this um and notably the only thing that we're passing in about the SQL table is this information right here which is the schema but we need to know we we want to do analysis on like what is the position column we need to know the values of that so that we can filter it because basically what was happening and what was going wrong is that we were looking for point guard spelled point guard not PG and so that's a mistake that the L made because it doesn't know the values of the column so I'm guessing when we made this template we probably did this because llama gets really confused by by some of the um SQL rows let's put in two and and let's see what happens here um that's kind of the downside of working with the local models they're not necessarily always the best on so here so here you can see oh is that a respon oh no that didn't resp okay so here you can see that we have the two rows from this table that we put in and you can see here that it's a little bit misaligned but position small forward shooting guard so hopefully what should happen is it should let the llm know that the values here are like this style they're not fully written out point guard it should be like PG and so here actually okay perfect so we can see that it recognizes that PG okay so we get a response let's look at this response we have some great background um okay average age of point guards is 27.5 while the average age of centers is slightly lower um H okay so here it's citing some different numbers [Music] um so so it's here it's hallucinating a little bit let's take a look at this prompt and see why it thinks that so it gets this gets this okay so so this so it's getting two different sources of information or two different two different responses one is um this which is saying the average age of point guards is that so what I'm guessing is actually I I let's take a look at some of the other ones [Music] um so this is just hallucinating it looks like like it has something with centers so so okay so so basically this is a hallucinated response um and to to backtrack into how I got that like if we look at the final prompt that's going into the research report we can see that one of the questions and one of the answers that we get is this this is that conflict with this and this I trust these and these much more that they're accurate because these are just simple questions this is like way more complicated this answer so I'm guessing that this is hallucinated if I go back and I find where that ANW responded from I can see the data that it's getting it's hallucinating a lot this is part of the issue with um local models so so what I'm thinking that I'm going to do so so what I want I want to do a little prompt engineering and I probably want to change language models because Ama or just llama in general um you know it's it's not as good as chat GPT it's fine for some of these things and it I think this is this is a separate conversation but it's really important to be able to know when to use different models and when they're good at and when you need to do more complex kind of like um synthesis of information you probably want a larger model at least at the moment if we look here oh I I can open this in a playground okay I wasn't expecting that but that's fine um uh I don't yeah okay so this um so so Lama it can't really be used because we don't support it but what we can do is we can take this we can put it here um and we can use so so now we can try out so this is the prompt template this is the prompt with all the inputs that gets in and we can use we can choose any output that we want let's see what like like what would open AI have said okay so it's listing it out that's much more faithful than olama which just hallucinated things so what I'm going to do is I'm going to change the synthesis part from being done by AMA to being done by chat open AI um cool let's run that and let's see what happens so we can see things in action again let's take a look at maybe what's going on um it's generating that SQL query generating that SQL query that's complicated SQL query but looks like it hasn't errored so far um this is now summarizing some of the outputs here um yeah okay so we're getting the average age of centers we're getting the average age of point guards this last one is very confusing um what is it even doing select age count Star as count of point guards count Star as count of centers from NBA rosters okay so this is like no information or no useful information still running yeah so this this might take a this might take a while again um I will pause the video and come back when it's finished all right so we've got our report um age comparison between point guards and centers um gives some nice Preamble gets average age of point guards is this average age of centers is this and then mentions that 29 centers are in basketball so yeah the so the it it uses the given information um if we look at the prompt and what's passed in we can see that we get the average age of point guards is this the average age of centers is this and then it asks this question about the distribution of point guards versus centers in basketball and it gets back this which is true there's there's 29 centers in basketball in probably probably this database um and yeah so if we look at the response um yeah it's truthful it's rather long for this type of question but again this is just an example question and yeah I think that about covers everything last thing we're going to do is we're going to serve this with Lang serve um so Lang serve is a really easy way to deploy all these all these chains and agents and all of that and so you if you look in the P project. toml you can see that we've got this langing serve tool and it exports this module um so SQL research assistant chain if we go into here perfect good catch so we can see that we can we need to change this to SQL research assistant um so this remember I I copied this code over from regular research assistant which is why it was research assistant but we want to be importing SQL research assistant so we're importing SQL research assistant chain. chain if we go to there this is the chain that we defined now once we have that properly set up what we can do is just L chain serve this will run it up um we can go to Local Host 8,000 go in playground mode we can see now here so this is the this is one of the research variables that we could choose from doesn't really do that much there's the question if we go back here let's copy this question let's put it in there and now we can see the intermediate steps start to creep up this is uh we can see that there's some questions generated there's some intermediate responses um and it keeps on chugging along and then now we can see that the output starts streaming so this is a really nice we you know we build in streaming we build in all of that to L serve so that when you have long reports getting written like this you can still see it as it starts to progress um so this is yeah this is the research report um that about wraps it up uh I can't think of much else to cover cover hopefully this is a good um more advanced uh uh YouTube video on taking the research assistant template and modifying it so that I can do research over any source of knowledge in this case a SQL database and this this this is also really cool because it uses a bunch of different prompts and even different models remember we're using llama to from Lama to generate the SQL um so it uses these prompts and these models in a wide variety of of different ways and it's really this this complex cognitive architecture that we're that we're building up that's about it thank you guys for listening