PolarDB：模型使用流程及說明

文法說明

CREATE MODEL model_name WITH ( model_class = '', x_cols = '', y_cols='',model_parameter=()) AS (SELECT select_expr [, select_expr] ... FROM 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)

文法說明

DEPLOY MODEL 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，表示模型部署成功。

文法說明

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='')

參數說明

樣本

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= '')

參數說明

樣本

• 線上推理。

  使用已經建立好的模型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參數查看連結是否在有效期間內。如果您需要訪問該連結，請在有效期間內進行訪問。

文法說明

DROP MODEL model_name 

參數說明

樣本

刪除airlines_gbm模型。

/*polar4ai*/DROP MODEL airlines_gbm 

執行結果顯示類似如下內容，表示已成功刪除airlines_gbm模型：

Query OK, 0 rows affected (0.57 sec)