This is the multipage printable view of this section.
Click here to print.
Return to the regular view of this page.
Machine learning algorithms
Vertica supports a full range of machine learning functions that train a model on a set of data, and return a model that can be saved for later execution.
Vertica supports a full range of machine learning functions that train a model on a set of data, and return a model that can be saved for later execution.
These functions require the following privileges for nonsuperusers:
Note
Machine learning algorithms contain a subset of four classification functions:
1  ARIMA
Creates and trains an autoregressive integrated moving average (ARIMA) model from a time series with consistent timesteps.
Creates and trains an autoregressive integrated moving average (ARIMA) model from a time series with consistent timesteps. ARIMA models combine the abilities of AUTOREGRESSOR and MOVING_AVERAGE models by making future predictions based on both preceding time series values and errors of previous predictions. ARIMA models also provide the option to apply a differencing operation to the input data, which can turn a nonstationary time series into a stationary time series. After the model is trained, you can make predictions with the PREDICT_ARIMA function.
In Vertica, ARIMA is implemented using a Kalman Filter statespace approach, similar to Gardner, G., et al. This approach updates the statespace model with each element in the training data in order to calculate a loss score over the training data. A BFGS optimizer is then used to adjust the coefficients, and the statespace estimation is rerun until convergence. Because of this repeated estimation process, ARIMA consumes large amounts of memory when called with high values of p
and q
.
Given that the input data must be sorted by timestamp, this algorithm is singlethreaded.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Immutable
Syntax
ARIMA( 'modelname', 'inputrelation', 'timeseriescolumn', 'timestampcolumn'
USING PARAMETERS param=value[,...] )
Arguments
modelname
 Model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 Name of the table or view containing
timeseriescolumn
and timestampcolumn
.
timeseriescolumn
 Name of a NUMERIC column in
inputrelation
that contains the dependent variable or outcome.
timestampcolumn
 Name of an INTEGER, FLOAT, or TIMESTAMP column in
inputrelation
that represents the timestamp variable. The timestep between consecutive entries should be consistent throughout the timestampcolumn
.
Tip
If your
timestampcolumn
has varying timesteps, consider standardizing the step size with the
TIME_SLICE function.
Parameters
p
 Integer in the range [0, 1000], the number of lags to include in the autoregressive component of the computation. If
q
is unspecified or set to zero, p
must be set to a nonzero value. In some cases, using a large p
value can result in a memory overload error.
Note
The
AUTOREGRESSOR and
ARIMA models use different training techniques that produce distinct models when trained with matching parameter values on the same data. For example, if you train an autoregressor model using the same data and
p
value as an ARIMA model trained with
d
and
q
parameters set to zero, those two models will not be identical.
Default: 0
d
 Integer in the range [0, 10], the difference order of the model.
If the timeseriescolumn
is a nonstationary time series, whose statistical properties change over time, you can specify a nonzero d
value to difference the input data. This operation can remove or reduce trends in the time series data.
Differencing computes the differences between consecutive time series values and then trains the model on these values. The difference order d
, where 0 implies no differencing, determines how many times to repeat the differencing operation. For example, secondorder differencing takes the results of the firstorder operation and differences these values again to obtain the secondorder values. For an example that trains an ARIMA model that uses differencing, see ARIMA model example.
Default: 0
q
 Integer in the range [0, 1000], the number of lags to include in the moving average component of the computation. If
p
is unspecified or set to zero, q
must be set to a nonzero value. In some cases, using a large q
value can result in a memory overload error.
Note
The
MOVING_AVERAGE and
ARIMA models use different training techniques that produce distinct models when trained with matching parameter values on the same data. For example, if you train a movingaverage model using the same data and
q
value as an ARIMA model trained with
p
and
d
parameters set to zero, those two models will not be identical.
Default: 0
missing
 Method for handling missing values, one of the following strings:

'drop': Missing values are ignored.

'raise': Missing values raise an error.

'zero': Missing values are set to zero.

'linear_interpolation': Missing values are replaced by a linearly interpolated value based on the nearest valid entries before and after the missing value. In cases where the first or last values in a dataset are missing, the function errors.
Default: 'linear_interpolation'
init_method
 Initialization method, one of the following strings:
Default: 'Zero'
epsilon
 Float in the range (0.0, 1.0), controls the convergence criteria of the optimization algorithm.
Default: 1e6
max_iterations
 Integer in the range [1, 1000000), the maximum number of training iterations. If you set this value too low, the algorithm might not converge.
Default: 100
Model attributes
coefficients
 Coefficients of the model:

phi
: parameters for the autoregressive component of the computation. The number of returned phi
values is equal to the value of p
.

theta
: parameters for the moving average component of the computation. The number of returned theta
values is equal to the value of q
.
p, q, d
 ARIMA component values:

p
: number of lags included in the autoregressive component of the computation

d
: difference order of the model

q
: number of lags included in the moving average component of the computation
mean
 The model mean, average of the accepted sample values from
timeseriescolumn
regularization
 Type of regularization used when training the model
lambda
 Regularization parameter. Higher values indicates stronger regularization.
mean_squared_error
 Mean squared error of the model on the training set
rejected_row_count
 Number of samples rejected during training
accepted_row_count
 Number of samples accepted for training from the data set
timeseries_name
 Name of the
timeseriescolumn
used to train the model
timestamp_name
 Name of the
timestampcolumn
used to train the model
missing_method
 Method used for handling missing values
call_string
 SQL statement used to train the model
Examples
The function requires that at least one of the p
and q
parameters be a positive, nonzero integer. The following example trains a model where both of these parameters are set to two:
=> SELECT ARIMA('arima_temp', 'temp_data', 'temperature', 'time' USING PARAMETERS p=2, q=2);
ARIMA

Finished in 24 iterations.
3650 elements accepted, 0 elements rejected.
(1 row)
To see a summary of the model, including all model coefficients and parameter values, call GET_MODEL_SUMMARY:
=> SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='arima_temp');
GET_MODEL_SUMMARY

============
coefficients
============
parameter value
+
phi_1  1.23639
phi_2 0.24201
theta_1 0.64535
theta_2 0.23046
==============
regularization
==============
none
===============
timeseries_name
===============
temperature
==============
timestamp_name
==============
time
==============
missing_method
==============
linear_interpolation
===========
call_string
===========
ARIMA('public.arima_temp', 'temp_data', 'temperature', 'time' USING PARAMETERS p=2, d=0, q=2, missing='linear_interpolation', init_method='Zero', epsilon=1e06, max_iterations=100);
===============
Additional Info
===============
Name  Value
+
p  2
q  2
d  0
mean 11.17775
lambda  1.00000
mean_squared_error 5.80628
rejected_row_count 0
accepted_row_count 3650
(1 row)
For an indepth example that trains and makes predictions with ARIMA models, see ARIMA model example.
See also
2  AUTOREGRESSOR
Creates an autoregressive (AR) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_AR.
Creates and trains an autoregression (AR) or vector autoregression (VAR) model, depending on the number of provided value columns:
 One value column: the function executes autoregression and returns a trained AR model. AR is a univariate autoregressive time series algorithm that predicts a variable's future values based on its preceding values. The user specifies the number of lagged timesteps taken into account during computation, and the model then predicts future values as a linear combination of the values at each lag.
 Multiple value columns: the function executes vector autoregression and returns a trained VAR model. VAR is a multivariate autoregressive time series algorithm that captures the relationship between multiple time series variables over time. Unlike AR, which only considers a single variable, VAR models incorporate feedback between different variables in the model, enabling the model to analyze how variables interact across lagged time steps. For example, with two variables—atmospheric pressure and rain accumulation—a VAR model could determine whether a drop in pressure tends to result in rain at a future date.
To make predictions with a VAR or AR model, use the PREDICT_AUTOREGRESSOR function.
Because the input data must be sorted by timestamp, this function is singlethreaded.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
AUTOREGRESSOR ('modelname', 'inputrelation', 'valuecolumns', 'timestampcolumn'
[ USING PARAMETERS param=value[,...] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view containing
timestampcolumn
and valuecolumns
.
This algorithm expects a stationary time series as input; using a time series with a mean that shifts over time may lead to weaker results.
valuecolumns
 Commaseparated list of one or more NUMERIC input columns that contain the dependent variables or outcomes.
The number of value columns determines whether the function performs the autoregression (AR) or vector autoregression (VAR) algorithm. If only one input column is provided, the function executes autoregression. For multiple input columns, the function performs the VAR algorithm.
timestampcolumn
 Name of an INTEGER, FLOAT, or TIMESTAMP column in
inputrelation
that represents the timestamp variable. The timestep between consecutive entries must be consistent throughout the timestampcolumn
.
Tip
If your
timestampcolumn
has varying timesteps, consider standardizing the step size with the
TIME_SLICE function.
Parameters
p
 INTEGER in the range [1, 1999], the number of lags to consider in the computation. Larger values for
p
weaken the correlation.
Note
The
AUTOREGRESSOR and
ARIMA models use different training techniques that produce distinct models when trained with matching parameter values on the same data. For example, if you train an autoregressor model using the same data and
p
value as an ARIMA model trained with
d
and
q
parameters set to zero, those two models will not be identical.
Default: 3
method
 One of the following algorithms for training the model:
Default: 'OLS'
missing
 One of the following methods for handling missing values:

'drop': Missing values are ignored.

'error': Missing values raise an error.

'zero': Missing values are replaced with 0.

'linear_interpolation': Missing values are replaced by linearlyinterpolated values based on the nearest valid entries before and after the missing value. This means that in cases where the first or last values in a dataset are missing, they will simply be dropped. The VAR algorithm does not support linear interpolation.
The default method depends on the model type:
 AR: 'linear_interpolation'
 VAR: 'error'
regularization
 For the OLS training method only, one of the following regularization methods used when fitting the data:
Default: None
lambda
 For the OLS training method only, FLOAT in the range [0, 100000], the regularization value, lambda.
Default: 1.0
compute_mse
 BOOLEAN, whether to calculate and output the mean squared error (MSE).
Default: False
subtract_mean
 BOOLEAN, whether the mean of each column in
valuecolumns
is subtracted from its column values before calculating the model coefficients. This parameter only applies if method
is set to 'YuleWalker'. If set to False, the model saves the column means as zero.
Default: False
Examples
The following example creates and trains an autoregression model using the YuleWalker training algorithm and a lag of 3:
=> SELECT AUTOREGRESSOR('AR_temperature_yw', 'temp_data', 'Temperature', 'time' USING PARAMETERS p=3, method='yulewalker');
AUTOREGRESSOR

Finished. 3650 elements accepted, 0 elements rejected.
(1 row)
The following example creates and trains a VAR model with a lag of 2:
=> SELECT AUTOREGRESSOR('VAR_temperature', 'temp_data_VAR', 'temp_location1, temp_location2', 'time' USING PARAMETERS p=2);
WARNING 0: Only the Yule Walker method is currently supported for Vector Autoregression, setting method to Yule Walker
AUTOREGRESSOR

Finished. 3650 elements accepted, 0 elements rejected.
(1 row)
See Autoregressive model example and VAR model example for extended examples that train and make predictions with AR and VAR models.
See also
3  BISECTING_KMEANS
Executes the bisecting kmeans algorithm on an input relation.
Executes the bisecting kmeans algorithm on an input relation. The result is a trained model with a hierarchy of cluster centers, with a range of k values, each of which can be used for prediction.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
BISECTING_KMEANS('modelname', 'inputrelation', 'inputcolumns', 'numclusters'
[ USING PARAMETERS
[exclude_columns = 'excludecolumns']
[, bisection_iterations = bisectioniterations]
[, split_method = 'splitmethod']
[, min_divisible_cluster_size = minclustersize]
[, kmeans_max_iterations = kmeansmaxiterations]
[, kmeans_epsilon = kmeansepsilon]
[, kmeans_center_init_method = 'kmeansinitmethod']
[, distance_method = 'distancemethod']
[, output_view = 'outputview']
[, key_columns = 'keycolumns'] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 Table or view that contains the input data for kmeans. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
inputcolumns
 Commaseparated list of columns to use from the input relation, or asterisk (*) to select all columns. Input columns must be of data type numeric.
numclusters
 Number of clusters to create, an integer ≤ 10,000. This argument represents the
k
in kmeans.
Parameters
exclude_columns
Commaseparated list of column names from inputcolumns
to exclude from processing.
bisection_iterations
 Integer between 1  1MM inclusive, specifies number of iterations the bisecting kmeans algorithm performs for each bisection step. This corresponds to how many times a standalone kmeans algorithm runs in each bisection step.
A setting >1 allows the algorithm to run and choose the best kmeans run within each bisection step. If you use kmeanspp, the value of bisection_iterations
is always 1, because kmeanspp is more costly to run but also better than the alternatives, so it does not require multiple runs.
Default: 1
split_method
 The method used to choose a cluster to bisect/split, one of:
Default: sum_squares
min_divisible_cluster_size
 Integer ≥ 2, specifies minimum number of points of a divisible cluster.
Default: 2
kmeans_max_iterations
 Integer between 1 and 1MM inclusive, specifies the maximum number of iterations the kmeans algorithm performs. If you set this value to a number lower than the number of iterations needed for convergence, the algorithm might not converge.
Default: 10
kmeans_epsilon
 Integer between 1 and 1MM inclusive, determines whether the kmeans algorithm has converged. The algorithm is considered converged after no center has moved more than a distance of
epsilon
from the previous iteration.
Default: 1e4
kmeans_center_init_method
 The method used to find the initial cluster centers in kmeans, one of:

kmeanspp
(default): kmeans++ algorithm

pseudo
: Uses "pseudo center" approach used by Spark, bisects given center without iterating over points
distance_method
 The measure for distance between two data points. Only Euclidean distance is supported at this time.
Default: euclidean
output_view
 Name of the view where you save the assignment of each point to its cluster. You must have CREATE privileges on the view schema.
key_columns
 Commaseparated list of column names that identify the output rows. Columns must be in the
inputcolumns
argument list. To exclude these and other input columns from being used by the algorithm, list them in parameter exclude_columns
.
Model attributes
centers
 A list of centers of the K centroids.
hierarchy
 The hierarchy of K clusters, including:

ParentCluster: Parent cluster centroid of each centroid—that is, the centroid of the cluster from which a cluster is obtained by bisection.

LeftChildCluster: Left child cluster centroid of each centroid—that is, the centroid of the first subcluster obtained by bisecting a cluster.

RightChildCluster: the right child cluster centroid of each centroid—that is, the centroid of the second subcluster obtained by bisecting a cluster.

BisectionLevel: Specifies which bisection step a cluster is obtained from.

WithinSS: Withincluster sum of squares for the current cluster

TotalWithinSS: Total withincluster sum of squares of leaf clusters thus far obtained.
metrics
 Several metrics related to the quality of the clustering, including

Total sum of squares

Total withincluster sum of squares

Betweencluster sum of squares

Betweencluster sum of squares / Total sum of squares

Sum of squares for cluster x
, center_id y
[...]
Examples
SELECT BISECTING_KMEANS('myModel', 'iris1', '*', '5'
USING PARAMETERS exclude_columns = 'Species,id', split_method ='sum_squares', output_view = 'myBKmeansView');
See also
4  KMEANS
Executes the kmeans algorithm on an input relation.
Executes the kmeans algorithm on an input relation. The result is a model with a list of cluster centers.
You can export the resulting kmeans model in VERTICA_MODELS or PMML format to apply it on data outside Vertica. You can also train a kmeans model elsewhere, then import it to Vertica in PMML format to predict on data in Vertica.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
KMEANS ( 'modelname', 'inputrelation', 'inputcolumns', 'numclusters'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, max_iterations = maxiterations]
[, epsilon = epsilonvalue]
[, { init_method = 'initmethod' }  { initial_centers_table = 'inittable' } ]
[, output_view = 'outputview']
[, key_columns = 'keycolumns'] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the input data for kmeans. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
inputcolumns
 Commaseparated list of columns to use from the input relation, or asterisk (*) to select all columns. Input columns must be of data type numeric.
numclusters
 The number of clusters to create, an integer ≤ 10,000. This argument represents the
k
in kmeans.
Parameters
Important
Parameters init_method
and initial_centers_table
are mutually exclusive. If you set both, the function returns an error.
exclude_columns
Commaseparated list of column names from inputcolumns
to exclude from processing.
max_iterations
 The maximum number of iterations the algorithm performs. If you set this value to a number lower than the number of iterations needed for convergence, the algorithm may not converge.
Default: 10
epsilon
 Determines whether the algorithm has converged. The algorithm is considered converged after no center has moved more than a distance of
'epsilon' from the previous iteration.
Default: 1e4
init_method
 The method used to find the initial cluster centers, one of the following:

random

kmeanspp
(default): kmeans++ algorithm
This value can be memory intensive for high k. If the function returns an error that not enough memory is available, decrease the value of k or use the random
method.
initial_centers_table
 The table with the initial cluster centers to use. Supply this value if you know the initial centers to use and do not want Vertica to find the initial cluster centers for you.
output_view
 The name of the view where you save the assignments of each point to its cluster. You must have CREATE privileges on the schema where the view is saved.
key_columns
 Commaseparated list of column names from
inputcolumns
that will appear as the columns of output_view
. These columns should be picked such that their contents identify each input data point. This parameter is only used if output_view
is specified. Columns listed in inputcolumns
that are only meant to be used as key_columns
and not for training should be listed in exclude_columns
.
Model attributes
centers
 A list that contains the center of each cluster.
metrics
 A string summary of several metrics related to the quality of the clustering.
Examples
The following example creates kmeans model myKmeansModel
and applies it to input table iris1
. The call to APPLY_KMEANS
mixes column names and constants. When a constant is passed in place of a column name, the constant is substituted for the value of the column in all rows:
=> SELECT KMEANS('myKmeansModel', 'iris1', '*', 5
USING PARAMETERS max_iterations=20, output_view='myKmeansView', key_columns='id', exclude_columns='Species, id');
KMEANS

Finished in 12 iterations
(1 row)
=> SELECT id, APPLY_KMEANS(Sepal_Length, 2.2, 1.3, Petal_Width
USING PARAMETERS model_name='myKmeansModel', match_by_pos='true') FROM iris2;
id  APPLY_KMEANS
+
5  1
10  1
14  1
15  1
21  1
22  1
24  1
25  1
32  1
33  1
34  1
35  1
38  1
39  1
42  1
...
(60 rows)
See also
5  KPROTOTYPES
Executes the kprototypes algorithm on an input relation.
Executes the kprototypes algorithm on an input relation. The result is a model with a list of cluster centers.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Syntax
SELECT KPROTOTYPES ('`*`modelname`*`', '`*`inputrelation`*`', '`*`inputcolumns`*`', `*`numclusters`*`
[USING PARAMETERS [exclude_columns = '`*`excludecolumns`*`']
[, max_iterations = '`*`maxiterations`*`']
[, epsilon = `*`epsilon`*`]
[, {[init_method = '`*`initmethod`*`'] }  { initial_centers_table = '`*`inittable`*`' } ]
[, gamma = '`*`gamma`*`']
[, output_view = '`*`outputview`*`']
[, key_columns = '`*`keycolumns`*`']]);
Behavior type
Volatile
Arguments
modelname
 Name of the model resulting from the training.
inputrelation
 Name of the table or view containing the training samples.
inputcolumns
 String containing a commaseparated list of columns to use from the inputrelation, or asterisk (*) to select all columns.
numclusters
 Integer ≤ 10,000 representing the number of clusters to create. This argument represents the k in kprototypes.
Parameters
excludecolumns
 String containing a commaseparated list of column names from inputcolumns to exclude from processing.
Default: (empty)
max_iterations
 Integer ≤ 1M representing the maximum number of iterations the algorithm performs.
Default: Integer ≤ 1M
epsilon
 Integer which determines whether the algorithm has converged.
Default: 1e4
init_method
 String specifying the method used to find the initial kprototypes cluster centers.
Default: "random"
initial_centers_table
 The table with the initial cluster centers to use.
gamma
 Float between 0 and 10000 specifying the weighing factor for categorical columns. It can determine relative importance of numerical and categorical attributes
Default: Inferred from data.
output_view
 The name of the view where you save the assignments of each point to its cluster
key_columns
 Commaseparated list of column names that identify the output rows. Columns must be in the inputcolumns argument list
Examples
The following example creates kprototypes model small_model
and applies it to input table small_test_mixed
:
=> SELECT KPROTOTYPES('small_model_initcenters', 'small_test_mixed', 'x0, country', 3 USING PARAMETERS initial_centers_table='small_test_mixed_centers', key_columns='pid');
KPROTOTYPES

Finished in 2 iterations
(1 row)
=> SELECT country, x0, APPLY_KPROTOTYPES(country, x0
USING PARAMETERS model_name='small_model')
FROM small_test_mixed;
country  x0  apply_kprototypes
++
'China'  20  0
'US'  85  2
'Russia'  80  1
'Brazil'  78  1
'US'  23  0
'US'  50  0
'Canada'  24  0
'Canada'  18  0
'Russia'  90  2
'Russia'  98  2
'Brazil'  89  2
...
(45 rows)
See also
6  LINEAR_REG
Executes linear regression on an input relation, and returns a linear regression model.
Executes linear regression on an input relation, and returns a linear regression model.
You can export the resulting linear regression model in VERTICA_MODELS or PMML format to apply it on data outside Vertica. You can also train a linear regression model elsewhere, then import it to Vertica in PMML format to model on data inside Vertica.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
LINEAR_REG ( 'modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, optimizer = 'optimizermethod']
[, regularization = 'regularizationmethod']
[, epsilon = epsilonvalue]
[, max_iterations = iterations]
[, lambda = lamdavalue]
[, alpha = alphavalue]
[, fit_intercept = booleanvalue] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 Table or view that contains the training data for building the model. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 Name of the input column that represents the dependent variable or outcome. All values in this column must be numeric, otherwise the model is invalid.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
optimizer
 Optimizer method used to train the model, one of the following:
Default: CGD
if regularizationmethod
is set to L1
or ENet
, otherwise Newton
.
regularization
 Method of regularization, one of the following:

None
(default)

L1

L2

ENet
epsilon
FLOAT in the range (0.0, 1.0), the error value at which to stop training. Training stops if either the difference between the actual and predicted values is less than or equal to epsilon
or if the number of iterations exceeds max_iterations
.
Default: 1e6
max_iterations
INTEGER in the range (0, 1000000), the maximum number of training iterations. Training stops if either the number of iterations exceeds max_iterations
or if the difference between the actual and predicted values is less than or equal to epsilon
.
Default: 100
lambda
 Integer ≥ 0, specifies the value of the
regularization
parameter.
Default: 1
alpha
 Integer ≥ 0, specifies the value of the ENET
regularization
parameter, which defines how much L1 versus L2 regularization to provide. A value of 1 is equivalent to L1 and a value of 0 is equivalent to L2.
Value range: [0,1]
Default: 0.5
fit_intercept
 Boolean, specifies whether the model includes an intercept. By setting to false, no intercept will be used in training the model. Note that setting
fit_intercept
to false does not work well with the BFGS optimizer.
Default: True
Model attributes
data
 The data for the function, including:

coeffNames
: Name of the coefficients. This starts with intercept and then follows with the names of the predictors in the same order specified in the call.

coeff
: Vector of estimated coefficients, with the same order as coeffNames

stdErr
: Vector of the standard error of the coefficients, with the same order as coeffNames

zValue
(for logistic regression): Vector of zvalues of the coefficients, in the same order as coeffNames

tValue
(for linear regression): Vector of tvalues of the coefficients, in the same order as coeffNames

pValue
: Vector of pvalues of the coefficients, in the same order as coeffNames
regularization
 Type of regularization to use when training the model.
lambda
 Regularization parameter. Higher values enforce stronger regularization. This value must be nonnegative.
alpha
 Elastic net mixture parameter.
iterations
 Number of iterations that actually occur for the convergence before exceeding
max_iterations
.
skippedRows
 Number of rows of the input relation that were skipped because they contained an invalid value.
processedRows
 Total number of input relation rows minus
skippedRows
.
callStr
 Value of all input arguments specified when the function was called.
Examples
=> SELECT LINEAR_REG('myLinearRegModel', 'faithful', 'eruptions', 'waiting'
USING PARAMETERS optimizer='BFGS', fit_intercept=true);
LINEAR_REG

Finished in 10 iterations
(1 row)
See also
7  LOGISTIC_REG
Executes logistic regression on an input relation.
Executes logistic regression on an input relation. The result is a logistic regression model.
You can export the resulting logistic regression model in VERTICA_MODELS or PMML format to apply it on data outside Vertica. You can also train a logistic regression model elsewhere, then import it to Vertica in PMML format to predict on data in Vertica.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
LOGISTIC_REG ( 'modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS [exclude_columns = 'excludedcolumns']
[, optimizer = 'optimizermethod']
[, regularization = 'regularizationmethod']
[, epsilon = epsilonvalue]
[, max_iterations = iterations]
[, lambda = lamdavalue]
[, alpha = alphavalue]
[, fit_intercept = booleanvalue] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training data for building the model. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 The input column that represents the dependent variable or outcome. The column value must be 0 or 1, and of type numeric or BOOLEAN. The function automatically skips all other values.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
optimizer
 The optimizer method used to train the model, one of the following:
Default: CGD
if regularizationmethod
is set to L1
or ENet
, otherwise Newton
.
regularization
 The method of regularization, one of the following:

None
(default)

L1

L2

ENet
epsilon
FLOAT in the range (0.0, 1.0), the error value at which to stop training. Training stops if either the difference between the actual and predicted values is less than or equal to epsilon
or if the number of iterations exceeds max_iterations
.
Default: 1e6
max_iterations
INTEGER in the range (0, 1000000), the maximum number of training iterations. Training stops if either the number of iterations exceeds max_iterations
or if the difference between the actual and predicted values is less than or equal to epsilon
.
Default: 100
lambda
 Integer ≥ 0, specifies the value of the
regularization
parameter.
Default: 1
alpha
 Integer ≥ 0, specifies the value of the ENET
regularization
parameter, which defines how much L1 versus L2 regularization to provide. A value of 1 is equivalent to L1 and a value of 0 is equivalent to L2.
Value range: [0,1]
Default: 0.5
fit_intercept
 Boolean, specifies whether the model includes an intercept. By setting to false, no intercept will be used in training the model. Note that setting
fit_intercept
to false does not work well with the BFGS optimizer.
Default: True
Model attributes
data
 The data for the function, including:

coeffNames
: Name of the coefficients. This starts with intercept and then follows with the names of the predictors in the same order specified in the call.

coeff
: Vector of estimated coefficients, with the same order as coeffNames

stdErr
: Vector of the standard error of the coefficients, with the same order as coeffNames

zValue
(for logistic regression): Vector of zvalues of the coefficients, in the same order as coeffNames

tValue
(for linear regression): Vector of tvalues of the coefficients, in the same order as coeffNames

pValue
: Vector of pvalues of the coefficients, in the same order as coeffNames
regularization
 Type of regularization to use when training the model.
lambda
 Regularization parameter. Higher values enforce stronger regularization. This value must be nonnegative.
alpha
 Elastic net mixture parameter.
iterations
 Number of iterations that actually occur for the convergence before exceeding
max_iterations
.
skippedRows
 Number of rows of the input relation that were skipped because they contained an invalid value.
processedRows
 Total number of input relation rows minus
skippedRows
.
callStr
 Value of all input arguments specified when the function was called.
Privileges
Superuser, or SELECT privileges on the input relation
Examples
=> SELECT LOGISTIC_REG('myLogisticRegModel', 'mtcars', 'am',
'mpg, cyl, disp, hp, drat, wt, qsec, vs, gear, carb'
USING PARAMETERS exclude_columns='hp', optimizer='BFGS', fit_intercept=true);
LOGISTIC_REG

Finished in 20 iterations
(1 row)
See also
8  MOVING_AVERAGE
Creates a movingaverage (MA) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_MOVING_AVERAGE.
Creates a movingaverage (MA) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_MOVING_AVERAGE.
Moving average models use the errors of previous predictions to make future predictions. More specifically, the userspecified lag determines how many previous predictions and errors it takes into account during computation.
Since its input data must be sorted by timestamp, this algorithm is singlethreaded.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
MOVING_AVERAGE ('modelname', 'inputrelation', 'datacolumn', 'timestampcolumn'
[ USING PARAMETERS
[ q = lags ]
[, missing = "imputationmethod" ]
[, regularization = "regularizationmethod" ]
[, lambda = regularizationvalue ]
[, compute_mse = boolean ]
] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view containing the
timestampcolumn
.
This algorithm expects a stationary time series as input; using a time series with a mean that shifts over time may lead to weaker results.
datacolumn
 An input column of type NUMERIC that contains the dependent variables or outcomes.
timestampcolumn
 One INTEGER, FLOAT, or TIMESTAMP column that represent the timestamp variable. Timesteps must be consistent.
Parameters
q
 INTEGER in the range [1, 67), the number of lags to consider in the computation.
Note
The
MOVING_AVERAGE and
ARIMA models use different training techniques that produce distinct models when trained with matching parameter values on the same data. For example, if you train a movingaverage model using the same data and
q
value as an ARIMA model trained with
p
and
d
parameters set to zero, those two models will not be identical.
Default: 1
missing
 One of the following methods for handling missing values:

drop: Missing values are ignored.

error: Missing values raise an error.

zero: Missing values are replaced with 0.

linear_interpolation: Missing values are replaced by linearly interpolated values based on the nearest valid entries before and after the missing value. This means that in cases where the first or last values in a dataset are missing, they will simply be dropped.
Default: linear_interpolation
regularization
 One of the following regularization methods used when fitting the data:
Default: None
lambda
 FLOAT in the range [0, 100000], the regularization value, lambda.
Default: 1.0
compute_mse
 BOOLEAN, whether to calculate and output the mean squared error (MSE).
This parameter only accepts "true" or "false" rather than the standard literal equivalents for BOOLEANs like 1 or 0.
Default: False
Examples
See Movingaverage model example.
See also
9  NAIVE_BAYES
Executes the Naive Bayes algorithm on an input relation and returns a Naive Bayes model.
Executes the Naive Bayes algorithm on an input relation and returns a Naive Bayes model.
Columns are treated according to data type:

FLOAT: Values are assumed to follow some Gaussian distribution.

INTEGER: Values are assumed to belong to one multinomial distribution.

CHAR/VARCHAR: Values are assumed to follow some categorical distribution. The string values stored in these columns must not be greater than 128 characters.

BOOLEAN: Values are treated as categorical with two values.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
NAIVE_BAYES ( 'modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS [exclude_columns = 'excludedcolumns'] [, alpha = alphavalue] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training data for building the model. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 Name of the input column that represents the dependent variable, or outcome. This column must contain discrete labels that represent different class labels.
The response column must be of type numeric, CHAR/VARCHAR, or BOOLEAN; otherwise the model is invalid.
Note
Vertica automatically casts
numeric response column values to VARCHAR.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric, CHAR/VARCHAR, or BOOLEAN; otherwise the model is invalid. BOOLEAN column values are converted to FLOAT values before training: 0 for false, 1 for true.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
alpha
 Float, specifies use of Laplace smoothing if the event model is categorical, multinomial, or Bernoulli.
Default: 1.0
Model attributes
colsInfo
 The information from the response and predictor columns used in training:

index: The index (starting at 0) of the column as provided in training. Index 0 is used for the response column.

name: The column name.

type: The label used for response with a value of Gaussian, Multinominal, Categorical, or Bernoulli.
alpha
 The smooth parameter value.
prior
 The percentage of each class among all training samples:
nRowsTotal
 The number of samples accepted for training from the data set.
nRowsRejected
 The number of samples rejected for training.
callStr
 The SQL statement used to replicate the training.
Gaussian
 The Gaussian model conditioned on the class indicated by the class_name:

index: The index of the predictor column.

mu: The mean value of the model.

sigmaSq: The squared standard deviation of the model.
Multinominal
 The Multinomial model conditioned on the class indicated by the class_name:
Bernoulli
 The Bernoulli model conditioned on the class indicated by the class_name:
Categorical
 The Gaussian model conditioned on the class indicated by the class_name:
Privileges
Superuser, or SELECT privileges on the input relation.
Examples
=> SELECT NAIVE_BAYES('naive_house84_model', 'house84_train', 'party', '*'
USING PARAMETERS exclude_columns='party, id');
NAIVE_BAYES

Finished. Accepted Rows: 324 Rejected Rows: 0
(1 row)
See also
10  PLS_REG
Executes PLS regression on an input relation, and returns a PLS regression model.
Executes the Partial Least Squares (PLS) regression algorithm on an input relation, and returns a PLS regression model.
Combining aspects of PCA (principal component analysis) and linear regression, the PLS regression algorithm extracts a set of latent components that explain as much covariance as possible between the predictor and response variables, and then performs a regression that predicts response values using the extracted components.
This technique is particularly useful when the number of predictor variables is greater than the number of observations or the predictor variables are highly collinear. If either of these conditions is true of the input relation, ordinary linear regression fails to converge to an accurate model.
The PLS_REG function supports PLS regression with only one response column, often referred to as PLS1. PLS regression with multiple response columns, known as PLS2, is not currently supported.
To make predictions with a PLS model, use the PREDICT_PLS_REG function.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Syntax
PLS_REG ( 'modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS param=value[,...] ] )
Arguments
modelname
 Model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 Table or view that contains the training data.
responsecolumn
 Name of the input column that represents the dependent variable or outcome. All values in this column must be numeric.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
num_components
 Number of components in the model. The value must be an integer in the range [1, min(N, P)], where N is the number of rows in
inputrelation
and P is the number of columns in predictorcolumns
.
Default: 2
scale
 Boolean, whether to standardize the response and predictor columns.
Default: True
Model attributes
details
 Information about the model coefficients, including:
coeffNames
: Name of the coefficients, starting with the intercept and following with the names of the predictors in the same order specified in the call.
coeff
: Vector of estimated coefficients, with the same order as coeffNames
.
responses
 Name of the response column.
call_string
 SQL statement used to train the model.
Additional Info
 Additional information about the model, including:
is_scaled
: Whether the input columns were scaled before model training.
n_components
: Number of components in the model.
rejected_row_count
: Number of rows in inputrelation
that were rejected because they contained an invalid value.
accepted_row_count
: Number of rows in inputrelation
accepted for training the model.
Privileges
Nonsuperusers:
Examples
The following example trains a PLS regression model with the default number of components:
=> CREATE TABLE pls_data (y float, x1 float, x2 float, x3 float, x4 float, x5 float);
=> COPY pls_data FROM STDIN;
123025
234044
345083
\.
=> SELECT PLS_REG('pls_model', 'pls_data', 'y', 'x1,x2');
WARNING 0: There are not more than 1 meaningful component(s)
PLS_REG

Number of components 1.
Accepted Rows: 3 Rejected Rows: 0
(1 row)
For an indepth example that trains and makes predictions with a PLS model, see PLS regression.
See also
11  POISSON_REG
Executes Poisson regression on an input relation, and returns a Poisson regression model.
Executes Poisson regression on an input relation, and returns a Poisson regression model.
You can export the resulting Poisson regression model in VERTICA_MODELS or PMML format to apply it on data outside Vertica. You can also train a Poisson regression model elsewhere, then import it to Vertica in PMML format to apply it on data inside Vertica.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
POISSON_REG ( 'modelname', 'inputtable', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, optimizer = 'optimizermethod']
[, regularization = 'regularizationmethod']
[, epsilon = epsilonvalue]
[, max_iterations = iterations]
[, lambda = lamdavalue]
[, fit_intercept = booleanvalue] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputtable
 Table or view that contains the training data for building the model. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 Name of input column that represents the dependent variable or outcome. All values in this column must be numeric, otherwise the model is invalid.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
optimizer
 Optimizer method used to train the model. The currently supported method is
Newton
.
regularization
 Method of regularization, one of the following:
epsilon
 FLOAT in the range (0.0, 1.0), the error value at which to stop training. Training stops if either the relative change in Poisson deviance is less than or equal to epsilon or if the number of iterations exceeds
max_iterations
.
Default: 1e6
max_iterations
 INTEGER in the range (0, 1000000), the maximum number of training iterations. Training stops if either the number of iterations exceeds
max_iterations
or the relative change in Poisson deviance is less than or equal to epsilon.
lambda
 FLOAT ≥ 0, specifies the
regularization
strength.
Default: 1.0
fit_intercept
 Boolean, specifies whether the model includes an intercept. By setting to false, no intercept will be used in training the model.”
Default: True
Model attributes
data
 Data for the function, including:

coeffNames
: Name of the coefficients. This starts with intercept and then follows with the names of the predictors in the same order specified in the call.

coeff
: Vector of estimated coefficients, with the same order as coeffNames

stdErr
: Vector of the standard error of the coefficients, with the same order as coeffNames

zValue
: (for logistic and Poisson regression): Vector of zvalues of the coefficients, in the same order as coeffNames

tValue
(for linear regression): Vector of tvalues of the coefficients, in the same order as coeffNames

pValue
: Vector of pvalues of the coefficients, in the same order as coeffNames
regularization
 Type of regularization to use when training the model.
lambda
 Regularization parameter. Higher values enforce stronger regularization. This value must be nonnegative.
iterations
 Number of iterations that actually occur for the convergence before exceeding
max_iterations
.
skippedRows
 Number of rows of the input relation that were skipped because they contained an invalid value.
processedRows
 Total number of input relation rows minus
skippedRows
.
callStr
 Value of all input arguments specified when the function was called.
Examples
=> SELECT POISSON_REG('myModel', 'numericFaithful', 'eruptions', 'waiting' USING PARAMETERS epsilon=1e8);
poisson_reg

Finished in 7 iterations
(1 row)
See also
12  RF_CLASSIFIER
Trains a random forest model for classification on an input relation.
Trains a random forest model for classification on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
RF_CLASSIFIER ( 'modelname', inputrelation, 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, ntree = numtrees]
[, mtry = numfeatures]
[, sampling_size = samplingsize]
[, max_depth = depth]
[, max_breadth = breadth]
[, min_leaf_size = leafsize]
[, min_info_gain = threshold]
[, nbins = numbins] ] )
Arguments
modelname
 Identifies the model stored as a result of the training, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training samples. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 An input column of type numeric, CHAR/VARCHAR, or BOOLEAN that represents the dependent variable.
Note
Vertica automatically casts
numeric response column values to VARCHAR.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric, CHAR/VARCHAR, or BOOLEAN; otherwise the model is invalid.
Vertica XGBoost and Random Forest algorithms offer native support for categorical columns (BOOL/VARCHAR). Simply pass the categorical columns as predictors to the models and the algorithm will automatically treat the columns as categorical and will not attempt to split them into bins in the same manner as numerical columns; Vertica treats these columns as true categorical values and does not simply cast them to continuous values underthehood.
Parameters
exclude_columns
Commaseparated list of column names from inputcolumns
to exclude from processing.
ntree
Integer in the range [1, 1000], the number of trees in the forest.
Default: 20
mtry
 Integer in the range [1,
numberpredictors
], the number of randomly chosen features from which to pick the best feature to split on a given tree node.
Default: Square root of the total number of predictors
sampling_size
Float in the range (0.0, 1.0], the portion of the input data set that is randomly picked for training each tree.
Default: 0.632
max_depth
Integer in the range [1, 100], the maximum depth for growing each tree. For example, a max_depth
of 0 represents a tree with only a root node, and a max_depth
of 2 represents a tree with four leaf nodes.
Default: 5
max_breadth
Integer in the range [1, 1e9], the maximum number of leaf nodes a tree can have.
Default: 32
min_leaf_size
Integer in the range [1, 1e6], the minimum number of samples each branch must have after splitting a node. A split that results in fewer remaining samples in its left or right branch is be discarded, and the node is treated as a leaf node.
Default: 1
min_info_gain
Float in the range [0.0, 1.0), the minimum threshold for including a split. A split with information gain less than this threshold is discarded.
Default: 0.0
nbins
Integer in the range [2, 1000], the number of bins to use for discretizing continuous features.
Default: 32
Model attributes
data
 Data for the function, including:
ntree
 Number of trees in the model.
skippedRows
 Number of rows in
input_relation
that were skipped because they contained an invalid value.
processedRows
 Total number of rows in
input_relation
minus skippedRows
.
callStr
 Value of all input arguments that were specified at the time the function was called.
Examples
=> SELECT RF_CLASSIFIER ('myRFModel', 'iris', 'Species', 'Sepal_Length, Sepal_Width,
Petal_Length, Petal_Width' USING PARAMETERS ntree=100, sampling_size=0.3);
RF_CLASSIFIER

Finished training
(1 row)
See also
13  RF_REGRESSOR
Trains a random forest model for regression on an input relation.
Trains a random forest model for regression on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
RF_REGRESSOR ( 'modelname', inputrelation, 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, ntree = numtrees]
[, mtry = numfeatures]
[, sampling_size = samplingsize]
[, max_depth = depth]
[, max_breadth = breadth]
[, min_leaf_size = leafsize]
[, min_info_gain = threshold]
[, nbins = numbins] ] )
Arguments
modelname
 The model that is stored as a result of training, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training samples. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 A numeric input column that represents the dependent variable.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric, CHAR/VARCHAR, or BOOLEAN; otherwise the model is invalid.
Vertica XGBoost and Random Forest algorithms offer native support for categorical columns (BOOL/VARCHAR). Simply pass the categorical columns as predictors to the models and the algorithm will automatically treat the columns as categorical and will not attempt to split them into bins in the same manner as numerical columns; Vertica treats these columns as true categorical values and does not simply cast them to continuous values underthehood.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
ntree
Integer in the range [1, 1000], the number of trees in the forest.
Default: 20
mtry
 Integer in the range [1,
numberpredictors
], the number of features to consider at the split of a tree node.
Default: Onethird the total number of predictors
sampling_size
Float in the range (0.0, 1.0], the portion of the input data set that is randomly picked for training each tree.
Default: 0.632
max_depth
Integer in the range [1, 100], the maximum depth for growing each tree. For example, a max_depth
of 0 represents a tree with only a root node, and a max_depth
of 2 represents a tree with four leaf nodes.
Default: 5
max_breadth
Integer in the range [1, 1e9], the maximum number of leaf nodes a tree can have.
Default: 32
min_leaf_size
 Integer in the range [1, 1e6], the minimum number of samples each branch must have after splitting a node. A split that results in fewer remaining samples in its left or right branch is be discarded, and the node is treated as a leaf node.
The default value of this parameter differs from that of analogous parameters in libraries like sklearn and will therefore yield a model with predicted values that differ from the original response values.
Default: 5
min_info_gain
Float in the range [0.0, 1.0), the minimum threshold for including a split. A split with information gain less than this threshold is discarded.
Default: 0.0
nbins
Integer in the range [2, 1000], the number of bins to use for discretizing continuous features.
Default: 32
Model attributes
data
 Data for the function, including:
ntree
 Number of trees in the model.
skippedRows
 Number of rows in
input_relation
that were skipped because they contained an invalid value.
processedRows
 Total number of rows in
input_relation
minus skippedRows
.
callStr
 Value of all input arguments that were specified at the time the function was called.
Examples
=> SELECT RF_REGRESSOR ('myRFRegressorModel', 'mtcars', 'carb', 'mpg, cyl, hp, drat, wt' USING PARAMETERS
ntree=100, sampling_size=0.3);
RF_REGRESSOR

Finished
(1 row)
See also
14  SVM_CLASSIFIER
Trains the SVM model on an input relation.
Trains the SVM model on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
SVM_CLASSIFIER ( 'modelname', inputrelation, 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, C = 'cost']
[, epsilon = 'epsilonvalue']
[, max_iterations = 'maxiterations']
[, class_weights = 'weight']
[, intercept_mode = 'interceptmode']
[, intercept_scaling = 'scale'] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training data. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 The input column that represents the dependent variable or outcome. The column value must be 0 or 1, and of type numeric or BOOLEAN, otherwise the function returns with an error.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
C
 Weight for misclassification cost. The algorithm minimizes the regularization cost and the misclassification cost.
Default: 1.0
epsilon
 Used to control accuracy.
Default: 1e3
max_iterations
 Maximum number of iterations that the algorithm performs.
Default: 100
class_weights
 Specifies how to determine weights of the two classes, one of the following:

None
(default): No weights are used

value0
, value1
: Two commadelimited strings that specify two positive FLOAT values, where value0
assigns a weight to class 0, and value1
assigns a weight to class 1.

auto
: Weights each class according to the number of samples.
intercept_mode
 Specifies how to treat the intercept, one of the following:
intercept_scaling
 Float value that serves as the value of a dummy feature whose coefficient Vertica uses to calculate the model intercept. Because the dummy feature is not in the training data, its values are set to a constant, by default 1.
Model attributes
coeff
 Coefficients in the model:
nAccepted
 Number of samples accepted for training from the data set
nRejected
 Number of samples rejected when training
nIteration
 Number of iterations used in training
callStr
 SQL statement used to replicate the training
Examples
The following example uses SVM_CLASSIFIER
on the mtcars
table:
=> SELECT SVM_CLASSIFIER(
'mySvmClassModel', 'mtcars', 'am', 'mpg,cyl,disp,hp,drat,wt,qsec,vs,gear,carb'
USING PARAMETERS exclude_columns = 'hp,drat');
SVM_CLASSIFIER

Finished in 15 iterations.
Accepted Rows: 32 Rejected Rows: 0
(1 row)
See also
15  SVM_REGRESSOR
Trains the SVM model on an input relation.
Trains the SVM model on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
SVM_REGRESSOR ( 'modelname', inputrelation, 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS
[exclude_columns = 'excludedcolumns']
[, error_tolerance = errortolerance]
[, C = cost]
[, epsilon = epsilonvalue]
[, max_iterations = maxiterations]
[, intercept_mode = 'mode']
[, intercept_scaling = 'scale'] ] )
Arguments
modelname
 Identifies the model to create, where
modelname
conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.
inputrelation
 The table or view that contains the training data. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 An input column that represents the dependent variable or outcome. The column must be a numeric data type.
predictorcolumns
Commaseparated list of columns in the input relation that represent independent variables for the model, or asterisk (*) to select all columns. If you select all columns, the argument list for parameter exclude_columns
must include responsecolumn
, and any columns that are invalid as predictor columns.
All predictor columns must be of type numeric or BOOLEAN; otherwise the model is invalid.
Note
All BOOLEAN predictor values are converted to FLOAT values before training: 0 for false, 1 for true. No type checking occurs during prediction, so you can use a BOOLEAN predictor column in training, and during prediction provide a FLOAT column of the same name. In this case, all FLOAT values must be either 0 or 1.
Parameters
exclude_columns
 Commaseparated list of columns from
predictorcolumns
to exclude from processing.
error_tolerance
 Defines the acceptable error margin. Any data points outside this region add a penalty to the cost function.
Default: 0.1
C
 The weight for misclassification cost. The algorithm minimizes the regularization cost and the misclassification cost.
Default: 1.0
epsilon
 Used to control accuracy.
Default: 1e3
max_iterations
 The maximum number of iterations that the algorithm performs.
Default: 100
intercept_mode
 A string that specifies how to treat the intercept, one of the following
intercept_scaling
 A FLOAT value, serves as the value of a dummy feature whose coefficient Vertica uses to calculate the model intercept. Because the dummy feature is not in the training data, its values are set to a constant, by default set to 1.
Model attributes
coeff
 Coefficients in the model:
nAccepted
 Number of samples accepted for training from the data set
nRejected
 Number of samples rejected when training
nIteration
 Number of iterations used in training
callStr
 SQL statement used to replicate the training
Examples
=> SELECT SVM_REGRESSOR('mySvmRegModel', 'faithful', 'eruptions', 'waiting'
USING PARAMETERS error_tolerance=0.1, max_iterations=100);
SVM_REGRESSOR

Finished in 5 iterations.
Accepted Rows: 272 Rejected Rows: 0
(1 row)
See also
16  XGB_CLASSIFIER
Trains an XGBoost model for classification on an input relation.
Trains an XGBoost model for classification on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
XGB_CLASSIFIER ('modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS param=value[,...] ] )
Arguments
modelname
Name of the model (caseinsensitive).
inputrelation
 The table or view that contains the training samples. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 An input column of type CHAR or VARCHAR that represents the dependent variable or outcome.
predictorcolumns
 Commaseparated list of columns to use from the input relation, or asterisk (*) to select all columns. Columns must be of data types CHAR, VARCHAR, BOOL, INT, or FLOAT.
Columns of type CHAR, VARCHAR, and BOOL are treated as categorical features; all others are treated as numeric features.
Vertica XGBoost and Random Forest algorithms offer native support for categorical columns (BOOL/VARCHAR). Simply pass the categorical columns as predictors to the models and the algorithm will automatically treat the columns as categorical and will not attempt to split them into bins in the same manner as numerical columns; Vertica treats these columns as true categorical values and does not simply cast them to continuous values underthehood.
Parameters
exclude_columns
Commaseparated list of column names from inputcolumns
to exclude from processing.
max_ntree
 Integer in the range [1,1000] that sets the maximum number of trees to create.
Default: 10
max_depth
 Integer in the range [1,40] that specifies the maximum depth of each tree.
Default: 6
objective
 The objective/loss function used to iteratively improve the model. 'crossentropy' is the only option.
Default: 'crossentropy'
split_proposal_method
 The splitting strategy for the feature columns. 'global' is the only option. This method calculates the split for each feature column only at the beginning of the algorithm. The feature columns are split into the number of bins specified by
nbins
.
Default: 'global'
learning_rate
 Float in the range (0,1] that specifies the weight for each tree's prediction. Setting this parameter can reduce each tree's impact and thereby prevent earlier trees from monopolizing improvements at the expense of contributions from later trees.
Default: 0.3
min_split_loss
 Float in the range [0,1000] that specifies the minimum amount of improvement each split must achieve on the model's objective function value to avoid being pruned.
If set to 0 or omitted, no minimum is set. In this case, trees are pruned according to positive or negative objective function values.
Default: 0.0 (disable)
weight_reg
 Float in the range [0,1000] that specifies the regularization term applied to the weights of classification tree leaves. The higher the setting, the sparser or smoother the weights are, which can help prevent overfitting.
Default: 1.0
nbins
 Integer in the range (1,1000] that specifies the number of bins to use for finding splits in each column. More bins leads to longer runtime but more finegrained and possibly better splits.
Default: 32
sampling_size
 Float in the range (0,1] that specifies the fraction of rows to use in each training iteration.
A value of 1 indicates that all rows are used.
Default: 1.0
col_sample_by_tree
 Float in the range (0,1] that specifies the fraction of columns (features), chosen at random, to use when building each tree.
A value of 1 indicates that all columns are used.
col_sample_by
parameters "stack" on top of each other if several are specified. That is, given a set of 24 columns, for col_sample_by_tree=0.5
andcol_sample_by_node=0.5
,col_sample_by_tree
samples 12 columns, reducing the available, unsampled column pool to 12. col_sample_by_node
then samples half of the remaining pool, so each node samples 6 columns.
This algorithm will always sample at least one column.
Default: 1
col_sample_by_node
 Float in the range (0,1] that specifies the fraction of columns (features), chosen at random, to use when evaluating each split.
A value of 1 indicates that all columns are used.
col_sample_by
parameters "stack" on top of each other if several are specified. That is, given a set of 24 columns, for col_sample_by_tree=0.5
andcol_sample_by_node=0.5
,col_sample_by_tree
samples 12 columns, reducing the available, unsampled column pool to 12. col_sample_by_node
then samples half of the remaining pool, so each node samples 6 columns.
This algorithm will always sample at least one column.
Default: 1
Examples
See XGBoost for classification.
17  XGB_REGRESSOR
Trains an XGBoost model for regression on an input relation.
Trains an XGBoost model for regression on an input relation.
This is a metafunction. You must call metafunctions in a toplevel SELECT statement.
Behavior type
Volatile
Syntax
XGB_REGRESSOR ('modelname', 'inputrelation', 'responsecolumn', 'predictorcolumns'
[ USING PARAMETERS param=value[,...] ] )
Arguments
modelname
Name of the model (caseinsensitive).
inputrelation
 The table or view that contains the training samples. If the input relation is defined in Hive, use
SYNC_WITH_HCATALOG_SCHEMA
to sync the hcatalog
schema, and then run the machine learning function.
responsecolumn
 An input column of type INTEGER or FLOAT that represents the dependent variable or outcome.
predictorcolumns
 Commaseparated list of columns to use from the input relation, or asterisk (*) to select all columns. Columns must be of data types CHAR, VARCHAR, BOOL, INT, or FLOAT.
Columns of type CHAR, VARCHAR, and BOOL are treated as categorical features; all others are treated as numeric features.
Vertica XGBoost and Random Forest algorithms offer native support for categorical columns (BOOL/VARCHAR). Simply pass the categorical columns as predictors to the models and the algorithm will automatically treat the columns as categorical and will not attempt to split them into bins in the same manner as numerical columns; Vertica treats these columns as true categorical values and does not simply cast them to continuous values underthehood.
Parameters
exclude_columns
Commaseparated list of column names from inputcolumns
to exclude from processing.
max_ntree
 Integer in the range [1,1000] that sets the maximum number of trees to create.
Default: 10
max_depth
 Integer in the range [1,40] that specifies the maximum depth of each tree.
Default: 6
objective
 The objective/loss function used to iteratively improve the model. 'squarederror' is the only option.
Default: 'squarederror'
split_proposal_method
 The splitting strategy for the feature columns. 'global' is the only option. This method calculates the split for each feature column only at the beginning of the algorithm. The feature columns are split into the number of bins specified by
nbins
.
Default: 'global'
learning_rate
 Float in the range (0,1] that specifies the weight for each tree's prediction. Setting this parameter can reduce each tree's impact and thereby prevent earlier trees from monopolizing improvements at the expense of contributions from later trees.
Default: 0.3
min_split_loss
 Float in the range [0,1000] that specifies the minimum amount of improvement each split must achieve on the model's objective function value to avoid being pruned.
If set to 0 or omitted, no minimum is set. In this case, trees are pruned according to positive or negative objective function values.
Default: 0.0 (disable)
weight_reg
 Float in the range [0,1000] that specifies the regularization term applied to the weights of classification tree leaves. The higher the setting, the sparser or smoother the weights are, which can help prevent overfitting.
Default: 1.0
nbins
 Integer in the range (1,1000] that specifies the number of bins to use for finding splits in each column. More bins leads to longer runtime but more finegrained and possibly better splits.
Default: 32
sampling_size
 Float in the range (0,1] that specifies the fraction of rows to use in each training iteration.
A value of 1 indicates that all rows are used.
Default: 1.0
col_sample_by_tree
 Float in the range (0,1] that specifies the fraction of columns (features), chosen at random, to use when building each tree.
A value of 1 indicates that all columns are used.
col_sample_by
parameters "stack" on top of each other if several are specified. That is, given a set of 24 columns, for col_sample_by_tree=0.5
andcol_sample_by_node=0.5
,col_sample_by_tree
samples 12 columns, reducing the available, unsampled column pool to 12. col_sample_by_node
then samples half of the remaining pool, so each node samples 6 columns.
This algorithm will always sample at least one column.
Default: 1
col_sample_by_node
 Float in the range (0,1] that specifies the fraction of columns (features), chosen at random, to use when evaluating each split.
A value of 1 indicates that all columns are used.
col_sample_by
parameters "stack" on top of each other if several are specified. That is, given a set of 24 columns, for col_sample_by_tree=0.5
andcol_sample_by_node=0.5
,col_sample_by_tree
samples 12 columns, reducing the available, unsampled column pool to 12. col_sample_by_node
then samples half of the remaining pool, so each node samples 6 columns.
This algorithm will always sample at least one column.
Default: 1
Examples
See XGBoost for regression.