模型使用流程及说明

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

PolarDB for AI模型简介

使用内置模型

PolarDB for AI内置了两大机器学习模型:NL2SQL大模型和通义千问大模型。您无需进行额外的模型配置,即可使用这个两大机器学习模型提供的PolarDB for AI功能。

具体请参见:

构建自定义模型

在客户的实际业务场景中,模型经过算法调优后,模型结构可能会根据业务效果进行调整,内置的机器学习模型可能无法满足用户的实际业务需求。因此,PolarDB for AI还支持构建自定义模型的功能,并支持将模型运行在机密容器中,进一步保障了客户模型中数据的安全。

您可按照如下流程构建并使用自定义模型:

加载外部模型

此外,基于PolarDB for AI的MLOps能力,您也可以直接将外部训练好的模型上传和部署在PolarDB中,并使用外部模型,如如基于Sklearn框架的LightGBM、GBDT,或者基于TensorFlow/PyTorch框架下的深度学习算法等。

您可按照如下流程加载外部模型:

创建模型

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

语法说明

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值为gbdt时,model_parameter取值请参见参数说明

  • model_class值为linearreg时,model_parameter取值请参见参数说明

  • model_class值为svr时,model_parameter取值请参见参数说明

  • model_class值为bst时,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,后续可以借助该平台来管理上传的模型。PolarDB for AI支持的模型格式有PMML、ONNX、Checkpoint(适用于Tensorflow、PyTorch平台)。

语法说明

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

表名。

x_cols

模型训练输入列。多个列名之间使用英文逗号(,)分隔。

y_cols

模型训练输出列。

metrics

模型评估指标。取值如下:

  • acc:准确率,适用于分类任务。

  • r2_score:决定系数,适用于回归任务。

  • ks:ks值,表示模型能将正负样本区分出来的程度,适用于分类任务。

  • auc:auc值,表示ROC曲线与坐标轴围成的面积,适用于分类任务。

  • Fscore:F-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参数查看链接是否在有效期内。如果您需要访问该链接,请在有效期内进行访问。

模型推理

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

语法说明

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,您可以通过该任务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参数查看链接是否在有效期内。如果您需要访问该链接,请在有效期内进行访问。

离线评估与离线推理任务

查看离线任务列表

查看当前集群下的所有离线任务。

说明

离线任务是指运行时间长,且无法实时获取结果的批处理任务,任务类型包括离线评估和离线推理两种类型。

语法说明

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参数查看链接是否在有效期内。如果您需要访问该链接,请在有效期内进行访问。

删除模型

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

语法说明

DROP MODEL model_name 

参数说明

参数

说明

model_name

模型名称。

示例

删除airlines_gbm模型。

/*polar4ai*/DROP MODEL airlines_gbm 

执行结果显示类似如下内容,表示已成功删除airlines_gbm模型:

Query OK, 0 rows affected (0.57 sec)

自定义模型最佳实践

接下来,我们将通过一个示例,带您了解自定义模型管理的完整链路。本最佳实践以模型的离线训练和在线推理为例进行介绍。

  1. 离线训练模型

    以LightGBM算法为例,离线训练脚本如下:

    # 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}')
    

    其中,模型文件导出为pkl格式,并且调用predict方法返回推理结果,同时还需要提供模型运行依赖的python文件。

    文件requirements.txt内容示例如下:

    lightgbm==3.3.3
  2. 上传模型

    1. 执行以下命令,将模型上传至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')

      其中,model_locationreq_location分别表示模型文件地址以及模型运行时所依赖的文件地址,您可以将这两个文件提前准备好,并上传至自己的私有oss bucket,再通过以上命令上传至PolarDB for AI平台。

      返回结果如下:

      Query OK, 0 rows affected (0.29 sec)
    2. 执行以下命令,查看模型状态。

      /*polar4ai*/ SHOW 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,表示模型上传成功。

  3. 部署模型

    1. 执行以下命令,将模型部署在PolarDB for AI上。

      /*polar4ai*/ DEPLOY MODEL my_model;

      返回结果如下:

      Query OK, 0 rows affected (0.29 sec)
    2. 执行以下命令,查看模型状态。

      /*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,表示模型部署成功。

  4. 模型在线推理

    执行以下命令,执行模型在线推理任务。

    /*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='');

    返回结果如下:

    +------+---------------------+
    | 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)