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 non-superusers:
CREATE privileges on the schema where the model is created
SELECT privileges on the input relation
Note
Machine learning algorithms contain a subset of four classification functions:
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 non-stationary 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 state-space approach, similar to Gardner, G., et al. This approach updates the state-space 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 state-space 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 single-threaded.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
ARIMA( 'model-name', 'input-relation', 'timeseries-column', 'timestamp-column'
USING PARAMETERS param=value[,...] )
Arguments
model-name
Model to create, where model-name 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.
input-relation
Name of the table or view containing timeseries-column and timestamp-column.
timeseries-column
Name of a NUMERIC column in input-relation that contains the dependent variable or outcome.
timestamp-column
Name of an INTEGER, FLOAT, or TIMESTAMP column in input-relation that represents the timestamp variable. The timestep between consecutive entries should be consistent throughout the timestamp-column.
Tip
If your timestamp-column 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 timeseries-column is a non-stationary time series, whose statistical properties change over time, you can specify a non-zero 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, second-order differencing takes the results of the first-order operation and differences these values again to obtain the second-order 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 moving-average 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:
'Zero': Coefficients are initialized to zero.
'Hannan-Rissanen' or 'HR': Coefficients are initialized using the Hannan-Rissanen algorithm.
Default: 'Zero'
epsilon
Float in the range (0.0, 1.0), controls the convergence criteria of the optimization algorithm.
Default: 1e-6
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 timeseries-column
regularization
Type of regularization used when training the model
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 timeseries-column used to train the model
timestamp_name
Name of the timestamp-column 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:
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.
Identifies the model to create, where model-name 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.
input-relation
The table or view containing timestamp-column and value-columns.
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.
value-columns
Comma-separated 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.
timestamp-column
Name of an INTEGER, FLOAT, or TIMESTAMP column in input-relation that represents the timestamp variable. The timestep between consecutive entries must be consistent throughout the timestamp-column.
Tip
If your timestamp-column 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:
'OLS' (Ordinary Least Squares)
'Yule-Walker'
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 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. 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:
None
'L2': Weight regularization term which penalizes the squared weight value
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 value-columns is subtracted from its column values before calculating the model coefficients. This parameter only applies if method is set to 'Yule-Walker'. 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 Yule-Walker training algorithm and a lag of 3:
Executes the bisecting k-means algorithm on an input relation.
Executes the bisecting k-means 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 meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
Table or view that contains the input data for k-means. 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.
input-columns
Comma-separated list of columns to use from the input relation, or asterisk (*) to select all columns. Input columns must be of data type numeric.
num-clusters
Number of clusters to create, an integer ≤ 10,000. This argument represents the k in k-means.
Parameters
exclude_columns
Comma-separated list of column names from input-columns to exclude from processing.
bisection_iterations
Integer between 1 - 1MM inclusive, specifies number of iterations the bisecting k-means algorithm performs for each bisection step. This corresponds to how many times a standalone k-means algorithm runs in each bisection step.
A setting >1 allows the algorithm to run and choose the best k-means 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:
size: Choose the largest cluster to bisect.
sum_squares: Choose the cluster with the largest within-cluster sum of squares to bisect.
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 k-means 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 k-means algorithm has converged. The algorithm is considered converged after no center has moved more than a distance of epsilon from the previous iteration.
Default: 1e-4
kmeans_center_init_method
The method used to find the initial cluster centers in k-means, 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
Comma-separated list of column names that identify the output rows. Columns must be in the input-columns 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 sub-cluster obtained by bisecting a cluster.
RightChildCluster: the right child cluster centroid of each centroid—that is, the centroid of the second sub-cluster obtained by bisecting a cluster.
BisectionLevel: Specifies which bisection step a cluster is obtained from.
WithinSS: Within-cluster sum of squares for the current cluster
TotalWithinSS: Total within-cluster 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 within-cluster sum of squares
Between-cluster sum of squares
Between-cluster sum of squares / Total sum of squares
Executes the k-means algorithm on an input relation.
Executes the k-means algorithm on an input relation. The result is a model with a list of cluster centers.
You can export the resulting k-means model in VERTICA_MODELS or PMML format to apply it on data outside Vertica. You can also train a k-means model elsewhere, then import it to Vertica in PMML format to predict on data in Vertica.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
The table or view that contains the input data for k-means. 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.
input-columns
Comma-separated list of columns to use from the input relation, or asterisk (*) to select all columns. Input columns must be of data type numeric.
num-clusters
The number of clusters to create, an integer ≤ 10,000. This argument represents the k in k-means.
Parameters
Important
Parameters init_method and initial_centers_table are mutually exclusive. If you set both, the function returns an error.
exclude_columns
Comma-separated list of column names from input-columns 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: 1e-4
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
Comma-separated list of column names from input-columns 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 input-columns 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 k-means 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:
Name of the table or view containing the training samples.
input-columns
String containing a comma-separated list of columns to use from the input-relation, or asterisk (*) to select all columns.
num-clusters
Integer ≤ 10,000 representing the number of clusters to create. This argument represents the k in k-prototypes.
Parameters
exclude-columns
String containing a comma-separated list of column names from input-columns 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: 1e-4
init_method
String specifying the method used to find the initial k-prototypes 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
Comma-separated list of column names that identify the output rows. Columns must be in the input-columns argument list
Examples
The following example creates k-prototypes model small_model and applies it to input table small_test_mixed:
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 meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
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.
response-column
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.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns to exclude from processing.
optimizer
Optimizer method used to train the model, one of the following:
If you select CGD, regularization-method must be set to L1 or ENet, otherwise the function returns an error.
Default:CGD if regularization-method 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: 1e-6
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 z-values of the coefficients, in the same order as coeffNames
tValue (for linear regression): Vector of t-values of the coefficients, in the same order as coeffNames
pValue: Vector of p-values 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)
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 meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
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.
response-column
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.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns to exclude from processing.
optimizer
The optimizer method used to train the model, one of the following:
If you select CGD, regularization-method must be set to L1 or ENet, otherwise the function returns an error.
Default:CGD if regularization-method 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: 1e-6
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 z-values of the coefficients, in the same order as coeffNames
tValue (for linear regression): Vector of t-values of the coefficients, in the same order as coeffNames
pValue: Vector of p-values 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
Creates a moving-average (MA) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_MOVING_AVERAGE.
Creates a moving-average (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 user-specified 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 single-threaded.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
The table or view containing the timestamp-column.
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.
data-column
An input column of type NUMERIC that contains the dependent variables or outcomes.
timestamp-column
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 moving-average 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:
None
L2: weight regularization term which penalizes the squared weight value
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.
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 meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-relation
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.
response-column
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.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns 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:
label: The class label.
value: The percentage of each class.
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:
index: The index of the predictor column.
prob: The probability conditioned on the class indicated by the class_name.
Bernoulli
The Bernoulli model conditioned on the class indicated by the class_name:
index: The index of the predictor column.
probTrue: The probability of having the value TRUE in this predictor column.
Categorical
The Gaussian model conditioned on the class indicated by the class_name:
category: The value in the predictor name.
<class_name>: The probability of having that value conditioned on the class indicated by the class_name.
Privileges
Superuser, or SELECT privileges on the input relation.
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 meta-function. You must call meta-functions in a top-level SELECT statement.
Model to create, where model-name 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.
input-relation
Table or view that contains the training data.
response-column
Name of the input column that represents the dependent variable or outcome. All values in this column must be numeric.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns 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 input-relation and P is the number of columns in predictor-columns.
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 input-relation that were rejected because they contained an invalid value.
accepted_row_count: Number of rows in input-relation accepted for training the model.
Privileges
Non-superusers:
CREATE privileges on the schema where the model is created
SELECT privileges on the input relation
Examples
The following example trains a PLS regression model with the default number of components:
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 meta-function. You must call meta-functions in a top-level SELECT statement.
Identifies the model to create, where model-name 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.
input-table
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.
response-column
Name of input column that represents the dependent variable or outcome. All values in this column must be numeric, otherwise the model is invalid.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns 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:
None (default)
L2
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: 1e-6
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 z-values of the coefficients, in the same order as coeffNames
tValue (for linear regression): Vector of t-values of the coefficients, in the same order as coeffNames
pValue: Vector of p-values 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=1e-8);
poisson_reg
---------------------------
Finished in 7 iterations
(1 row)
Identifies the model stored as a result of the training, where model-name 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.
input-relation
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.
response-column
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.
predictor-columns
Comma-separated 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 response-column, 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 under-the-hood.
Parameters
exclude_columns
Comma-separated list of column names from input-columns 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, number-predictors], 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:
predictorNames: The name of the predictors in the same order they were specified for training the model.
predictorTypes: The type of the predictors in the same order as their names in predictorNames.
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)
The model that is stored as a result of training, where model-name 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.
input-relation
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.
response-column
A numeric input column that represents the dependent variable.
predictor-columns
Comma-separated 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 response-column, 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 under-the-hood.
Parameters
exclude_columns
Comma-separated list of columns from predictor-columns 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, number-predictors], the number of features to consider at the split of a tree node.
Default: One-third 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:
predictorNames: The name of the predictors in the same order they were specified for training the model.
predictorTypes: The type of the predictors in the same order as their names in predictorNames.
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.
Identifies the model to create, where model-name 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.
input-relation
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.
response-column
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.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns 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: 1e-3
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 comma-delimited 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:
regularized (default): Fits the intercept and applies a regularization on it.
unregularized: Fits the intercept but does not include it in regularization.
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:
colNames: Intercept, or predictor column name
coefficients: Coefficient value
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:
Identifies the model to create, where model-name 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.
input-relation
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.
response-column
An input column that represents the dependent variable or outcome. The column must be a numeric data type.
predictor-columns
Comma-separated 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 response-column, 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
Comma-separated list of columns from predictor-columns 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: 1e-3
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
regularized (default): Fits the intercept and applies a regularization on it.
unregularized: Fits the intercept but does not include it in regularization.
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:
colNames: Intercept, or predictor column name
coefficients: Coefficient value
nAccepted
Number of samples accepted for training from the data set
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.
response-column
An input column of type CHAR or VARCHAR that represents the dependent variable or outcome.
predictor-columns
Comma-separated 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 under-the-hood.
Parameters
exclude_columns
Comma-separated list of column names from input-columns 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 over-fitting.
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 fine-grained 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.
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.
response-column
An input column of type INTEGER or FLOAT that represents the dependent variable or outcome.
predictor-columns
Comma-separated 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 under-the-hood.
Parameters
exclude_columns
Comma-separated list of column names from input-columns 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 over-fitting.
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 fine-grained 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.