[ Web Proxy ]
URL:
Viewing: https://docs.cloud.google.com/spanner/docs/ml-tutorial [Back]  [Original]
Generate ML predictions using SQL  |  Spanner  |  Google Cloud Documentation Skip to main content
Google Cloud Documentation [Google Cloud Documentation]
Technology areas Cross-product tools
Search:
Console
[]
Start free
Overview Guides Reference Samples Support Resources
Google Cloud Documentation [Google Cloud Documentation]
Send feedback

Generate ML predictions using SQL Stay organized with collections Save and categorize content based on your preferences.

This page shows how to generate machine learning (ML) predictions from a Spanner database. ML predictions work with both GoogleSQL-dialect databases and PostgreSQL-dialect databases.

Spanner Agent Platform integration gives you the ability to generate predictions using your SQL code by calling the ML.PREDICT function for GoogleSQL or the spanner.ML_PREDICT_ROW function for PostgreSQL. For more information about Spanner Agent Platform integration, see Spanner Agent Platform integration overview.

Before you begin

Before you can generate predictions from a Spanner instance, you must prepare your database and select a model.

Configure access for Spanner Agent Platform integration to Agent Platform endpoints

Spanner creates the service agent and grants the necessary permissions automatically when Spanner executes the first MODEL DDL statement. If both the Spanner database and the Gemini Enterprise Agent Platform endpoint are in the same project, no additional setup is required.

If the Spanner service agent account doesn't exist for your Spanner project, create it by running the following command:

gcloud beta services identity create --service=spanner.googleapis.com --project={PROJECT}`

Follow the steps described in the Grant a single role to grant the Spanner API Service Agent role to the Spanner service agent account service-PROJECT_NUMBER@gcp-sa-spanner.iam.gserviceaccount.com on your Agent Platform project.

Select a model

When you use the ML.PREDICT (for GoogleSQL) or the spanner.ML_PREDICT_ROW (for PostgreSQL) function, you must specify the location of the ML model. Your selected model can be one of the following:

To learn more about Spanner Agent Platform integration, see How does Spanner Agent Platform integration work?.

Generate predictions

Depending on the type of your selected model, the steps to generating your predictions will differ.

Use a model in Model Garden

To generate a prediction using a model from Model Garden, select a model from Model Garden.

GoogleSQL

Before you use a model with ML.PREDICT(), you need to register the model using the CREATE MODEL statement.

CREATE MODEL 'MODEL_NAME'
INPUT (INPUT_COLUMN_NAME INPUT_COLUMN_TYPE)
OUTPUT (OUTPUT_COLUMN_NAME OUTPUT_COLUMN_TYPE)
REMOTE
OPTIONS (
  endpoint = '//aiplatform.googleapis.com/projects/PROJECT_ID/locations/LOCATION_ID/publishers/google/models/model_id'
);

Replace the following:

Use the ML.PREDICT GoogleSQL function with the model selected from Model Garden to generate your prediction.

SELECT * FROM ML.PREDICT(
  MODEL `MODEL_NAME`,
  `INPUT_RELATION`[, `PARAMETERS`])

Replace the following:

You can also use SAFE.ML.PREDICT to return null instead of an error in your predictions. This is helpful in cases when running large queries where some failed predictions are tolerable.

PostgreSQL

Use the ML_PREDICT_ROW PostgreSQL function with the model selected from Model Garden to generate your prediction.

SELECT spanner.ml_predict_row(
  'projects/PROJECT_ID/locations/REGION_ID/publishers/google/models/MODEL_ID'::text,
  '{
    "instances": [ INSTANCES ],
    "parameters": { PARAMETERS }
   }'::jsonb);

Replace the following:

This query produces a JSON response. For more information about the model's JSON response messages, see PredictResponse.

Use a Agent Platform model endpoint

To use a trained or downloaded model with Spanner Agent Platform integration, you need to deploy the model to Agent Platform. For more information on how to deploy a model to an endpoint in Agent Platform, see Deploy a model to an endpoint.

GoogleSQL

Use the ML.PREDICT GoogleSQL function with the model in a Agent Platform endpoint to generate your prediction. Before you use a model with ML.PREDICT(), you need to register the model using the CREATE MODEL statement. Each deployed model has its own unique schema. The following is an example schema from Classification and regression overview

CREATE MODEL MyClassificationModel
INPUT (
  length FLOAT64,
  material STRING(MAX),
  tag_array ARRAY<STRING(MAX)>
)
OUTPUT (
  scores ARRAY<FLOAT64>,
  classes ARRAY<STRING(MAX)>
)
REMOTE
OPTIONS (
  endpoint = '//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/endpoints/ENDPOINT_ID'
)

Replace the following:

Use the ML.PREDICT GoogleSQL function with the model selected from Model Garden to generate your prediction.

SELECT * FROM ML.PREDICT(
  `MODEL_ID`,
  `INPUT_RELATION`[, `PARAMETERS`])

Replace the following:

This query produces a relation containing all output columns of the model and all columns of the input relation.

PostgreSQL

Use the ML.PREDICT PostgreSQL function with the model in an Agent Platform endpoint to generate your prediction.

  SELECT spanner.ml_predict_row(
    'projects/PROJECT_ID/locations/REGION_ID/endpoints/ENDPOINT_ID'::text,
    '{
      "instances": [ INSTANCES ],
      "parameters": { PARAMETERS }
      }'::jsonb);
  ```

Replace the following:

This query produces a JSON response. For more information about the model's JSON response messages, see PredictResponse.

Examples for using ML functions to generate predictions

Preview — Gemini in Spanner

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

The following example uses the gemini-pro model, from Model Garden, to generate text based on a short prompt that is provided as an argument. This model is available as part of Gemini in Spanner.

GoogleSQL

Register the gemini-pro model

CREATE MODEL GeminiPro
INPUT (prompt STRING(MAX))
OUTPUT (content STRING(MAX))
REMOTE
OPTIONS (
  endpoint = '//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/publishers/google/models/gemini-pro',
  default_batch_size = 1
);

Replace the following:

Run the model

SELECT content
FROM ML.PREDICT(
  MODEL GeminiPro,
  (SELECT "Is 7 a prime number?" AS prompt),
  STRUCT(256 AS maxOutputTokens, 0.2 AS temperature, 40 as topK, 0.95 AS topP)
);

Expected output

The expected out is as follows:

+--------------------+
| content            |
+--------------------+
| "Yes"              |
+--------------------+

PostgreSQL

Run the model

select spanner.ml_predict_row(
  '{
    "endpoint": "projects/PROJECT_ID/locations/us-central1/publishers/google/models/gemini-pro",
    "default_batch_size": 1
   }'::jsonb,
  '{
    "instances":[{"prompt": "Is 7 a prime number?"}],
    "parameters":{"maxOutputTokens":256, "topK": 40, "topP":0.96, "temperature":0.2}
    }'
);

Expected output

The expected out is the following:

+--------------------+
| content            |
+--------------------+
| "Yes"              |
+--------------------+
Send feedback

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-08-11 UTC.

Need to tell us more? [[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2026-08-11 UTC."],[],[]]
Web Proxy Viewer  |  New URL  |  Original Page