DB4AI: Database-driven AI-Native DB4AI Engine
Tags: opengauss
Published: 2023-04-13 09:43:22

DB4AI uses database capabilities to drive AI tasks and implement data storage and technology stack isomorphism. By integrating AI algorithms into the database, openGauss supports the native AI computing engine, model management, AI operators, and native AI execution plan, providing users with inclusive AI technologies. Different from the traditional AI modeling process, DB4AI one-stop modeling eliminates repeated data flowing among different platforms, simplifies the development process, and plans the optimal execution path through the database, so that developers can focus on the tuning of specific services and models. It outcompetes similar products in ease-of-use and performance.

**Native DB4AI Engine**

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.

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

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

**Usage Guide**
1. Introduce the algorithms supported in this version.

The DB4AI of the current version supports the following new algorithms:

Table 2 Supported algorithms

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

2. Learn about the model training syntax.

2.1 CREATE MODEL

You can run the **CREATE MODEL** statement to create and train a model. This SQL statement uses the public Iris dataset for model training.

2.2 The following uses multiclass as an example to describe how to train a model. Specify **sepal_length**, **sepal_width**, **petal_length**, and **petal_width** as feature columns in the **tb_iris** training set, and use the multiclass algorithm to create and save the **iris_classification_model** model.

openGauss=# CREATE MODEL iris_classification_model USING xgboost_regression_logistic FEATURES sepal_length, sepal_width,petal_length,petal_width TARGET target_type < 2 FROM tb_iris_1 WITH nthread=4, max_depth=8;
MODEL CREATED. PROCESSED 1

In the preceding command:

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

2.2.2 **USING** specifies the algorithm name.

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

2.2.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**.

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

The framework supports various hyperparameter combinations for different operators.

Table 3 Hyperparameters supported by operators

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

The default value and value range of each hyperparameter are as follows:

Table 4 Default values and value ranges of hyperparameters

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

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

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

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

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

3. 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.

The model details are stored in the system catalog in binary mode. You can use the **gs_explain_model** function to view the model details. The statement is as follows:

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

4. 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…;

openGauss=# SELECT id, PREDICT BY iris_classification (FEATURES sepal_length,sepal_width,petal_length,petal_width) as "PREDICT" FROM tb_iris limit 3;

id  | PREDICT
-----+---------
  84 |       2
  85 |       0
  86 |       0
(3 rows)

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.

5. 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** clause or an optional parameter. The supported parameters are as follows:

Table 5 Parameters supported by EXPLAIN

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

Example:

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/08bajg3tzok9y1manvnl.png)

6. Perform troubleshooting in case of exceptions.

6.1 Training phase

6.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';
ERROR:  Invalid hyperparameter value for optimizer. Valid values are: gd, ngd.

6.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. For example:

openGauss=# CREATE MODEL patient_linear_regression USING linear_regression FEATURES second_attack,treatment TARGET trait_anxiety  FROM patients;
ERROR:  The model name "patient_linear_regression" already exists in gs_model_warehouse.

6.1.3 Scenario 3: If the value in the **FEATURE** or **TARGETS** column is *, an error message with the cause is displayed. For example:

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

6.1.4 Scenario 4: If the keyword **TARGET** is used in the unsupervised learning method or is not applicable to the supervised learning method, an error message with the cause is displayed. For example:

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

6.1.5 Scenario 5: If there is only one category in the **TARGET** column, an error message with the cause is displayed. For example:

openGauss=# CREATE MODEL ecoli_svmc USING multiclass FEATURES f1, f2, f3, f4, f5, f6, f7 TARGET cat FROM (SELECT * FROM db4ai_ecoli WHERE cat='cp');
ERROR:  At least two categories are needed

6.1.6 Scenario 6: DB4AI filters out data that contains null values during training. When the model data involved in training is null, an error message with the cause is displayed. For example:

openGauss=# create model iris_classification_model using xgboost_regression_logistic features message_regular target error_level from error_code;
ERROR:  Training data is empty, please check the input data.

6.1.7 Scenario 7: The DB4AI algorithm has restrictions on the supported data types. If the data type is not in the whitelist, an error message is returned and the invalid OID is displayed. You can check the OID in **pg_type** to determine the invalid data type. For example:

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

6.1.8 Scenario 8: 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.

6.2 Model parsing

6.2.1 Scenario 9: If the model name cannot be found in the system catalog, an error message with the cause is displayed. For example:

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

6.3 Prediction phase

6.3.1 Scenario 10: If the model name cannot be found in the system catalog, an error message with the cause is displayed. For example:

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

6.3.2 Scenario 11: If the data dimension and data type of the **FEATURES** task are inconsistent with those of the training set, an error message with the cause is displayed. For example:

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

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