LLMs for Data Analysis in R

Transcript#

This transcript was generated automatically and may contain errors.

Okay, welcome everyone. Today you are here for the workshop on LLMs for Data Analysis in R with Sarah Altman from Posit. And Sarah, we're so happy to have you here giving this workshop and I will let you take it from here.

Great, yeah, thanks. Happy to be here. I'm going to send some links in the chat and then I'll share my screen. This is the website for the workshop and then the repository. You don't need to clone the repo or anything. We'll get set up in a minute, but just so you have that. And then let me share my slides. Okay. Can you see that, Garrett? Okay, great.

I was going to show this while we were getting ready, but we're going to do all these instructions in a second, so don't worry about this. We have everything set up for you in an environment, so you're not going to need to install anything locally or anything. But welcome to LLMs for Data Analysis in R.

Just a little bit about this workshop before we get started. So like Emily said, I'm Sarah. I work on the AI team at Posit and I'm also joined by Garrett. Garrett, do you want to briefly introduce yourself? Hi, everyone. I'm Garrett. I work with Sarah at Posit. I'm on the education team.

Yeah. Do you also want to maybe talk about your workshop that's in a month really quickly? Yes. I believe on June 11th we're scheduling a workshop that's very similar to this topic. It will be about how to use AI to develop everything from R code to Python code to dashboards to shiny apps to apps that use AI as a chatbot inside the app and how to deploy those. So look for that.

Yeah. I think at the end we'll talk, maybe overlap a little bit with that workshop, but I just wanted to mention that in case we get to the end of this workshop and you wanted to hear more about those topics. Yeah. Check out Garrett's workshop.

Okay. I pasted this in the chat. Maybe Garrett, you could resend that in case anyone joined after that. But this is where all of the slides and information is going to live. You might want to open this just because sometimes there's things that you'll need to click and it's going to be easier to click it from there than to type the links yourselves. And you can follow along with the slides. Sorry. We're now joined by my cat. And there are set up instructions, but we're going to give you some time in just a minute to do that.

They have very jagged performance. So, there's some easy tasks they're great at, there's easy tasks they're terrible at, and there's hard tasks that they're good at, hard tasks that they're bad at.

And so, because of that, I think in general, just embrace the experimental process with LLMs. We will talk about a way to formally run experiments for LLMs in the next section. But in general, just try things out, explore, try and reason about their behavior from observing it, instead of thinking through, okay, I know this is how transformers work, so this is going to happen.

But we're going to just transition really quickly to one other point, which is that we saw how to use the chat method to send one request and get one response back. If you want to keep chatting back and forth, ellmer also has built in functions for that. You would either start an ongoing back and forth chat in the console with live console, or in the browser with live browser.

Programming with LLMs

So, now we're going to talk about programming with LLMs. So far, we've really just done basic chat, and there's lots more that you can do. So, you learned how to use ellmer. The next step is really thinking about which model and provider you want to use. There are a lot of providers and many models to choose from. First, I just want to clarify the provider is going to be the company that hosts and serves your model. So, this is something like Anthropic or OpenAI. If you're using something like Hugging Face, that might be your provider.

This is the company that hosts and serves the models. And then a model is a specific LLM with particular capabilities. And models can vary across several dimensions. One is the amount of content you can give them. So, how many tokens can you give the model in a single request? This might range from like 200,000 to 1 million tokens, which is actually generally quite a lot. Speed. So, this is how many tokens per second they can generate and intake. Cost. How much does it cost to use the model? At this point, it's usually about like $1 to $5 per million input tokens for the major frontier models, maybe less than one for smaller models. And input tokens are usually priced less than output tokens. Those are the tokens the model is sending back to you. There's intelligence. How smart or how good is the model at tasks you want to use it for? And then additional capabilities. So, many models can also do things like that. They have vision capabilities. They can do reasoning, extended reasoning or thinking. They have tools, all that. And we'll talk about tools in a bit.

Model naming conventions

I find the naming conventions really confusing. So, we just go through that really quickly. There are many models. If you pay attention to like news at all about LLMs, there's like new models coming out all the time, and the naming conventions can be opaque. So, the Anthropic models, which is what we're going to be using today, have three. So, Anthropic has Claude models, the three model families. It took me a really long time to realize that they are named after types of poetry. So, Haiku is a small poem. The Haiku model is the smallest, fastest, and cheapest model line. Sana is a larger model or larger poem. So, it's the sort of larger, more expensive. And then Opus is a very long poem. So, this is the largest, most expensive, most intelligent model. And then the number that you might see after this, like Opus 7, that is the version. So, a higher number means a more recent version.

For OpenAI, they have GPT models. The GPT with no suffix or GPT Pro is the most expensive, smartest, slowest model, and then you have Mini and Nano.

There are also local or open weights models. So, these might be things that you personally could download and run locally on your computer, or maybe your organization has a server that it runs these models on for you. I think these naming schemes can be even more confusing. I'll just go over this really quickly. So, for example, Quen 3.6 and Jemma 4 are two recent local models. And these, I just took a picture of the various options you have for each of these models, which I find opaque and confusing. Generally, how these are named is you have the model name. It's like Quen 3.6. The number of parameters, for example, would be like 35B. That means there's 35 billion parameters. This is the model size, how big the model is. And then you might have an additional suffix with an A that's the number of activated parameters. This is like the number of parameters that are actually in use anytime you send a query.

So, for example, Jemma 4.31B has 31 billion parameters. If it has that A3B at the end, that means there's 3 billion active parameters. This isn't necessary for today, so to speak, but I think it can be confusing and sort of be a barrier to using the models. And so, I find it helpful to understand their names.

Okay. People often want to know what model to use, and I think it can really vary on your needs, your organization's needs, and what models you have access to. Generally, we recommend picking a recent frontier model from one of the big labs because they tend to be best performing models and then move to a cheaper one if you need. But you don't always have your pick of model. Your organization might constrain you to only a particular provider or only local models. So, it really can depend on where you work, what kind of data you use, and what you have access to.

Using different providers and models in ellmer

Okay. So, let's take a look at how to actually use different providers and models in ellmer. So, ellmer supports all of the major providers plus local models and enterprise options. And you can access models from these various providers by using the chat function that corresponds to the provider.

So, we have the major paid providers, open weights, models, and enterprise options like AWS Bedrock. You might have noticed that earlier we were using just a function called chat. instead of these chat underscore functions. And you can do that too. You can use either one. So, LLM has a shortcut where you just call chat and then pass the provider name as a string. If you just pass the provider name, it's going to pick a reasonable model for you to use. It has a default built-in. Same thing for OpenAI. If you want to specify a particular model with the chat function, use the format provider slash model name. So, again, you can use either chat or the chat underscore functions. It's really the same thing. In the exercises today, we tend to use chat just because it's easier to swap things out with that.

Multimodal input and structured output

So, far we've just been sending text back and forth to the LLM, but you can also send other types of information. This is called multimodal input. So, images get tokenized just like text. For an LLM, a picture is roughly 227 words or maybe 170 tokens. And so, in ellmer, we pass other types of input with content underscore functions. So, for images, we use content image file. This passes an image from your local file system to the chat. You can also do the same thing if you have an image at a particular URL with content image URL. Or if you have a PDF, you want to extract information from that PDF using an LLM, transform it into something else, you can use content PDF file to pass a PDF from your file system.

Okay. So, now we're going to talk about structured output. And this is how you can get LLMs to return data in a particular structured format instead of free text, which is really useful if you are doing, you know, any kind of data analysis.

So, here's a common problem. You might have some messy text data and you need to extract specific fields like name and age. You can definitely do this by writing R code. It might involve a complex regular expression, a lot of string parsing, but you can do it. And it might look something like this. And it works. But we have to write a lot of code. And this is actually something that LLMs are really good at, generally. And it's, like, easy to use for this use case. And we can just ask it to extract the name and age. And it can reason flexibly about what those values are. So, we don't have to write those complicated regular expressions.

So, here we've just set up a basic chat. This is in prompt to extract the name and age. And now when we ask it to extract that from two of those elements in the vector, those free text elements, it gives us back a name and age. So, this is great. But this is still just free text. It would probably be better if we could get it into some kind of R object, like a list or a data frame. So, something like this, where you have a list with the name and the age, like an object that we're used to working with in R. And you can pretty easily do this with ellmer with the chat structured function or method. So, this returns a piece of structured data instead of just free text. The trick is that we first have to tell it what structure we want.

So, to do so, we use these type functions to define a structure with its types. So, here we use type object to make a list. This is going to specify that we need a list. And then name should be a string and age should be an integer. There are type functions for all the types. Now, we can give chat structured, again, that bit of free text, our type specification. And it'll give us the data back in the format that we requested. And this is now ready for use in your analysis. If we wanted to do it over all of those free text entries, we could. And then we would have data that would be ready to use.

So, notice that we did type string for name. And so, name is a string. And type integer for age. And so, age comes back as an integer. And we're going to see these type functions again later. There's, and again, yeah, there's one for every type. If you want to see all of them, you can just do, you know, ellmer colon colon type underscore and see all of the type functions there are to choose from. You can also add descriptions to help an LLM understand what each field means.

Okay. So, now you're going to try this out yourself. So, in the 04 folder, there's some notes. This is just the wrong file path. You're going to extract data from those notes using the method we just saw. And I've given you scaffolding for the expected structure. You just need to fill it in with those type functions and then write a little bit more code.

If, did anyone have particular questions about this exercise or difficulties? Otherwise, I think we'll move on. I'm not going to go over it. But let me know if you have, if you ran into problems. The question about recommending checks to extract the corrected data, I think in general, like the model seeing information where there is none is a problem. And can be sort of difficult to get around if it really thinks there's going to be information when it is in fact not there. But I think you could try to fix it with better prompting. Or you might have like, you know, some kind of deterministic check either before it gets to the model or after. Like you might have if you're writing a general normal R function, even if there is no LLM. But I would say in general we sometimes run into problems where if information does not match the model's expectation or information isn't there and it thinks it's going to, that can be a problem for LLMs.

So, it's about an hour and a half. We're just going to take a three minute break before we get to the next section in case you want to stand. Yeah, it's almost been an hour and a half. Stand up or get some water or anything. I'll be answering questions in the chat, though. Or you're welcome to come off mute and ask questions during this time. We'll come back at 1253.

So, next we're going to talk about prompt writing or prompt engineering. So, prompt writing is a large part of working with LLMs because many times the work of getting

LLMs to do what you want them to do is figuring out how to give them the context they need in order to be useful. And so, one of those bits of context that you can give them is written information in the form of prompts. We already did this a little bit. So, we're just going to talk about it in a little bit more detail.

Okay. Just as a reminder, you can use the system prompt argument to pass system prompts to the chat object in ellmer. So, here we just put some prompt in a string and then we pass it to the system prompt argument of chat.

And usually you'll want to actually put your prompt in an external file, probably a markdown file, and then read it in and pass that to the system prompt instead of putting everything as a string and storing it in an object.

Exercise five: prompt design

Okay. So, in the fifth exercise, there are some note files stored in an object and your job is to edit the prompt MD file to instruct the model to organize the notes in a certain way each time. You can make up a format that you want, but it might be something like, you know, always with the date at the top, have a summary sentence at the top, put everything in bullets, something like that. So, play around with the prompt in order to get it to organize the notes in the way that you want and run all of the notes through the system prompt.

Did it work, Sarah?

I looked away. Did my R session? Okay. I think it worked. I think I just restarted R and it it worked. So, I've seen this before. That means that you got disconnected from instruct, but to get reconnected, you have to go to the screen where it said open external window. Okay. Maybe try refreshing that, though. Yeah. Thanks.

I'll just I'll try reopening from the link.

Sorry. Do you mind resending the new new instruct link?

Everyone else, I'm monitoring the stats for instruct, but we really have no control over it, but it says their performance is yellow out of green, yellow, red.

Working for you. I just sent you another link that I think will work for you. Thanks.

You'll have to manually clone in the git repo. Yeah, I also have, I have everything locally too, so I can, I can do that.

Did anyone have any particular problems with the prompting?

I think I have an environment that's working. Will this have keys built in?

Yes, but now that I think about it, they're through Amazon Bedrock, so you might want to paste in an API key. I'll work on that when we do the next exercise, as long as no one had particular questions about getting this going.

Tips for getting LLMs to do what you want

So, in general, if you're trying to get an LLM to do what you want it to do, sometimes it's just not possible to get it to fully do what you want it to do. But to start, you might ask yourself three questions, which is, one, do you use the best model or the best model for your use case? And if not, try using a better or more recent model, because they really can vary a lot. In the prompt, did you really clearly explain what you want the model to do?

And into this process, is very similar in many ways to explaining how to do something to another person or documenting a process. And so you can kind of evaluate if you clearly explained, in much the same way as you would evaluate if you clearly explained to another person.

And then the third question is, did you provide examples of what you want? Generally, it's very helpful to add examples into the system prompt.

Okay, if you've done those three things and it is still not doing what you want, you might need to look into something else or it might not be really possible to get it to perform as you want it to do. Okay, but these, if you answer these three questions, you've done these three things, that can really help a lot and take care of a lot of the sort of model misbehaving that people often see.

Okay, and you might ask, like, what you should put in the system prompt versus the user prompt, meaning, like, the prompt that you're sending to the model, you know, each time you send a request. And the short answer is that if you have any instructions or background knowledge, put that in the system prompt. This is going to be something that guides the entire conversation, and so you want to put that in the system prompt. You don't have to send it each time.

Okay, a couple other tips. You can use LLMs to help you draft or improve your prompts. Cloud is a prompt generator. This can generally be helpful if you're new to prompt writing. Structure is also really helpful. Again, this is not that different from, you know, writing a set of instructions for another person. So you can use markdown headings or XML tags to give structure to your prompts. You can also use variables to insert dynamic content into your prompt. So, for example, like, if you have content in another file where that file is changing or you want to conditionally read something, you can use variables. You do need to be aware of something called prompt injection, which is where you could insert malicious information into a prompt, get the LLM to do stuff you don't want. In general, you want to make sure you're doing this with files that you trust and have, you know, verified do not have anything malicious in them. And you often generally need to be aware of what files your LLM has access to.

Okay. A couple other tips. Like I said, generally you want to get prompts out of your code and into separate files. This is easier for you to read. It's easier to read diffs if you're using version control. It's easier to organize. It might not make that much difference with the LLM, but it's going to make a difference for you as the person and anyone you're collaborating with.

It can also be really helpful to force the model to say things out loud. We're going to talk about tool calling in a bit, but you might only want it to do three rounds of that. And if you force it to say that out loud to itself or to add it in a memo to itself, it's sort of like its internal monologue, it can help it actually obey the rules that you're telling it to obey. On the ellmer website, there's a vignette about prompt design. And this is really helpful if you're thinking about running prompt. So, I encourage you to take a look at that.

Tool calling

Okay. Any questions about prompt writing? Cool. So, now we're going to talk about tool calling.

And we just saw how to add knowledge with prompting. And so, now we're going to talk about how to add abilities with tools. And this is another way to make LLMs useful for you. So, prompting is generally for adding knowledge. Tools are for adding abilities that LLMs don't have out of the box. So, before we talk about tool calling, we're going to take a look at how LLMs actually work and clarify what they actually can and can't do because it can be a little confusing.

So, on their own, do you think LLMs can access the internet, run code, or send an email? We can try it out. Here's a chat where we're just asking Claude Haiku what the weather is like. I think the implication is that right now in San Francisco.

And we're going to get a response where it basically says, I don't have access to that, and I can't tell you. And that's because LLMs don't actually have access to real time data. They don't have a built in internet connection. They were trained up until a particular date, and that is the boundary of their knowledge. And anything past that, they don't know about.

And what about doing something to affect the world, like writing a file to your computer? So, what if we ask it to write a data frame to a CSV file? Again, it's going to say, I can't really do that. Because LLMs can't affect the world. They can't change your environment or any environment. And at this point, you might be thinking, like, this can't possibly be true. I know that these things can do it. I'm told they can do everything for me. And you're saying they can't even tell me the current weather. And the distinction is that on their own, LLMs cannot do this. But they can if you give them the right tools. So, we're going to see how to do that with ellmer.

And the distinction is that on their own, LLMs cannot do this. But they can if you give them the right tools.

A tool is essentially a function and metadata. And if you've ever written in R function and then documented it, this is basically the same thing. If you've written in R function and written documentation, you can write a tool. It's basically the same process, just with an LLM as the intended audience instead of another person.

Okay. So, we can use tools to do things like bring real time or up-to-date information to the model or let the model interact with the world. So, we're going to see how to implement tools in R with ellmer.

I'm going to go over this at a high level, and then we'll take a closer look at the specifics of how to do this. So, the first step is just to write or find an R function that carries out your desired functionality. So, let's say we want to make a tool so that the LLM can access information about the current weather. We can write a function get weather. We would have some code in there that returns the weather for latitude and longitude. And then the second step is to document that function for the LLM. In ellmer, you do this with the tool function, and we pass it the name of the function. And again, like documentation, this is you're annotating the function for the model. We're giving it a description, and we'll give it the arguments. I'm going to show you the exact code in a bit. This is just at a high level what's happening. You write a function, you document it for the LLM. And then the third step is to register the tool. This tells your chat object about the tool so the LLM knows it exists and can request to use it when it thinks it is required.

Okay. So, let's take a look in a little bit more detail. So, again, the first step is to create a function that does our desired functionality. This function is a little silly because we're just wrapping an existing function, but I wanted to show you how this would work. So, we're going to use the weather package to get a forecast for a lat and long. Then we annotate it with the ellmer function tool. So, we pass it the function, a description, just what it does, and the arguments. You might recognize these type functions from when we talked about structured output. These are the same functions. We're giving a type for each argument so the LLM knows exactly what to do when it requests that the tool be run. Okay. And then, finally, we register it. And, again, this is telling the model that the tool exists and it's available for use.

This is just that simplified a little bit. Like I said, that code was a little bit weird because we wrapped an existing function. If you want to use a function that already exists, you don't have to do that. You can just do what I did here and do an inline anonymous function, but you don't need to worry about this if that sounds complicated. But let's try it. So, I've registered the tool. The chat object knows about it. Now when we ask what the weather is like right now, it is going to actually give us a useful response. So, let's go through this. Notice that it has this tool call at the start. So, we know that it did a tool call. It called get weather with a lat and long. The weather API returned weather information to the LLM. And then the model was able to incorporate that information into its response. So, we just gave the model a new ability.

And again, we're reusing these type functions that we saw earlier to specify the type for each argument. And these functions specify object types in a way that it's easy for the LLM to understand, which is why we keep using these and they're in ellmer. And so, you just choose the type for your argument. And there are type functions for all of the types. And you can see the full list on the ellmer website.

How tool calling works under the hood

Okay. So, we saw how this works in R. But let's talk a little bit about how it actually works. So, again, as we keep seeing, you have a conversation. You send a message as the user to the LLM. And let's say we're, again, asking about the weather. And let's say that the LLM has access to our weather tool already. It knows that it does not have access to realtime weather data. And it knows it has access to the tool. So, it is going to choose to request that it be called. So, it decides to use the weather tool. And it specifies the code to run.

But one of the, like, main things to know about tool calling is that the LLM itself is not running the code. When we register a tool with ellmer and chat and the LLM calls it, that's running in your own R session. The tool, like, it's essentially like you ran it, you know, in your console or something. But the LLM has requested that it be run. The code doesn't run, you know, in the cloud or on Anthropic servers or something. Okay. So, the LLM specifies which code to run. So, it's saying it gives a particular latitude and longitude that it knows from its knowledge base.

ellmer takes that tool call request, actually executes it in your R session, querying the weather API. The weather API sends back some data. That goes to the LLM. And then it's able to incorporate that in its response. Again, ellmer is taking care of all of this communication for you. So, you don't really even see that this happens because it's handling it all under the hood.

ellmer takes that tool call request, actually executes it in your R session, querying the weather API. The weather API sends back some data. That goes to the LLM. And then it's able to incorporate that in its response. Again, ellmer is taking care of all of this communication for you. So, you don't really even see that this happens because it's handling it all under the hood.

Okay. So, again, the LLM cannot run code by itself. It just requests that tools be run. If when for these exercises, the code is being the tool code is being run in your session in the instruct environment, it's not being run in the LLM itself because they can't run code.

And instead, the LLM has control over two things. When the tool is called. So, it has the ability to understand, you know, the user is asking for the weather. I'm going to use the weather tool and pick between tools if it has the option. So, it controls when the tool is called as well as how the tool is called. And this mainly means which arguments it's passing in.

And so, this is really sort of like the power behind tool calling is that it can do those two things pretty well. So, again, the LLM is choosing these arguments. I didn't tell it the latitude and longitude for San Francisco. It has that knowledge already and it's able to pass it in. If our function had more arguments, like a time, it would also be able to use its knowledge and knowledge of what the user is requesting to pick the right arguments.

Okay. If you already have a function and it is documented, so this might be, you know, a function from a package or built in or one that you've created and documented yourself, you can use the ellmer helper function create tool def which will spit out this tool definition for you so you don't have to type all of that. And even if it's just a starting point, this can be really helpful.

Exercise six: tool calling with health data

So, now you're going to try tool calling yourself. So, there's some health expenditure data in your folder. So, your job is to write a get country spending function that takes a country name and a year and returns health spending by purpose. There's some code started already for you in there. And then you're going to wrap it as a tool and register it like we saw and then try it out by asking the model about spending for a specific country.

If anyone's logging back into instruct and they get a request for email verification, if you can take a screenshot of that and put it into the chat, I could take that. That'd be useful for me. That's a new behavior. To be honest, it sounds like instruct is dealing with some sort of attack. I feel like we're stress testing instruct or something.

I can run all the code locally, but it'll take me a minute to get this set up. It didn't work when I entered the code and also the workbench instances. Or it's working, but I would have to reinstall the packages.

So, what we'll do is, for those of you who were able to run the code, do you have, does anyone have any questions or get stuck? I realize that this is mostly, you know, you trying to get instruct to work. But we can, I'm going to keep going here, and then if we get an environment that is set up and working for me, at least, I can go back and talk about some of the exercises and show the remaining demo.

Okay. So, these are questions to ask yourself if you were able to get this code to work. So, we can still talk about these sort of abstractly. So, the point of this exercise was to write a tool where the model can specify a year and a country. It gets back a bit of data, and then it's able to incorporate that data into its response. And so, the first question is, does the model have access to the underlying data? And no, it does see the result of the tool call, but it doesn't actually have access to that underlying data frame. Instead, what it's doing is just passing in a country name and a year and then getting a result back. And again, the model is just controlling when to use the tool and what arguments to pass to the function.

And then, I think there is actually a lot that we could do to make this setup better. This is sort of a minimal tool setup. One thing you might do is give the model access to the list of country and years available so it doesn't have to guess and think through how this function could fail and do some just sort of normal function writing to throw errors if that happens. And error messages, just like they're helpful for people, can also be helpful for the algorithm. It is going to see that response. If it requests a tool be run and it gets an error, it's going to see the error message. So, again, a lot of this with tool writing and prompt writing, there is a lot of overlap for doing these things for people and documenting things well, writing code that is easy to use, error messages that make sense, all of that. Those are transferable skills when working with LLMs.

Okay. If you want to give your chat the ability to search the web, ellmer does have built-in web search tools so you don't need to write these from scratch. This is just sort of for your awareness. You can use these built-in web search tools. This is the Cloud one but there's ones for the other providers as well.

What's coming next

Okay. So, we have three sections left. So, up to this point, we've been mostly talking about how to use ellmer to access and program with LLM APIs from R. We're going to shift gears a little bit and first talk about query chat, which is a way to build shiny apps for your data where you can use natural language to query your data and update your app. Then we'll talk about coding agents and then finally we'll talk a little bit about privacy and security with LLMs.

So, what is query chat? So, query chat is another Posit package. It lets you explore tabular data in a shiny app using natural language, meaning you can just chat to ask questions about your

data or update the app. So, instead of writing your own SQL or D player code, you're just asking questions about the data in plain English. I just want to say for this part, you really don't need to know shiny at all. If you're going to make your own query chat app and customize things, you probably need to know a bit about shiny. But for this section, you really don't. We want to show you query chat because we think it is really useful and also a good example of how to make something that uses an LLM that is constrained, takes advantage of what LLMs are, and takes advantage of what LLMs are good at. So, you really just need to know that shiny is packaged for making web apps in R.

Again, query chat, we're going to explore the data with natural language built on top of LLM and shiny and it works with data frames or a database connection if you have it. This is kind of abstract. It's going to be easier to take a look. So, I have, I think someone mentioned this works for them when they ran it. This should work for you to run it yourself if you have instruct working. But this is an example query chat dashboard. So, I'm going to point out a couple things. So, we have a chat in the sidebar here. And this kind of looks like a normal shiny app. We have some value boxes, plots, a map. And then here it's showing us some SQL code. So, if you don't know SQL, this is basically saying take all the columns, everything from the table Airbnb data.

Query chat demo

What query chat is nice for is that you can ask questions about the data in the chat or request that the dashboard be updated to only show a subset of the data. So let's do that second one first.

And save only this, again this is Airbnb data, show only superhost listings with more than 100 reviews.

And first, you pay attention to that the app itself updated. So based on what I said here, it's now only showing superhost listings with more than 100 reviews.

The nice thing is that it shows you the exact SQL that it wrote here and up here. And if we want to see what underlying data was created, we can click table.

So we can do things like that where it's going to filter the data for us and then update everything in the app.

But we can also ask questions generally about our data. So I think if we ask it this, it is not going to update the app, but run a query, get a result, and then give us the answer right in the chat here. elegant and edgy loft has the highest occupancy rate.

Great. So this is really cool. We'll talk a little bit about how it works. So the, again, you can ask a question in natural language, and then the LLM is writing a SQL query. We just have been talking about how tool calling works. And so this is a tool it has access to. And again, it's going to specify what SQL to write, but it's not actually running the code itself. It's in a tool, it requests that tool be run, the tool is run, it gives it back the results. And so because the setup, the LLM is never touching your raw data directly.

Yeah, so again, the LLM isn't executing the query, the database is executing the query, but the LLM is saying which SQL to write based on your question. And then it's giving you back the real filtered data.

And this sort of constrained approach has a variety of benefits. Um, the first is that the database is running the query, and not the LLM or like the LLM running it somewhere else, which means that the numbers are not hallucinated, it's just SQL that's being run, and then the result is being brought back into the chat or into the underlying data.

It's safe, this is query chat is limited to read only query, so it is not going to delete the tables in your database. It's reproducible, you saw that the SQL, you can see the SQL, this can be exported, it can be rerun. And I think these, like if you can make something with an LLM that has these three benefits, that's like you made a great thing with an LLM basically, because all of these three things can often be difficult to get. LLMs are non-deterministic, they can do unsafe things if unlimited, and often the things that they do are non-reproducible, again, because they're, you know, non-deterministic, or they aren't running code to get the answers or something like that.

And I think these, like if you can make something with an LLM that has these three benefits, that's like you made a great thing with an LLM basically, because all of these three things can often be difficult to get.

Okay. So I'm going to show you how to make a really simple query chat app. This is basically the simplest version that you can make. It's just one function from the query chat package, which is query chat app, and then you pass it your data frame. So again, query chat is built on Shiny, but you don't really need to know Shiny to run this code and get a sense of what query chat can do. So you'll run this in just a second.

Okay. And before you take a look at it yourself, let's just take a closer look at how it works under the hood. So again, the LLM, I guess even before this diagram starts, you ask the LLM a question, it takes in that question, and based on what you're saying, devises a SQL query to either answer your question or change the data in a way that will help you answer it, sends that SQL query to the database, database executes the query, and then that updates the underlying data in the app, which is why the tables and the plots and those value boxes all change. If you are familiar with Shiny, what it's doing is updating reactives, and so when it filters it, the data is updated, all of the UI in the app is updated.

Okay. It turns out this is really powerful, and you can do all sorts of things with this format.