How to manage models - PolarDB - Alibaba Cloud Documentation Center

Model overview

Category	Description
Built-in model	You can run SQL statements in PolarDB to call built-in large AI models for complex analysis tasks, without needing to manually synchronize data with other AI platforms. For more information, see Qwen LLM data inference and interaction.
Custom model	If a built-in large AI model does not meet your business requirements, you can create a fine-tuned custom model. Additionally, PolarDB for AI supports running models in confidential containers to enhance data security. The workflow to build a custom model is as follows: Model creation Model evaluation Model inference
External model	You can upload and deploy existing AI models to PolarDB for model inference. Examples include LightGBM and GBDT models from the scikit-learn framework, or deep learning models based on the TensorFlow or PyTorch frameworks. The workflow for an external model is as follows: Model upload Model deployment Model evaluation Model inference

Create a model

Creates a machine learning model. This is an asynchronous process, and you must query the model status to confirm its completion.

Syntax

CREATE MODEL model_name WITH ( model_class = '', x_cols = '', y_cols='',model_parameter=()) AS (SELECT select_expr [, select_expr] ... FROM table_reference)

Parameters

Parameter	Description
model_name	The model name.
model_class	The model type. Valid values: `lightgbm`: For more information, see LightGBM algorithm. `deepfm`: For more information, see DeepFM algorithm. `kmeans`: For more information, see K-means clustering algorithm. `randomforestreg`: For more information, see random forest regression algorithm. `gbrt`: For more information, see GBRT algorithm. `linearreg`: For more information, see linear regression algorithm. `svr`: For more information, see support vector regression algorithm. `bst`: For more information, see BST algorithm.
x_cols	The input columns. Note Separate multiple column names with commas (,).
y_cols	The output column.
model_parameter	The model parameters. When the value of `model_class` is lightgbm, see Parameter description for the values of `model_parameter`. When `model_class` is set to `deepfm`, see Parameter descriptions for the value of `model_parameter`. When `model_class` is set to `kmeans`, see Parameter description for the value of `model_parameter`. When `model_class` is set to randomforestreg, see Parameter description for the value of `model_parameter`. When `model_class` is gbrt, see Parameter description for the settings of `model_parameter`. When `model_class` is set to `linearreg`, see Parameter description for the values of `model_parameter`. When the value of `model_class` is `svr`, see Parameter description for the value of `model_parameter`. When the value of `model_class` is bst, see Parameter description for the values of `model_parameter`.
select_expr	The column names.
table_reference	The table name.

Example

Create the airlines_gbm model.

/*polar4ai*/CREATE MODEL airlines_gbm WITH (model_class='lightgbm', x_cols ='Airline,Flight,AirportFrom,AirportTo,DayOfWeek,Time,Length', y_cols='Delay',model_parameter=(boosting_type='gbdt', n_estimators=100, max_depth=8, num_leaves=256)) as (SELECT * FROM db4ai.airlines_train)

Sample result:

Query OK, 0 rows affected (0.79 sec)

Upload a model

PolarDB for AI lets you upload and manage your offline machine learning models. Supported formats include PMML, ONNX, and Checkpoint (for TensorFlow and PyTorch).

Syntax

UPLOAD MODEL model_name WITH (model_location = '', req_location = '')

Parameters

Parameter	Description
model_name	The name of the model.
model_location	The path to the model file.
req_location	The path to the model's dependency file.

Example

Upload a model named my_model to PolarDB for AI.

/*polar4ai*/UPLOAD MODEL my_model WITH (model_location='https://xxxx.oss-cn-hangzhou.aliyuncs.com/xxxx/model.pkl?Expires=xxxx&OSSAccessKeyId=xxxx&Signature=xxxx', req_location='https://xxxx.oss-cn-hangzhou.aliyuncs.com/xxxx/requirements.txt?Expires=xxxx&OSSAccessKeyId=xxxx&Signature=xxxx');

Query OK, 0 rows affected (0.29 sec)

To query the model status, run the following statement:

/*polar4ai*/ SHOW MODEL my_model;

+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| modelStatus | modelPath                                                                                                                   |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| saved       | http://db4ai-collie-cn-hangzhou.aliyuncs.com/xxxxx.pkl?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx  |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)

A modelStatus of saved indicates a successful upload.

Deploy a model

Deploy an uploaded model to the PolarDB for AI platform. A successful deployment brings the model online, enabling faster model inference.

Syntax

DEPLOY MODEL model_name

Parameters

Parameter	Description
model_name	The name of the model to deploy.

Example

Deploy the model named my_model to the PolarDB for AI platform.

/*polar4ai*/ DEPLOY MODEL my_model;

Example output:

Query OK, 0 rows affected (0.29 sec)

Run the following command to check the model status:

/*polar4ai*/ SHOW MODEL my_model;

Example output:

+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| modelStatus | modelPath                                                                                                                   |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| serving     | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/xxxxx.pkl?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx  |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)

A modelStatus of serving indicates a successful deployment.

View models

Note

You can view only the custom models and external models that you create or upload. You cannot view the built-in models in PolarDB for AI.

List models

Lists all custom and external models in the current cluster.

Syntax

SHOW MODELS

Example

/*polar4ai*/SHOW MODELS

The following is a sample result:

+-----------------------+-----------------+--------------+
| model_name            | model_class     | model_status |
+-----------------------+-----------------+--------------+
| airline_rfr           | randomforestreg | saved        |
| gbrt1                 | gbrt            | saved        |
| airline_deepfm        | deepfm          | saved        |
| airlines_gbm          | lightgbm        | saved        |
| lgbm1                 | lightgbm        | saved        |
| blackfriday_linearreg | linearreg       | saved        |
+-----------------------+-----------------+--------------+
6 rows in set (0.24 sec)

Model status

Displays the current status of a model. After you create a model, you can use this command to check whether the training is complete. A model can have one of the following statuses:

training: The model is being trained.
loading_data: Data is being loaded for the model.
trained: The model has been trained.
saved: The model has been saved.
serving: The model is deployed and ready for use.
deleted: The model has been deleted.

Syntax

SHOW MODEL model_name

Parameters

Parameter	Description
model_name	The model name.

Example

View the current status of the airlines_gbm model.

/*polar4ai*/SHOW MODEL airlines_gbm;

The following is a sample result:

+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| modelStatus | modelPath                                                                                                                   |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| saved       | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/xxxxx.pkl?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx  |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)

Note

The modelPath is valid for 100 minutes. You can check the Expires parameter in modelPath to see if the link is still valid. If you need to access the link, make sure to do so before it expires.

Model details

Syntax

DESCRIBE MODEL model_name

Parameters

Parameter	Description
model_name	The model name.

Example

View the details of the airlines_gbm model.

/*polar4ai*/DESCRIBE MODEL airlines_gbm;

The following is a sample result:

+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| model_name        | model_description                                                                                                                                                                                                                                                                 |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| airlines_gbm      | basic information:model_name:airlines_gbm model_class:lightgbm feature important:features,imp_gain,imp_splitAirline,0.3327,0.0376 AirportFrom,0.2178,0.1842 Time,0.1893,0.1999 AirportTo,0.1668,0.187 DayOfWeek,0.0384,0.1236 Length,0.0307,0.1269 Flight,0.0242,0.1408           |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.65 sec)

Model evaluation

Evaluates a trained model's performance.

Syntax

SELECT select_expr [, select_expr] ... FROM EVALUATE (MODEL model_name, SELECT select_expr_for_prediction [, select_expr_for_prediction] ... FROM table_reference ) WITH (x_cols = '', y_cols='', metrics='')

Parameters

Parameter	Description
select_expr	The columns to display in the model evaluation results.
model_name	The name of the model.
select_expr_for_prediction	The columns to use for model evaluation.
table_reference	The name of the table.
x_cols	The input columns used to create the model. Separate multiple column names with a comma (,).
y_cols	The output column used to create the model.
metrics	The evaluation metrics. Valid values: `acc`: Accuracy. This metric applies to classification tasks. `r2_score`: Coefficient of determination. This metric applies to regression tasks. `ks`: KS value. Measures how well the model separates positive and negative samples. This metric applies to classification tasks. `auc`: AUC value. Represents the area under the Receiver Operating Characteristic (ROC) curve. This metric applies to classification tasks. `Fscore`: F-score. The harmonic mean of precision and recall. This metric applies to classification tasks.

Examples

Use the created airlines_gbm model to perform model evaluation.

/*polar4ai*/SELECT Delay FROM evaluate(MODEL airlines_gbm, SELECT * FROM db4ai.airlines_test) WITH (x_cols = 'Airline,Flight,AirportFrom,AirportTo,DayOfWeek,Time,Length', y_cols='Delay', metrics='acc');

Sample result:

+--------------------------------------+
| task_id                              |
+--------------------------------------+
| df05244e-21f7-11ed-be66-xxxxxxxxxxxx |
+--------------------------------------+
1 row in set (0.95 sec)

Use the following command to check the task status.

/*polar4ai*/SHOW TASK `df05244e-21f7-11ed-be66-xxxxxxxxxxxx`;

Sample result:

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| taskStatus | filePath                                                                                                                                           | results         | startTime                  | endTime                    |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| finish     | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/pc-xxxxxxx/airlines_gbm/xxxxx.csv?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx | {"acc": 0.6694} | 2022-08-22 17:22:21.122101 | 2022-08-22 17:22:39.428811 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
1 row in set (0.24 sec)

Note

The filePath is valid for 100 minutes. You can check the Expires parameter in the filePath to see if the link is still valid. If you need to access the link, please do so within its validity period.

Model inference

Use a trained model to perform online or offline inference.

Syntax

SELECT select_expr [, select_expr] ... FROM PREDICT (MODEL model_name, SELECT select_expr_for_prediction [, select_expr_for_prediction] ... FROM table_reference LIMIT row_count) WITH (x_cols= '')

Parameters

Parameter	Description
select_expr	Specifies the columns to display in the model inference results.
model_name	Specifies the name of the model.
select_expr_for_prediction	Specifies the input data columns to use for inference.
table_reference	Specifies the input table.
mode	Specifies the inference mode. Set this parameter to async to perform offline inference. If you omit this parameter, the system performs online inference.
row_count	Specifies the number of samples to use for inference.
x_cols	Specifies the input columns for the model. Separate multiple column names with a comma (,).

Examples

Online inference

Use the airlines_gbm model to perform online inference.

/*polar4ai*/SELECT Delay FROM PREDICT(MODEL airlines_gbm, SELECT * FROM db4ai.airlines_test LIMIT 10) WITH (x_cols = 'Airline,Flight,AirportFrom,AirportTo,DayOfWeek,Time,Length', y_cols='Delay');

The following is a sample result:

+-------+-------------------+
| Delay | predicted_results |
+-------+-------------------+
|     1 | 0                 |
|     0 | 0                 |
|     0 | 0                 |
|     0 | 0                 |
|     0 | 0                 |
|     0 | 0                 |
|     1 | 0                 |
|     0 | 0                 |
|     0 | 0                 |
|     1 | 0                 |
+-------+-------------------+
10 rows in set (0.74 sec)

Offline inference

If you run inference on more than 1,000 samples, PolarDB for AI returns the following error:

ERROR 9050 (HY000): Please limit the SQL selected data length to less than '1000' or convert to offline prediction

To create an offline task, run the following statement:

/*polar4ai*/SELECT Delay FROM predict(MODEL airlines_gbm, SELECT * FROM db4ai.airlines_test) WITH (x_cols = 'Airline,Flight,AirportFrom,AirportTo,DayOfWeek,Time,Length', y_cols='Delay', mode='async');

The following is a sample result:

+--------------------------------------+
| task_id                              |
+--------------------------------------+
| bd0c1722-21e7-11ed-94a8-xxxxxxxxxxxx |
+--------------------------------------+
1 row in set (0.75 sec)

The query returns the task's task_id. You can use this task ID to query the task status and the download URL for the result.

/*polar4ai*/SHOW TASK `bd0c1722-21e7-11ed-94a8-xxxxxxxxxxxx`

The following is a sample result:

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| taskStatus | filePath                                                                                                                                           | results         | startTime                  | endTime                    |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| finish     | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/pc-xxxxxxx/airlines_gbm/xxxxx.csv?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx |                 | 2022-08-22 14:57:51.355240 | 2022-08-22 14:58:18.316818 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
1 row in set (0.24 sec)

Note

The filePath is valid for 100 minutes. You can check the Expires parameter in the filePath to see if the link is still valid. If you need to access the link, please do so within its validity period.

Offline evaluation and inference

Note

An offline task is a long-running batch processing task that does not return results in real time. Offline tasks include offline evaluation and offline inference.

View tasks

View all offline tasks in the current cluster.

Syntax

SHOW TASKS

Example

Run the following command to view all offline tasks in the current cluster.

/*polar4ai*/SHOW TASKS;

The following is a sample result:

+--------------------------------------+------------+-------------+----------------------------+----------------------------+
| task_id                              | task_type  | task_status | start_timestr              | end_timestr                |
+--------------------------------------+------------+-------------+----------------------------+----------------------------+
| 2cba0c74-1f8f-11ed-934a-xxxxxxxxxxxx | prediction | finish      | 2022-08-19 15:18:51.206829 |                            |
| 77b3a186-1f94-11ed-8eaa-xxxxxxxxxxxx | evaluation | finish      | 2022-08-19 15:56:44.465594 |                            |
| 972547a4-1fa3-11ed-9c6b-xxxxxxxxxxxx | evaluation | finish      | 2022-08-19 17:44:59.790353 | 2022-08-19 17:45:23.750100 |
| bd0c1722-21e7-11ed-94a8-xxxxxxxxxxxx | prediction | finish      | 2022-08-22 14:57:51.355240 | 2022-08-22 14:58:18.316818 |
| df05244e-21f7-11ed-be66-xxxxxxxxxxxx | evaluation | finish      | 2022-08-22 16:53:20.381577 | 2022-08-22 16:53:37.189953 |
| ec956db8-21fb-11ed-8400-xxxxxxxxxxxx | evaluation | finish      | 2022-08-22 17:22:21.122101 | 2022-08-22 17:22:39.428811 |
+--------------------------------------+------------+-------------+----------------------------+----------------------------+
9 rows in set (0.18 sec)

View task status

An offline task can have one of the following statuses:

init: The task is being initialized.
running: The task is running.
finish: The task is complete.
fail: The task has failed.

Syntax

SHOW TASK `task_id`

Parameters

Parameter	Description
task_id	The task ID.

Example

Run the following command to view the status of the task with the ID df05244e-21f7-11ed-be66-xxxxxxxxxxxx.

/*polar4ai*/SHOW TASK `df05244e-21f7-11ed-be66-xxxxxxxxxxxx`

The following is a sample result:

+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| taskStatus | filePath                                                                                                                                           | results         | startTime                  | endTime                    |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| finish     | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/pc-xxxxxxx/airlines_gbm/xxxxx.csv?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx | {"acc": 0.6694} | 2022-08-22 17:22:21.122101 | 2022-08-22 17:22:39.428811 |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
1 row in set (0.24 sec)

Note

The filePath is valid for 100 minutes. You can check the Expires parameter in the filePath to see if the link is still valid. If you need to access the link, please do so within its validity period.

Delete task

Syntax

DROP TASK `task_id`

Parameters

Parameter	Description
task_id	The task ID.

Example

Delete the task with the ID df05244e-21f7-11ed-be66-xxxxxxxxxxxx.

/*polar4ai*/DROP TASK `df05244e-21f7-11ed-be66-xxxxxxxxxxxx`

Delete a model

Use this statement to delete a model that you no longer need.

Syntax

DROP MODEL model_name

Parameters

Parameter	Description
model_name	The name of the model.

Example

Delete the airlines_gbm model.

/*polar4ai*/DROP MODEL airlines_gbm

A successful deletion of the airlines_gbm model returns the following output:

Query OK, 0 rows affected (0.57 sec)

Best practices for custom models

This topic walks you through the complete workflow for managing custom models, from offline creation to online inference.

Create a model offline.

The following script creates a LightGBM model offline:

# coding: utf-8
from pathlib import Path

import pandas as pd
from sklearn.metrics import mean_squared_error

import lightgbm as lgb
import joblib

def train_model():
    print('Loading data...')
    # load or create your dataset
    df_train = pd.read_csv('regression.train', header=None, sep='\t')
    df_test = pd.read_csv('regression.test', header=None, sep='\t')

    y_train = df_train[0]
    y_test = df_test[0]
    X_train = df_train.drop(0, axis=1)
    X_test = df_test.drop(0, axis=1)

    # create dataset for lightgbm
    lgb_train = lgb.Dataset(X_train, y_train)
    lgb_eval = lgb.Dataset(X_test, y_test, reference=lgb_train)


    # specify your configurations as a dict
    params = {
        'boosting_type': 'gbdt',
        'objective': 'regression',
        'metric': {'l2', 'l1'},
        'num_leaves': 31,
        'learning_rate': 0.05,
        'feature_fraction': 0.9,
        'bagging_fraction': 0.8,
        'bagging_freq': 5,
        'verbose': 0
    }

    print('Starting training...')
    # train
    gbm = lgb.train(params,
                    lgb_train,
                    num_boost_round=20,
                    valid_sets=lgb_eval,
                    callbacks=[lgb.early_stopping(stopping_rounds=5)])


    print('Saving model...')
    # save model to file
    # gbm.save_model('model.txt')
    joblib.dump(gbm, 'lgb.pkl')

    print('Starting predicting...')
    # predict
    y_pred = gbm.predict(X_test, num_iteration=gbm.best_iteration)
    # eval
    rmse_test = mean_squared_error(y_test, y_pred) ** 0.5
    print(f'The RMSE of prediction is: {rmse_test}')

The script exports the model file in the .pkl format and calls the predict method to return the inference result. The model also requires a Python dependency file.

The following example shows the content of the requirements.txt file:

lightgbm==3.3.3

Upload the model.

Run the following command to upload the model to PolarDB for AI.
```
/*polar4ai*/UPLOAD MODEL my_model WITH (model_location='https://xxxx.oss-cn-hangzhou.aliyuncs.com/xxxx/model.pkl?Expires=xxxx&OSSAccessKeyId=xxxx&Signature=xxxx', req_location='https://xxxx.oss-cn-hangzhou.aliyuncs.com/xxxx/requirements.txt?Expires=xxxx&OSSAccessKeyId=xxxx&Signature=xxxx')
```
The model_location parameter specifies the URL of the model file, and req_location specifies the URL of the model's dependency file. Prepare these two files and upload them to your private Object Storage Service (OSS) bucket. Then, run the command to import them into PolarDB for AI.

Sample result:
```
Query OK, 0 rows affected (0.29 sec)
```

Run the following command to check the model status.

/*polar4ai*/ SHOW MODEL my_model;

Sample result:

+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| modelStatus | modelPath                                                                                                                   |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| saved       | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/xxxxx.pkl?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx  |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)

A modelStatus of saved indicates a successful upload.

Deploy the model.

Run the following command to deploy the model on PolarDB for AI.
```
/*polar4ai*/ DEPLOY MODEL my_model;
```
Sample result:
```
Query OK, 0 rows affected (0.29 sec)
```

Run the following command to check the model status.

/*polar4ai*/ SHOW MODEL my_model;

Sample result:

+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| modelStatus | modelPath                                                                                                                   |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| serving     | http://db4ai-collie-cn-hangzhou.oss-cn-hangzhou.aliyuncs.com/xxxxx.pkl?OSSAccessKeyId=xxxxxx&Expires=xxxx&Signature=xxxxxx  |
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)

A modelStatus of serving indicates a successful deployment.

Run online inference.

Run the following command to perform online inference.

/*polar4ai*/ SELECT Y FROM PREDICT(MODEL my_model, SELECT * FROM db4ai.regression_test LIMIT 10) WITH (x_cols = 'x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15,x16,x17,x18,x19,x20,x21,x22,x23,x24,x25,x26,x27,x28', y_cols='');

Sample result:

+------+---------------------+
| Y    | predicted_results   |
+------+---------------------+
|  1.0 | 0.6262147669037363  |
|  0.0 | 0.5082804008241021  |
|  0.0 | 0.37533158372209957 |
|  1.0 | 0.461974928099089   |
|  0.0 | 0.3777339456553666  |
|  0.0 | 0.35045096227525735 |
|  0.0 | 0.4178165504012342  |
|  1.0 | 0.40869795422774036 |
|  1.0 | 0.6826481286570045  |
|  0.0 | 0.47021259543154736 |
+------+---------------------+
10 rows in set (0.95 sec)

Alibaba Cloud is only responsible for maintaining the underlying architecture and the provided operating system. You are responsible for components above the operating system, such as the applications you install. You retain control of the runtime environment for your instance. Additionally, upgrading the operating system yourself can cause adverse effects, including downtime. You are responsible for understanding the risks and taking the necessary precautions.