SQL Reference

Machine Learning Model Functions

﻿ supports the following SQL functions for machine learning models.
The scope of a machine learning model is the schema.
Supported Machine Learning Models
Supported machine learning models are divided into these categories. 
Regression Models
﻿Simple Linear Regression﻿ 
﻿Multiple Linear Regression﻿ 
﻿Vector Autoregression﻿﻿
﻿Polynomial Regression﻿﻿
﻿Linear Combination Refression﻿ 
﻿Nonlinear Regression﻿﻿
Classification Models
﻿K Nearest Neighbor Classification﻿ 
﻿Naive Bayes Classification﻿ 
﻿Decision Tree﻿﻿
﻿Logistic Regression﻿ 
﻿Support Vector Machine﻿﻿
Clustering and Dimension Reduction Models
﻿Principal Component Analysis﻿﻿
﻿K-Means Clustering﻿﻿
﻿Gaussian Mixture﻿﻿
﻿Linear Discriminant Analysis﻿﻿
Other Models
﻿Association Rules﻿﻿
﻿Feedforward Neural Network﻿ 
﻿
Machine Learning Model Syntaxes
You can 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
SQL
CREATE MLMODEL <model_name> TYPE <model_type> 
    ON( <SQL SELECT statement> ) [options(<option_list>)]
CREATE MLMODEL <model_name> TYPE <model_type> 
    ON( <SQL SELECT statement> ) [options(<option_list>)]
﻿
model_name
Parameter
Data Type
Description
model name
VARCHAR
The name of the model to create.
model_type
Parameter
Data Type
Description
model type
VARCHAR
The type of machine learning model to create. 
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
option_list
Parameter
Data Type
Description
option_list
VARCHAR
The options for the specified machine learning model that is specified as a comma-separated list in the format: <option name 1> -> <value 1>, <option name 2> -> <value 2> , and so on. Names and values must be all enclosed in single quotes and are case sensitive with the exception that Boolean values can be true, false, TRUE, or FALSE. Refer to the respective model for the full options list. 
﻿
Example options list:
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
)
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.
You cannot create a machine learning model with an existing schema and name combination.
Example
Assume that 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.
SQL
CREATE MLMODEL my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM mldata
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);
CREATE MLMODEL my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM mldata
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);
﻿
CREATE MLMODEL OR REPLACE
Create a machine learning model when the model with the specified name does not exist. Otherwise, replace the existing model. You can use the existing model with the existing data and the existing options until the replacement process completes.
Syntax
SQL
CREATE MLMODEL OR REPLACE <model name> TYPE <model type> 
    ON( <SQL SELECT statement> ) [options(<option list>)]
CREATE MLMODEL OR REPLACE <model name> TYPE <model type> 
    ON( <SQL SELECT statement> ) [options(<option list>)]
﻿
Refer to CREATE MLMODEL﻿  for the parameters. 
Example
Assume that you created the mldata table that contains the data for the model. Then, you can create or replace the my_model machine learning model based on that data.
SQL
CREATE MLMODEL OR REPLACE my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM mldata
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);
CREATE MLMODEL OR REPLACE my_model
TYPE SIMPLE LINEAR REGRESSION ON (
  SELECT
    x1,
    y
  FROM mldata
)
options(
  'yIntercept' -> '10',
  'metrics' -> 'true'
);
﻿
ALTER MLMODEL
Rename a machine learning model.
Syntax
SQL
ALTER MLMODEL [IF EXISTS] <model name> RENAME TO <new model name>
ALTER MLMODEL [IF EXISTS] <model name> RENAME TO <new model name>
﻿
Parameter
Data Type
Description
model name
VARCHAR
The name of the model to rename.
new model name
VARCHAR
The new name of the model.
Example
SQL
ALTER MLMODEL my_model RENAME TO my_model_slr;
ALTER MLMODEL my_model RENAME TO my_model_slr;
﻿
DROP MLMODEL
Drop a machine learning model.
Syntax
SQL
DROP MLMODEL model_name [, ...]
DROP MLMODEL model_name [, ...]
﻿
Parameter
Data Type
Description
model name
VARCHAR
The name of the model to drop.
You can drop multiple models by specifying additional model names and separating each with commas. 
Example
To drop a machine learning model, my_model:
SQL
DROP MLMODEL my_model;
DROP MLMODEL my_model;
﻿
To drop multiple machine learning models:
SQL
DROP MLMODEL my_model1, my_model2;
DROP MLMODEL my_model1, my_model2;
﻿
EXPORT MLMODEL
Return the command that can recreate the machine learning model.
Syntax
SQL
EXPORT MLMODEL <model name>
EXPORT MLMODEL <model name>
﻿
Parameter
Data Type
Description
model name
VARCHAR
The name of the model to create.
Example
SQL
EXPORT MLMODEL my_model;
EXPORT MLMODEL my_model;
﻿
Output
SQL
export                                       
---------------------------------------------
CREATE MLMODEL "my_schema"."my_model" TYPE simple linear regression ON(select x, y from mldemo.slr) OPTIONS('metrics' -> 'false'); 

Fetched 1 row
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 command includes the schema explicitly.
REFRESH MLMODEL
Retrain a machine learning model without changing any model options.
Syntax
SQL
REFRESH MLMODEL <model name>
REFRESH MLMODEL <model name>
﻿
Parameter
Data Type
Description
model name
VARCHAR
The name of the model to retrain.
Example
SQL
REFRESH MLMODEL my_model;
REFRESH MLMODEL my_model;
﻿
SELECT
Execute a query against a machine learning model.
Syntax
SQL
SELECT <model name>(expression, ...) FROM <tableReference>
SELECT <model name>(expression, ...) FROM <tableReference>
﻿
Example
Create a table with data for the model.
SQL
CREATE TABLE mldemo.mlr AS (SELECT a.c1 AS x1, b.c1 AS x2, 1 + 2*a.c1 + 3*b.c1 AS y 
FROM sys.dummy10 a, sys.dummy10 b);
Modified 100 rows
CREATE TABLE mldemo.mlr AS (SELECT a.c1 AS x1, b.c1 AS x2, 1 + 2*a.c1 + 3*b.c1 AS y 
FROM sys.dummy10 a, sys.dummy10 b);
Modified 100 rows
﻿
Create a multiple linear regression model based on the data.
SQL
CREATE MLMODEL mlr_model TYPE MULTIPLE LINEAR REGRESSION ON (SELECT * FROM mldemo.mlr) 
options('metrics' -> 'true');
Modified 0 rows
CREATE MLMODEL mlr_model TYPE MULTIPLE LINEAR REGRESSION ON (SELECT * FROM mldemo.mlr) 
options('metrics' -> 'true');
Modified 0 rows
﻿
Execute a SELECT query against the multiple linear regression to see the actual and predicted values. Limit the result set to 10 rows.
SQL
SELECT x1, x2, y AS actual, mlr_model(x1, x2) AS predicted FROM mldemo.mlr LIMIT 10;
x1         x2         actual              predicted
----------------------------------------------------------------
6          1          16                  15.999999999999975
6          2          19                  18.999999999999975
6          3          22                  21.999999999999975
6          4          25                  24.999999999999975
6          5          28                  27.999999999999975
6          6          31                  30.999999999999975
6          7          34                  33.99999999999997
6          8          37                  36.99999999999997
6          9          40                  39.99999999999997
6          10         43                  42.99999999999997

Fetched 10 rows
SELECT x1, x2, y AS actual, mlr_model(x1, x2) AS predicted FROM mldemo.mlr LIMIT 10;
x1         x2         actual              predicted
----------------------------------------------------------------
6          1          16                  15.999999999999975
6          2          19                  18.999999999999975
6          3          22                  21.999999999999975
6          4          25                  24.999999999999975
6          5          28                  27.999999999999975
6          6          31                  30.999999999999975
6          7          34                  33.99999999999997
6          8          37                  36.99999999999997
6          9          40                  39.99999999999997
6          10         43                  42.99999999999997

Fetched 10 rows
﻿
Related Links
﻿Machine Learning in Ocient﻿ 

Parameter	Data Type	Description
model name	VARCHAR	The name of the model to create.

Parameter	Data Type	Description
model type	VARCHAR	The type of machine learning model to create.

Parameter	Data Type	Description
option_list	VARCHAR	The options for the specified machine learning model that is specified as a comma-separated list in the format: <option name 1> -> <value 1>, <option name 2> -> <value 2> , and so on. Names and values must be all enclosed in single quotes and are case sensitive with the exception that Boolean values can be true, false, TRUE, or FALSE. Refer to the respective model for the full options list. Example options list: options( 'yIntercept' -> '10', 'metrics' -> 'true' )

Parameter	Data Type	Description
model name	VARCHAR	The name of the model to rename.
new model name	VARCHAR	The new name of the model.

Parameter	Data Type	Description
model name	VARCHAR	The name of the model to drop. You can drop multiple models by specifying additional model names and separating each with commas.

Parameter	Data Type	Description
model name	VARCHAR	The name of the model to create.

Parameter	Data Type	Description
model name	VARCHAR	The name of the model to retrain.

Updated 09 Aug 2024

Did this page help you?

Spatiotemporal Operators

Regression Models