Cloud SQL lets you get online predictions inyour SQL code by calling theml_predict_row() function. For more information, seeBuild generative AIapplications using Cloud SQL.

Before you begin

Before you can invoke online predictions from a Cloud SQL instance,you must prepare your database and select an appropriate ML model.

Prepare your database

1. To prepare your database,set up integration between Cloud SQL and Vertex AI.

2. Grant permissions for database users to use theml_predict_row() functionto run predictions:

   1. Connect apsql client to the primary instance, asdescribed inConnect using a a psql client.

   2. At thepsql command prompt, connect to the database and grant permissions:

      \cDB_NAMEGRANTEXECUTEONFUNCTIONml_predict_rowTOUSER_NAME;

      Replace the following:

      • DB_NAME: the name of the database for which you'regranting permissions

      • USER_NAME: the name of the user for whom you're grantingpermissions

Select an ML model

When you call theml_predict_row() function,you must specify the location of an ML model. The model that you specifycan be one of these:

• A model that's running in theVertex AI Model Garden.

  Theml_predict_row() function supportsinvoking predictions only on tabular or custom models.

• A Vertex AI model with an active endpoint that you have Identity and Access Management (IAM) permission to access.

  Cloud SQL doesn't supportprivate endpointsfor getting online predictions.

Invoke online predictions

You can use theml_predict_row() SQL functionto invoke online predictions against your data.

The format of the function's initial argument depends on whether the ML modelthat you want to use is in the Vertex AI Model Gardenor is an endpoint running in a Google Cloud project.

Use a model in the Vertex AI Model Garden

To invoke an online prediction using an ML model that's running in theVertex AI Model Garden, use the following syntax for theml_predict_row() SQL function:

Make the following replacements:

• MODEL_ID: the ID of the ML model to use (for example,gemini-2.0)

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

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

SELECTML_PREDICT_ROW('publishers/google/models/MODEL_ID','{ "instances": [INSTANCES ], "parameters":PARAMETERS }');

Use a Vertex AI model endpoint

To invoke an online prediction using a Vertex AI model endpoint, use the following syntax for theml_predict_row() SQL function:

Make the following replacements:

• ENDPOINT_ID: the ID of the model's endpoint

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

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

For information about the model's JSON response messages, seePredictResponse.

Example invocations

The following example usesPaLM 2 for Text, availablein the Model Garden, to generate text based on a short prompt that'sprovided as a literal argument toml_predict_row():

selectML_PREDICT_ROW('projects/PROJECT_ID/locations/us-central1/publishers/google/models/text-bison','{"instances":[{"prompt": "What are three advantages of using Cloud SQL as my SQL database server?"}], "parameters":{"maxOutputTokens":1024, "topK": 40, "topP":0.8, "temperature":0.2}}');

The response is a JSON object. For more information about the format of theobject, seeResponse body.

The next example modifies the previous one in the following ways:

• The example uses the contents of the current database'smessages.messagecolumn as input.

• The example demonstrates the use of thejson_build_object() functionas an aid to formatting the function's parameters.

selectML_PREDICT_ROW('projects/PROJECT_ID/locations/us-central1/publishers/google/models/text-bison',json_build_object('instances',json_build_object('prompt',message),'parameters',json_build_object('maxOutputTokens',1024,'topK',40,'topP',0.8,'temperature',0.2)))frommessages;

For every row in themessages table, the returned JSON object now containsone entry in itspredictions array.

Because the response is a JSON object, you can pull specific fields from it:

For more example arguments toml_predict_row(), seeTry the Vertex AI Gemini API.

What's next

• Build generative AI applications using Cloud SQL

• Integrate Cloud SQL with Vertex AI

• Understand an example of an embedding workflow

• Build LLM-powered applications using LangChain