Cloud BigQuery ML / Shell Open Source

bqml_vertex_gemini — BigQuery × Vertex Gemini

bqml_vertex_gemini demonstrates how to invoke Vertex AI Gemini models from BigQuery ML remote functions, enabling LLM inference directly on warehouse-scale data without moving it out of BigQuery.

View on GitHub ← All Projects

bash setup.sh --project YOUR_PROJECT --location us-central1
SELECT GEMINI_GENERATE(description) FROM `project.dataset.products` LIMIT 100

Features

Run Vertex AI Gemini LLM API calls directly from within BigQuery ML — no data movement, no external pipelines.

🤖

In-Database LLM

Call Gemini from a SQL SELECT statement — no Python, no ETL, no data movement.

☁️

Vertex AI Integration

Uses BigQuery ML remote functions to call the Vertex AI API with IAM-based auth.

⚡

Massively Parallel

BigQuery's distributed execution engine fans out Gemini calls across millions of rows in parallel.

🔄

Demo Included

Complete end-to-end demo: schema, remote function setup, sample queries, and result analysis.

Documentation & Architecture

This project demonstrates how to generate nutrition labels for food products using Google Cloud BigQuery and the Gemini Pro language model through BigQuery ML.

Prerequisites

Before you begin, ensure you have the following:

Google Cloud Account: You need an active Google Cloud account.
Google Cloud CLI (gcloud) Installed and Configured: Make sure you have the gcloud command-line tool installed and configured to connect to your Google Cloud account. You can find installation instructions on the official Google Cloud documentation. Ensure you have initialized the CLI using gcloud init.
BigQuery Enabled: The BigQuery API must be enabled in your Google Cloud project. You can enable it through the Google Cloud Console.
bq Command-Line Tool: The bq command-line tool, which is part of the gcloud CLI, should be available in your system’s PATH.
Vertex AI API Enabled: The Vertex AI API needs to be enabled in your Google Cloud project. You can enable it through the Google Cloud Console.
Google Cloud Project: sandboxportal: This project is specifically configured to use the project ID sandboxportal. Ensure your gcloud CLI is configured to use this project. You can set the default project using gcloud config set project sandboxportal.
BigQuery Dataset: sandboxdataset: A BigQuery dataset named sandboxdataset must exist within your sandboxportal project. You can create a dataset using the Google Cloud Console or using the bq command:
```
bq mk --dataset sandboxportal:sandboxdataset
```

Project Files

food_products.csv: Contains the source data for food products (e.g., brand names).
genai_food_labels.sql: The BigQuery SQL script that uses the ML.GENERATE_TEXT function with the Gemini Pro model to generate nutrition labels.
LICENSE: Contains the licensing information for the project.
load_source.sh: A shell script to load the food_products.csv file into the food_products BigQuery table.
README.md: This file, providing instructions and prerequisites for the project.
run_genai_food_labels.sh: A shell script to execute the genai_food_labels.sql script on BigQuery.
setup_bqml_vertex.sh: A shell script to set up the BigQuery ML model that uses Vertex AI’s Gemini Pro model.

Running the Project

Follow these steps to run the food label generation project:

Ensure Prerequisites are Met: Verify that you have completed all the prerequisites listed in the Prerequisites section. This includes having a Google Cloud account, gcloud CLI configured, BigQuery and Vertex AI APIs enabled, the sandboxportal project set, and the sandboxdataset created.
Load Food Product Data: Navigate to the directory containing the project files in your terminal and run the load_source.sh script. This will load the data from food_products.csv into the food_products table in your sandboxdataset.
```
./load_source.sh
```
Set Up the BigQuery ML Model (Gemini Pro): Execute the setup_bqml_vertex.sh script. This script will create the BigQuery ML model (sandboxportal.sandboxdataset.model_cloud_ai_gemini_pro) that uses the Gemini Pro model from Vertex AI.
```
./setup_bqml_vertex.sh
```
(Note: Ensure the script content correctly sets up the model.)
Execute the GenAI Query (Generate Food Labels): Run the run_genai_food_labels.sh script. This script will execute the SQL query in genai_food_labels.sql, which uses the Gemini Pro model to generate nutrition labels based on the data in the food_products table. The results will be stored in the genai_food_labels table within your sandboxdataset.
```
./run_genai_food_labels.sh
```
Verify the Results: After the run_genai_food_labels.sh script completes, you can verify the generated food labels by querying the genai_food_labels table in the Google Cloud Console or using the bq command:
```
bq query --nouse_legacy_sql 'SELECT * FROM `sandboxportal.sandboxdataset.genai_food_labels` LIMIT 10;'
```

By following these steps in order, you should be able to successfully execute the GenAI query and generate food labels for your products.