The ML.EVALUATE Function

ML.EVALUATE function

Use the ML.EVALUATE function to evaluate model metrics. The ML.EVALUATE function can be used with both linear regression and logistic regression models. You can also use the ML.ROC_CURVE function to evaluate logistic regression models.

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

The output columns for a logistic regression are:

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

The output columns for a linear regression are:

  • mean_absolute_error
  • mean_squared_error
  • mean_squared_log_error
  • median_absolute_error
  • r2_score
  • explained_variance

ML.EVALUATE limitations

The BigQuery ML ML.EVALUATE function is subject to the following limitations:

  • If a linear regression model contains labels that are not of type FLOAT64, the ML.EVALUATE function returns the following error: Invalid argument type passed to a function.

    To work around this limitation, use the CAST function to cast your label to FLOAT64.

ML.EVALUATE syntax

ML.EVALUATE(MODEL model_name,
           {TABLE table_name | (query_statement)}
           [, STRUCT( 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

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`.

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.

query_statement

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.

All columns referenced by the query_statement are used as inputs to the model.

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.

ML.EVALUATE example

The following example assumes your model and input table are in your default project.

The following query is used to create a logistic regression model.

CREATE MODEL
  `mydataset.mymodel`
OPTIONS
  (model_type='logistic_reg',
    input_label_cols=['custom_label']) AS
SELECT
  custom_label,
  column1
FROM
  `mydataset.mytable`

The following query evaluates the model using 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))
Was this page helpful? Let us know how we did:

Send feedback about...