1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Roles required to select or create a project

   • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
   • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

2. Verify that billing is enabled for your Google Cloud project.

3. Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

Required permissions

To create the model using BigQuery ML, you need the followingIAM permissions:

• bigquery.jobs.create
• bigquery.models.create
• bigquery.models.getData
• bigquery.models.updateData
• bigquery.models.updateMetadata

To run inference, you need the following permissions:

• bigquery.models.getData on the model
• bigquery.jobs.create

Introduction

A common task in machine learning is to classify data into one of two types,known as labels. For example, a retailer might want to predict whether a givencustomer will purchase a new product, based on other information about thatcustomer. In that case, the two labels might bewill buy andwon't buy. Theretailer can construct a dataset such that one column represents both labels,and also contains customer information such as the customer's location, theirprevious purchases, and their reported preferences. The retailer can then use abinary logistic regression model that uses this customer information to predictwhich label best represents each customer.

In this tutorial, you create a binary logistic regression model that predictswhether a US Census respondent's income falls into one of two ranges based onthe respondent's demographic attributes.

Create a dataset

Create a BigQuery dataset to store your model:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, click your project name.

4. Clickmore_vertView actions > Create dataset.

5. On theCreate dataset page, do the following:

   • ForDataset ID, entercensus.

   • ForLocation type, selectMulti-region, and then selectUS (multiple regions in United States).

     The public datasets are stored in theUSmulti-region. Forsimplicity, store your dataset in the same location.

   • Leave the remaining default settings as they are, and clickCreate dataset.

Examine the data

Examine the dataset and identify which columns to use astraining data for the logistic regression model. Select 100 rows from thecensus_adult_income table:

importbigframes.pandasasbpddf=bpd.read_gbq("bigquery-public-data.ml_datasets.census_adult_income",columns=("age","workclass","marital_status","education_num","occupation","hours_per_week","income_bracket","functional_weight",),max_results=100,)df.peek()# Output:# age      workclass       marital_status  education_num          occupation  hours_per_week income_bracket  functional_weight#  47      Local-gov   Married-civ-spouse             13      Prof-specialty              40           >50K             198660#  56        Private        Never-married              9        Adm-clerical              40          <=50K              85018#  40        Private   Married-civ-spouse             12        Tech-support              40           >50K             285787#  34   Self-emp-inc   Married-civ-spouse              9        Craft-repair              54           >50K             207668#  23        Private   Married-civ-spouse             10   Handlers-cleaners              40          <=50K              40060

The query results show that theincome_bracket column in thecensus_adult_income table has only one of two values:<=50K or>50K.

Prepare the sample data

In this tutorial, you predict census respondent income based on values of thefollowing columns in thecensus_adult_income table:

• age: the age of the respondent.
• workclass: class of work performed. For example local government, private,or self-employed.
• marital_status
• education_num: the respondent's higheset level of education.
• occupation
• hours_per_week: hours worked per week.

You exclude columns that duplicate data. For example, theeducation column,because theeducation andeducation_num column values express thesame data in different formats.

Thefunctional_weight column is the number of individuals that the censusorganization believes a particular row represents. Because the value of thiscolumn is unrelated to the value of theincome_bracket for any given row, youuse the value in this column to separate the data into training, evaluation,and prediction sets by creating a newdataframe column that is derived fromthefunctional_weight column. You label 80% of the data for training themodel, 10% of data for evaluation, and 10% of the data for prediction.

importbigframes.pandasasbpdinput_data=bpd.read_gbq("bigquery-public-data.ml_datasets.census_adult_income",columns=("age","workclass","marital_status","education_num","occupation","hours_per_week","income_bracket","functional_weight",),)input_data["dataframe"]=bpd.Series("training",index=input_data.index,).case_when([(((input_data["functional_weight"]%10)==8),"evaluation"),(((input_data["functional_weight"]%10)==9),"prediction"),])delinput_data["functional_weight"]

Create a logistic regression model

Create a logistic regression model with the training data you labeled in theprevious section.

importbigframes.ml.linear_model# input_data is defined in an earlier step.training_data=input_data[input_data["dataframe"]=="training"]X=training_data.drop(columns=["income_bracket","dataframe"])y=training_data["income_bracket"]census_model=bigframes.ml.linear_model.LogisticRegression(# Balance the class labels in the training data by setting# class_weight="balanced".## By default, the training data is unweighted. If the labels# in the training data are imbalanced, the model may learn to# predict the most popular class of labels more heavily. In# this case, most of the respondents in the dataset are in the# lower income bracket. This may lead to a model that predicts# the lower income bracket too heavily. Class weights balance# the class labels by calculating the weights for each class in# inverse proportion to the frequency of that class.class_weight="balanced",max_iterations=15,)census_model.fit(X,y)census_model.to_gbq(your_model_id,# For example: "your-project.census.census_model"replace=True,)

Evaluate the model's performance

After creating the model, evaluate the model's performance against theevaluation data.

# Select model you'll use for predictions. `read_gbq_model` loads model# data from BigQuery, but you could also use the `census_model` object# from previous steps.census_model=bpd.read_gbq_model(your_model_id,# For example: "your-project.census.census_model")# input_data is defined in an earlier step.evaluation_data=input_data[input_data["dataframe"]=="evaluation"]X=evaluation_data.drop(columns=["income_bracket","dataframe"])y=evaluation_data["income_bracket"]# The score() method evaluates how the model performs compared to the# actual data. Output DataFrame matches that of ML.EVALUATE().score=census_model.score(X,y)score.peek()# Output:#    precision    recall  accuracy  f1_score  log_loss   roc_auc# 0   0.685764  0.536685   0.83819  0.602134  0.350417  0.882953

You can also look at the model'sEvaluation pane in the Google Cloud consoleto view the evaluation metrics calculated during the training:

Predict the income bracket

Use the model to predict the most likely income bracket for each respondent.

# Select model you'll use for predictions. `read_gbq_model` loads model# data from BigQuery, but you could also use the `census_model` object# from previous steps.census_model=bpd.read_gbq_model(your_model_id,# For example: "your-project.census.census_model")# input_data is defined in an earlier step.prediction_data=input_data[input_data["dataframe"]=="prediction"]predictions=census_model.predict(prediction_data)predictions.peek()# Output:#           predicted_income_bracket                     predicted_income_bracket_probs  age workclass  ... occupation  hours_per_week income_bracket   dataframe# 18004                    <=50K  [{'label': ' >50K', 'prob': 0.0763305999358786...   75         ?  ...          ?               6          <=50K  prediction# 18886                    <=50K  [{'label': ' >50K', 'prob': 0.0448866871906495...   73         ?  ...          ?              22           >50K  prediction# 31024                    <=50K  [{'label': ' >50K', 'prob': 0.0362982319421936...   69         ?  ...          ?               1          <=50K  prediction# 31022                    <=50K  [{'label': ' >50K', 'prob': 0.0787836112058324...   75         ?  ...          ?               5          <=50K  prediction# 23295                    <=50K  [{'label': ' >50K', 'prob': 0.3385373037905673...   78         ?  ...          ?              32          <=50K  prediction

Explain the prediction results

To understand why the model is generating these prediction results, you can usetheML.EXPLAIN_PREDICT function.

ML.EXPLAIN_PREDICT is an extended version of theML.PREDICT function.ML.EXPLAIN_PREDICT not only outputs prediction results, but also outputsadditional columns to explain the prediction results. For more informationabout explainability, seeBigQuery ML explainable AI overview.

Run theML.EXPLAIN_PREDICT query:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, run the following query:

   SELECT*FROMML.EXPLAIN_PREDICT(MODEL`census.census_model`,(SELECT*FROM`census.input_data`WHEREdataframe='evaluation'),STRUCT(3astop_k_features));

3. The results look similar to the following:

   

For logistic regression models,Shapleyvalues are used to determine relativefeature attribution for each feature in the model. Because thetop_k_featuresoption was set to3 in the query,ML.EXPLAIN_PREDICT outputs the top threefeature attributions for each row of theinput_data view. These attributionsare shown in descending order by the absolute value of the attribution.

Globally explain the model

To know which features are the most important to determine the income bracket,use theML.GLOBAL_EXPLAIN function.

Get global explanations for the model:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

2. In the query editor, run the following query to get global explanations:

   SELECT*FROMML.GLOBAL_EXPLAIN(MODEL`census.census_model`)

3. The results look similar to the following: