Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-bigquery-generative-ai-intro

4 min read

Introduction to Generative AI functions in BigQuery

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/bigquery_generative_ai_intro.ipynb · Open on GitHub · intermediate

Introduces BigQuery generative AI functions for SQL-based text analysis and forecasting.

Summary

This notebook teaches how to use BigQuery AI functions with Gemini and a partner model through Cloud resource connections and remote models. It demonstrates text generation, structured extraction, row-level natural-language filtering, saving generated results to a BigQuery table, and time-series forecasting with AI.FORECAST. The workflow includes project setup, API enablement, IAM permissions for the connection service account, model invocation in SQL, visualization of forecast results, and cleanup.

Key code patterns

Cloud resource connection

bq mk --connection --location=us \
  --connection_type=CLOUD_RESOURCE test_connection
 
bq show --format=prettyjson --connection us.test_connection

BigQuery needs a Cloud resource connection and its service account to call Vertex AI models.

AI.GENERATE with Gemini

SELECT
  review,
  AI.GENERATE(('Extract the keywords from the text below: ', review),
    connection_id => 'us.test_connection',
    endpoint => 'gemini-2.5-flash').result AS keywords
FROM `bigquery-public-data.imdb.reviews`
LIMIT 5;

Runs Gemini prompts directly over table rows in SQL.

Structured output schema

AI.GENERATE(
  ('Extract the keywords from the text below: ', review),
  connection_id => 'us.test_connection',
  endpoint => 'gemini-2.5-flash',
  output_schema => 'keywords ARRAY< STRING>').keywords

Constrains generated responses into typed BigQuery fields.

Remote model for table generation

CREATE OR REPLACE MODEL `bq_ai_tutorial.gemini_2_5_flash`
REMOTE WITH CONNECTION `us.test_connection`
OPTIONS (ENDPOINT = 'gemini-2.5-flash');

Creates a reusable BigQuery remote model pointer for AI.GENERATE_TABLE.

AI.GENERATE_TABLE

SELECT review, keywords, sentiment
FROM AI.GENERATE_TABLE(
  MODEL `bq_ai_tutorial.gemini_2_5_flash`,
  (SELECT review, ('Extract keywords and sentiment:', review) AS prompt
   FROM `bigquery-public-data.imdb.reviews` LIMIT 5),
  STRUCT('keywords ARRAY< STRING>, sentiment STRING' AS output_schema));

Returns multiple structured generated columns from unstructured text.

Partner model generation

CREATE OR REPLACE MODEL `bq_ai_tutorial.claude_3_haiku`
REMOTE WITH CONNECTION `us.test_connection`
OPTIONS (ENDPOINT = 'claude-3-haiku@20240307');
 
SELECT review, result
FROM AI.GENERATE_TEXT(MODEL `bq_ai_tutorial.claude_3_haiku`, input, STRUCT(500 AS max_output_tokens));

Uses AI.GENERATE_TEXT for a Vertex AI partner model enabled in Model Garden.

Scalar AI filters

WHERE
  AI.GENERATE_DOUBLE(('What is the average height in meters?', species_scientific_name),
    connection_id => 'us.test_connection', endpoint => 'gemini-2.5-flash').result BETWEEN 10 AND 20
  AND AI.GENERATE_BOOL(('Is this tree species drought tolerant?', species_scientific_name),
    connection_id => 'us.test_connection', endpoint => 'gemini-2.5-flash').result = true

Applies LLM-derived boolean and numeric values inside WHERE clauses.

AI.FORECAST

SELECT *
FROM AI.FORECAST(
  (SELECT TIMESTAMP_TRUNC(start_date, HOUR) AS trip_hour,
          subscriber_type, COUNT(*) AS num_trips
   FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
   GROUP BY trip_hour, subscriber_type),
  horizon => 720,
  confidence_level => 0.95,
  timestamp_col => 'trip_hour',
  data_col => 'num_trips',
  id_cols => ['subscriber_type']);

Forecasts hourly bikeshare trips by subscriber type without training a custom model.

Models & APIs used

  • Models: gemini-2.5-flash, claude-3-haiku@20240307, TimesFM
  • APIs / services: BigQuery, BigQuery ML, BigQuery Connection, Vertex AI
  • SDKs / libraries: google.colab, matplotlib

When to use this

Use this pattern when you want to apply generative analysis, structured extraction, AI filtering, or time-series forecasting directly inside BigQuery SQL.

Gotchas & caveats

  • Billing must be enabled for the Google Cloud project.
  • BigQuery, BigQuery Connection, and Vertex AI APIs must be enabled.
  • Local notebook runs require Cloud SDK authentication.
  • A BigQuery Cloud resource connection is required for Vertex AI access.
  • The connection service account needs roles/bigquery.connectionUser and roles/aiplatform.user.
  • The notebook waits 60 seconds for IAM propagation before running later cells.
  • Setting maxOutputTokens too low can truncate Gemini 2.5 Flash responses because thinking consumes output tokens.
  • thinking_budget can limit tokens used by the thinking process.
  • AI.GENERATE with output_schema can return one specified schema field or full_response JSON, but not multiple schema columns directly.
  • AI.GENERATE_TABLE requires a BigQuery remote model.
  • Anthropic Claude 3 Haiku must be enabled in Vertex AI before BigQuery can use it.

Best practices

  • Create a Cloud resource connection for BigQuery access to Vertex AI services.
  • Grant the connection service account the required BigQuery connection and Vertex AI roles.
  • Wait for IAM changes to propagate before running model calls.
  • Use model_params to control temperature, maxOutputTokens, and thinking_budget.
  • Use output_schema when generated results need typed structure.
  • Use AI.GENERATE_TABLE when multiple generated columns are needed in one query.
  • Create remote models in a dataset for reuse across analyses.
  • Save generated results with CREATE OR REPLACE TABLE when outputs should persist.
  • Clean up the tutorial dataset and BigQuery connection when finished.

Graph View

Backlinks