Check out the new Gemini API Cookbook and our community forum.

Build an AI data exploration agent with Gemini

Build an artificial intelligence (AI) powered chat interface for your business system programming interfaces and let your colleagues ask questions about your business data. Doing analysis and reporting on your organization's data is essential for improving how you do business. Finding problems, discovering trends, and investigating results are all possible with the right data, but as a developer, helping your non-coding colleagues discover the right data can be challenging.

The SQL Talk project is an open source application that uses generative AI technology to answer questions about business data. The project uses the Gemini API and the Function Calling feature to transform business data questions into SQL queries and API calls, and then transform results back into plain language. You can use this project as a starting point for building a data agent for your own business, and help your colleagues get answers without you having to write a bunch of single-use code.

For a video overview of the project and how to extend it, including insights from the folks who build it, check out AI Data Agent | Build with Google AI. Otherwise, you can get started extending the project using the following instructions.

SQL Talk project application user interface

Figure 1. SQL Talk project application user interface.

Project setup

These instructions walk you through getting the SQL Talk project setup for development and testing. The general steps are creating the SQL Talk project, setting up a Google Cloud Shell Editor development project, getting a Google Cloud Project ID, and using a script to configure the project settings. These instructions describe how to set up the project using the Cloud Shell Editor, which makes setting up, running and updating the project quick and convenient.

Create a Cloud Shell instance for the project

You can set up the SQL Talk project by cloning the project repository into a Cloud Shell instance. This process sets up a virtual development environment instance inside the Google Cloud infrastructure for the project and clones the code repository into that virtual environment.

To create a Cloud Shell instance for SQL Talk:

Click the following link to create a Cloud Shell instance of SQL Talk.

This link sets up a Google Cloud Shell Editor instance and clones the repository for SQL Talk into the instance.

Google Cloud Shell Editor with the SQL Talk project code shown

Figure 2. SQL Talk project cloned into a Google Cloud Shell Editor development environment.

Get a Cloud project ID

The SQL Talk project uses a Google Cloud project ID to connect to Google Cloud services, including the Gemini models. You configure a project ID within the Cloud Shell Editor to allow the application to connect with these services.

To connect the Cloud Shell Editor to a project ID:

Navigate to the Google Cloud console and sign in, if needed.
Select an existing Cloud Project, or create a new one.
Make a note of the Cloud Project ID for the project.
Connect the Editor instance to your project and configure the project. In the Cloud Shell Editor window, terminal panel enter the following command:
```
gcloud config set project YOUR_PROJECT_ID
```

Configure the SQL Talk project

The SQL Talk project uses Google Cloud services to run the project, including the BigQuery database service and the Vertex AI API to connect to Gemini models. The SQL Talk project includes a setup.sh script that configures the project with the required Google Cloud services and starts the default version of the project.

To configure and run the project Cloud Shell Editor instance:

In the Cloud Shell Editor window terminal panel, navigate to the Cloud Shell SQL Talk (/sql-talk-app) project directory:
```
cd ~/cloudshell_open/generative-ai/gemini/function-calling/sql-talk-app
```
In the terminal panel, enter the following command:
```
bash setup.sh
```

When the setup script successfully completes, you should see a message similar to the following in the Cloud Shell Editor terminal window. This message indicates the setup process was successful and the SQL Talk application is running:

You can now view your Streamlit app in your browser.
Network URL: http://##.##.##.##:8080
External URL: http://##.##.##.##:8080

Test the project

Once you have completed setup of the SQL Talk project, you can test the application to verify it is functioning as expected. The setup script automatically starts the application when you run it, and you can restart the application using the following steps.

To run the SQL Talk application:

If the Cloud Shell Editor has been idle and disconnected, you may need to reconnect to your Cloud project ID.
```
gcloud config set project YOUR_PROJECT_ID
```
In the Cloud Shell Editor window terminal panel, navigate to the Cloud Shell SQL Talk project directory.
```
cd ~/cloudshell_open/generative-ai/gemini/function-calling/sql-talk-app
```

In the terminal panel, enter the following command.

~/miniforge/bin/streamlit run app.py --server.enableCORS=false \
    --server.enableXsrfProtection=false --server.port 8080

View the SQL Talk application. At the top right of the Cloud Shell Editor window, select the Web Preview button, and then Preview on port 8080.

Cloud Shell Editor header with Web Preview button highlighted

To view updates to the SQL Talk application:

In the SQL Talk web application Web Preview, reload the browser or browser tab.

To stop the SQL Talk application:

In the Cloud Shell Editor window terminal panel, type Ctrl-C.

Modify the application

You can change the behavior and add capabilities to the SQL Talk application by modifying the Python code for the application. This section describes how to add a new function call to the SQL Talk application.

The Gemini API Function Calling feature uses a specific syntax to define functions to be used by the generative model to answer questions or solve problems. This syntax does not need to precisely match the syntax of an actual API call. Instead, you use the Function Calling feature to force the generative model to provide specific data inputs or parameters that conform to the API call syntax, and then use those parameters to execute the actual API calls within your application code.

This example implementation shows you how to create a function call definition for listing recent database queries or jobs, and then map that definition to the actual API call within the SQL Talk application code.

Add a function call definition

Add a new function call for listing recent database queries or jobs. This definition is used by the generative model to understand what the API call does and what input parameters it requires. This example function definition is defined with no parameters.

To add a new function call to the application:

In the Cloud Shell Editor window, open the sql-talk-app/app.py code file.

After the list_datasets_funcfunction declaration, add a new FunctionDeclaration.

list_jobs_func = FunctionDeclaration(
    name="list_jobs",
    description="Get a list of the 10 most recent database requests to help answer the user's question",
    parameters={
        "type": "object",
        "properties": {
  },
},
)

Add the new function to the sql_query_tool object.

sql_query_tool = Tool(
    function_declarations=[
        sql_query_func,
        list_datasets_func,
        List_jobs_func,  # add this new function
        list_tables_func,
        get_table_func,
    ],
)

To add code to run the API call for the new function:

In the sql-talk-app/app.py code file, add a new if clause to the while function_calling_in_process:loop.

if response.function_call.name == "list_datasets":
    ...

# add this if clause for list_jobs function
if response.function_call.name == "list_jobs":
    api_response = client.list_jobs(max_results=10)  # API request(s)
    api_response = str([job.job_id for job in api_response])
    api_requests_and_responses.append(
      [response.function_call.name,params, api_response])

Additional resources

For more information about the SQL Query project, see the code repository and check out the Cloud Community blog post about the SQL Talk project. If you need help building the application or are looking to collaborate with other developers, check out the Google Developers Community Discord server, and the Google Cloud AI/ML Forum. For more Build with Google AI projects, check out the video playlist.