Integrate QueryData with an application

This tutorial describes how to set up and use QueryData in AlloyDB for PostgreSQL using the Google Cloud console and integrate it with your application. Learn how to build the context set file, create a context set that uses the context set file, use MCP Toolbox to call the QueryData API to generate SQL queries for natural language questions, and integrate it with your application.

For more information, see QueryData overview .

Objectives

Create database tables and populate them with data.
Build a context set file with Gemini CLI and MCP toolbox.
Create a context set and upload context set file.
Test QueryData and generate SQL queries in Studio.
Integrate QueryData with your application using Gemini Data Analytics QueryData tool in MCP Toolbox.
Add grounding to LLM responses using value search queries.

Costs

In this document, you use the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use the pricing calculator .

New Google Cloud users might be eligible for a free trial .

To avoid continued billing, delete the resources you created when you finish the tasks in this document. For more information, see Clean up .

Before you begin

Complete the following prerequisites before creating a context set.

Enable required services

Enable the following services for your project:

Prepare an AlloyDB for PostgreSQL cluster, instance, and database

Make sure that you have access to an existing AlloyDB cluster and instance or create a new one .
This tutorial requires you to have a database in your AlloyDB instance. For more information, see Create a database .

Required roles and permissions

Add an Identity and Access Management (IAM) user or service account to the cluster at the database level. For more information, see Manage database users .
Grant the alloydb.databaseUser , the serviceusage.serviceUsageConsumer , and the geminidataanalytics.queryDataUser roles to the IAM user at the project level. For more information, see Add IAM policy binding for a project .

Grant `executesql` permission to AlloyDB for PostgreSQL instance

To grant the executesql permission to the AlloyDB for PostgreSQL instance and set the data_api_access instance setting to the value ALLOW_DATA_API , use the following curl command :

curl -X PATCH \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json" \
     https://alloydb.googleapis.com/v1alpha/projects/ PROJECT_ID 
/locations/ LOCATION 
/clusters/ CLUSTER_ID 
/instances/ INSTANCE_ID 
?updateMask=dataApiAccess \
     -d '{
       "dataApiAccess": "ENABLED",
     }'

Replace the following:

PROJECT_ID : The ID of your Google Cloud project.
LOCATION : The region where your AlloyDB cluster is located.
CLUSTER_ID : The ID of your AlloyDB cluster.
INSTANCE_ID : The ID of your AlloyDB instance.

To perform steps in this tutorial, sign in to Google Cloud , and then authenticate to the database using IAM authentication.

Create the `flights` and `airports` schema and tables

In this section, you create the flights and airports database tables for this tutorial.

In the Google Cloud console, go to the AlloyDB page.

Go to AlloyDB
Select a cluster from the list.
In the navigation menu, click AlloyDB Studio.
Sign in to Studiousing Identity and Access Management authentication.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
Click New SQL editor tabor New tabto open a new tab.

To create the airports table and schema, execute the following SQL statement:

  CREATE 
  
 TABLE 
  
 IF 
  
 NOT 
  
 EXISTS 
  
 airports 
  
 ( 
  
 id 
  
 INT 
  
 PRIMARY 
  
 KEY 
 , 
  
 iata 
  
 TEXT 
 , 
  
 name 
  
 TEXT 
 , 
  
 city 
  
 TEXT 
 , 
  
 country 
  
 TEXT 
  
 );

Create the flights table and schema:

  CREATE 
  
 TABLE 
  
 IF 
  
 NOT 
  
 EXISTS 
  
 flights 
  
 ( 
  
 id 
  
 INT 
  
 PRIMARY 
  
 KEY 
 , 
  
 airline 
  
 VARCHAR 
 ( 
 10 
 ), 
  
 flight_number 
  
 INT 
 , 
  
 departure_airport 
  
 VARCHAR 
 ( 
 5 
 ), 
  
 arrival_airport 
  
 VARCHAR 
 ( 
 5 
 ), 
  
 departure_time 
  
 TIMESTAMP 
 , 
  
 arrival_time 
  
 TIMESTAMP 
 , 
  
 departure_gate 
  
 VARCHAR 
 ( 
 10 
 ), 
  
 arrival_gate 
  
 VARCHAR 
 ( 
 10 
 ) 
 );

Populate the `flights` and `airports` tables

In this section, you populate the flights and airports tables using the provided SQL scripts.

Populate the airports table.

See SQL script

  -- Insert Data into Airports 
 INSERT 
  
 INTO 
  
 airports 
  
 ( 
 id 
 , 
  
 iata 
 , 
  
 name 
 , 
  
 city 
 , 
  
 country 
 ) 
  
 VALUES 
 ( 
 3249 
 , 
  
 'BOS' 
 , 
  
 'General Edward Lawrence Logan International Airport' 
 , 
  
 'Boston' 
 , 
  
 'United States' 
 ), 
 ( 
 3257 
 , 
  
 'OGG' 
 , 
  
 'Kahului Airport' 
 , 
  
 'Kahului' 
 , 
  
 'United States' 
 ), 
 ( 
 3259 
 , 
  
 'MCI' 
 , 
  
 'Kansas City International Airport' 
 , 
  
 'Kansas City' 
 , 
  
 'United States' 
 ), 
 ( 
 3263 
 , 
  
 'PHX' 
 , 
  
 'Phoenix Sky Harbor International Airport' 
 , 
  
 'Phoenix' 
 , 
  
 'United States' 
 ), 
 ( 
 3270 
 , 
  
 'SFO' 
 , 
  
 'San Francisco International Airport' 
 , 
  
 'San Francisco' 
 , 
  
 'United States' 
 ), 
 ( 
 3285 
 , 
  
 'LAX' 
 , 
  
 'Los Angeles International Airport' 
 , 
  
 'Los Angeles' 
 , 
  
 'United States' 
 ), 
 ( 
 3287 
 , 
  
 'CLE' 
 , 
  
 'Cleveland Hopkins International Airport' 
 , 
  
 'Cleveland' 
 , 
  
 'United States' 
 ), 
 ( 
 3295 
 , 
  
 'EWR' 
 , 
  
 'Newark Liberty International Airport' 
 , 
  
 'Newark' 
 , 
  
 'United States' 
 ), 
 ( 
 3296 
 , 
  
 'BOI' 
 , 
  
 'Boise Air Terminal/Gowen Field' 
 , 
  
 'Boise' 
 , 
  
 'United States' 
 ), 
 ( 
 3303 
 , 
  
 'DAL' 
 , 
  
 'Dallas Love Field' 
 , 
  
 'Dallas' 
 , 
  
 'United States' 
 ), 
 ( 
 3315 
 , 
  
 'KOA' 
 , 
  
 'Ellison Onizuka Kona International At Keahole Airport' 
 , 
  
 'Kona' 
 , 
  
 'United States' 
 ), 
 ( 
 3321 
 , 
  
 'DCA' 
 , 
  
 'Ronald Reagan Washington National Airport' 
 , 
  
 'Washington' 
 , 
  
 'United States' 
 ), 
 ( 
 3336 
 , 
  
 'SLC' 
 , 
  
 'Salt Lake City International Airport' 
 , 
  
 'Salt Lake City' 
 , 
  
 'United States' 
 ), 
 ( 
 3350 
 , 
  
 'IAH' 
 , 
  
 'George Bush Intercontinental Houston Airport' 
 , 
  
 'Houston' 
 , 
  
 'United States' 
 ), 
 ( 
 3370 
 , 
  
 'PIT' 
 , 
  
 'Pittsburgh International Airport' 
 , 
  
 'Pittsburgh' 
 , 
  
 'United States' 
 ), 
 ( 
 3376 
 , 
  
 'MIA' 
 , 
  
 'Miami International Airport' 
 , 
  
 'Miami' 
 , 
  
 'United States' 
 ), 
 ( 
 3377 
 , 
  
 'SEA' 
 , 
  
 'Seattle Tacoma International Airport' 
 , 
  
 'Seattle' 
 , 
  
 'United States' 
 ), 
 ( 
 3382 
 , 
  
 'LGB' 
 , 
  
 'Long Beach /Daugherty Field/ Airport' 
 , 
  
 'Long Beach' 
 , 
  
 'United States' 
 ), 
 ( 
 3385 
 , 
  
 'IND' 
 , 
  
 'Indianapolis International Airport' 
 , 
  
 'Indianapolis' 
 , 
  
 'United States' 
 ), 
 ( 
 3402 
 , 
  
 'LIH' 
 , 
  
 'Lihue Airport' 
 , 
  
 'Lihue' 
 , 
  
 'United States' 
 ), 
 ( 
 3421 
 , 
  
 'SAT' 
 , 
  
 'San Antonio International Airport' 
 , 
  
 'San Antonio' 
 , 
  
 'United States' 
 ), 
 ( 
 3426 
 , 
  
 'RDU' 
 , 
  
 'Raleigh Durham International Airport' 
 , 
  
 'Raleigh-durham' 
 , 
  
 'United States' 
 ), 
 ( 
 3436 
 , 
  
 'TUS' 
 , 
  
 'Tucson International Airport' 
 , 
  
 'Tucson' 
 , 
  
 'United States' 
 ), 
 ( 
 3444 
 , 
  
 'BUR' 
 , 
  
 'Bob Hope Airport' 
 , 
  
 'Burbank' 
 , 
  
 'United States' 
 ), 
 ( 
 3445 
 , 
  
 'DTW' 
 , 
  
 'Detroit Metropolitan Wayne County Airport' 
 , 
  
 'Detroit' 
 , 
  
 'United States' 
 ), 
 ( 
 3470 
 , 
  
 'DFW' 
 , 
  
 'Dallas Fort Worth International Airport' 
 , 
  
 'Dallas-Fort Worth' 
 , 
  
 'United States' 
 ), 
 ( 
 3473 
 , 
  
 'AUS' 
 , 
  
 'Austin Bergstrom International Airport' 
 , 
  
 'Austin' 
 , 
  
 'United States' 
 ), 
 ( 
 3478 
 , 
  
 'STL' 
 , 
  
 'St Louis Lambert International Airport' 
 , 
  
 'St. Louis' 
 , 
  
 'United States' 
 ), 
 ( 
 3482 
 , 
  
 'ATL' 
 , 
  
 'Hartsfield Jackson Atlanta International Airport' 
 , 
  
 'Atlanta' 
 , 
  
 'United States' 
 ), 
 ( 
 3487 
 , 
  
 'FAT' 
 , 
  
 'Fresno Yosemite International Airport' 
 , 
  
 'Fresno' 
 , 
  
 'United States' 
 ), 
 ( 
 3514 
 , 
  
 'IAD' 
 , 
  
 'Washington Dulles International Airport' 
 , 
  
 'Washington' 
 , 
  
 'United States' 
 ), 
 ( 
 3517 
 , 
  
 'MKE' 
 , 
  
 'General Mitchell International Airport' 
 , 
  
 'Milwaukee' 
 , 
  
 'United States' 
 ), 
 ( 
 3520 
 , 
  
 'PDX' 
 , 
  
 'Portland International Airport' 
 , 
  
 'Portland' 
 , 
  
 'United States' 
 ), 
 ( 
 3528 
 , 
  
 'HNL' 
 , 
  
 'Daniel K Inouye International Airport' 
 , 
  
 'Honolulu' 
 , 
  
 'United States' 
 ), 
 ( 
 3531 
 , 
  
 'SAN' 
 , 
  
 'San Diego International Airport' 
 , 
  
 'San Diego' 
 , 
  
 'United States' 
 ), 
 ( 
 3534 
 , 
  
 'ONT' 
 , 
  
 'Ontario International Airport' 
 , 
  
 'Ontario' 
 , 
  
 'United States' 
 ), 
 ( 
 3547 
 , 
  
 'MDW' 
 , 
  
 'Chicago Midway International Airport' 
 , 
  
 'Chicago' 
 , 
  
 'United States' 
 ), 
 ( 
 3551 
 , 
  
 'DEN' 
 , 
  
 'Denver International Airport' 
 , 
  
 'Denver' 
 , 
  
 'United States' 
 ), 
 ( 
 3552 
 , 
  
 'PHL' 
 , 
  
 'Philadelphia International Airport' 
 , 
  
 'Philadelphia' 
 , 
  
 'United States' 
 ), 
 ( 
 3597 
 , 
  
 'JFK' 
 , 
  
 'John F Kennedy International Airport' 
 , 
  
 'New York' 
 , 
  
 'United States' 
 ), 
 ( 
 3607 
 , 
  
 'RNO' 
 , 
  
 'Reno Tahoe International Airport' 
 , 
  
 'Reno' 
 , 
  
 'United States' 
 ), 
 ( 
 3617 
 , 
  
 'SMF' 
 , 
  
 'Sacramento International Airport' 
 , 
  
 'Sacramento' 
 , 
  
 'United States' 
 ), 
 ( 
 3619 
 , 
  
 'COS' 
 , 
  
 'City of Colorado Springs Municipal Airport' 
 , 
  
 'Colorado Springs' 
 , 
  
 'United States' 
 ), 
 ( 
 3639 
 , 
  
 'PSP' 
 , 
  
 'Palm Springs International Airport' 
 , 
  
 'Palm Springs' 
 , 
  
 'United States' 
 ), 
 ( 
 3649 
 , 
  
 'BWI' 
 , 
  
 'Baltimore/Washington International Thurgood Marshall Airport' 
 , 
  
 'Baltimore' 
 , 
  
 'United States' 
 ), 
 ( 
 3658 
 , 
  
 'MSP' 
 , 
  
 'Minneapolis-St Paul International/Wold-Chamberlain Airport' 
 , 
  
 'Minneapolis' 
 , 
  
 'United States' 
 ), 
 ( 
 3661 
 , 
  
 'MSY' 
 , 
  
 'Louis Armstrong New Orleans International Airport' 
 , 
  
 'New Orleans' 
 , 
  
 'United States' 
 ), 
 ( 
 3667 
 , 
  
 'SNA' 
 , 
  
 'John Wayne Airport-Orange County Airport' 
 , 
  
 'Santa Ana' 
 , 
  
 'United States' 
 ), 
 ( 
 3676 
 , 
  
 'CLT' 
 , 
  
 'Charlotte Douglas International Airport' 
 , 
  
 'Charlotte' 
 , 
  
 'United States' 
 ), 
 ( 
 3677 
 , 
  
 'LAS' 
 , 
  
 'McCarran International Airport' 
 , 
  
 'Las Vegas' 
 , 
  
 'United States' 
 ), 
 ( 
 3678 
 , 
  
 'MCO' 
 , 
  
 'Orlando International Airport' 
 , 
  
 'Orlando' 
 , 
  
 'United States' 
 ), 
 ( 
 3742 
 , 
  
 'MRY' 
 , 
  
 'Monterey Peninsula Airport' 
 , 
  
 'Monterey' 
 , 
  
 'United States' 
 ), 
 ( 
 3743 
 , 
  
 'SBA' 
 , 
  
 'Santa Barbara Municipal Airport' 
 , 
  
 'Santa Barbara' 
 , 
  
 'United States' 
 ), 
 ( 
 3875 
 , 
  
 'RDD' 
 , 
  
 'Redding Municipal Airport' 
 , 
  
 'Redding' 
 , 
  
 'United States' 
 ), 
 ( 
 3876 
 , 
  
 'EUG' 
 , 
  
 'Mahlon Sweet Field' 
 , 
  
 'Eugene' 
 , 
  
 'United States' 
 ), 
 ( 
 3878 
 , 
  
 'MFR' 
 , 
  
 'Rogue Valley International Medford Airport' 
 , 
  
 'Medford' 
 , 
  
 'United States' 
 ), 
 ( 
 3880 
 , 
  
 'RDM' 
 , 
  
 'Roberts Field' 
 , 
  
 'Redmond-Bend' 
 , 
  
 'United States' 
 ), 
 ( 
 4111 
 , 
  
 'ACV' 
 , 
  
 'California Redwood Coast-Humboldt County Airport' 
 , 
  
 'Arcata CA' 
 , 
  
 'United States' 
 ), 
 ( 
 4419 
 , 
  
 'CEC' 
 , 
  
 'Jack Mc Namara Field Airport' 
 , 
  
 'Crescent City' 
 , 
  
 'United States' 
 ), 
 ( 
 4460 
 , 
  
 'SBP' 
 , 
  
 'San Luis County Regional Airport' 
 , 
  
 'San Luis Obispo' 
 , 
  
 'United States' 
 ), 
 ( 
 4768 
 , 
  
 'PSC' 
 , 
  
 'Tri Cities Airport' 
 , 
  
 'Pasco' 
 , 
  
 'United States' 
 ), 
 ( 
 5088 
 , 
  
 'SUN' 
 , 
  
 'Friedman Memorial Airport' 
 , 
  
 'Hailey' 
 , 
  
 'United States' 
 ), 
 ( 
 5305 
 , 
  
 'ASE' 
 , 
  
 'Aspen-Pitkin Co/Sardy Field' 
 , 
  
 'Aspen' 
 , 
  
 'United States' 
 ), 
 ( 
 5362 
 , 
  
 'MMH' 
 , 
  
 'Mammoth Yosemite Airport' 
 , 
  
 'Mammoth Lakes' 
 , 
  
 'United States' 
 );

Integrate QueryData with an application

Objectives

Costs

Before you begin

Enable required services

Prepare an AlloyDB for PostgreSQL cluster, instance, and database

Required roles and permissions

Grant `executesql` permission to AlloyDB for PostgreSQL instance

Create the `flights` and `airports` schema and tables

Populate the `flights` and `airports` tables

See SQL script

See SQL script

Create a context set in Studio

Test QueryData in Studio

Generate context for the context set

Set up your environment

Generate template context

Generate value search context

Upload context set file to the QueryData

Generate SQL query using QueryData

Integrate QueryData with your application

Iterate agent performance

Refine response accuracy

Multiple agents

Add a secure access layer using SQL views

Clean up

Delete the context set

Delete the cluster

What's next

Integrate QueryData with an application Stay organized with collections Save and categorize content based on your preferences.

Objectives

Costs

Before you begin

Enable required services

Prepare an AlloyDB for PostgreSQL cluster, instance, and database

Required roles and permissions

Grant executesql permission to AlloyDB for PostgreSQL instance

Create the flights and airports schema and tables

Populate the flights and airports tables

See SQL script

See SQL script

Create a context set in Studio

Test QueryData in Studio

Generate context for the context set

Set up your environment

Generate template context

Generate value search context

Upload context set file to the QueryData

Generate SQL query using QueryData

Integrate QueryData with your application

Iterate agent performance

Refine response accuracy

Multiple agents

Add a secure access layer using SQL views

Clean up

Delete the context set

Delete the cluster

What's next

Integrate QueryData with an application

Grant `executesql` permission to AlloyDB for PostgreSQL instance

Create the `flights` and `airports` schema and tables

Populate the `flights` and `airports` tables