Use case for a custom model - PolarDB - Alibaba Cloud Documentation Center

This topic describes a sample use case in which a custom model is used for prediction and classification.

Assume that you need to predict whether patients are sick based on detected data.

You make the prediction based on a dataset that consists of regularized 30-dimensional vectors. The dataset is obtained from the Wisconsin Breast Cancer Dataset and has been imported into a PolarDB database, as shown in the following figure.

The diagnosis column in the dataset is used to determine whether patients are sick.

A logistic regression prediction model that is created on the TensorFlow platform is used to perform the prediction. The model is saved as a binary file and uploaded to an Object Storage Service (OSS) bucket. For more information about the model creation process, visit the following URL: https://www.tensorflow.org/guide/core/logistic_regression_core.

To use a custom model for prediction and classification, perform the following steps:

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

Connect to a database and upload the created model to the AI node.

Execute the following statement to upload the model namedlr_model. You can specify a custom name for the model. The model name must start with a letter and cannot contain hyphens (-). The model name specified in statements used in the subsequent sections must be consistent with this custom name.
```
/*polar4ai*/ UPLOAD MODEL lr_model WITH (model_location = 'https://shared-model.oss-cn-hangzhou.aliyuncs.com/logistic_regression_model.tgz');
```

Execute the following statement to view the model status:

/*polar4ai*/ SHOW MODEL lr_model;

The following result is returned:

+-------------+----------------------------------------------------+
| modelStatus | modelPath                                          |
+-------------+----------------------------------------------------+
| saved_oss   | http://bucket_prefix/logistic_regression_model.tgz |
+-------------+----------------------------------------------------+
1 row in set (0.16 sec)

If the model status is saved_oss, the model is uploaded.

Deploy the model.

Execute the following statement to deploy the model:

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

Execute the following statement to view the model status:

/*polar4ai*/ SHOW MODEL lr_model;

The following result is returned:

+-------------+---------------------------------------------------------+
| modelStatus | modelPath                                               |
+-------------+---------------------------------------------------------+
| serving     | http://user_bucket_prefix/logistic_regression_model.tgz |
+-------------+---------------------------------------------------------+
1 row in set (0.16 sec)

If the model status is serving, the model is deployed.

Create a function.
Execute the following statement to create the lr_model function:
```
CREATE FUNCTION lr_model RETURNS REAL SONAME "#ailib#_lr_model.so";
```
The following results indicate that the function is created:
```
Query OK, 0 rows affected (0.11 sec)
```

Execute the following statement to call the lr_model function for prediction and classification:

SELECT lr_model(radius_mean, texture_mean, perimeter_mean, area_mean, smoothness_mean, compactness_mean, concavity_mean, concave_poinits_mean, symmetry_mean, fractal_dimension_mean, radius_ste, texture_ste, perimeter_ste, area_ste, smoothness_ste, compactness_ste, concavity_ste, concave_poinits_ste, symmetry_ste, fractal_dimension_ste, radius_largest, texture_largest, perimeter_largest, area_largest, smoothness_largest, compactness_largest, concavity_largest, concave_poinits_largest, symmetry_largest, fractal_dimension_largest) AS classification_result FROM logistic_regression_model_norm_table limit 10;

The following result is returned:

+-----------------------+
| classification_result |
+-----------------------+
| 1.00000000            |
| 1.00000000            |
| 1.00000000            |
| 0.00000000            |
| 1.00000000            |
| 0.00000000            |
| 1.00000000            |
| 0.00000000            |
| 1.00000000            |
| 0.00000000            |
+-----------------------+
10 rows in set (0.71 sec)

In the preceding result, the value 1 indicates that the patients are sick, and the value 0 indicates that the patients are not sick.