Crafting Complex SQL Queries with Generative AI Assistance

Introduction

SQL, or Structured Query Language, has been the standard for interacting with relational databases since it was first developed at IBM in the 1970s. In the decades since, SQL has become an essential tool for businesses across every industry. The ability to store, manage, and analyze large datasets with SQL databases powers everything from financial reporting to marketing analytics to scientific research.

However, while SQL is incredibly powerful, it also has a reputation for being difficult to learn and use, especially for non-technical professionals. Writing SQL queries requires an understanding of database schemas, join logic, aggregations, and other concepts that are not always intuitive. As a result, in many organizations, a small data team becomes a bottleneck for analytics and reporting needs across the company.

But recent breakthroughs in artificial intelligence are starting to change this dynamic. A new class of AI tools powered by large language models (LLMs) are making it possible to interact with databases using natural language rather than SQL syntax. With AI-powered SQL assistants, the ability to ask and answer questions from data could soon be accessible to people across an organization, not just specialized data practitioners. In this article, we‘ll take a closer look at how generative AI works and walk through an example of using a tool called Vanna AI to query a database using natural language.

Generative AI and Large Language Models

Generative AI refers to artificial intelligence systems that can generate new content – text, images, audio, code, etc. – rather than just analyzing or acting on existing data. One of the most powerful types of generative AI is large language models or LLMs. LLMs are trained on massive amounts of text data to build statistical representations of language. Given a text prompt, they can then generate fluent continuations in the style of the prompt.

In the last few years, AI labs have developed LLMs with incredible natural language abilities by training them on web-scale datasets. Models like GPT-3 from OpenAI, PaLM from Google, and others have shown the ability to engage in freeform dialogue, answer questions, and even write coherent essays – all from open-ended text prompts. Developers are now fine-tuning these base models and using techniques like prompt engineering to create powerful applications for specific domains.

Some of the most exciting applications have been in the realm of data and analytics. By training LLMs on SQL syntax, database schemas, and domain-specific data, it‘s possible to create AI systems that can interpret natural language questions and automatically write the corresponding SQL code to answer the question. Rather than writing SQL directly, users can simply ask a question as if they were talking to a human analyst. The AI then does the work of figuring out the relevant tables and columns, joining data, and aggregating results.

Several SQL assistant tools powered by LLMs have emerged to make this vision a reality. For example, Vanna is an AI agent that can connect to a database and answer questions using natural language. You train Vanna on the specifics of your data, and it learns to interpret queries and generate SQL code. Similar tools include SQL Genie for querying databases with natural language and Speak2SQL for automatically generating SQL from text descriptions.

The potential for these AI SQL assistants is immense. They could enable professionals across an organization – salespeople, marketers, product managers, executives, etc. – to directly ask questions and get insights from company data without needing to involve data specialists. Business intelligence could shift from a centralized, request-driven model to a decentralized, self-serve paradigm. Domain experts would be empowered to test hypotheses, build reports, and discover insights with company data on their own.

Tutorial: Using Vanna to Query a Database with Natural Language

To illustrate the power of AI SQL assistants, let‘s walk through a concrete example of using Vanna AI to answer questions about a sample database. We‘ll be using the Chinook database, which contains information about a fictional digital music store, including tables for artists, albums, tracks, invoices, and more.

The first step is to install the Vanna Python package and create an API key. You can install Vanna with a simple pip command:

pip install vanna

Then import the package and use the vn.get_api_key() function to create your login:

import vanna as vn

api_key = vn.get_api_key(‘[email protected]‘)
vn.set_api_key(api_key) 

Vanna allows you to create multiple AI agents, each trained on a specific database. For this example, we‘ll use the pre-trained Chinook demo agent. To activate it, we use the vn.set_model() function:

vn.set_model(‘chinook‘)

Next, we need to connect our AI agent to the actual Chinook database. Vanna supports connecting to several different types of databases, including SQLite, Postgres, MySQL, Snowflake, and more. The Chinook database is available as a SQLite file. We can connect to it by providing the URL to the file:

chinook_url = "https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"
vn.connect_to_sqlite(url=chinook_url)

With our model activated and database connected, we‘re now ready to start asking questions. The vn.ask() function allows us to query the database using natural language. For example, let‘s ask for the top 5 genres by sales revenue:

vn.ask("What are the top 5 genres by sales revenue?")

Vanna will generate the corresponding SQL query to answer the question, execute the query on the database, and return the results in a Pandas DataFrame. It even generates a visualization of the data and suggests some relevant follow-up questions!

Behind the scenes, Vanna is using its LLM to interpret the intent of the question and generate SQL code matching the Chinook database schema, like:

SELECT 
    g.Name AS Genre,
    SUM(il.UnitPrice * il.Quantity) AS Revenue 
FROM 
    Genre g
    JOIN Track t ON g.GenreId = t.GenreId
    JOIN InvoiceLine il ON t.TrackId = il.TrackId
GROUP BY 
    g.Name
ORDER BY 
    Revenue DESC
LIMIT 5;

We can ask Vanna to explain its reasoning by passing verbose=True to the ask() function:

vn.ask("What are the top 5 genres by sales revenue?", verbose=True)

This will return the generated SQL query along with a natural language explanation of the steps Vanna took to answer the question. The verbose output is a great way to understand how Vanna is interpreting questions and to debug any unexpected results.

Of course, questions can get much more complicated than this simple example. We might want to ask things like:

• "Which artist had the most tracks sold in 2022?"
• "Show me the total number of Jazz tracks sold in each country."
• "What percentage of total revenue came from each genre last quarter?"
• "Break down the average revenue per customer by employee for customers in the United States."

The power of AI SQL assistants is that we can ask these types of analytical questions without having to worry about writing the corresponding SQL queries ourselves. The LLM-powered agent handles the complexity of joins, aggregations, filters, and subqueries behind the scenes.

The Importance of Training Data

Vanna was able to generate valid SQL for the Chinook database out-of-the-box because it was pre-trained on that specific dataset. But to use Vanna or any other AI SQL assistant with your own database, you‘ll need to train it on your particular data and schema.

The vn.train() function allows you to feed examples of natural language questions and corresponding SQL queries so that the agent can learn the specifics of your database. You can also provide the agent with data catalog information like table and column descriptions to help it interpret user intent.

The quantity and quality of training data is really the key factor that determines the accuracy and reliability of an AI SQL assistant. Careful prompt engineering to generate a diverse set of example questions covering key use cases, edge cases, and error handling will result in a more robust agent. Investing time to thoroughly train your AI agent upfront will pay dividends in the long run in the form of better answers and less manual debugging.

Current Limitations and Future Potential

While AI SQL assistants are incredibly promising, they are still a new technology with some limitations. Accuracy is a challenge, especially with very large and complex databases. The LLMs that power these tools can sometimes "hallucinate" results, generating SQL queries that look plausible but return incorrect results or even nonsensical output. Careful testing is paramount when implementing these tools.

The user experience for AI SQL assistants is also still immature. Most only return results as raw DataFrames or simple charts at the moment. Ideally these tools would integrate with existing business intelligence platforms and support interactive visualizations and dashboarding. More robust access control systems are also needed to apply governance policies and user permissions to AI-generated queries.

However, despite these early limitations, the future potential for generative AI in the world of databases and analytics is immense. As the underlying LLMs continue to improve in their reasoning and language understanding abilities, the accuracy and flexibility of AI SQL assistants will grow. These tools have the potential to truly democratize data within organizations – empowering every knowledge worker to make data-driven decisions.

Importantly, AI SQL assistants aren‘t likely to replace the work of data analysts and data scientists entirely, but rather to augment them. Conducting the initial training of an AI agent will itself require deep knowledge of SQL and databases. Human experts will still be needed to monitor the outputs of AI-generated queries, to handle more advanced use cases, and to integrate AI into analytics workflows.

Conclusion

Over the past few years, advances in natural language AI in the form of large language models have opened up exciting possibilities for eliminating barriers to working with data. By serving as an intelligent interpreter between human users and SQL databases, AI SQL assistants like Vanna can enable more people within organizations to directly answer ad-hoc questions with data.

As we‘ve seen, the combination of pre-trained LLMs with custom training data allows these AI agents to understand the intricacies of a particular database and reliably generate complex SQL queries from natural language questions. The potential to improve the speed and scale of data-driven decision making across companies is immense.

While the technology is still nascent, it‘s improving rapidly. The main bottleneck is now the curation of high-quality training data to ensure the AI reliably returns accurate results. With careful prompt engineering to train the models, we can create bespoke AI SQL assistants tuned to the needs of our organizations.

Looking forward, it‘s not hard to imagine a future where AI becomes the primary interface for interacting with company data and KPI dashboards get replaced by simple chat interfaces. Knowledge workers across teams will be able to ask questions of datasets as easily as if they were talking to a human analyst. Data teams will spend less time fielding ad-hoc request tickets and more time on higher-level analysis, governance, and training AI to enable true self-serve analytics.