Querying structured data: Text-to-SQL
1. Querying structured data: Text-to-SQL
Welcome back. We covered the basics of how Text to SQL allows us to ask questions and get accurate answers from our structured data in an earlier video. So let's go into more detail here before we start building our applications in the following modules. So let's start with the basics of Text to SQL and build from there. So with Text to SQL, we first give our LLM a natural language question, such as how much money did we make last year before costs? Now, if this was a question that was asked of an analyst that had just started at a company, that person would immediately need to know a bunch of information to answer that question. They need to know about the database and the tables in the database to have a chance of answering the question given to them. But before they even thought about writing their SQL query, they'd also need to know information about the schema. They'd need to know all of the column names in all of the tables and what the tables represent to know where to look in these tables and columns to find the information that they need to answer the question. They'd need to decipher whether the profits column represents the net or gross profits or something else entirely. From there, they'd need to know what types of data are in each column of each table, and they'd need to know which table or tables have the information that they need to query to answer the question. And this is why analysts are so valuable. They understand the data, and they're able to answer questions using the data and discover new insights. But what if we could automate this so that the user could interact with the database through an LLM? Good thing, then, that we can do this. Imagine for a second that I am someone who prides themselves on delivering valuable insights to people. One morning, my boss storms into my office looking frustrated and asks, why are last week's sales numbers dropping in the southwest region? I tell my boss that I'll look into it. I open up my SQL editor and start crafting this query from scratch. A couple of hours later, I hand the results, and after a quick glance, my boss asks, is there a specific product or service category, or is it across the board? I head back to my SQL editor. So after building another bespoke query, and then another couple of hours later, I head back to my boss and present what I discovered. Similar to last time, my boss scans the results and asks another question. How does this compare to last year, and what's the trend over the last three years? At this point, everyone is starting to feel frustrated. I'm becoming frustrated with having to constantly craft bespoke queries, and my boss is frustrated that the answers keep taking so long to get. So let's talk about dashboards for a second. Dashboards are very popular business intelligence tools, but they only display what they are built to display. So if someone has a question that the dashboard does not answer, they get up and head over to someone like me to ask the question. This can result in me becoming very busy. At this point, I might decide to build a dashboard, but this presents a new problem, death by dashboard. Everyone wants to avoid a death by dashboard situation where data is stale, unresponsive to changing conditions, or does not answer the questions that people are really asking. When this happens, users either do not use the dashboards or are frustrated using them. This isn't just about slow queries, it's about a gap in agility. Both of these structures cause a lag between asking a question and actioning on the answer. Decision makers are stuck, waiting to get the information that they need to make decisions while the business keeps moving forward. Let's take a look at how we can avoid this fate by empowering people with natural language tools that deliver accurate information on a much shorter timeline. Text-to-SQL is an approach that allows users to interact with structured data using natural language. It offers another solution to the hallucination problem by allowing us to ground LM responses with information from structured data sources. So let's talk about the Text-to-SQL steps. To successfully generate SQL, the LM needs to solve a range of challenges from correctly interpreting the question, understanding the schemas of the data, and the ability to generate correct SQL. It needs to be able to do this even for rare and complex cases like nested subqueries or window functions. Conceptually, an effective Text-to-SQL system will solve these challenges and navigate the gray area of understanding and how to best answer the user's question with the schema and data available, just like a human analyst would. Let's look at the key components of a Text-to-SQL system. First, we need to understand the question, which consists of semantic parsing and handling ambiguity. Semantic parsing breaks down the natural language into its logical components. Examples of this are intent, entities, and relationships. Handling ambiguity addresses multiple interpretations of queries by clarifying dimensions such as time or location. At this time, the system might also assess if the question is reasonably answerable and in scope, or if it should decline to answer. Then the system must map the components of the question to the schema of the database being used. If the system does not understand the structure of the database it's querying, it will return incorrect answers to the questions that users ask. To overcome this, the model uses schema comprehension and contextual mapping. Schema comprehension deals with identifying tables, columns, and the relationship between these, while contextual mapping is where the model associates user intent with specific tables and columns. An example of this could be mapping sales to a specific column in a revenue table. So once the model understands what the user is asking, understands the relationships, and knows the locations of the data that it requires, it needs to generate the SQL query. Here, we get into query formulation and validation, where the model translates the user query into valid and syntactically correct SQL that will return the correct information. Once this is all done, the resulting SQL needs to be executed for the user to see results. These results may be displayed in a table or a chart that the user can easily understand. So applications that use Text-to-SQL enable users to directly query their data and get responses that are highly precise. These answers can then be used to power BI applications, or they can be used to ground LLM generation in conversational apps. Really flexible tools. In this video, we looked at an overview of the basics of Text-to-SQL and how we get accurate responses from our structured data. Being able to ask questions to an LLM in natural language and have the LLM generate accurate and executable SQL to return a table or chart is a game changer. Think about all of the ways you could apply this. So in the next video, we'll move on to the meat of the course, but there are two more important steps before we get there. Please go through the short reading after this video to set up your account and your notebook environment. If you've taken our Intro to Generative AI course, we've improved a couple things and made it even easier to create a new notebook. After you finish the readings, we'll talk quickly about the concrete projects you'll build in the course, discuss goal setting, and then we'll dive into the practical modules of this course. I'll see you then.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.