PolarDB:Use case for a custom model

Step 1: Upload the model to the AI node

Connect to your database and run the following statement to upload the model. In this example, the model is named lr_model. Choose a name that starts with a letter and contains no hyphens (-). Use this name consistently in all subsequent statements.

/*polar4ai*/ UPLOAD MODEL lr_model WITH (model_location = 'https://shared-model.oss-cn-hangzhou.aliyuncs.com/logistic_regression_model.tgz');

To confirm the upload succeeded, check the model status:

/*polar4ai*/ SHOW MODEL lr_model;

Expected output:

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

A modelStatus of `saved_oss` confirms that the model is stored in OSS and ready for deployment.

Step 2: Deploy the model

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

The mode = 'in_db' parameter deploys the model to the AI node for in-database inference, so predictions run without sending data to an external service.

To confirm deployment, check the model status again:

/*polar4ai*/ SHOW MODEL lr_model;

Expected output:

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

A modelStatus of `serving` confirms that the model is deployed and available for queries.

Step 3: Create a SQL function for the model

CREATE FUNCTION lr_model RETURNS REAL SONAME "#ailib#_lr_model.so";

This statement binds the model to a SQL user-defined function (UDF). The function name must match the model name (lr_model). If you used a different model name, replace both lr_model occurrences accordingly.

Expected output:

Query OK, 0 rows affected (0.11 sec)

Step 4: Run classification

Call the function in a SELECT statement, passing all 30 feature columns as arguments:

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;

Expected output:

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

Each row in classification_result corresponds to one patient record:

• `1` — the model predicts the patient is sick

• `0` — the model predicts the patient is not sick

Remove the LIMIT 10 clause to run predictions across the full dataset.