Tutorial: Exporting BQML Models to Online AI Platform Prediction

BigQuery ML provides a user-friendly interface to easily create ML models from any size data sets as well as make batch predictions at scale. To make online predictions, BQML provides an export function to export TensorFlow SavedModel. This tutorial will cover the steps in the following three parts:

Create model in BQML
Extract model from BQML and load model onto AI Platform
Run online prediction via Python REST client

PART 1: Create model in BQML

Set variables for this part of the tutorial



In [ ]:

    
PROJECT_ID=[] #enter your project name



In [ ]:

    
!gcloud config set project $PROJECT_ID

Create a dataset to store artifacts



In [ ]:

    
!bq show natality || bq mk natality



In [ ]:

    
!bq ls -a



In [ ]:

    
!bq show --format=pretty natality

Load Magic. This will allow access to BQ from this notebook.



In [ ]:

    
%load_ext google.cloud.bigquery

Review a sample of the data this tutorial will use.



In [ ]:

    
%%bigquery
    SELECT
      weight_pounds,
      is_male,
      gestation_weeks,
      mother_age
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL
    LIMIT 5

Split Data

The following query creates a view that adds a computed column. This will be used for splitting the data into three parts. Training will comprise of the largest part and will be used by BQML to train the model. Internally, BQML will split this into training and validation. Evaluation is the holdout set to test the model performance. Prediction will be used by AI Platform Prediction after the model is extracted from BQML and hosted in AI Platform Prediction.



In [ ]:

    
%%bigquery
    CREATE OR REPLACE VIEW
      `natality.input_view` AS
    SELECT
      weight_pounds,
      is_male,
      gestation_weeks,
      mother_age,
      CASE
        WHEN MOD(CAST(ROUND(weight_pounds*100) as int64), 10) < 8 THEN 'training'
        WHEN MOD(CAST(ROUND(weight_pounds*100) as int64), 10) = 8 THEN 'evaluation'
        WHEN MOD(CAST(ROUND(weight_pounds*100) as int64), 10) = 9 THEN 'prediction'
      END AS datasplit
    FROM
      `bigquery-public-data.samples.natality`
    WHERE
      weight_pounds IS NOT NULL



In [ ]:

    
%%bigquery
    SELECT *
    FROM
      `natality.input_view`
    LIMIT 100

Train a Linear Regression model in BQML



In [ ]:

    
%%bigquery
    CREATE OR REPLACE MODEL
      `natality.natality_model`
    OPTIONS
      (model_type='linear_reg',
        input_label_cols=['weight_pounds']) AS
            SELECT
              weight_pounds,
              is_male,
              gestation_weeks,
              mother_age
            FROM
              `natality.input_view`
            WHERE
              datasplit = 'training'

List models with the bq ls -m option.



In [ ]:

    
!bq ls -m --format=pretty natality

Get details about natality_model.



In [ ]:

    
!bq show -m --format=pretty natality.natality_model

Run ML.EVALUATE against the "evaluation" set



In [ ]:

    
%%bigquery
    SELECT
      *
    FROM
      ML.EVALUATE(MODEL `natality.natality_model`,
        ( SELECT
          weight_pounds,
          is_male,
          gestation_weeks,
          mother_age
          FROM
            `natality.input_view`
          WHERE
            datasplit = 'evaluation'))

Run batch prediction with ML.PREDICT against the "prediction" set

Note this prediction is done in BQML. Online prediction via AI Platform below.



In [ ]:

    
%%bigquery
    SELECT
      predicted_weight_pounds
    FROM
      ML.PREDICT(MODEL `natality.natality_model`,
        (
        SELECT
          is_male,
          gestation_weeks,
          mother_age
        FROM
          `natality.input_view`
        WHERE
          datasplit = 'prediction'
        LIMIT 100))

PART 2: Extract model from BQML and load model onto AI Platform

Set variables for this part of the tutorial

AI Platform Prediction supports a model/version struture where one model can have multiple versions. BQML model structure does not include versions. In this part of the tutorial, the BQML model will be extracted into a version directory in GCS. Later, AI Platform will pick this up as a version of a model.



In [ ]:

    
import os
import random

REGION=[] #e.g. 'us-central1'
MODEL_VERSION=[] #e.g. 'v1'

MODEL_BUCKET='gs://{}-{}'.format(PROJECT_ID,str(random.randrange(1000,10000)))
MODEL_PATH=os.path.join(MODEL_BUCKET,'export/natality_model',MODEL_VERSION)

Create GCS bucket to store extracted SavedModel



In [ ]:

    
!gsutil mb $MODEL_BUCKET



In [ ]:

    
import os
stream = os.popen("bq extract -m natality.natality_model {}".format(MODEL_PATH))



In [ ]:

    
stream.read()



In [ ]:

    
!bq ls -m natality

Extract model via bq extract



In [ ]:

    
!bq extract -m natality.natality_model $MODEL_PATH

Use saved_model_cli to get details of the extracted model

The saved_model_cli output shows the expected input tensors to the model. This will become important in structuring the prediction request packets. As seen below, the model will expect requests in a format of: {'is_male': [DT_BOOL], 'gestation_weeks': [DT_FLOAT], 'mother_age': [DT_FLOAT], 'mother_race': [DT_STRING]}



In [ ]:

    
!saved_model_cli show --dir $MODEL_PATH --tag_set serve --signature_def serving_default

Create Model in AI Platform Prediction



In [ ]:

    
!gcloud ai-platform models create natality_model --regions=$REGION

Create a Version in the Model in AI Platform Prediction



In [ ]:

    
!gcloud ai-platform versions create v1 --model=natality_model --framework=tensorflow --runtime-version=1.15 --origin=$MODEL_PATH

PART 3: Run online prediction via Python REST client

This section of the tutorial will first create a BQ Python client to query the "prediction" dataset into a dataframe. Then a slice of the dataframe will be made into an online request via REST API to AI Platform Prediction.



In [ ]:

    
from google.cloud import bigquery
client = bigquery.Client()

Create the dataframe to load the "prediction" dataset from BQ



In [ ]:

    
sql = """
    SELECT
      is_male,
      gestation_weeks,
      mother_age
    FROM
      `natality.input_view`
    WHERE
      datasplit = 'prediction'
    LIMIT 100
"""

df = client.query(sql).to_dataframe()

Take a small slice of the dataframe and conver to a list of dictionaries.



In [ ]:

    
request=df.to_dict(orient='records')[2:7]



In [ ]:

    
request

Install API client



In [ ]:

    
!pip install --upgrade google-api-python-client



In [ ]:

    
import googleapiclient.discovery



In [ ]:

    
service = googleapiclient.discovery.build('ml','v1')



In [ ]:

    
name = 'projects/{}/models/natality_model/versions/{}'.format(PROJECT_ID, MODEL_VERSION)

Execute online prediction from AI Platform



In [ ]:

    
response=service.projects().predict(
    name=name,
    body={'instances':request}
).execute()



In [ ]:

    
response

Clean Up



In [ ]:

    
!gcloud ai-platform versions delete v1 --model=natality_model --quiet



In [ ]:

    
!gcloud ai-platform models delete natality_model --quiet



In [ ]:

    
!bq rm -r -f natality



In [ ]:

    
!gsutil rm -r $MODEL_BUCKET



In [ ]: