ML.EVALUATE
function
Use the ML.EVALUATE
function to evaluate model metrics. The ML.EVALUATE
function can be used with linear regression, logistic regression, k-means,
matrix factorization, and ARIMA-based time series models. You can also use the
ML.ROC_CURVE
function to evaluate logistic regression models, but ML.ROC_CURVE
is not
supported for multiclass models.
The output of the ML.EVALUATE
function is a single row containing common
metrics applicable to the type of model supplied.
ML.EVALUATE
returns the following columns for a logistic or multiclass
regression model:
precision
recall
accuracy
f1_score
log_loss
roc_auc
ML.EVALUATE
returns the following columns for a linear regression or explicit
matrix factorization model:
mean_absolute_error
mean_squared_error
mean_squared_log_error
median_absolute_error
r2_score
explained_variance
ML.EVALUATE
returns the following columns for a k-means model:
- Davies-Bouldin Index
- Mean squared distance
ML.EVALUATE
returns the following columns for an implicit matrix factorization
model:
- mean_average_precision
mean_squared_error
- normalized_discounted_cumulative_gain
- average_rank (PDF download)
ML.EVALUATE
returns the following columns for ARIMA-based time series models:
time_series_id
: the identifier of a time series. Only present when forecasting multiple time series at once. The column name and type are inherited from theTIME_SERIES_ID_COL
option as specified in the model creation query.non_seasonal_p
non_seasonal_d
non_seasonal_q
has_drift
log_likelihood
AIC
variance
seasonal_periods
ML.EVALUATE
limitations
The ML.EVALUATE
function is subject to the following limitations:
ML.EVALUATE
does not support imported TensorFlow models.- The
threshold
option cannot be used with multiclass logistic regression models or matrix factorization models.
ML.EVALUATE
syntax
ML.EVALUATE(MODEL model_name [, {TABLE table_name | (query_statement)}] [, STRUCT(<T> AS threshold)])
model_name
model_name
is the name of the model you're evaluating. If you do
not have a default project configured, prepend the project ID to the model
name in following format: `[PROJECT_ID].[DATASET].[MODEL]` (including the
backticks); for example, `myproject.mydataset.mymodel`.
table_name
(Optional) table_name
is the name of the input table that contains the
evaluation data. If you do not have a default project configured, prepend the
project ID to the table name in following format: `[PROJECT_ID].[DATASET].[TABLE]`
(including the backticks); for example, `myproject.mydataset.mytable`.
If table_name
is specified, the input column names in the table must match the
column names in the model, and their types should be compatible according to
BigQuery implicit coercion rules. The input must have a column that matches the
label column name provided during training. This value is provided using the
input_label_cols
option. If input_label_cols
is unspecified, the column
named "label" in the training data is used.
If neither table_name
nor query_statement
is specified, ML.EVALUATE
computes the evaluation results as follows:
- If the data is split during training, the split evaluation data is used to compute the evaluation results.
- If the data is not split during training, the entire training input is used to compute the evaluation results.
query_statement
(Optional) The query_statement
clause specifies the standard SQL query that is
used to generate the evaluation data. See the
Standard SQL Query Syntax
page for the supported SQL syntax of the query_statement
clause.
If query_statement
is specified, the input column names from the query must
match the column names in the model, and their types should be compatible
according to BigQuery implicit coercion rules.
The input must have a column that matches the label column name provided during
training. This value is provided using the input_label_cols
option. If
input_label_cols
is unspecified, the column named "label" in the training data
is used. The extra columns are ignored.
If the TRANSFORM
clause was present in the CREATE MODEL
statement that created model_name
,
then only the input columns present in the
TRANSFORM
clause must appear in query_statement
.
If neither table_name
nor query_statement
is specified, ML.EVALUATE
computes the evaluation results as follows:
- If the data is split during training, the split evaluation data is used to compute the evaluation results.
- If the data is not split during training, the entire training input is used to
compute the evaluation results.
Because k-means models do not allow data split, calling
ML.EVALUATE
on a k-means model without specifying aquery_statement
ortable_name
will compute results on the entire training input.
threshold
(Optional) threshold
is a custom threshold for your logistic regression model
to be used for evaluation. The default value is 0.5. The threshold value that is
supplied must be of type STRUCT
.
A zero value for precision or recall means that the selected threshold produced no true positive labels. A NaN value for precision means that the selected threshold produced no positive labels, neither true positives nor false positives.
If both table_name
and query_statement
are unspecified, you cannot use a
threshold. Also, threshold
cannot be used with multiclass logistic regression
models.
ML.EVALUATE
examples
ML.EVALUATE
with no input data specified
The following query is used to evaluate a model with no input data specified.
SELECT * FROM ML.EVALUATE(MODEL `mydataset.mymodel`)
ML.EVALUATE
with a custom threshold and input data
The following query evaluates the model by specifying input data and a custom threshold of 0.55.
SELECT * FROM ML.EVALUATE(MODEL `mydataset.mymodel`, ( SELECT custom_label, column1, column2 FROM `mydataset.mytable`), STRUCT(0.55 AS threshold))