Import and deploy a model for inference using PolarDB for AI - PolarDB

Row-oriented AI lets you import a trained machine learning (ML) model into your PolarDB for MySQL cluster and call it directly from SQL queries for inference.

Prerequisites

Before you begin, ensure that you have:

Enabled the PolarDB for AI feature for your cluster. For more information, see Enable the PolarDB for AI feature.
Enabled the cold data archiving feature for your cluster. For more information, see Enable cold data archiving.

For large language models (LLMs), skip Step 1 (upload) and go directly to Step 2 (deploy), then continue with Steps 3 and 4.

Step 1: Upload the model

Connect to your database and run the following statement to upload the model to the AI node in your cluster. The model must be stored at a publicly accessible URL.

/*polar4ai*/ UPLOAD MODEL model_name WITH (model_location = 'https://mybucket.oss-cn-hangzhou.aliyuncs.com/logisitic_regression.tgz');

To confirm the upload, check the model status:

/*polar4ai*/ SHOW MODEL model_name;

After a successful upload, the model status is saved_oss.

Parameter	Description
`model_name`	The name of the pre-created model. Must start with a letter and cannot contain hyphens (-).
`model_location`	The public URL used to download the model.

Step 2: Deploy the model

Deploying the model loads it onto the AI node and makes it available for inference.

/*polar4ai*/ DEPLOY MODEL model_name WITH (mode = 'in_db');

After running this statement, check the model status to confirm deployment:

/*polar4ai*/ SHOW MODEL model_name;

When the model status changes to serving, the model is ready for use.

Deployment may take some time. Do not proceed to the next step until the status is serving. The model_name value must match the name used in Step 1, and mode must be set to in_db.

Step 3: Create a function

Creating a user-defined function (UDF) exposes the deployed model as a callable SQL function. The UDF is backed by the .so file generated during deployment.

Permissions required

To create a UDF, use a privileged account. If you need to grant a standard account the necessary permissions, run the following statements using the privileged account:

GRANT INSERT ON mysql.* TO 'normal'@'localhost';
GRANT DELETE ON mysql.* TO 'normal'@'localhost';
FLUSH PRIVILEGES;

Create the function

CREATE FUNCTION function_name RETURNS return_value SONAME "soname";

Parameter	Description
`function_name`	The function name. Must match the model name specified in Step 1.
`return_value`	The return type. Supported types: `REAL`, `STRING`, and `INTEGER`. Must match the model's actual output type.
`soname`	The `.so` file name, in the format `ailib#_ModelName.so`.

To verify that the function was created successfully, query the system table:

SELECT * FROM mysql.func;

Step 4: Call the function for inference

Run inference by calling the function in a SELECT statement. Pass the relevant feature columns from your data table or provide the input directly.

-- Inference from a table
SELECT function_name(feature1, feature2) FROM predict_table;

-- Inference from a direct input
SELECT function_name("content");

Parameter	Description
`function_name`	The name of the function created in Step 3.
`feature1`, `feature2`	The column names used as input features for inference.
`predict_table`	The table containing the data to run inference on.
`content`	A direct input value for inference.

(Optional) Delete the function and model

When you no longer need the function and model, delete the function first, then delete the model.

Delete the function:

DROP FUNCTION function_name;

Delete the model:

/*polar4ai*/ DROP MODEL model_name;