The ML.RECOMMEND function

This document describes theML.RECOMMEND function, which lets you generatea predicted rating for every user-item rowcombination for amatrix factorization model.Because the input data for amatrix factorization model tends to be a sparse matrix with missing values,ML.RECOMMEND can return the predictions for those missing values withoutrequiring specification of each entry.

ML.RECOMMEND(  MODEL `PROJECT_ID.DATASET.MODEL_NAME`,  [, { TABLE `PROJECT_ID.DATASET.TABLE` | (QUERY_STATEMENT) }]  [, STRUCT(TRIAL_ID AS trial_id)])

• PROJECT_ID: the project that contains theresource.
• DATASET: the dataset that contains theresource.
• MODEL: the name of the model.

• TABLE: The name of the input table that contains theuser and item data.

  If you specify input data by using either theTABLE orQUERY_STATEMENTargument, the user and item columns must match the user and item columns inthe model, and their types must be compatible according toBigQueryimplicit coercion rules.

  If the input table does not contain both the user and item column, the inputtable can only contain one column. If the table contains both the user anditem columns, then the non-user or item columns are passed through andavailable for use in the query.

• QUERY_STATEMENT: The GoogleSQL query that isused to generate the evaluation data. For the supported SQL syntax for theQUERY_STATEMENT clause in GoogleSQL, seeQuery syntax.

  If you specify input data by using either theTABLE orQUERY_STATEMENTargument, the user and item columns must match the user and item columns inthe model, and their types must be compatible according toBigQueryimplicit coercion rules.

• TRIAL_ID: anINT64 value that identifies thehyperparameter tuning trial that you want the function to evaluate. Thefunction uses the optimal trial by default. Only specify this argument if youran hyperparameter tuning when creating the model.

Output

ML.RECOMMEND outputs at least 3 columns for all cases; theuser column, theitem column and a column for predicted recommendations.

The output ofML.RECOMMEND is computed as follows:

• If both the user and item columns are in the input data,thenML.RECOMMEND returns a rating for each user-item pair.
• If only the user or only the item is specified (for example, if the tableidentified by thetable argument onlycontains the user column), then all the item ratings for every user in thetable are outputted.
• If either the user or item feature was not in the training dataset, the ratingthat is returned is the intercept of the feature that was provided, eitheritem or user, added to theglobal__intercept__ offset. For example,global__intercept__ + __intercept__['user_a'].
• If input data is specified but does not provideeither the user or item column,ML.RECOMMEND returns an error.
• If no input data is specified,ML.RECOMMENDoutputs the ratings for every user and item combination seen during training.

If the model was trained withfeedback_type=EXPLICIT, a user column calleduser, and an item column calleditem, thenML.RECOMMEND returns thefollowing columns:

• user: aSTRING value containing the user data.
• item: aSTRING value containing the item data
• predicted_<rating_col_name>: aFLOAT64 value that contains the rating foreach user-item pair. Because the input ratings from training are assumed to beexplicit feedback, the predicted ratings are approximately in the range of theoriginal input, although ratings outside the range are also normal.

If the model was trained withfeedback_type=IMPLICIT, a user column calleduser, and an item column calleditem, thenML.RECOMMEND returns thefollowing columns:

• user: aSTRING value containing the user data.
• item: aSTRING value containing the item data
• predicted_<rating_col_name>_confidence: aFLOAT64 value that contains therelative confidence for each user-item pair. The input ratings fromtraining are assumed to be a proxy for user confidence. Therefore, if the modelhas converged, the predicted confidences lie between approximately 0 and 1 (butcan lie just outside that range). If the model hasn't converged, the predictedconfidences can be any value. If your model isn't converging and your ratingsare very large, try decreasing theWALS_ALPHA value that's specified in theCREATE MODEL statement for the model. If your model isn't converging andyour ratings are very small, try increasing theWALS_ALPHA value.

Examples

The following examples show how to use theML.RECOMMEND function.

No input data

The following example generates predicted ratings for everyuser-item pair in the inputs ofmymodel because there is no input dataspecified.

SELECT*FROMML.RECOMMEND(MODEL`mydataset.mymodel`)

With input data

The following example generates predicted ratings for each user-item row inmydataset.mytable assuming thatmydataset.mymodel was trained using the usercolumnuser and item columnitem.

SELECT*FROMML.RECOMMEND(MODEL`mydataset.mymodel`,(SELECTuser,itemFROM`mydataset.mytable`))

What's next

• For more information about model inference, seeModel inference overview.
• For more information about supported SQL statements and functions for MLmodels, seeEnd-to-end user journeys for ML models.