Unlocking the Power of Natural Language: Building a Custom Text-to-SQL Agent with Amazon Bedrock and Converse API

Introduction

Developing robust text-to-SQL capabilities is a critical challenge in the field of natural language processing (NLP) and database management. The complexity of NLP and database management increases in this field, particularly while dealing with complex queries and database structures. In this post, we introduce a straightforward but powerful solution with accompanying code to text-to-SQL using a custom agent implementation along with Amazon Bedrock and Converse API.

Solution Overview

The proposed solution leverages an AWS Lambda function that acts as an interface between the user’s natural language queries and a relational database, ensuring efficient management and retrieval of data.

A Custom Agent Built Using Converse API

Explore how to create a custom agent, ConverseSQLAgent, that utilizes conversational features and tool selection for enhanced SQL query execution.

The Agent Loop: Planning, Self-Correction, and Learning

Delve into the key functionalities of the agent: planning, execution with tool use, self-correction through SQLAlchemy, and long-term memory learning.

Planning and Carry-Over

Learn how the agent formulates a comprehensive plan of execution to tackle user queries effectively.

Execution and Tool Use

Understand how the agent selects and utilizes various tools in executing SQL queries seamlessly.

SQLAlchemy and Self-Correction

Discover the utility of SQLAlchemy in error handling and self-correction within the agent’s operations.

Reflection and Long-Term Learning

Find out how the agent employs a hierarchical memory structure for continual learning and improved performance.

Prerequisites

A list of essential prerequisites to get started with the implementation of the solution.

Deploy the Solution

Step-by-step instructions for cloning the repository and deploying the solution in your environment.

Cleanup

Instructions for disposing of the stack after testing the solution.

Conclusion

Recap the significance of developing effective text-to-SQL capabilities and how the ConverseSQLAgent presents a revolutionary approach to streamline data queries using natural language.

About the Authors

Meet the authors who have contributed their expertise to design and implement this innovative solution.

Unlocking the Power of Natural Language with Text-to-SQL Solutions

Developing robust text-to-SQL capabilities stands as a critical challenge in the realm of natural language processing (NLP) and database management. As organizations strive to make data more accessible, the intricacies of NLP and the complexity of database structures can pose significant hurdles—especially when handling complex queries. In this post, we present a straightforward yet powerful solution: a custom agent implementation utilizing Amazon Bedrock and Converse API to transform natural language queries into SQL statements.

The Game-Changer in Data Interaction

The ability to convert natural language queries into SQL is revolutionary for businesses and organizations. This functionality allows users to interact with databases intuitively and effectively. However, translating human language into SQL is fraught with challenges, including diverse database schemas, the relationships between tables, and the subtleties of natural language. These complexities can lead to inaccurate or incomplete SQL queries, compromising data integrity and diminishing user experience.

Our custom agent implementation tackles these challenges efficiently. This agent can comprehend queries, develop a plan for execution, generate SQL statements, self-correct when errors occur, and learn from its interactions over time. With continued use, the agent evolves a nuanced understanding of user intents, enhancing its ability to provide accurate and meaningful SQL responses.

Solution Overview

The heart of our solution is an AWS Lambda function, which houses the logic of our agent. This agent communicates with various AWS services, including:

• Amazon DynamoDB for long-term memory retention
• Anthropic’s Claude Sonnet via Amazon Bedrock through the Converse API
• AWS Secrets Manager to securely manage database connection details and credentials
• Amazon Relational Database Service (RDS), featuring an example PostgreSQL database named the HR Database

To ensure privacy and security, the Lambda function connects to a Virtual Private Cloud (VPC) and leverages AWS PrivateLink for secure communication with DynamoDB, Amazon Bedrock, and Secrets Manager while accessing the RDS database.

In our demo, you can interact with the agent via the Lambda function by submitting natural language queries such as: “How many employees are there in each department?” or “What is the employee mix by gender in each region?”

A Custom Agent Built with Converse API

The Converse API, provided by Amazon Bedrock, supports the development of conversational applications, including tool use that enables a large language model (LLM) to choose appropriate tools based on the context of the conversation. In this implementation, we’ve developed a custom agent called ConverseSQLAgent, designed specifically for executing long-term tasks and following a defined plan of execution.

The Agent Loop: Planning, Self-Correction, and Long-Term Learning

The ConverseSQLAgent encompasses several important features:

1. Planning and Carry-Over:

   • At the outset, the agent creates a detailed execution plan for the text-to-SQL process, carefully considering the user’s request. This plan is guided by a system prompt that dictates the agent’s behavior. To maintain context over long interactions, the agent injects the initial plan back into the system prompt. As it progresses, it can update its plan based on new insights or necessary adaptions.

2. Execution and Tool Use:

   • After formulating a plan, the agent executes it step-by-step. It can call upon various tools at its disposal, which are defined in a structured toolConfig. Each tool has a specific purpose, and when a tool is used, it outputs a tool use block that the application processes accordingly.

3. SQLAlchemy and Self-Correction:

   • Utilizing SQLAlchemy, a powerful Python SQL toolkit, the agent can interact with multiple types of databases seamlessly. The InvokeSQLQuery tool allows the agent to execute SQL statements, with additional implementations like GetDatabaseSchemas designed to accelerate query generation. When errors arise during database querying, the agent employs self-correction techniques to rectify the query in real-time.
4. Reflection and Long-Term Learning:
   • Beyond immediate corrections, the agent learns from its experiences. It maintains a hierarchical memory system in Amazon DynamoDB, where its main memory points to various historical memories. By reviewing its past decisions and outcomes, it can optimize its future query efficiency and accuracy.

Prerequisites

Before diving into the implementation, ensure you have the following prerequisites:

• An AWS account
• Basic knowledge of SQL
• Familiarity with AWS services such as Lambda, RDS, and DynamoDB

Deploying the Solution

The complete code and step-by-step guidelines are available on GitHub. To clone the repository:

git clone https://github.com/aws-samples/aws-field-samples.git

Move to the ConverseSqlAgent folder and follow the instructions in the README file.

Cleanup

Once you’re done experimenting, remember to clean up your stack with the following command:

cdk destroy

Conclusion

Building robust text-to-SQL capabilities remains a significant challenge in NLP and database management. While current solutions have made strides, complexities in database structures and intricate queries still need robust handling.

Our ConverseSQLAgent, utilizing Amazon Bedrock and Converse API, offers a promising pathway to bridge the gap between natural language and structured queries. Its architecture facilitates effective understanding of user inquiries, execution of SQL statements, and continuous improvement of capabilities. As companies pursue more intuitive methods to manage data, solutions like the ConverseSQLAgent represent a monumental step forward, unlocking new potentials for productivity and informed decision-making.

About the Authors

Pavan Kumar is a Solutions Architect at AWS, passionate about designing scalable cloud solutions.
Abdullah Siddiqui is a Partner Sales Solutions Architect at AWS, specializing in resilience and migrations.
Parag Srivastava is a Solutions Architect at AWS, focusing on cloud adoption and digital transformation.

To delve deeper into generative AI and related technologies, check out the additional resources linked within our document. Transform your data interaction experience today!