Generate ML predictions using SQL

This page shows how to generate machine learning (ML) predictions from aSpanner database. ML predictions work with bothGoogleSQL-dialect databases and PostgreSQL-dialect databases.

Spanner Vertex AI integration gives you the ability to generate predictions using yourSQL code by calling theML.PREDICTfunction for GoogleSQL or thespanner.ML_PREDICT_ROWfunction for PostgreSQL. For more information aboutSpanner Vertex AI integration, seeSpanner Vertex AI 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 Vertex AI integration to Vertex AI endpoints

Spanner creates the service agent and grants the necessary permissionsautomatically when Spanner executes the first MODEL DDL statement. Ifboth the Spanner database and the Vertex AI endpoint are inthe same project, no additional setup is required.

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

gcloudbetaservicesidentitycreate--service=spanner.googleapis.com--project={PROJECT}`

Follow the steps described in theGrant a single role togrant theSpanner API Service Agent role to theSpannerservice agent accountservice-PROJECT_NUMBER@gcp-sa-spanner.iam.gserviceaccount.com on yourVertex AI project.

Select a model

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

To learn more about Spanner Vertex AI integration, seeHow does Spanner Vertex AI integration work?.

Generate predictions

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

Use a model in the Vertex AI Model Garden

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

GoogleSQL

Before you use a model withML.PREDICT(), you need to register the modelusing theCREATE MODEL statement.

CREATEMODEL'MODEL_NAME'INPUT(INPUT_COLUMN_NAMEINPUT_COLUMN_TYPE)OUTPUT(OUTPUT_COLUMN_NAMEOUTPUT_COLUMN_TYPE)REMOTEOPTIONS(endpoint='//aiplatform.googleapis.com/projects/PROJECT_ID/locations/LOCATION_ID/publishers/google/models/model_id');

Replace the following:

  • MODEL_NAME: the name you want to give your model

  • INPUT_COLUMN_NAME: the name of your input column. For example,if using thegemini-pro model, the input column name isprompt.

  • INPUT_COLUMN_TYPE: the data type forINPUT_COLUMN_NAME

  • OUTPUT_COLUMN_NAME: the name of your output column. For example,if using thegemini-pro model, the output column name iscontent.

  • OUTPUT_COLUMN_TYPE: the data type forOUTPUT_COLUMN_NAME

  • PROJECT_ID: the ID of your Google Cloud project

  • REGION_ID: the ID of the Google Cloud region that the model islocated in—for example,us-central1

  • MODEL_ID: the ID of the ML model you want to use—for example,gemini-pro

    For more information about models, seeModel API reference for Generative AI.

Use theML.PREDICTGoogleSQL function with the model selectedfrom the Model Garden to generate your prediction.

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

Replace the following:

  • MODEL_NAME: the name you want to give your model

    For more information about models, seeModel API reference for Generative AI.

  • INPUT_RELATION: eitherTABLE table_name or a subquery the table or subquery supplying datato run the ML prediction on.

  • PARAMETERS: aSTRUCT value that contains parameterssupported bymodel_id.

You can also useSAFE.ML.PREDICT to returnnull instead of an error inyour predictions. This is helpful in cases when running large querieswhere some failed predictions are tolerable.

PostgreSQL

Use theML_PREDICT_ROW PostgreSQL function with the model selectedfrom the Model Garden to generate your prediction.

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

Replace the following:

  • PROJECT_ID: the ID of your Google Cloud project

  • REGION_ID: the ID of the Google Cloud region that the model islocated in—for example,us-central1

  • MODEL_ID: the ID of the ML model you want to use—for example,gemini-pro

    For more information about models, seeModel API reference for Generative AI.

  • INSTANCES: the inputs for the prediction call, in JSON format

  • PARAMETERS: optional parameters for the prediction call, in JSON format

This query produces a JSON response. For more information about themodel's JSON response messages, seePredictResponse.

Use a Vertex AI model endpoint

To use a trained or downloaded model with Spanner Vertex AI integration, you need to deploy the modelto Vertex AI. For more information on howto deploy a model to an endpoint in Vertex AI, seeDeploy a model to an endpoint.

GoogleSQL

Use theML.PREDICT GoogleSQL function with the model in aVertex AI endpoint to generate your prediction. Before you use amodel withML.PREDICT(), you need to register the model usingtheCREATE MODELstatement. Each deployed model has its own uniqueschema. The following is an example schema fromClassification and regression Overview

CREATEMODELMyClassificationModelINPUT(lengthFLOAT64,materialSTRING(MAX),tag_arrayARRAY<STRING(MAX)>)OUTPUT(scoresARRAY<FLOAT64>,classesARRAY<STRING(MAX)>)REMOTEOPTIONS(endpoint='//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/endpoints/ENDPOINT_ID')

Replace the following:

  • PROJECT_ID: the ID of your Google Cloud project

  • LOCATION: the ID of the Google Cloud region that the model islocated in—for example,us-central1

  • ENDPOINT_ID: the ID of the ML model you want to use—for example,gemini-pro

    For more information about models, seeModel API reference for Generative AI.

Use theML.PREDICTGoogleSQL function with the model selectedfrom the Model Garden to generate your prediction.

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

Replace the following:

  • MODEL_ID: the ID of the ML model you want to use.

  • INPUT_RELATION: the table or subquery that you wantto run the ML prediction on.

  • PARAMETERS: aSTRUCT value that contains parameterssupported bymodel_name.

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

PostgreSQL

Use theML.PREDICT PostgreSQL function with the model in aVertex AI endpoint to generate your prediction.

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

  • PROJECT_ID: the ID of the Google Cloud project that the model islocated in

  • REGION_ID: the ID of the Google Cloud region the model islocated in—for example,us-central1

  • ENDPOINT_ID: the ID of the model endpoint

  • INSTANCES: the inputs to the prediction call, in JSON format

  • PARAMETERS: optional parameters to the prediction call, in JSON format

This query produces a JSON response. For more information about themodel's JSON response messages, seePredictResponse.

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 theService Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following example uses thegemini-pro model,from the Model Garden, to generate text based on a short promptthat is provided as an argument. This model is available as part ofGemini in Spanner.

GoogleSQL

Register thegemini-pro model

CREATEMODELGeminiProINPUT(promptSTRING(MAX))OUTPUT(contentSTRING(MAX))REMOTEOPTIONS(endpoint='//aiplatform.googleapis.com/projects/PROJECT/locations/LOCATION/publishers/google/models/gemini-pro',default_batch_size=1);

Replace the following:

  • PROJECT: the project ID
  • LOCATION: the region where you are using Vertex AI

Run the model

SELECTcontentFROMML.PREDICT(MODELGeminiPro,(SELECT"Is 7 a prime number?"ASprompt),STRUCT(256ASmaxOutputTokens,0.2AStemperature,40astopK,0.95AStopP));

Expected output

The expected out is as follows:

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

PostgreSQL

Run the model

selectspanner.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"              |+--------------------+

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

Last updated 2025-12-15 UTC.