Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ SQL Reference/ SQL Syntax/ C/ CREATE MODEL
Updated on 2024-06-03 GMT+08:00
View PDF
Share

CREATE MODEL

Description

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 retraining 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;

Helpful Links

DROP MODEL and PREDICT BY

Parent topic: C