DB4AI
Tags: opengauss
Published: 2023-03-09 07:03:01

The current version of openGauss supports the native DB4AI capability. By introducing native AI operators, openGauss simplifies the operation process and fully utilizes the optimization and execution capabilities of the database optimizer and executor to obtain the high-performance model training capability in the database. With a simpler model training and prediction process and higher performance, developers can focus on model tuning and data analysis in a shorter period of time, avoiding fragmented technology stacks and redundant code implementation.

DB4AI of the current version supports logistic regression (binary classification tasks), linear regression, and vector machine algorithms (classification tasks) based on the SGD operator, as well as the K-Means clustering algorithm based on the K-Means operator.

**Keyword Parsing**
Table 1 DB4AI syntax and keywords

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3petd0kvzrz041pcnqyp.png)

**Usage Guide**
1. You can run the **CREATE MODEL** statement to create and train a model.

Taking dataset kmeans_2d as an example, the data content of the table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w977dxv6zxnnwsjnndon.png)

The data type of the **position** field in this table is double precision[].

Specify **position** as a feature column in the kmeans_2d training set, and use the K-Means algorithm to create and save the point_kmeans model.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lcjmg959m27b07efexti.png)

In the preceding command:

1. The **CREATE MODEL** statement is used to train and save a model.

2. **USING** specifies the algorithm name.

3. **FEATURES** specifies the features of the training model and needs to be added based on the column name of the training data table.

4. **TARGET** specifies the training target of the model. It can be the column name of the data table required for training or an expression, for example, **price > 10000**.

5. **WITH** specifies the hyperparameters used for model training. When the hyperparameters are not set by the user, the framework uses the default value.

The framework supports various hyperparameter combinations for different operators. For details, see Table 2.

Table 2 Hyperparameters supported by operators

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/69pnt1pfxi91pzkds6wu.png)

Table 3 shows the default value and value range of each hyperparameter.

Table 3 Default value and value range of each hyperparameter

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8r5jzg9x42cypo7psh7z.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8g0zzg5f2czluz9uda1l.png)

If the model is saved successfully, the following information is returned:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/abyu57xlfcq19ayj042r.png)

2. View the model information.

After the training is complete, the model is stored in the gs_model_warehouse system catalog. You can view information about the model and training process in the gs_model_warehouse system catalog.

You can view a model by viewing the system catalog. For example, run the following SQL statement to view the model named point_kmeans:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rw0ufmimo1caoj5mgb5h.png)

3. Use an existing model to perform a prediction task.

Use the **SELECT** and **PREDICT BY** keywords to complete the prediction task based on the existing model.

Query syntax: SELECT… PREDICT BY… (FEATURES…)… FROM…;

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/51dvzb86au7ul50vaccx.png)

For the same prediction task, the results of the same model are stable. In addition, models trained based on the same hyperparameter and training set are stable. AI model training is random (random gradient descent of data distribution each batch). Therefore, the computing performance and results of different models can vary slightly.

4. View the execution plan.

You can use the **EXPLAIN** statement to analyze the execution plan in the model training or prediction process of **CREATE MODEL** and **PREDICT BY**. The keyword **EXPLAIN** can be followed by a **CREATE MODEL** or **PREDICT BY** statement/clause or an optional parameter. Table 4 shows the supported parameters.

Table 4 Parameters supported by EXPLAIN

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zrdx7l892vob5w23zfvn.png)

Examples:
openGauss=# Explain CREATE MODEL patient_logisitic_regression USING logistic_regression FEATURES second_attack, treatment TARGET trait_anxiety > 50 FROM patients WITH batch_size=10, learning_rate = 0.05;
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g77w1s8kprjolyli0v8i.png)

5. Perform troubleshooting in case of exceptions.

5.1 Training phase

5.1.1 Scenario 1: When the value of the hyperparameter exceeds the value range, the model training fails and an error message is returned. For example:
openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET trait_anxiety  FROM patients WITH optimizer='aa';

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5nkskokrc53t2e8q44tm.png)

5.1.2 Scenario 2: If the model name already exists, the model fails to be saved, and an error message with the cause is displayed:
openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET trait_anxiety  FROM patients;

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4120o8y3hqndg4xvnhms.png)

5.1.3 Scenario 3: If the value in the FEATURE or TARGETS column is *, ERROR is returned with the error cause:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f82tp7v4c59y2v5d26dn.png)

5.1.4 Scenario 4: If the keyword TARGET is used in the unsupervised learning method or is not applicable to the supervised learning method, ERROR is returned with the error cause:

openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment FROM patients;
ERROR:  Supervised ML algorithms require TARGET clause
-----------------------------------------------------------------------------------------------------------------------------
CREATE MODEL patient_linear_regression USING linear_regression TARGET trait_anxiety  FROM patients;   ERROR:  Supervised ML algorithms require FEATURES clause

5.1.5 Scenario 5: If the GUC parameter statement_timeout is set, the statement that is executed due to training timeout will be terminated. In this case, execute the CREATE MODEL statement. Parameters such as the size of the training set, number of training rounds (iteration), early termination conditions (tolerance and max_seconds), and number of parallel threads (nthread) affect the training duration. When the duration exceeds the database limit, the statement execution is terminated and model training fails.

5.2 Prediction phase

5.2.1 Scenario 6: If the model name cannot be found in the system catalog, the database reports ERROR:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3kiz6vpsshcfxltcjbsz.png)

5.2.2 Scenario 7: If the data dimension and data type of the FEATURES task are inconsistent with those of the training set, ERROR is reported with the error cause. For example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/75mz3w4mwv0093bo8ibo.png)