The ML.EVALUATE function

ML.EVALUATE function

Use the ML.EVALUATE function to evaluate model metrics.

For information about model evaluation in BigQuery ML, see Model evaluation overview.

For information about supported model types of each SQL statement and function, and all supported SQL statements and functions for each model type, read End-to-end user journey for each model.

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.

Notes:

• ML.EVALUATE requires input data with some older models, and returns an error if input data is absent. In this case, provide input data when using ML.EVALUATE.
• Neither table_name nor query_statement is required when evaluating the ARIMA-based time series models.

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 a query_statement or table_name will compute results on the entire training input.

Note: ML.EVALUATE requires input data with some older models, and will return an error if it is absent. If so, provide input data when using ML.EVALUATE with these models.

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 output

The output of the ML.EVALUATE function is a single row containing common metrics applicable to the type of model supplied.

Note: precision, recall, f1_score, log_loss, and roc_auc are macro-averaged for all the class labels. accuracy is computed as a global total or micro-average. For a macro-average, metrics are calculated for each label and then an unweighted average is taken of those values. For a micro-average, the metric is calculated globally by counting the total number of correctly predicted rows.

ML.EVALUATE returns the following columns for a regression model, which includes linear regression models, boosted tree regressor, DNN regressor, deep-and-wide regressor, and AutoML Tables regressor:

• 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 classification model, which includes logistic regression models, boosted tree classifier, DNN classifier, deep-and-wide classifier, and AutoML Tables classifier:

• precision
• recall
• accuracy
• f1_score
• log_loss
• roc_auc

ML.EVALUATE returns the following columns for a k-means model:

• Davies-Bouldin Index
• Mean squared distance

ML.EVALUATE returns the following columns for a matrix factorization model with implicit feedback:

• mean_average_precision
• mean_squared_error
• normalized_discounted_cumulative_gain
• average_rank (PDF download)

ML.EVALUATE returns the following columns for a matrix factorization model with explicit feedback:

• 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 PCA model: total_explained_variance_ratio.

It is the percentage of the cumulative variance explained by all the returned principal components. For more information, see the ml.principal_component_info function.

The ML.EVALUATE function returns the following columns for a trained time-series ARIMA or ARIMA_PLUS model:

• time_series_id_col or time_series_id_cols: the identifiers of a time series. Only present when forecasting multiple time series at once. The column names and types are inherited from the TIME_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
• has_holiday_effect
• has_spikes_and_dips
• has_step_changes

Notes:

• For old trained ARIMA models, this function only returns the evaluation metrics for the first 5,000 time series, in the ascending order of time_series_id_col or time_series_id_cols. For newly trained ARIMA_PLUS models, this function returns the evaluation metrics for all the time series, in the ascending order of time_series_id_col or time_series_id_cols.
• The has_holiday_effect, has_spikes_and_dips, and has_step_changes columns are only populated for ARIMA_PLUS models that have decompose_time_series enabled.
• The seasonal_periods column has the following valid values: DAILY, WEEKLY, MONTHLY, QUARTERLY, YEARLY, and NO_SEASONALITY. It can be a combination of these values, as a time series can have multiple seasonal periods.
• When non_seasonal_d is not `1`, has_drift is set to FALSE, as non_seasonal_d doesn't apply in those cases.

ML.EVALUATE returns the following columns for an Autoencoder model:

• mean_absolute_error
• mean_squared_error
• mean_squared_log_error

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