Viewing: cloud.google.com

Perform feature engineering with the TRANSFORM clause Stay organized with collections Save and categorize content based on your preferences.

This tutorial teaches you how to use the TRANSFORM clause of the CREATE MODEL statement to perform feature engineering at the same time that you create and train a model. Using the TRANSFORM clause, you can specify one or more preprocessing functions to transform the input data you use to train the model. The preprocessing that you apply to the model is automatically applied when you use the model with the ML.EVALUATE and ML.PREDICT functions.

This tutorial uses the public bigquery-public-data.ml_datasets.penguin dataset.

Objectives

This tutorial guides you through completing the following tasks:

Creating a linear regression model to predict service call type by using the CREATE MODEL statement. Within the CREATE MODEL statement, use the ML.QUANTILE_BUCKETIZE and ML.FEATURE_CROSS functions to preprocess data.
Evaluating the model by using the ML.EVALUATE function.
Getting predictions from the model by using the ML.PREDICT function.

Costs

This tutorial uses billable components of Google Cloud, including:

BigQuery
BigQuery ML

For more information about BigQuery costs, see the BigQuery pricing page.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

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 the resourcemanager.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

Verify that billing is enabled for your Google Cloud project.

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 the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to
Enable the BigQuery API.
Roles required to enable APIs
To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.
Enable the API

Create a dataset

To create a BigQuery dataset, select one of the following options:

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the left pane, click Explorer:

[Highlighted button for the Explorer pane.]

If you don't see the left pane, click Expand left pane to open the pane.
In Explorer, expand your project, and then click Datasets.
On the Datasets page, click Create dataset.
In the Create dataset pane, do the following:
- For Dataset ID, enter bqml_tutorial.
- For Data location, select US.
Leave the remaining default settings as they are.
Click Create dataset.

bq

To create a new dataset, use the bq mk --dataset command.

Create a dataset named bqml_tutorial with the data location set to US:

bq mk --dataset \
  --location=US \
  --description "BigQuery ML tutorial dataset." \
  bqml_tutorial

Confirm that the dataset was created:
```
bq ls
```

API

Call the datasets.insert method with a defined dataset resource:

{
  "datasetReference": {
     "datasetId": "bqml_tutorial"
  }
}

Create the model

Create a linear regression model to predict penguin weight and train it on the penguins sample table.

The OPTIONS(model_type='linear_reg', input_label_cols=['body_mass_g']) clause indicates that you are creating a linear regression model. A linear regression model generates a continuous value from a linear combination of input features. The body_mass_g column is the input label column. For linear regression models, the label column must be real valued (that is, the column values must be real numbers).

This query's TRANSFORM clause uses the following columns from the SELECT statement:

body_mass_g: Used in training without any change.
culmen_depth_mm: Used in training without any change.
flipper_length_mm: Used in training without any change.
bucketized_culmen_length: Generated from culmen_length_mm by bucketizing culmen_length_mm based on quantiles using the ML.QUANTILE_BUCKETIZE() analytic function.
culmen_length_mm: The original culmen_length_mm value, cast to a STRING value and used in training.
species_sex: Generated from crossing species and sex using the ML.FEATURE_CROSS function.

You don't need to use all of the columns from the training table in theTRANSFORM clause.

The WHERE clause—WHERE body_mass_g IS NOT NULL AND RAND() < 0.2— excludes rows where the penguins weight is NULL, and uses the RAND function to draw a random sample of the data.

Follow these steps to create the model:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, paste in the following query and click Run:

CREATE OR REPLACE MODEL `bqml_tutorial.penguin_transform`
  TRANSFORM(
    body_mass_g,
    culmen_depth_mm,
    flipper_length_mm,
    ML.QUANTILE_BUCKETIZE(culmen_length_mm, 10) OVER () AS bucketized_culmen_length,
    CAST(culmen_length_mm AS string) AS culmen_length_mm,
    ML.FEATURE_CROSS(STRUCT(species, sex)) AS species_sex)
  OPTIONS (
    model_type = 'linear_reg',
    input_label_cols = ['body_mass_g'])
AS
SELECT
  *
FROM
  `bigquery-public-data.ml_datasets.penguins`
WHERE
  body_mass_g IS NOT NULL
  AND RAND() 

Web Proxy Viewer  |  New URL  |  Original Page