Generate SQL from questions in natural language using NLP2SQL

Google Cloud Community Articles
,
1

Editor’s note: This is the first post in a three-part series on how generative AI is changing how we interact with databases. This post introduces NLP2SQL, allowing you to query databases using natural language. Check out parts 2 and 3 for a specific approach and architecture for building an NLP2SQL solution.

Tabular data is one of the most common data formats across diverse domains. Tabular data is organized as rows and columns with each feature representing a specific feature(s). Relational databases are used to store and retrieve tabular data using strict code syntax based Structured Query Language (SQL).

Tabular data constitutes one of the most prevalent data formats across various domains. It is organized into rows and columns, with each feature representing specific characteristics. Relational databases serve as the primary mechanism for storing and retrieving tabular data, utilizing a stringent code syntax based on Structured Query Language (SQL). Extracting pertinent data and insights from this data necessitates database expertise. Consequently, ordinary users often find interacting with databases cumbersome, even for basic data retrieval, creating dependencies on the database team to furnish specific queries or endpoints with tailored functionality.

In this section, we will delve into the transformation brought about by Generative AI in the traditional method of database interaction, facilitating a natural language-based interface that simulates a human-like conversation.

Why NLP2SQL?

Generative AI has revolutionized the way humans interact with machines, enabling more natural and human-like communication. Through its contextual understanding capabilities, generative AI can process natural language-based text and execute operations accordingly, leading to significant advancements in human-machine interaction. One such application might be that of a “Talk to Database” or Natural Language to Structured Query Language (NLP2SQL) interface.

NLP2SQL aims to automate the process of translating human language queries to SQL through Natural Language Processing. NLP2SQL would enable non-technical users who lack the expertise in SQL to interact with the database. Instead of manually crafting complex SQL queries, technical Database users can express their intent in plain language, thus saving time and effort. This capability could also aid intelligent services for automated data analytics and conversational agents to respond to queries based on tabular data.

The same approach and solution can also be further extended to other SQL or NoSQL databases since the fundamental concept and logic remain consistent. However, for the purpose of this article, we are utilizing SQL generation as an illustrative example.

How large language models can help NLP2SQL?

The recent large language models (LLM) have shown immense potential in revolutionizing NLP2SQL translation. LLMs excel at the ability to grasp complex sentence structures, synonyms and implicit relationships between entities in natural language queries. LLMs are also proven to understand the database schema information that allows them to generate SQL queries that are not only syntactically correct, but also semantically aligned with the underlying database schema. LLMs like Gemini are trained on diverse datasets covering various domains and query styles allowing them to generalize well to unseen queries and adapt to specific domains without requiring extensive training.

There are several methodologies by which a Generative Artificial Intelligence (GenAI) model can be employed to address a specific use case. This principle also applies to NLP2SQL. The following are some specific techniques for generating Structured Query Languages (SQLs):

  • Prompt Engineering: Adopting prompt strategies like SQLPrompt are observed to improve the performance of SQL generation. Prompts can include information about the database schema, desired output format, and zero or few shot examples for the same.
  • Agent based SQL generation: Dedicated LLM based agents are created for specific tasks like reformulating the question, generating the SQL, validating the SQL. It can involve single or multiple agents in a workflow, each fulfilling a predefined role, from requirement collection to validating the generated outcomes before presenting them to the user.
  • Fine-tuning on Domain-Specific Data: Fine-tuning LLMs on domain-specific datasets can further improve their accuracy for particular use cases or industries.

In this series of articles, the central focus will predominantly lie on the exploration of Prompt Engineering and Agent methodologies from the perspective of their application in diverse use cases. Furthermore, the analysis will consider factors such as the complexity of SQL queries and the count of data tables requiring evaluation prior to the generation of a definitive response.

Challenges in NLP2SQL

  • Ambiguity of Natural Language: Natural language is inherently ambiguous and context dependent. A phrase can have multiple interpretations making it difficult to translate it into a precise SQL query.
  • Schema Awareness: Awareness of the relationships between the tables and columns along with the deeper understanding of descriptions of column fields is critical for NLP2SQL to work across different database schema.
  • Complexity of Queries and sub-queries: Natural Language queries can express complex concepts involving multiple tables, aggregations, joins and nested conditions. Handling these complexities would require advanced parsing and semantic understanding capabilities
  • SQL dialects: Dialects can have subtle differences in syntax, such as keywords, function names and data types. These variations could lead to errors or incorrect translations
  • Adherence to SQL guidelines and best practices: While there could be different ways of writing a SQL query, not all the ways would be an efficient and faster way to retrieve the data.

Above mentioned challenges can be largely addressed by the following practices:

  1. Improving the understanding of user intent in Natural Language queries and database schema to enrich the context in the prompt
  2. Improving the ways to understanding Adopting streamlined approaches to retrieve the database schema elements

Best practices for SQL generation prompts

Below are some of the guidelines and best practices with sample codes for prompt engineering for SQL generation:

  • Eliminate ambiguity in table design: Ambiguous data structures hinder LLMs. Address this proactively by designing clear and consistent tables and views.

    • Naming conventions: Start with clear, natural language-friendly schema: Use self-explanatory names and consistent conventions.
    • Descriptions: Provide column and table descriptions to describe each table and column as well as the expected values. For ex, column sales_2023_ne will have a detailed description as Sales for 2023 North East including states Massachusetts, New York, Connecticut, Vermont, Maine and New Hampshire
    • Views: For tables that are already defined, create domain-specific views for complex queries and aggregate relevant data for specific tasks.
    • Denormalized table: When applicable, use flattened table schema design to avoid multiple table joins.

  • Break down complex tasks for LLM success: LLMs excel at smaller, focused tasks. Split large, complex requests into meaningful subtasks to leverage this strength. Example: In the case of analyzing monthly revenue and identifying potential hot/risky products, instead of asking questions like “Summarize sales performance in Jan 2023”. You ask multiple questions to break down the analytics task. For example, you may breakdown the original question into smaller tasks:

Top 5 sold products in January 2023:

SELECT product, COUNT(*) AS sales
FROM sales_data
WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1
GROUP BY product
ORDER BY sales DESC
LIMIT 5;

Bottom 5 selling products in January 2023:

SELECT product, COUNT(*) AS sales
FROM sales_data
WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1
GROUP BY product
ORDER BY sales ASC
LIMIT 5;

Average product revenue for January 2023:

SELECT AVG(revenue) AS avg_revenue
FROM sales_data
WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1;

By decomposing complex tasks, you empower LLMs to deliver accurate and insightful results.

Effective prompt engineering is an iterative process. Experiment and refine prompts based on LLM responses to achieve optimal results.

  • Safeguard Your SQL database with multi-level protection and validation:
    LLMs can be inadvertently or intentionally manipulated to generate harmful SQL queries. Implement these safeguards to protect your database:

  • Defensive prompting: Explicitly instruct the LLM to avoid generating queries that delete, drop, or create null records.

  • Database-level access controls: Restrict allowed operations at the database or table level to prevent unauthorized actions.

  • Controlled environments: Test LLM-generated queries in a sandbox before executing them in production.

  • User reporting mechanisms: Empower users to report issues and train them on safe LLM usage.

  • Input and Output Validation:

    • Verify and filter both user input and LLM-generated queries for malicious content.
    • Use natural language understanding to identify potentially harmful input.
    • Check for suspicious characters, sequences, and SQL-specific operators.
    • Employ allowlisting with allowed characters and sequences.
    • Remember to continuously refine validation mechanisms to address evolving threats.

  • Continuously improve through feedback mechanisms: Feedback is essential for enhancing LLM accuracy and addressing shortcomings. Here’s how to establish an effective feedback loop:

  • Collect user feedback:

    • Implement user-friendly mechanisms for providing feedback on generated SQL statements (e.g., rating systems, comment boxes).
    • Encourage detailed feedback on errors, inaccuracies, or suggestions for improvement.

  • Expert review:

    • Engage domain experts to review feedback and identify actionable insights.
    • Prioritize feedback that highlights recurring issues or patterns to address common challenges.

  • Refine LLM performance:

    • Utilize feedback to create additional training examples that address identified issues.
    • Fine-tune or tune the LLM with the enhanced dataset to improve accuracy and performance.

Link to sample Code with prompt best practices for SQL generation.

Ready to Unlock the Power of Data with NLP2SQL?

The second part of our blog series examines a specific approach and reference architecture designed to harness the transformative power of Generative AI for database interaction. Explore the technical details and understand how this innovative solution can streamline your data workflows.

Contributors

Authors:

Other contributors:

2 Likes