This is the multi-page printable view of this section. Click here to print.
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
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. 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[,...] )
model-namemodel-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-relationtimeseries-column and timestamp-column.timeseries-columninput-relation that contains the dependent variable or outcome.timestamp-columninput-relation that represents the timestamp variable. The timestep between consecutive entries should be consistent throughout the timestamp-column.
timestamp-column has varying timesteps, consider standardizing the step size with the TIME_SLICE function.
pq 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.
p value as an ARIMA model trained with d and q parameters set to zero, those two models will not be identical.
Default: 0
dIf 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
qp 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.
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'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'Zero': Coefficients are initialized to zero.
'Hannan-Rissanen' or 'HR': Coefficients are initialized using the Hannan-Rissanen algorithm.
Default: 'Zero'
epsilonDefault: 1e-6
max_iterationsDefault: 100
coefficientsphi: 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, dp: 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
meantimeseries-columnregularizationlambdamean_squared_errorrejected_row_countaccepted_row_counttimeseries_nametimeseries-column used to train the modeltimestamp_nametimestamp-column used to train the modelmissing_methodcall_stringThe 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=1e-06, 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 in-depth example that trains and makes predictions with ARIMA models, see ARIMA model example.
Creates and trains an autoregression (AR) or vector autoregression (VAR) model, depending on the number of provided value columns:
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 single-threaded.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
AUTOREGRESSOR ('model-name', 'input-relation', 'value-columns', 'timestamp-column'
[ USING PARAMETERS param=value[,...] ] )
model-namemodel-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-relationtimestamp-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-columnsThe 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-columninput-relation that represents the timestamp variable. The timestep between consecutive entries must be consistent throughout the timestamp-column.
timestamp-column has varying timesteps, consider standardizing the step size with the TIME_SLICE function.
pp weaken the correlation.
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'OLS' (Ordinary Least Squares): default for AR models; not supported for VAR models.
'Yule-Walker': default for VAR models. Sets the alpha constant in the trained model to 0.0.
missing'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:
regularizationNone
'L2': Weight regularization term which penalizes the squared weight value
Default: None
lambdaDefault: 1.0
compute_mseDefault: False
subtract_meanvalue-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
The following example creates and trains an autoregression model using the Yule-Walker training algorithm and a lag of 3:
=> SELECT AUTOREGRESSOR('AR_temperature_yw', 'temp_data', 'Temperature', 'time' USING PARAMETERS p=3, method='yule-walker');
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.
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.
BISECTING_KMEANS('model-name', 'input-relation', 'input-columns', 'num-clusters'
[ USING PARAMETERS
[exclude_columns = 'exclude-columns']
[, bisection_iterations = bisection-iterations]
[, split_method = 'split-method']
[, min_divisible_cluster_size = min-cluster-size]
[, kmeans_max_iterations = kmeans-max-iterations]
[, kmeans_epsilon = kmeans-epsilon]
[, kmeans_center_init_method = 'kmeans-init-method']
[, distance_method = 'distance-method']
[, output_view = 'output-view']
[, key_columns = 'key-columns'] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.input-columnsnum-clustersk in k-means.exclude_columnsComma-separated list of column names from input-columns to exclude from processing.
bisection_iterationsA 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_methodsize: 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_sizeDefault: 2
kmeans_max_iterationsDefault: 10
kmeans_epsilonepsilon from the previous iteration.
Default: 1e-4
kmeans_center_init_methodkmeanspp (default): kmeans++ algorithm
pseudo: Uses "pseudo center" approach used by Spark, bisects given center without iterating over points
distance_methodDefault: euclidean
output_viewkey_columnsinput-columns argument list. To exclude these and other input columns from being used by the algorithm, list them in parameter exclude_columns.centershierarchyParentCluster: 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.
metricsTotal sum of squares
Total within-cluster sum of squares
Between-cluster sum of squares
Between-cluster sum of squares / Total sum of squares
Sum of squares for cluster x, center_id y[...]
SELECT BISECTING_KMEANS('myModel', 'iris1', '*', '5'
USING PARAMETERS exclude_columns = 'Species,id', split_method ='sum_squares', output_view = 'myBKmeansView');
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.
KMEANS ( 'model-name', 'input-relation', 'input-columns', 'num-clusters'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, max_iterations = max-iterations]
[, epsilon = epsilon-value]
[, { init_method = 'init-method' } | { initial_centers_table = 'init-table' } ]
[, output_view = 'output-view']
[, key_columns = 'key-columns'] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.input-columnsnum-clustersk in k-means.init_method and initial_centers_table are mutually exclusive. If you set both, the function returns an error.
exclude_columnsComma-separated list of column names from input-columns to exclude from processing.
max_iterationsDefault: 10
epsilonDefault: 1e-4
init_methodrandom
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_tableoutput_viewkey_columnsinput-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.centersmetricsThe 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:
=> 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)
Executes the k-prototypes algorithm on an input relation. The result is a model with a list of cluster centers.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
SELECT KPROTOTYPES ('`*`model-name`*`', '`*`input-relation`*`', '`*`input-columns`*`', `*`num-clusters`*`
[USING PARAMETERS [exclude_columns = '`*`exclude-columns`*`']
[, max_iterations = '`*`max-iterations`*`']
[, epsilon = `*`epsilon`*`]
[, {[init_method = '`*`init-method`*`'] } | { initial_centers_table = '`*`init-table`*`' } ]
[, gamma = '`*`gamma`*`']
[, output_view = '`*`output-view`*`']
[, key_columns = '`*`key-columns`*`']]);
model-nameinput-relationinput-columnsnum-clustersexclude-columnsDefault: (empty)
max_iterationsDefault: Integer ≤ 1M
epsilonDefault: 1e-4
init_methodDefault: "random"
initial_centers_tablegammaDefault: Inferred from data.
output_viewkey_columnsThe following example creates k-prototypes 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)
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.
LINEAR_REG ( 'model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, optimizer = 'optimizer-method']
[, regularization = 'regularization-method']
[, epsilon = epsilon-value]
[, max_iterations = iterations]
[, lambda = lamda-value]
[, alpha = alpha-value]
[, fit_intercept = boolean-value] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.optimizerCGD, 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.
regularizationNone (default)
L1
L2
ENet
epsilonFLOAT 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_iterationsINTEGER 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
lambdaregularization parameter.
Default: 1
alpharegularization 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_interceptfit_intercept to false does not work well with the BFGS optimizer.
Default: True
datacoeffNames: 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
regularizationlambdaalphaiterationsmax_iterations.skippedRowsprocessedRowsskippedRows.callStr=> 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. 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.
LOGISTIC_REG ( 'model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS [exclude_columns = 'excluded-columns']
[, optimizer = 'optimizer-method']
[, regularization = 'regularization-method']
[, epsilon = epsilon-value]
[, max_iterations = iterations]
[, lambda = lamda-value]
[, alpha = alpha-value]
[, fit_intercept = boolean-value] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.optimizerCGD, 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.
regularizationNone (default)
L1
L2
ENet
epsilonFLOAT 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_iterationsINTEGER 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
lambdaregularization parameter.
Default: 1
alpharegularization 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_interceptfit_intercept to false does not work well with the BFGS optimizer.
Default: True
datacoeffNames: 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
regularizationlambdaalphaiterationsmax_iterations.skippedRowsprocessedRowsskippedRows.callStrSuperuser, or SELECT privileges on the input relation
=> 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)
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.
MOVING_AVERAGE ('model-name', 'input-relation', 'data-column', 'timestamp-column'
[ USING PARAMETERS
[ q = lags ]
[, missing = "imputation-method" ]
[, regularization = "regularization-method" ]
[, lambda = regularization-value ]
[, compute_mse = boolean ]
] )
model-namemodel-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-relationtimestamp-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-columntimestamp-columnqq value as an ARIMA model trained with p and d parameters set to zero, those two models will not be identical.
Default: 1
missingdrop: 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
regularizationNone
L2: weight regularization term which penalizes the squared weight value
Default: None
lambdaDefault: 1.0
compute_mseThis parameter only accepts "true" or "false" rather than the standard literal equivalents for BOOLEANs like 1 or 0.
Default: False
See Moving-average model example.
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.
NAIVE_BAYES ( 'model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS [exclude_columns = 'excluded-columns'] [, alpha = alpha-value] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnThe response column must be of type numeric, CHAR/VARCHAR, or BOOLEAN; otherwise the model is invalid.
predictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.alphaDefault: 1.0
colsInfoindex: 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.
alphapriorlabel: The class label.
value: The percentage of each class.
nRowsTotalnRowsRejectedcallStrGaussianindex: The index of the predictor column.
mu: The mean value of the model.
sigmaSq: The squared standard deviation of the model.
Multinominalindex: The index of the predictor column.
prob: The probability conditioned on the class indicated by the class_name.
Bernoulliindex: The index of the predictor column.
probTrue: The probability of having the value TRUE in this predictor column.
Categoricalcategory: The value in the predictor name.
<class_name>: The probability of having that value conditioned on the class indicated by the class_name.
Superuser, or SELECT privileges on the input relation.
=> 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)
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.
PLS_REG ( 'model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS param=value[,...] ] )
model-namemodel-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-relationresponse-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.num_componentsinput-relation and P is the number of columns in predictor-columns.
Default: 2
scaleDefault: True
detailscoeffNames: 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.responsescall_stringAdditional Infois_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.Non-superusers:
CREATE privileges on the schema where the model is created
SELECT privileges on the input relation
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;
1|2|3|0|2|5
2|3|4|0|4|4
3|4|5|0|8|3
\.
=> 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 in-depth example that trains and makes predictions with a PLS model, see PLS regression.
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.
POISSON_REG ( 'model-name', 'input-table', 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, optimizer = 'optimizer-method']
[, regularization = 'regularization-method']
[, epsilon = epsilon-value]
[, max_iterations = iterations]
[, lambda = lamda-value]
[, fit_intercept = boolean-value] ] )
model-namemodel-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-tableSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.optimizerNewton.regularizationNone (default)
L2
epsilonmax_iterations.
Default: 1e-6
max_iterationsmax_iterations or the relative change in Poisson deviance is less than or equal to epsilon.lambdaregularization strength.
Default: 1.0
fit_interceptDefault: True
datacoeffNames: 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
regularizationlambdaiterationsmax_iterations.skippedRowsprocessedRowsskippedRows.callStr=> SELECT POISSON_REG('myModel', 'numericFaithful', 'eruptions', 'waiting' USING PARAMETERS epsilon=1e-8);
poisson_reg
---------------------------
Finished in 7 iterations
(1 row)
Trains a random forest model for classification on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
RF_CLASSIFIER ( 'model-name', input-relation, 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, ntree = num-trees]
[, mtry = num-features]
[, sampling_size = sampling-size]
[, max_depth = depth]
[, max_breadth = breadth]
[, min_leaf_size = leaf-size]
[, min_info_gain = threshold]
[, nbins = num-bins] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnsComma-separated list of column names from input-columns to exclude from processing.
ntreeInteger in the range [1, 1000], the number of trees in the forest.
Default: 20
mtrynumber-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_sizeFloat 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_depthInteger 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_breadthInteger in the range [1, 1e9], the maximum number of leaf nodes a tree can have.
Default: 32
min_leaf_sizeInteger 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_gainFloat 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
nbinsInteger in the range [2, 1000], the number of bins to use for discretizing continuous features.
Default: 32
datapredictorNames: 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.
ntreeskippedRowsinput_relation that were skipped because they contained an invalid value.processedRowsinput_relation minus skippedRows.callStr=> 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)
Trains a random forest model for regression on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
RF_REGRESSOR ( 'model-name', input-relation, 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, ntree = num-trees]
[, mtry = num-features]
[, sampling_size = sampling-size]
[, max_depth = depth]
[, max_breadth = breadth]
[, min_leaf_size = leaf-size]
[, min_info_gain = threshold]
[, nbins = num-bins] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.ntreeInteger in the range [1, 1000], the number of trees in the forest.
Default: 20
mtrynumber-predictors], the number of features to consider at the split of a tree node.
Default: One-third the total number of predictors
sampling_sizeFloat 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_depthInteger 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_breadthInteger in the range [1, 1e9], the maximum number of leaf nodes a tree can have.
Default: 32
min_leaf_sizeThe 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_gainFloat 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
nbinsInteger in the range [2, 1000], the number of bins to use for discretizing continuous features.
Default: 32
datapredictorNames: 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.
ntreeskippedRowsinput_relation that were skipped because they contained an invalid value.processedRowsinput_relation minus skippedRows.callStr=> SELECT RF_REGRESSOR ('myRFRegressorModel', 'mtcars', 'carb', 'mpg, cyl, hp, drat, wt' USING PARAMETERS
ntree=100, sampling_size=0.3);
RF_REGRESSOR
--------------
Finished
(1 row)
Trains the SVM model on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
SVM_CLASSIFIER ( 'model-name', input-relation, 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, C = 'cost']
[, epsilon = 'epsilon-value']
[, max_iterations = 'max-iterations']
[, class_weights = 'weight']
[, intercept_mode = 'intercept-mode']
[, intercept_scaling = 'scale'] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.CDefault: 1.0
epsilonDefault: 1e-3
max_iterationsDefault: 100
class_weightsNone (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_moderegularized (default): Fits the intercept and applies a regularization on it.
unregularized: Fits the intercept but does not include it in regularization.
intercept_scalingcoeffcolNames: Intercept, or predictor column name
coefficients: Coefficient value
nAcceptednRejectednIterationcallStrThe 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)
Trains the SVM model on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
SVM_REGRESSOR ( 'model-name', input-relation, 'response-column', 'predictor-columns'
[ USING PARAMETERS
[exclude_columns = 'excluded-columns']
[, error_tolerance = error-tolerance]
[, C = cost]
[, epsilon = epsilon-value]
[, max_iterations = max-iterations]
[, intercept_mode = 'mode']
[, intercept_scaling = 'scale'] ] )
model-namemodel-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-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsComma-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.
exclude_columnspredictor-columns to exclude from processing.error_toleranceDefault: 0.1
CDefault: 1.0
epsilonDefault: 1e-3
max_iterationsDefault: 100
intercept_moderegularized (default): Fits the intercept and applies a regularization on it.
unregularized: Fits the intercept but does not include it in regularization.
intercept_scalingcoeffcolNames: Intercept, or predictor column name
coefficients: Coefficient value
nAcceptednRejectednIterationcallStr
=> 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)
Trains an XGBoost model for classification on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
XGB_CLASSIFIER ('model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS param=value[,...] ] )
model-nameName of the model (case-insensitive).
input-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsColumns 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.
exclude_columnsComma-separated list of column names from input-columns to exclude from processing.
max_ntreeDefault: 10
max_depthDefault: 6
objectiveDefault: 'crossentropy'
split_proposal_methodnbins.
Default: 'global'
learning_rateDefault: 0.3
min_split_lossIf 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_regDefault: 1.0
nbinsDefault: 32
sampling_sizeA value of 1 indicates that all rows are used.
Default: 1.0
col_sample_by_treeA 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_nodeA 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
Trains an XGBoost model for regression on an input relation.
This is a meta-function. You must call meta-functions in a top-level SELECT statement.
XGB_REGRESSOR ('model-name', 'input-relation', 'response-column', 'predictor-columns'
[ USING PARAMETERS param=value[,...] ] )
model-nameName of the model (case-insensitive).
input-relationSYNC_WITH_HCATALOG_SCHEMA to sync the hcatalog schema, and then run the machine learning function.response-columnpredictor-columnsColumns 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.
exclude_columnsComma-separated list of column names from input-columns to exclude from processing.
max_ntreeDefault: 10
max_depthDefault: 6
objectiveDefault: 'squarederror'
split_proposal_methodnbins.
Default: 'global'
learning_rateDefault: 0.3
min_split_lossIf 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_regDefault: 1.0
nbinsDefault: 32
sampling_sizeA value of 1 indicates that all rows are used.
Default: 1.0
col_sample_by_treeA 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_nodeA 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