The ML.TRANSFORM function

This document describes theML.TRANSFORM function, which you can useto preprocess feature data. This function processes input data byapplying the data transformations captured in theTRANSFORM clauseof an existing model. The statistics that were calculated for datatransformation during model training are applied to the input data of the function.

For more information about which models support this function, seeEnd-to-end user journeys for ML models.

Syntax

ML.TRANSFORM(  MODEL `PROJECT_ID.DATASET.MODEL_NAME`,  { TABLE `PROJECT_ID.DATASET.TABLE_NAME` | (QUERY_STATEMENT) })

Arguments

ML.TRANSFORM takes the following arguments:

• PROJECT_ID: the project that contains theresource.
• DATASET: the BigQuery dataset thatcontains the resource.
• MODEL_NAME: the name of a model. The modelmust have been created by using aCREATE MODEL statement that includes aTRANSFORM clauseto manually preprocess feature data. You can check to see if a model uses aTRANSFORM clause by using thebq show commandto look at themodel's metadata.If the model was trained using aTRANSFORM clause, the model metadatacontains a section about the transform columns. The function returns an errorif you specify a model that was trained without aTRANSFORM clause.

• TABLE_NAME: the name of the input table thatcontains the feature data to preprocess.

  If you specify a value for theTABLE_NAME argument, the input column namesin the table must match the input column names in the model'sTRANSFORMclause, and their types should be compatible according toBigQueryimplicit coercion rules.You can get the input column names and data types from themodel's metadata,in the section about the feature columns.

• QUERY_STATEMENT: A query that generates the featuredata to preprocess. For the supported SQL syntax of theQUERY_STATEMENTclause, seeGoogleSQL query syntax.

  If you specify a value for theQUERY_STATEMENT argument, the input columnnames from the query must match the input column names in the model'sTRANSFORM clause, and their types should be compatible according toBigQueryimplicit coercion rules.You can get the input column names and data types from themodel's metadata,in the section about the feature columns.

Output

ML.TRANSFORM returns the columns specified in the model'sTRANSFORM clause.

Example

The following example returns feature data that has been preprocessed byusing theTRANSFORM clause included in the model namedmydataset.mymodelin your default project.

Create the model that contains theTRANSFORM clause:

CREATEORREPLACEMODEL`mydataset.mymodel`TRANSFORM(species,island,ML.MAX_ABS_SCALER(culmen_length_mm)OVER()ASculmen_length_mm,ML.MAX_ABS_SCALER(flipper_length_mm)OVER()ASflipper_length_mm,sex,body_mass_g)OPTIONS(model_type='linear_reg',input_label_cols=['body_mass_g'])AS(SELECT*FROM`bigquery-public-data.ml_datasets.penguins`WHEREbody_mass_gISNOTNULL);

Return feature data preprocessed by the model'sTRANSFORM clause:

SELECT*FROMML.TRANSFORM(MODEL`mydataset.mymodel`,TABLE`bigquery-public-data.ml_datasets.penguins`);

The result is similar to the following:

+-------------------------------------+--------+---------------------+---------------------+--------+-----------------+-------------+| species                             | island | culmen_length_mm    | flipper_length_mm   | sex    | culmen_depth_mm | body_mass_g |--------------------------------------+--------+ ------------------- +---------------------+--------+-----------------+-------------+| Adelie Penguin (Pygoscelis adeliae) | Dream  | 0.61409395973154368 | 0.79653679653679654 | Female | 18.4            | 3475.0      || Adelie Penguin (Pygoscelis adeliae) | Dream  | 0.66778523489932884 | 0.79653679653679654 | Male   | 19.1            | 4650.0      |+-------------------------------------+--------+---------------------+---------------------+--------+-----------------+-------------+

What's next

• For information about feature preprocessing, seeFeature preprocessing overview.