7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

Core Focus: The exam guide asks you to "use pretrained Google large language models (LLMs) using remote connection in BigQuery." In practice that means four steps: create a Cloud resource connection, grant its service account access to the model, create a remote model over a Gemini endpoint, and call it from SQL with generative AI functions. No data leaves BigQuery for an external system you have to manage, and no model training is needed.

---

What You Can Do with an LLM in SQL

Combined with object tables (Section 3.3), the same approach works on PDFs and images stored in Cloud Storage.

---

Step 1: Create a Cloud Resource Connection

A Cloud resource connection gives BigQuery its own service account for calling other Google Cloud services:

bq mk --connection \
  --location=US \
  --project_id=my-project \
  --connection_type=CLOUD_RESOURCE \
  gemini_conn

# Show the connection, including its service account ID
bq show --connection my-project.us.gemini_conn

The connection belongs to a location. The dataset that will hold the remote model must be in the same location as the connection.

Step 2: Grant the Connection's Service Account Access

The connection's service account (it looks like bqcx-...@gcp-sa-bigquery-condel.iam.gserviceaccount.com) needs the Agent Platform User role, roles/aiplatform.user, which was called Vertex AI User before the April 2026 rename. Grant it in the project where the remote model is created when you reference the model by name. Missing this grant produces an error saying the bqcx-... account does not have permission to access the resource.

Step 3: Create the Remote Model

CREATE OR REPLACE MODEL `support.gemini_flash`
  REMOTE WITH CONNECTION `my-project.us.gemini_conn`
  OPTIONS (ENDPOINT = 'gemini-3.5-flash');

• ENDPOINT names a supported Gemini model (or a full endpoint URL). Model versions retire on a published schedule (for example, gemini-2.5-flash retires on October 20, 2026), so check the supported-model list when you create or update a remote model.
• REMOTE WITH CONNECTION DEFAULT uses the project's default connection instead of naming one.
• Creating a remote model processes no bytes and incurs no BigQuery charge. You pay for model usage when you call it, plus normal BigQuery processing.
• Remote models can also point to Anthropic Claude, Llama, and Mistral models from Model Garden, or to models you deployed to your own endpoint.

Step 4: Call the Model with SQL

AI.GENERATE_TEXT is a table function: you pass it a model and a table or query that produces a column named prompt, and it returns one row per input row with the model's response alongside your other columns. It is the preferred successor to ML.GENERATE_TEXT, which still works and differs mainly in output column names.

SELECT *
FROM AI.GENERATE_TEXT(
  MODEL `support.gemini_flash`,
  (
    SELECT
      ticket_id,
      CONCAT(
        'Classify the sentiment of this support ticket as POSITIVE, NEUTRAL, or NEGATIVE. ',
        'Reply with one word. Ticket: ', body
      ) AS prompt
    FROM `support.tickets`
    WHERE created_date = CURRENT_DATE()
  ),
  STRUCT(0.0 AS temperature, 10 AS max_output_tokens)
);

• A low temperature makes classification and extraction answers more consistent.
• max_output_tokens caps response length and cost.
• Test on a small WHERE or LIMIT subset before running the prompt over millions of rows.

BigQuery also offers row-level AI functions, such as AI.GENERATE, AI.GENERATE_BOOL, AI.GENERATE_INT, and AI.GENERATE_DOUBLE, that return typed values for use directly in a SELECT or WHERE, and AI.GENERATE_TABLE for structured output. For semantic search, AI.GENERATE_EMBEDDING (successor to ML.GENERATE_EMBEDDING) creates embeddings from a remote embedding model, and VECTOR_SEARCH finds the nearest rows.

---

Choosing an LLM Approach

---

Common Exam Traps

• Forgetting the IAM grant: The connection's service account, not the user running the query, must have the Agent Platform (Vertex AI) User role.
• Location mismatch: The connection, the dataset holding the remote model, and the data must line up by location.
• Calling a table function like a scalar: AI.GENERATE_TEXT and ML.GENERATE_TEXT go in the FROM clause; you cannot wrap them around a column in the SELECT list.
• Training a custom model for a generic language task: Sentiment, summarization, and extraction usually need only a pretrained model and a good prompt.
• Expecting identical answers every run: LLM output can vary; lower the temperature and validate outputs before using them in reports.