Machine Learning Model Functions - Ocient Documentation

OcientML™ functionality supports these SQL functions for machine learning models.

The scope of a machine learning model is the schema.

Supported Machine Learning Models

Supported machine learning models and reference material are divided into these categories.

For functions to help organize data before training a model, see Data Preparation.

Regression Models

Classification Models

Clustering and Dimension Reduction Models

Ensemble Models

Other Models

For a view of the full list of model options, see Machine Learning Model Options.

Execute a Query Using a Machine Learning Model

To create a machine learning model and manage the model, see Machine Learning Models for the corresponding syntax. After you create the model, you can execute a query using the model with this syntax. Syntax

SQL

SELECT model_name ( expression [, ... ] ) FROM table_reference

Parameter	Data Type	Description
`model_name`	identifier	The name of a machine learning model created using a `CREATE MODEL` SQL statement. The model name must be a valid identifier and reference an existing trained model.
`expression`	identifier string numeric	One or more expressions that serve as input features for the machine learning model evaluation. These expressions must match the expected input schema that the model was trained on. Expressions can be any combination of literal values, column names, arithmetic expressions, and function invocations, with parentheses for grouping.
`table_reference`	identifier	The name of a table, view, or subquery that provides the data for model evaluation. The table must contain columns or computed expressions that match the expected input features of the model.

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

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;

Output

Text

x1         x2         actual              predicted
----------------------------------------------------------------
        1          16                  15.999999999999975
        2          19                  18.999999999999975
        3          22                  21.999999999999975
        4          25                  24.999999999999975
        5          28                  27.999999999999975
        6          31                  30.999999999999975
        7          34                  33.99999999999997
        8          37                  36.99999999999997
        9          40                  39.99999999999997
        10         43                  42.99999999999997

Fetched 10 rows

Machine Learning Meta Functions

Machine Learning meta functions provide additional ways to invoke a trained model. Unlike calling a model directly by name, meta functions wrap the model invocation and control how the model produces its output.

predict

The predict meta function behaves the same as invoking the model directly as a function. The function returns the value predicted by the specified model for the input features. Syntax

SQL

SELECT predict( model_name( [ hyperargument [, ...] ] ), feature [, ...] ) FROM table_reference

Parameter	Data Type	Description
`model_name`	identifier	The name of a trained machine learning model.
`hyperargument`	varies	Optional model-specific arguments that control how the model makes predictions. For example, the component index in principal component analysis, or a voting flag for ensemble models.
`feature`	varies	One or more input feature expressions matching the schema the model was trained on.

Example

SQL

SELECT predict(my_model(), x1, x2) FROM my_table;

Because many models do not require hyperarguments, the hyperargument list and its surrounding parentheses are optional.

SQL

SELECT predict(my_model, x1, x2) FROM my_table;

predict_probabilities

The predict_probabilities meta function returns a tuple of label-score pairs for each possible class the model can predict. The model assigns each label a score determined by the input features. Then, the model sorts the pairs in decreasing order of score, with ties broken by decreasing label value. This function is supported only for these model types:

Decision Tree
Random Forest
Logistic Regression
Support Vector Machine
Gaussian Discriminant Analysis
Gaussian Mixture Model
MLP (only when trained with softmax output for classification)

All other model types reject the predict_probabilities function, including all regression variants, Naive Bayes, Linear Discriminant Analysis, Principal Component Analysis, ensemble models (Bagging, Stacking), K-Means, Power Iteration Clustering, K Nearest Neighbors, and anomaly detection models. Undersampling models do not support either meta function because they produce output tables rather than row-level predictions. Syntax

SQL

SELECT predict_probabilities( model_name( [ hyperargument [, ...] ] ), feature [, ...] ) FROM table_reference

Parameter	Data Type	Description
`model_name`	identifier	The name of a trained machine learning model.
`hyperargument`	varies	Optional model-specific arguments that control how the model makes predictions.
`feature`	varies	One or more input feature expressions matching the schema the model was trained on.

Example

SQL

SELECT predict_probabilities(my_classifier(), x1, x2, x3) FROM my_table;

Machine Learning in Ocient Machine Learning Models

​Supported Machine Learning Models

​Regression Models

​Classification Models

​Clustering and Dimension Reduction Models

​Ensemble Models

​Other Models

​Execute a Query Using a Machine Learning Model

​Machine Learning Meta Functions

​predict

​predict_probabilities

​Related Links

Supported Machine Learning Models

Regression Models

Classification Models

Clustering and Dimension Reduction Models

Ensemble Models

Other Models

Execute a Query Using a Machine Learning Model

Machine Learning Meta Functions

predict

predict_probabilities

Related Links