Machine learning functions in GoogleSQL

GoogleSQL for Spanner supports the following machine learning (ML) functions.

Function list

NameSummary
ML.PREDICT Apply ML computations defined by a model to each row of an input relation.

ML.PREDICT

ML.PREDICT(input_model,input_relation[,model_parameters])input_model:MODELmodel_nameinput_relation:{input_table|input_subquery}input_table:TABLEtable_namemodel_parameters:STRUCT(parameter_valueASparameter_name[,...])

Description

ML.PREDICT is a table-valued function that helps to access registeredmachine learning (ML) models and use them to generate ML predictions.This function applies ML computations defined by a model to each row of aninput relation, and returns the results of those predictions. Additionally, youcan useML.PREDICT to perform vector search. When you useML.PREDICT forvector search, it converts your natural language query text into an embedding.

Note: Make sure that Spanner has access to the referencedVertex AI endpoint as described inModel endpoint access control.

Supported Argument Types

  • input_model: The model to use for predictions. Replacemodel_name withthe name of the model. To create a model, seeCREATE_MODEL.
  • input_relation: A table or subquery upon which to apply ML computations.The set of columns of the input relation must include all input columns ofthe input model; otherwise, the input won't have enough data to generatepredictions and the query won't compile. Additionally, the set can alsoinclude arbitrary pass-through columns that will be included in the output.The order of the columns in the input relation doesn't matter. The columnsof the input relation and model must be coercible.
  • input_table: The table containing the input data for predictions, forexample, a set of features. Replacetable_name with the name of the table.
  • input_subquery: The subquery that's used to generate the prediction inputdata.
  • model_parameters: ASTRUCT value that contains parameters supported bymodel_name. These parameters are passed to the model inference.

Return Type

A table with the following columns:

  • Model outputs
  • Pass-through columns from the input relation
Note: If a column of the input relation has the same name as one of theoutput columns, the value of the output column is returned.

Examples

The examples in this section reference a model calledDiamondAppraise andan input table calledDiamonds with the following columns:

  • DiamondAppraise model:

    Input columnsOutput columns
    value FLOAT64value FLOAT64
    carat FLOAT64lower_bound FLOAT64
    cut STRINGupper_bound FLOAT64
    color STRING(1)

  • Diamonds table:

    Columns
    Id INT64
    Carat FLOAT64
    Cut STRING
    Color STRING

The following query predicts the value of a diamond based on the diamond'scarat, cut, and color.

SELECTid,color,valueFROMML.PREDICT(MODELDiamondAppraise,TABLEDiamonds);+----+-------+-------+|id|color|value|+----+-------+-------+|1|I|280||2|G|447|+----+-------+-------+

You can include model-specific parameters. For example, in the following query,themaxOutputTokens parameter specifies thatoutput, the model inference,can contain 10 or fewer tokens. This query succeeds because the modelTextBison contains a parameter calledmaxOutputTokens.

SELECTprompt,outputFROMML.PREDICT(MODELTextBison,(SELECT"Is 13 prime?"asprompt),STRUCT(10ASmaxOutputTokens));+----------------+---------------------+|prompt|output|+----------------+---------------------+|"Is 13 prime?"|"Yes, 13 is prime."|+----------------+---------------------+

The following example generates an embedding for a natural language query. Theexample then uses that embedding to find the most similar entries in adatabase that are indexed by vector embeddings.

-- Generate the embedding from a natural language promptWITHembeddingAS(SELECTembeddings.valuesFROMML.PREDICT(MODELDiamondAppraise,(SELECT"What is the most valuable diamond?"asprompt)))-- Use embedding to find the most similar entries in the databaseSELECTid,color,value,(APPROX_COSINE_DISTANCE(valueEmbedding,embedding.values,options=>JSON'{"num_leaves_to_search": 10}'))asdistanceFROMproducts@{force_index=valueEmbeddingIndex},embeddingWHEREvalueEmbeddingISNOTNULLORDERBYdistanceLIMIT5;

You can useML.PREDICT in any DQL/DML statements, such asINSERT orUPDATE. For example:

INSERTINTOAppraisedDiamond(id,color,carat,value)SELECT1ASid,color,carat,valueFROMML.PREDICT(MODELDiamondAppraise,(SELECT@caratAScarat,@cutAScut,@colorAScolor));

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.