today's video is going to be all about llms langing and SQL. Looking back at 2023, chatting with data was one of the most in-demand applications of llms. Frameworks like Lang chain and llama index made it seamless to connect to SQL databases and ask basic questions about your data. And the key here is basic because once we move from basic to more advanced queries, the chain or agents tend to break, leaving many early adopters demoralized. But something to keep in mind is that the road to full automation goes through semi-automation. So here's what we're going to do in this video. I'm going to take a step back on the automation spectrum and take a look at how we can generate Advanced SQL queries in a robust way with Lang chain and one of the high IQ llms gbt 4 and CLA 3 Opus. Instead of using the out-of-the-box chains and agents, I'll show you how to assemble the components using langin expression language. And this is going to work for any SQL database. I'm also going

to address the elephant in the room, managing the risks involved with text to SQL. Do you really want an LLM-based agent run wild on your database? I'm using Google BigQuery to shed some light on this issue. The most dangerous database of them all. We'll get to why later in this video, but first let's see how we connect to a database like BigQuery or any SQL database with Langchain. All right, so here I am in the Colab notebook. I have a bunch of libraries that I'm going to install. I have Python, to fetch the API keys, Langchain, Anthropic. I also have SQLAlchemy and Google Cloud BigQuery that allows me to connect to BigQuery. I have my JSON key with the service account credentials and the ENV file with the API keys. So, I'm first going to load the API keys from the ENV file that allows me to connect to OpenAI and to Anthropic so that we can use the models from both services. Now, the first thing we're going to do is

that we're going to use gbt 4 and and clot 3 Opus to create a simple SQL chain. So I'm first going to import SQL database from Lang chain that allows me to connect to a database. And then I'm going to connect to Google bit query. My project is called rabid promotion. And then I have two data sets in that project that we're going to look at. One is called gpq chat, the other one is called gpq chat symbol. And then I'm specifying the path to the Json key that allows me to connect to bigquery. And I'm building the URL using the project, the data set, and the path to the key. And let's just have a look at the data sets in the big create console. Here we have tbq chat symbol. This is the Intensive features I've used in an earlier video. It's a simple data set without any nested structures. And here in gpq chat, we have an e-commerce data set. That's a more realistic data set with nested

structures. We have repeated records. We have three tables: customers, orders, and products. And we're going to see how the language models perform when we try to query this data set. All right, so I'm going to start out with GPQ chat symbol, and then I'm going to connect to the database using SQL database from URL, and then the URL I just specified. And then I can print out the tables in this data set using get usable table names. And here we have the one table in this data set: incentives. And if I try to do the same thing with the other data set, GBQ chat, then we can see that we get the three tables: customers, orders, and products. So now that we have established a connection to the tables, we can chat with the tables. So I'm going to instantiate two LLMs. I'm going to use GPT-4 and CL3 Opus. And then I'm also going to import create SQL chain from Lang chain chains. And this allows us to use Lang chains out-of-the-box functionality to build

chains for chatting with databases. I'm going to set up the chain. I'm going to start with dpt4, and then I'm going to invoke the chain asking how many customers are in the data set. This will give me a clean seal response that I can then run using the connection or the engine. And all we're doing here is really generating SQL with the language model, and then we're using a wrapper around SQL Alchemy to connect to the database and fetch the data. And as you can see, Lang chain SQL database is just using SQL Alchemy under the hood. Now let's try Claud 3 Opus. Just going to switch out the llm in the create SQL chain. And here we're also getting a response, but it's actually not as good as what we're getting with gbt4. First of all, we're not getting clean SQL. We're getting that SQL query before the SQL statement, but this is a passing problem. It's something that can be dealt with in L chain. And of course, you can't run the

quer now, which is a bit annoying, but I think the interesting part here is the SQL statement you see here. That Clod Opus is just counting rows, whereas GBT 4 was counting distinct emails. Now let's get to the real data set. So, I'm going to connect to GBT chat with the three tables: customers, orders, and products. And then I'm going to ask Clod 3 Opus the same question: how many customers are in the data set? And now we get into trouble. As you can see, Clod 3 Opus has difficulties dealing with array structures. And I can do the same thing with GBT 4, and here we get a similar error, problems with array struct. Now, this is just one of many error types you're going to face when you start dealing with real data sets. And that's because real data sets are far more complex than data sets that are usually being used to test out these LLMs. Will have nested arrays, we'll have data partitioning or other types of partitioning of the databases. And this is

a problem for even the high IQ llms. So if you head over to Lang chains SQL documentation, you can see there are basically two ways to interact with a database: either by using a chain or an agent. Agents are supposed to be a more flexible way to interact with the database, first of because it can understand the database schema, and because it can recover from errors. So let's instead try to use an agent on the gbq chat dataset. To do that, I'm going to import create SQL agent from agent toolkits in the Langin community library. Then I'm going to set up an agent executor using gbt 4 and the database connection. And then we simply invoke the agent executor with a question or prompt. In this case, we're going to ask for the number of customers in the dataset, like we did with the chain. This is going to take a little while to run, 10-20 seconds maybe. We can see that it counts distinct IDs from the customer table and it finds that there are 14 customers, which is

correct and we can do the same thing with CLA 3 opus. I'm just going to run the claw 3 agent executor in a different cell so that I can compare the outputs. If I run the CLA 3 Opus agent executor, I also get the correct results. However, note that the agent is still counting rows in the table and not distinct IDs, so it's making an assumption about the customer's table. So, it seems like the agents are slightly more robust in this case. They understand the scheme of the tables. So, let's take this a step further. Obviously, we don't just want to count rows in a table. We want to extract insides from the data set. So, I'm going to give the agent executor a more advanced prompt. I'm going to ask for the names of the five customers with the highest spend. And now, as you can see, I get a database error. And again, it's the same problem with the array struct. So, even the agent cannot overcome this. And this was CLA 3 Opus. I'm just

going to change the llm so we can see if gb4 can do this. And then I'm just going to invoke the agent executor again, and I get the same error as I did with CLA 3 Opus. So this is not really useful at all. And gbq chat is a data set that has a structure that is approximately the same as a real-world data set, but e-commerce data sets that you're going to be facing if you're working with e-commerce businesses are going to be even more complicated than this. So we need something different. We need something more robust. But before I'm going to go into how we can achieve that, I want to briefly talk about how to manage the risks associated with having an llm access your SQL database. So broadly speaking, you're going to be facing four types of risk. You'll have operational risk. You'll have data privacy risk. You will have security risk. And with a database service like biery, you're going to have financial risk. Let's start with operational.

risk. Operational risk manifests when llms by mistake disrupt system operations or data integrity. For example, an llm with file system access might delete important files. And one way to mitigate this is to make sure that service account credentials don't allow the agent to delete tables. Now, what about data privacy risk? Llms can access and disclose sensitive information within files or the databases they query. And this poses a significant risk, especially with regulations such as GDPR that govern data privacy. And the most straightforward way to deal with this is to actually take control over what information the llm gets to see. So when you're using an llm to generate SQL, you don't necessarily have to feed the output of the query back to the llm. And if you do feed back the output of the query, you can always sanitize the data, remove personal identifiable information (PII). Security risk occurs when llms can potentially interact with databases in a manner that compromises security. For instance, gaining unauthorized access to confidential information. And to mitigate that, use sandboxing. Make

sure that your agent only has access to the tables and data sets that it needs access to. Finally, in the case of biery, we have Financial Risk. The pay-as-you-go model of cloud-based databases like Google biery introduces Financial Risk, particularly if your LLM initiates extensive or inefficient queries that result in high data processing costs. The Google Cloud platform allows you to manage this risk. You can use Google Cloud codos to cap your spending on Individual Services. Alternatively, before you have the agent interact with the database, you can have it execute so-called dry runs that let you estimate the costs of a particular query. If you're working with bit, especially if you let your LLM access bitre, putting in these safety nets is absolutely essential because the costs you can incur are significant. So these are just some of the risks that you need to think about and take into account when you're building these LLM-based text-to-SQL, text-to-inside Solutions. What I'm going to show you now is a more pragmatic and robust approach.

that actually mitigates some of these risks and seems to be working better than the out-of-the-box solutions. I'm still using Lang chain components, and I'm going to assemble the components using Lang chain expression language. I'm just using what's on under the hood of what we just saw, and one of the things I'm going to do is I'm going to separate the generation of the SQL from the execution of the SQL. I'm just going to import Bitay from Google Cloud, and I'm going to import service account from Google or 2 so that I can execute the SQL generated by the chain and also fetch the schemas needed to generate the SQL in the first place. I'm setting the credentials using the JSON key we used before and the service account path, and then I'm instantiating the Google Bit Crate client using those credentials and the project ID. Now I can query the data sets using the Google Bit Crate client, and I can have the results returned in a data frame. So in order to be able to generate

SQL code beyond the basics. We need to feed the LLN detailed schema information. And I've written two functions that allow me to extract this information from B. The first of these two functions builds a schema description. It recursively generates a detailed description of a table schema, including nested fields. The second function fetches the schemas. It iterates over all the tables within a specified data set in B and uses the build schema description function. Now, these two functions allow me to fetch what I need to feed to the chain that I'm going to assemble. And I'm just taking control over what the SQL agent is doing under the hood so that I know that the chain is fed the right information. Now, if I run the fetch schemas function with the full data set ID and the B Ray client, I get a description of the schemas in text format. Next, I'm going to formulate my prompt using a chat prompt template. And I'm saying that based on this big schema below, write a SQL query that answers the user's questions. And then we'll fill in the question in a

placeholder and I'm also going to inject the schema information in the prom template using a runnable pass through, and to set up the chain, I'm going to need two things. I'm going to need string output passer and runnable pass through, and now I can use the fetch schemas function that I defined before to compose a langin runnable. Let me just explain how that works. Langin uses what's called a runnable protocol, which is an interface that allows you to assemble chains quickly. Suppose you want to set up a chain that allows you to write a story about any concepts. Then I will set up a template injecting the concept as a placeholder, and I'll Define my prompt from the template. Now I have a prompt and I have my language model LL mgbt 4, and I can compose these two components into a new runnable, and I do that using the pipe operator. And then I can invoke this runnable with the key I'm injecting into the template. So in this case, I'm now creating a store about a

frog, but sometimes we want to add extra keys to the template or somehow modify the input with an extra key. And we can use what's called a runnable pass-through for this. So, in this case, I'm using a runnable pass-through to add in an extra keyword, in this case, green, so that I'm modifying the concept I want to write a story about. So now, I write a story about a green frog instead of just a frog. So in our case here, we have the SQL response, which is a runnable, and we're using a runnable pass-through to add the extra key schema, the schema information, to the prompt. And the last component we're using here in the SQL response is just the string output passer that takes the output of an LLN and turns it into a string. Now, let's set up this SRET response with GPT-4 and then invoke it with the question. So I'm going to start out by asking how many customers are in the data set, and I'm going to ask it to only return SQL.

and we see that we get the right response. And if I use cloud for this, you see that I get the same thing as I did before. That still, this difference db4 is using distinct while Claud is counting the rows. I'm going to go back to GPT 4, and now I'm going to ask something more complicated. I'm going to ask the language model to use the created at timestamp in the orders table to understand how often customers are placing orders and to understand the recency of the orders. And then I'm adding in an extra line here in the prompt telling the model what the project is called, what the data set is called, and what the tables are called. You should probably put this in the get schemas function as well. And here we see that gb4 is returning nice clean SQL without any errors, and we can just take this clean SQL response and execute it in the bck ray client to get a pandas data frame. I'm just going to do the same thing with CLA 3.

so I'm going to switch out the llm in the SQL response, and then I'm going to run everything again. And here we also get a clean secret response that we can execute with the big Ray client. And you probably notice that there are differences in the generated SQL. One is using a join CLA is using a join, and gb4 was using a right join. So when you're using llms like this, you still need to understand what's going on in the SQL code. You still need to make sure that the llms are not making logical mistakes. So let's just execute the response. And here we have the data frame with the data. So with this approach, we can go from natural language to data frame in less than 5 seconds. So not only will this speed up your data analysis process, it also allows you to build your own custom text to inside Solutions using Lang chain. All right, that's it for now. If you enjoyed this video, like and subscribe. Thanks for watching.

Robust Text-to-SQL With LangChain: Claude 3 vs GPT-4

Learn the nuts and bolts of AI, Automation & Analytics with Ecommerce & Marketing use cases.