Using VertexAI LLMs in BigQuery

Properties2

Type	Practice
Note created	Mar 14, 2025

This snippet shows the easiest way to use large language models on BigQuery: by creating a model object via VertexAI, and use it just like any other BigQuery ML model with the GENERATE_TEXT function.

CREATE OR REPLACE MODEL
  `cartodb-on-gcp-datascience.dvicente.gemini-2-model`
  REMOTE WITH CONNECTION `cartodb-on-gcp-datascience.us.genai-connection`
  OPTIONS(ENDPOINT = 'gemini-2.0-flash-001');
 
SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `cartodb-on-gcp-datascience.dvicente.gemini-2-model`,
    (
      -- Prompt query, could also be a TABLE input
      SELECT 'What is this model?' AS prompt
    ),
    -- Additional options
    STRUCT(
      .05 AS TEMPERATURE,
      TRUE AS flatten_json_output
    )
  );