本文介绍了创建模型、查看模型列表、模型评估以及模型推理等功能的语法、参数说明及其示例内容。

前提条件

已在控制台开启PolarDB for AI功能。

注意事项

  • 使用PolarDB for AI功能时，使用客户端连接集群或使用命令行连接集群时必须连接PolarDB的集群地址，而不能连接主地址。查看连接地址详情请参见集群地址和主地址。
    说明 如果使用MySQL命令行连接集群地址，使用HINT语法时，需要在命令中增加-c选项。示例如下：
    mysql -h localhost -u username -p password -c
  • 执行SQL语句时，需要在SQL语句前添加/*polar4ai*/，且当前账号执行的SQL语句会被添加到审计日志中。

创建模型

创建一个机器学习模型。机器学习模型的训练过程是异步的，需要通过查询模型状态来判断该模型是否训练完成。

语法
CREATE MODEL model_name WITH ( model_class = '', x_cols = '', y_cols='',model_parameter=()) AS (SELECT select_expr [, select_expr] ... FROM table_reference)
参数说明
参数说明
model_name 模型名称。
model_class模型的类型。取值如下：
x_cols模型训练输入列。
说明 多个列名之间使用英文逗号（,）分隔。
y_cols模型训练输出列。
model_parameter模型训练参数。
  • 当model_class值为lightgbm时，model_parameter取值请参见参数说明。
  • 当model_class值为deepfm时，model_parameter取值请参见参数说明。
  • 当model_class值为kmeans时，model_parameter取值请参见参数说明。
  • 当model_class值为randomforestreg时，model_parameter取值请参见参数说明。
  • 当model_class值为gbrt时，model_parameter取值请参见参数说明。
  • 当model_class值为linearreg时，model_parameter取值请参见参数说明。
  • 当model_class值为svr时，model_parameter取值请参见参数说明。
select_expr 列名。
table_reference表名。
示例

创建airlines_gbm模型。

/*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)
执行结果如下：
Query OK, 0 rows affected (0.79 sec)

上传模型

PolarDB for AI支持上传自定义机器学习模型，您可以将离线训练好的模型上传至PolarDB for AI，后续可以借助该平台来管理上传的模型。

语法
UPLOAD MODEL model_name WITH (model_location = '', req_location = '')
参数说明
参数说明
model_name模型名称。
model_location模型文件地址。
req_location模型运行依赖的文件地址。
示例
将创建好的模型my_model上传至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)
执行以下命令，查看模型状态。
/*polar4ai*/ SHOW MODEL my_model;
返回结果如下：
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| 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)
modelStatus显示为saved，即表示模型上传成功。

部署模型

您可以将上传后的模型部署在PolarDB for AI平台上，部署成功后，模型为在线状态，后续调用模型进行模型推理时，速度会更快。

语法
DEPLOY MODEL model_name
参数说明
参数说明
model_name模型名称。
示例
将模型my_model部署在PolarDB for AI平台。
/*polar4ai*/ DEPLOY MODEL my_model;
返回结果如下：
Query OK, 0 rows affected (0.29 sec)
执行如下命令，查看模型状态：
/*polar4ai*/ SHOW MODEL my_model;
返回结果如下：
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| 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)
modelStatus显示为serving，表示模型部署成功。

查看模型列表

查看当前集群下的所有模型。

语法
SHOW MODELS
示例
/*polar4ai*/SHOW MODELS
执行结果如下：
+-----------------------+-----------------+--------------+
| 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)

查看模型状态

查看模型的当前状态。创建模型之后使用该语法来查看模型是否训练完成。模型状态主要有以下几种：
  • training：模型训练中。
  • loading_data：模型在加载数据中。
  • trained：模型已经完成训练。
  • saved：模型已经被保存。
  • serving：模型正在服务中。
  • deleted：模型已经被删除。
语法
SHOW MODEL model_name
参数说明
参数说明
model_name 模型名称。
示例

查看airlines_gbm模型的当前状态。

/*polar4ai*/SHOW MODEL airlines_gbm;
执行结果如下：
+-------------+-----------------------------------------------------------------------------------------------------------------------------+
| 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)
说明 modelPath的有效时间为100分钟，您可以通过modelPath中的Expires参数查看链接是否在有效期内。如果您需要访问该链接，请在有效期内进行访问。

查看模型详情

语法
DESCRIBE MODEL model_name
参数说明
参数说明
model_name模型名称。
示例
查看airlines_gbm模型的详细信息。
/*polar4ai*/DESCRIBE MODEL airlines_gbm;
执行结果如下：
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

模型评估

评估已经训练好的模型的效果。

语法
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='')
参数说明
参数说明
select_expr 模型评估结果中展示的列名。
model_name模型名称。
select_expr_for_prediction 用于进行模型评估的列名。
table_reference 表名。
row_count预测的记录数量。
x_cols 模型训练输入列。多个列名之间使用英文逗号（,）分隔。
y_cols模型训练输出列。
metrics模型评估指标。取值如下：
  • acc：准确率，用于分类任务。
  • r2_score：决定系数，用于回归任务。
示例
  1. 使用已经训练完成的airlines_gbm模型进行模型评估。
    /*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');
    执行结果如下：
    +--------------------------------------+
| task_id                              |
+--------------------------------------+
| df05244e-21f7-11ed-be66-xxxxxxxxxxxx |
+--------------------------------------+
1 row in set (0.95 sec)
  2. 执行如下命令，查看任务状态。
    /*polar4ai*/SHOW TASK `df05244e-21f7-11ed-be66-xxxxxxxxxxxx`;
    执行结果如下：
    +------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| 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)
    说明 filePath的有效时间为100分钟，您可以通过filePath中的Expires参数查看链接是否在有效期内。如果您需要访问该链接，请在有效期内进行访问。

查看任务列表

查看当前集群下的所有离线任务。
说明 离线任务是指运行时间长，且无法实时获取结果的批处理任务，任务类型包括离线评估和离线推理两种类型。
语法
SHOW TASKS
示例

执行以下命令，查看当前集群下的所有离线任务。

/*polar4ai*/SHOW TASKS;
执行结果如下：
+--------------------------------------+------------+-------------+----------------------------+----------------------------+
| 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)

查看任务状态

查看离线任务的当前状态。任务状态主要有以下几种：
  • init：任务初始化。
  • running：任务执行中。
  • finish：任务完成。
  • fail：任务失败。
语法
SHOW TASK `task_id`
参数说明
参数说明
task_id任务ID。
示例

查看任务ID为df05244e-21f7-11ed-be66-xxxxxxxxxxxx的任务状态。

/*polar4ai*/SHOW TASK `df05244e-21f7-11ed-be66-xxxxxxxxxxxx`
执行结果如下：
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| 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)
说明 filePath的有效时间为100分钟，您可以通过filePath中的Expires参数查看链接是否在有效期内。如果您需要访问该链接，请在有效期内进行访问。

模型推理

使用已经训练好的模型进行在线推理或离线推理。

语法
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= '')
参数说明
参数说明
select_expr模型推理结果中展示的列名。
model_name模型名称。
select_expr_for_prediction模型推理时使用的列名。
table_reference表名。
mode模式。填写async时，表示离线推理；不填时表示在线推理。
row_count推理的样本数量。
x_cols模型训练输入列。多个列名之间使用英文逗号（,）分隔。
示例
  • 在线推理。
    使用已经创建好的模型airlines_gbm进行在线推理。
    /*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');
    执行结果如下：
    +-------+-------------------+
| 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)
  • 离线推理。
    当推理样本数量大于1000时，PolarDB for AI会提示您采用离线任务：
    ERROR 9050 (HY000): Please limit the SQL selected data length to less than '1000' or convert to offline prediction
    创建离线任务如下：
    /*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');
    执行结果如下：
    +--------------------------------------+
| task_id                              |
+--------------------------------------+
| bd0c1722-21e7-11ed-94a8-xxxxxxxxxxxx |
+--------------------------------------+
1 row in set (0.75 sec)
    以上查询结果返回该任务的task_id，您可以通过查询任务状态来查看任务的执行状态以及对应结果的下载地址。
    /*polar4ai*/SHOW TASK `bd0c1722-21e7-11ed-94a8-xxxxxxxxxxxx`
    执行结果如下：
    +------------+----------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------------------+----------------------------+
| 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)
    说明 filePath的有效时间为100分钟，您可以通过filePath中的Expires参数查看链接是否在有效期内。如果您需要访问该链接，请在有效期内进行访问。

删除模型

当模型不再使用时，使用该语法删除已经创建的模型。

语法
DROP MODEL model_name
参数说明
参数说明
model_name 模型名称。
示例

删除airlines_gbm模型。

/*polar4ai*/DROP MODEL airlines_gbm
执行结果显示类似如下内容，表示已成功删除siku_kmeans模型：
Query OK, 0 rows affected (0.57 sec)