Calling Gemini from BigQuery — BigQuery ML & Data AI

Massive-Scale Generative SQL Analytics

BigQuery ML integrates directly with Vertex AI foundation models through Cloud Resource Connections. By linking a BigQuery connection to a remote Vertex endpoint, SQL analysts can run Gemini inference across millions of tabular rows in parallel with zero custom Python code.

-- 1. Declare remote Gemini model in BigQuery
CREATE OR REPLACE MODEL `retail_dw.gemini_remote_model`
REMOTE WITH CONNECTION `us-central1.vertex-ai-conn`
OPTIONS(ENDPOINT = 'gemini-3.8-flash');

-- 2. Execute parallel sentiment analysis & entity extraction across 500,000 customer reviews
SELECT
  review_id,
  customer_id,
  ml_generate_text_llm_result AS analysis
FROM ML.GENERATE_TEXT(
  MODEL `retail_dw.gemini_remote_model`,
  (
    SELECT
      review_id,
      customer_id,
      CONCAT('Analyze customer sentiment and extract mentioned product defects: ', review_text) AS prompt
    FROM `retail_dw.customer_reviews`
    LIMIT 10000
  ),
  STRUCT(
    0.1 AS temperature,
    256 AS max_output_tokens,
    TRUE AS flatten_json_output
  )
);

BigQuery automatically manages quota, parallelizes partition batches across worker slots, and surfaces structured results ready for Looker dashboards or downstream ETL pipelines.

Streamlined Scalar Inference: AI.GENERATE

Alongside remote models with ML.GENERATE_TEXT, BigQuery supports the streamlined AI.GENERATE function, which queries Vertex AI Gemini endpoints inline over text and multimodal columns using End User Credentials (EUC) without requiring an explicit CREATE MODEL statement:

SELECT
  review_id,
  AI.GENERATE(
    CONCAT('Classify sentiment and summarize key defect: ', review_text),
    endpoint => 'gemini-3.8-flash'
  ) AS review_summary
FROM `retail_dw.customer_reviews`
LIMIT 100;