OcientML™ functionality enables you to create a machine learning model, rename the model, export the syntax for the model creation, retrain the model, execute a query against the model, and drop the model.

CREATE MLMODEL

Train a new machine learning model of type <model type> on the result set returned by the SQL SELECT statement. After the database creates the model, <model name> becomes a callable function in SQL SELECT statements. 

Syntax

CREATE [ OR REPLACE ] MLMODEL <model_name> TYPE <model_type>
    ON( <SQL SELECT statement> ) [options(<option_list>)]

model_name

model_type

These models are supported. You can find full descriptions of each model in Regression Models, Classification Models, Clustering and Dimension Reduction Models, or Other Models.

• SIMPLE LINEAR REGRESSION
• MULTIPLE LINEAR REGRESSION
• POLYNOMIAL REGRESSION
• LINEAR COMBINATION REGRESSION
• VECTOR AUTOREGRESSION
• KMEANS
• KNN (K Nearest Neighbors)
• LOGISTIC REGRESSION
• NAIVE BAYES
• NONLINEAR REGRESSION
• FEEDFORWARD NETWORK
• PRINCIPAL COMPONENT ANALYSIS
• LINEAR DISCRIMINANT ANALYSIS
• SUPPORT VECTOR MACHINE
• DECISION TREE
• GAUSSIAN MIXTURE MODEL
• ASSOCIATION RULES
• GRADIENT BOOSTED TREES
• REGRESSION TREE

option_list

The SQL SELECT statement that serves as the basis for the model must return rows that fit the specified requirements of the model. For example, in multiple linear regression, the first N columns are the independent variables, and the last column is the dependent variable.

Example

Assume you created the mldata table that contains the data for the model. Then, you can create the my_model machine learning model based on that data.

CREATE MLMODEL my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM mldata
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);

ALTER MLMODEL RENAME

Rename a machine learning model. Use the IF EXISTS clause to ignore any models that do not exist.

Required Privileges

To rename a view, you must have the ALTER MLMODEL privilege for the model.

The Ocient System requires these privileges if this statement includes a change to the schema:

• VIEW privilege on the current schema of the model
• VIEW MLMODEL and CREATE MLMODEL privileges on the target schema (if the schema already exists)
• CREATE MLMODEL privilege on the database (if the schema does not exist)

Syntax

ALTER MLMODEL [IF EXISTS] <model name> RENAME TO <new model name>

Example

ALTER MLMODEL my_model RENAME TO my_model_slr;

DROP MLMODEL

Drop a machine learning model. Use the IF EXISTS clause to ignore any models that do not exist.

Syntax

DROP MLMODEL [IF EXISTS] model_name [, ...]

Example

Drop the machine learning model my_model.

DROP MLMODEL my_model;

Drop multiple machine learning models.

DROP MLMODEL my_model1, my_model2;

EXPORT MLMODEL

Return the SQL statement that can recreate the machine learning model.

Syntax

EXPORT MLMODEL <model name>

Example

EXPORT MLMODEL my_model;

Output

export
---------------------------------------------
CREATE MLMODEL "my_schema"."my_model" TYPE simple linear regression ON(select x, y from mldemo.slr) OPTIONS('metrics' -> 'false');
Fetched 1 row

The output SQL statement includes the schema explicitly.

REFRESH MLMODEL

Retrain a machine learning model without changing any model options.

Syntax

REFRESH MLMODEL <model name>

Example

REFRESH MLMODEL my_model;

Related Links

Machine Learning in Ocient

Machine Learning Model Functions

Regression Models

Classification Models

Clustering and Dimension Reduction Models

Other Models