Chatbot or Conversational app with relational/structed data in Bigquery

[CynosCloud]

Hello, 

I have been trying to build a chatbot or a conversational app using apps or Agents from Agent Builder. But it seems like there is no out of the box solution for connecting a chatbot to proper structured data in Bigquery. 

So I are trying to build a custom solution with the below steps - 

1. Create an agent to fetch to frontend with the users, fetch question from the user

2. Create a cloud function to use a LLM model (Gemini, text-bison, etc) to convert the question to SQL query and then query Bigquery with the resultant sql query. Convert sql result back to natural language. 

3. Throw the response back in the agent. 

Please let me know if there is any better approach for the requirement that I have. 

I have tried going through the community Q&A and the closest I found is the below link, where a similar type of problem is still unsolved. 

https://www.googlecloudcommunity.com/gc/AI-ML/Bigquery-DataStore-for-Agent-Builder-and-Gemini-API/m-... 

Thanks

[AndrewB]

@koverholt do you have any suggestions?

[koverholt · Accepted solution]

Thanks for the question! There's a few ways to implement this depending on your end goals. Here are some options that we commonly see developers take to build an agent + BQ functionality:

If you want to work directly with the Gemini API and BQ API with the highest level of customization, I built an sample app on how to accomplish this with Gemini + Function Calling to interact with data in BigQuery using pre-defined tools that interact with the BQ API and generate SQL queries: take a look at the source code and associated demo app.

If you want to work at the LangChain tool and retriever layer, you can use the LangChain retriever for Vertex AI Search (optionally with the BQ loader and/or Vertex AI chat integrations) to pull records from a data store created in Vertex AI Builder (but that's not directly compatible with Agent Console, as you mentioned). This way you can still make use of the easy indexing of BQ data in a data store, while maintaining control of the conversation flow and app logic in your Python / LangChain code.

If you want to make this work within the Agent Console tool, then you'll need to define a tool via a OpenAPI spec, function call, or Vertex AI Extension that interacts with BigQuery from your agent.

[CynosCloud]

Thanks @koverholt for the suggestions.