CREATE MODEL

Description

CREATE MODEL trains a machine learning model and saves the model.

Precautions

The model name must be unique. Pay attention to the naming format.
The AI training duration fluctuates greatly, and in some cases, the training duration is long. If the duration specified by the GUC parameter statement_timeout is too long, the training will be interrupted. You are advised to set statement_timeout to 0 so that the statement execution duration is not limited.

Syntax

CREATE  MODEL model_name USING architecture_name
FEATURES { {attribute_list} }
[TARGET attribute_name],
FROM ([schema.]table_name | subquery)
[WITH ([hyper_parameter_name = {hp_value | DEFAULT},]...)];

Parameters

model_name
Name of the training model, which must be unique.

Value range: a string. It must comply with the naming convention.
architecture_name
Algorithm type of the training model.

Value range: a string. Currently, the value can be logistic_regression, linear_regression, svm_classification, or kmeans.
attribute_list
Enumerated input column name of the training model.

Value range: a string. It must comply with the naming convention of data attributes.
attribute_name
Target column name of the training model in a supervised learning task (simple expression processing can be performed).

Value range: a string. It must comply with the naming convention of data attributes.
subquery
Data source.

Value range: a string. It must comply with the SQL syntax of databases.
hyper_parameter_name
Hyperparameter name of the machine learning model.

Value range: a string. The value range varies depending on the algorithm. For details, see "Hyperparameters supported by operators" in section "DB4AI: Database-driven AI > Native DB4AI Engine" in Feature Guide.
hp_value
Hyperparameter value.

Value range: a string. The value range varies depending on the algorithm. For details, see "Default values and value ranges of hyperparameters" in section "DB4AI: Database-driven AI > Native DB4AI Engine" in Feature Guide.

Examples

-- Create a data table.
gaussdb=# CREATE TABLE houses (
id INTEGER,
tax INTEGER,
bedroom INTEGER,
bath DOUBLE PRECISION,
price INTEGER,
size INTEGER,
lot INTEGER,
mark text
);

-- Insert training data.
gaussdb=# INSERT INTO houses(id, tax, bedroom, bath, price, size, lot, mark) VALUES
(1,590,2,1,50000,770,22100,'a+'),
(2,1050,3,2,85000,1410,12000,'a+'),
(3,20,2,1,22500,1060,3500,'a-'),
(4,870,2,2,90000,1300,17500,'a+'),
(5,1320,3,2,133000,1500,30000,'a+'),
(6,1350,2,1,90500,850,25700,'a-'),
(7,2790,3,2.5,260000,2130,25000,'a+'),
(8,680,2,1,142500,1170,22000,'a-'),
(9,1840,3,2,160000,1500,19000,'a+'),
(10,3680,4,2,240000,2790,20000,'a-'),
(11,1660,3,1,87000,1030,17500,'a+'),
(12,1620,3,2,118500,1250,20000,'a-'),
(13,3100,3,2,140000,1760,38000,'a+'),
(14,2090,2,3,148000,1550,14000,'a-'),
(15,650,3,1.5,65000,1450,12000,'a-');

-- Train the model.
gaussdb=# CREATE MODEL price_model USING logistic_regression
 FEATURES size, lot
 TARGET mark
 FROM HOUSES
 WITH learning_rate=0.88, max_iterations=default;

-- Delete the model.
gaussdb=# DROP MODEL price_model;

-- Delete the table.
gaussdb=# DROP TABLE houses;