The ML.EVALUATE Function

ML.EVALUATE function

Use the ML.EVALUATE function to evaluate model metrics. The ML.EVALUATE function can be used with linear regression, logistic regression, and k-means 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 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:

ML.EVALUATE limitations

The ML.ROC_CURVE function is subject to the following limitations:

  • The threshold option cannot be used with multiclass logistic regression 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`.

The input column names and data types in the model must match the column names and data types in the table.

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 and data types in the model must match the column names and data types in the table. 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 or table_name is specified, all columns contained in the table or query statement output are used as inputs to the model.

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.

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))
¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.