ad
ad

Agent Builder with BigQuery (SQL Agent on Google Cloud) - Tutorial

Education

Introduction

In this tutorial, we will explore how to create a conversational agent using Google Cloud's Agent Builder that interacts with structured data, specifically from BigQuery. We will guide you through the process of setting up an agent capable of querying e-commerce data from a public dataset in BigQuery. Additionally, we will address the limitations of the search application and demonstrate how to implement a more effective SQL agent by leveraging natural language processing.

Getting Started with BigQuery

We will begin with the e-commerce dataset available publicly in BigQuery. This dataset includes various tables containing information like events, inventory, items, and orders. For instance, the orders table consists of data related to order status, shipment information, and more. Our objective is to create a chatbot that answers questions based on this data.

Creating an Agent

To get started, navigate to Agent Builder. When creating an app, you can choose either the chat or agent option. As you attempt to create a data store, you may notice there's no option for BigQuery. This limitation is because BigQuery can't be directly used for conversational applications.

Instead, you can set up a search application that utilizes BigQuery as a data store. Once configured, if you enter a search query, the results will be a list of rows matching your query. Although this provides a structured view of the data, it falls short of answering complex questions requiring aggregation or SQL joins.

Alternative Approach: Using APIs

To overcome the limitations of the search application, we can utilize an open API tool that calls a cloud function. In this case, we implemented a function that invokes the Vertex AI Search API to interact with our search application configured for the BigQuery dataset.

Here’s a concise explanation of how it works:

  • The function takes a search query and uses the discovery engine SDK to retrieve results from the BigQuery datastore.
  • These results are then sent back to the agent, allowing it to construct its response.

In Agent Builder's example, we can test the integration and simulate interactions. For instance, if a user types "shoes orders," the agent would return a response based on the retrieved search results.

Developing a Natural Language to SQL Agent

Despite the search functionality, there's a more efficient way to utilize BigQuery using an SQL agent. This method enables the agent to convert natural language questions into SQL queries.

Steps to Create a SQL Agent

  1. Design a Cloud Function: This function connects to specific tables in BigQuery. When it receives a natural language query, it generates an SQL statement using the Gemini model, formatted based on sample schemas and data.

  2. Reflective Feedback Loop: After generating the SQL, the function utilizes a second prompt to ensure the query accurately addresses the user's request.

  3. Output Results: The function runs the SQL against BigQuery and returns results in a JSON format, which the agent can then use to provide more structured responses.

Testing the SQL Agent

To demonstrate the functionality of the SQL agent, we created another agent in Agent Builder that incorporates the natural language to SQL tool. By asking questions, we can see the agent return formatted tables or numerical responses, depending on the query's context. This agent can also run standalone through a user interface created using Mesop, providing a more appealing interface for engagement.

Conclusion

In this tutorial, we've learned how to build an agent using Google Cloud's Agent Builder that interacts with structured data in BigQuery. By leveraging both search applications and an SQL-based service, we can create meaningful interactions that provide the user with relevant data.

Keywords

FAQ

Q1: What is Agent Builder?
A1: Agent Builder is a tool on Google Cloud that allows developers to create conversational agents that can interact with various data sources, including structured data.

Q2: Can BigQuery be used directly in chat applications?
A2: No, BigQuery cannot be directly used in chat applications for conversational responses. Instead, you can create search or SQL-based applications that interact with the data.

Q3: How can I process natural language questions into SQL queries?
A3: By using a cloud function that utilizes the Gemini model to convert natural language queries into SQL and then run those queries against your BigQuery dataset.

Q4: What are the limitations of the search application?
A4: The search application returns a list of rows based on search queries but lacks the capability to perform aggregations or joins necessary for answering complex questions.

Q5: How can I test the agents created using Agent Builder?
A5: You can simulate user interactions within the Agent Builder interface, along with testing specific cloud functions to see how they respond to queries.