Regression

In the example in the previous section, we wanted to predict the number of tickets that would be sold for a particular showing of a movie. In that case, the label is a number, and so the type of machine learning problem it represents is called regression.

Classification

If the label is a categorical variable, the type of machine learning problem is called classification. The output of a classification model is the probability that a row belongs to a label value. For example, if you were to train a machine learning model to predict whether a show will sell out, you would be using a classification model, and the output of the model would be the probability that a show sells out.

Many classification problems have two classes: the show sells out or it doesn’t, a customer buys the item or they don’t, the flight is late or it isn’t. These are called binary classification problems. In such cases, the label column should be True or False, or it should be 1 or 0. The prediction from the model will be the probability that the label is True. We typically threshold the probability at 0.5 to determine the most likely class.

A classification problem can have multiple classes. For example, revisiting our bike rental scenario, you might want to predict the station at which a bicycle will be returned, and because there are hundreds of possible values for this categorical label, this is a multiclass classification problem. The output of such a machine learning model will be a set of probabilities, one for each station in the network, and the sum of these probabilities will be 1.0. In a multiclass problem, we typically care about the top three or top five predictions, not about the actual value of the probability.

Recommender

The special case of multiclass classification for which the task is to recommend the “next” product based on ratings or past purchases is called a recommender system. Although a recommendation problem could be solved in the standard way that all multiclass classification problems are, special machine learning model types have been built for these problems, and it is preferable to use these more specific model types. Recommender systems are also the preferable way to address customer targeting problems—to find customers who will like a product or promotional offer.

Clustering

If we don’t have a label at all, we cannot do supervised learning. We could find natural groupings within the data; this type of ML problem is called clustering.  We might employ clustering of customer features to perform customer segmentation, for example. Otherwise, we can use the Cloud Data Labeling Service to annotate our training dataset with human labelers as a precursor to carrying out supervised learning.

Unstructured data

In the discussion so far, we have assumed that our data consists of structured or semi-structured data. If some of the input features are unstructured (e.g., images or natural language text), consider using a preexisting model such as Cloud Vision API or Cloud Natural Language to process the unstructured data in question, and use the output of these APIs as numeric or categorical inputs to the machine learning model. For example, you could use the Natural Language API to identify key entities in customer emails or the sentiment of customer reviews, and use the entities as categorical variables and the sentiment as a numeric feature.

You also might be able to turn unstructured data into structured data through string functions or machine learning APIs. Splitting a text field into individual words and treating the presence/absence of individual words as features is a common technique, often called bag of words. In the movie title example, if you had a movie called The Spy Who Loved Me, you might have two features, has_spy and has_love, as True, and all other features would be false (you’d probably drop “the,” “Who,” and “Me” as being too common to be helpful in prediction). Or you might use the number of words in the title (maybe wordy titles are more likely to be indie films and more likely to appeal to different audiences).

If the label itself is unstructured (e.g., you want the model to craft the ideal response to customer questions based on a dataset of historical responses), this is a natural language generation problem—it’s outside the scope of what BigQuery can handle.

Summary of model types

Table 9-1 summarizes the machine learning problem types. We discuss the BigQuery model types in the following sections.

Impact of station

To check whether the duration of a rental varies by station, you can visualize the result of the following query in Data Studio using the start_station_name as the dimension and duration as the metric:³

SELECT 
  start_station_name
  , AVG(duration) AS duration
 FROM `bigquery-public-data`.london_bicycles.cycle_hire
 GROUP BY start_station_name

This yields the result shown in Figure 9-2.

Figure 9-2. It appears that there are a few stations that are associated with long-duration rentals

From Figure 9-2, it is clear that a handful of stations are associated with long-duration rentals (over 3,000 seconds), but that the majority of stations have durations that lie in a relatively narrow range. Had all the stations in London been associated with durations within a narrow range, the station at which the rental commenced would not have been a good feature. But in this problem, as the graph in Figure 9-2 demonstrates, the start_station_name does matter.

Note that you cannot use end_station_name as a feature because at the time the bicycle is being rented, you won’t know to which station the bicycle is going to be returned. Because we are creating a machine learning model to predict events in the future, you need to be mindful of not using any columns that will not be known at the time the prediction is made. This time/causality criterion imposes constraints on what features you can use.

Day of week

For the next candidate features, the process is similar. You can check whether dayofweek (or, similarly, hourofday) matters:

SELECT 
  EXTRACT(dayofweek FROM start_date) AS dayofweek
  , AVG(duration) AS duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire
GROUP BY dayofweek

Figure 9-3 shows the visualized result.

Figure 9-3. Longer duration rentals tend to happen on weekends and in the morning and early afternoon

From Figure 9-3, it is clear that the duration varies depending both on the day of the week and on the hour of the day. It appears that durations are longer on weekends (days 1 and 7) than on weekdays. Similarly, durations are longer early in the morning and in the midafternoon. Hence, both dayofweek and hourofday are good features.

Number of bicycles

Another potential feature is the number of bikes in the station. Perhaps, we hypothesize, people keep bicycles longer if there are fewer bicycles on rent at the station from which they rented. You can verify whether this is the case by using the following:

SELECT 
  bikes_count
  , AVG(duration) AS duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire
JOIN `bigquery-public-data`.london_bicycles.cycle_stations
ON cycle_hire.start_station_name = cycle_stations.name
GROUP BY bikes_count

Figure 9-4 presents the result via Data Studio.

Figure 9-4. Relationship between average duration of bicycle rides and the number of bicycles at the station the bicycle was rented from

In Figure 9-4, notice that the relationship is noisy with no visible trend (compared against hour-of-day, for example). This indicates that the number of bicycles is not a good feature. You can confirm this quantitatively by computing the Pearson correlation coefficient:

SELECT 
  CORR(bikes_count, duration) AS corr
FROM `bigquery-public-data`.london_bicycles.cycle_hire
JOIN `bigquery-public-data`.london_bicycles.cycle_stations
ON cycle_hire.start_station_name = cycle_stations.name

The result, –0.0039, indicates that the bikes_count and duration are essentially independent, because the Pearson coefficient will have an absolute value of 1.0 if they are linearly dependent, and 0.0 if they are linearly independent.

The Pearson correlation coefficient isn’t a perfect test for whether a feature is useful because it looks only at linear dependence. Sometimes, a feature might have a nonlinear dependence with the label. Still, the Pearson coefficient is a good starting point. Machine learning scientists often use more sophisticated statistical tests like mutual information, which computes the randomness of the feature with respect to the label.

Evaluating the model

This query took 2.5 minutes and was trained in just one iteration,⁶ something we can learn by looking at the “Training” tab in the BigQuery section of the GCP Cloud Console. The mean absolute error (available from the evaluation tab) is 1,026 seconds, or about 17 minutes.⁷ This means that you should expect to be able to predict the duration of bicycle rentals with an average error of about 17 minutes.

In addition to looking at the evaluation tab, you can obtain the evaluation results by running the following SQL query:

SELECT * FROM ML.EVALUATE(MODEL ch09eu.bicycle_model)

Note that the query OPTIONS also identifies the model type. Here, we have picked the simplest regression model that BigQuery supports. We strongly encourage you to pick the simplest model and to spend a lot of time considering and bringing in alternate data choices, because the payoff of a new/improved input feature greatly outweighs the payoff of a better model. Only when you have reached the limits of your data experimentation should you try more complex models.

Combining days of the week

There are other ways that you could have chosen to represent the features that you have. For example, recall that when we explored the relationship between dayofweek and the duration of rentals, we found that durations were longer on weekends than on weekdays. Therefore, instead of treating the raw value of dayofweek as a feature, you can employ this insight by fusing several dayofweek values into the weekday category:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_weekday
OPTIONS(input_label_cols=['duration'], model_type='linear_reg')
AS
 
SELECT 
  duration
  , start_station_name
  , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 
        'weekday', 'weekend') as dayofweek
  , CAST(EXTRACT(hour FROM start_date) AS STRING) AS hourofday
FROM `bigquery-public-data`.london_bicycles.cycle_hire

This model results in a mean absolute error of 967 seconds, which is less than the 1,026 seconds for the original model. So let’s go with the weekend-weekday model instead.

Bucketizing the hour of day

Again, based on the relationship between hourofday and the duration, you can experiment with bucketizing the variable into four bins—(–inf,5), [5,10),⁸ [10,17), and [17,inf):

CREATE OR REPLACE MODEL ch09eu.bicycle_model_bucketized
OPTIONS(input_label_cols=['duration'], model_type='linear_reg')
AS
 
SELECT 
  duration
  , start_station_name
  , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 'weekday', 'weekend')
   as dayofweek
  , ML.BUCKETIZE(EXTRACT(hour FROM start_date), [5, 10, 17]) AS hourofday
FROM `bigquery-public-data`.london_bicycles.cycle_hire

ML.BUCKETIZE is an example of a preprocessing function supported by BigQuery—we are passing in the number to bucketize and the bounds of the bins with –infinity and +infinity being assumed to be on either extremity. This model results in a mean absolute error of 901 seconds, which is less than the 967 seconds for the weekday-weekend model. So let’s choose the bucketized model.  

The need for TRANSFORM

In the previous prediction query, we had to pass in 'weekday' rather than '3' for dayofweek because the model was trained with dayofweek being either weekday or weekend. It is incorrect to pass in the raw data value of '17' for hourofday—we should be passing in the name of the bin that represents 5 p.m. The prediction code will need to carry out the same transformations on the raw data that the training code did in order to get these values correct.

Wouldn’t it be nice if BigQuery could remember the sets of transformations you did at the time of training and automatically apply them at the time of prediction? It can—that’s precisely what the TRANSFORM clause does!

You can even move the extraction of hour-of-day and day-of-week into the TRANSFORM clause so that the client code needs to give us only the timestamp at which the bicycle is being rented:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_bucketized
TRANSFORM(* EXCEPT(start_date)
         , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6,
'weekday', 'weekend') as dayofweek
         , ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [5, 10, 17]) AS hourofday
)
OPTIONS(input_label_cols=['duration'], model_type='linear_reg')
AS

SELECT 
  duration
  , start_station_name
  , start_date
FROM `bigquery-public-data`.london_bicycles.cycle_hire

Use the TRANSFORM clause and formulate the machine learning problem in such a way that anyone requiring prediction needs to provide just the raw data.⁹

If a TRANSFORM clause is specified, the model is trained on the output of the TRANSFORM clause. So here, the TRANSFORM clause passes on all of the features and labels from the original SELECT query, except for the start_date, and then adds a couple of features (dayofweek and hourofday) extracted from the start_date.

The resulting model requires just the start_station_name and start_date to predict the duration. The transformations are saved and carried out on the provided raw data to create input features for the model.

With the TRANSFORM clause in place, the prediction query becomes:

SELECT * FROM ML.PREDICT(MODEL ch09eu.bicycle_model_bucketized, 
  (SELECT 'Park Lane , Hyde Park' AS start_station_name
           , CURRENT_TIMESTAMP() AS start_date) 
)

The result (yours will vary because presumably the timeofday and dayofweek are different) is something like the following:

Generating batch predictions

You could also create a table of predictions for every hour at every station, starting at 3 a.m. the next day, using array generation:

DECLARE tomorrow_3am TIMESTAMP;
SET tomorrow_3am = TIMESTAMP_ADD(
  TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY)),
  INTERVAL 3 HOUR);
 
WITH generated AS (
  SELECT
     name AS start_station_name
     , GENERATE_TIMESTAMP_ARRAY( 
         tomorrow_3am,
         TIMESTAMP_ADD(tomorrow_3am, INTERVAL 24 HOUR),
         INTERVAL 1 HOUR) AS dates
  FROM 
     `bigquery-public-data`.london_bicycles.cycle_stations
),
 
features AS (
  SELECT 
     start_station_name
     , start_date
  FROM 
    generated
    , UNNEST(dates) AS start_date
)
 
SELECT * FROM ML.PREDICT(MODEL ch09eu.bicycle_model_bucketized,
  (SELECT * FROM features)
)

This returns nearly 20,000 predictions, some of which include the following:

The entire process of machine learning, from creating the training dataset to training and prediction, has thus been carried out without the need to move the data out of BigQuery.

Deep Neural Networks

A Deep Neural Network (DNN) can be thought of as an extension of linear models in which each node in the first layer consists of a weighted sum of the input features transformed through a (typically nonlinear) function. The second layer consists of nodes, each of which is a weighted sum of the outputs of the first layer transformed through a nonlinear function, and so on, as demonstrated in Figure 9-5.

Figure 9-5. A Deep Neural Network consists of layers of “nodes.” This example shows two layers between the inputs and outputs and each layer with three nodes, but we can have an arbitrary number of layers and an arbitrary number of nodes in each layer.

To train a DNN model with 64 nodes in the first layer and 32 nodes in the second layer, you would do the following:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_dnn
TRANSFORM(* EXCEPT(start_date)
           , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 'weekday',
'weekend') as dayofweek
           , ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [5, 10, 17]) AS hourofday
)
OPTIONS(input_label_cols=['duration'], 
        model_type='dnn_regressor',
        hidden_units=[64, 32])
AS
 
SELECT 
  duration
  , start_station_name
  , start_date
FROM `bigquery-public-data`.london_bicycles.cycle_hire

This model took about 20 minutes to train. It ended with a mean absolute error of 1,016 seconds. This is, of course, worse than the 901 seconds that we achieved with the linear model. Sadly, this is par for the course—DNNs are notoriously finicky to train.

One way to handle this finickiness is to perform hyperparameter tuning to search for optimal network parameters—this is supported by a full-fledged machine learning framework like Cloud AI Platform (CAIP).¹⁰ You might be better off doing this training there, or using AutoML (we explore both of these options later in this chapter), but for now let’s try using a smaller network:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_dnn
 TRANSFORM(* EXCEPT(start_date)
           , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 'weekday',
'weekend') as dayofweek
           , ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [5, 10, 17]) AS hourofday
)
OPTIONS(input_label_cols=['duration'], 
        model_type='dnn_regressor',
         hidden_units=[10, 5])
AS
 
SELECT 
  duration
  , start_station_name
  , start_date
FROM `bigquery-public-data`.london_bicycles.cycle_hire

This yields better performance (981 seconds) but is still not as good as the linear model. More hyperparameter tuning is needed to get a DNN model that does better than the linear model we started out with. Also, in general a DNN provides superior performance only if there are many continuous features.

Gradient-boosted trees

Decision trees are a popular technique in machine learning because of their ready interpretability (they are essentially just combinations of if-then rules). However, decision trees tend to have poor accuracy because the range of functions they can approximate is limited and can be prone to overfitting. One way of improving the performance of decision trees (at the expense of explainability¹¹) is to train an ensemble of decision trees, each of which is a poor predictor but when averaged together yield good performance. Boosting is a technique that is used to select trees in the ensemble, and XGBoost¹² is a scalable, distributed way to build boosted decision trees on extremely large and sparse datasets. XGBoost used to be considered the state-of-the-art machine learning technique until the advent of deep learning networks circa 2015. It continues to be popular on structured data problems.

You can train an XGBoost machine learning model in BigQuery by selecting the boosted_tree_regressor model type:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_xgboost
TRANSFORM(* EXCEPT(start_date)
          , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 
     'weekday', 'weekend') as dayofweek
          , ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [5, 10, 17]) AS hourofday
)
OPTIONS(input_label_cols=['duration'], 
        model_type='boosted_tree_regressor',
        max_tree_depth=4)
AS
 
SELECT 
  duration
  , start_station_name
  , start_date
FROM `bigquery-public-data`.london_bicycles.cycle_hire

The resulting model on this problem has poorer performance (1,363 seconds) than the linear model. The importance of the input features can be obtained by using this command:

SELECT * FROM ML.FEATURE_INFO(MODEL ch09eu.bicycle_model_xgboost)

Human insights and auxiliary data

Besides trying different model architectures and tuning the parameters of these models, we might consider adding new input features that incorporate human insights or provide auxiliary data to the machine learning model.

For example, in the previous model, we used ML.BUCKETIZE to split a continuous variable (the hour extracted from the timestamp) into four bins. Another extremely useful function is ML.FEATURE_CROSS, which can combine separate categorical features into an AND condition (this sort of relationship between features can be difficult for a machine learning model to learn). In our problem, intuition dictates that the combination of weekday and morning is a good predictor of bicycle rental duration, much more so than either weekday by itself or morning by itself. If so, it might be worthwhile to create a feature cross of the two features instead of treating the day and time separately:

ML.FEATURE_CROSS(STRUCT(
   IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 
       'weekday', 'weekend') as dayofweek, 
   ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), 
       [5, 10, 17]) AS hr
)) AS dayhr

In our models so far, we used start_station_name as an input to the model. This treats the stations as independent. In Chapter 8, we discussed the benefits of ST_GeoHash as a way to capture spatial proximity. Let’s, therefore, bring in the auxiliary information about the stations’ locations and use that as an additional input to the model.

Combining these two ideas, we now have the model training query:

CREATE OR REPLACE MODEL ch09eu.bicycle_model_fc_geo
 TRANSFORM(duration
       , ML.FEATURE_CROSS(STRUCT(
           IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6, 
              'weekday', 'weekend') as dayofweek, 
           ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), 
              [5, 10, 17]) AS hr
         )) AS dayhr
       , ST_GeoHash(ST_GeogPoint(latitude, longitude), 4) AS start_station_loc4
       , ST_GeoHash(ST_GeogPoint(latitude, longitude), 6) AS start_station_loc6
       , ST_GeoHash(ST_GeogPoint(latitude, longitude), 8) AS start_station_loc8
)
OPTIONS(input_label_cols=['duration'], model_type='linear_reg')
AS
 
SELECT 
  duration
  , latitude
  , longitude
  , start_date
FROM `bigquery-public-data`.london_bicycles.cycle_hire
JOIN `bigquery-public-data`.london_bicycles.cycle_stations
ON cycle_hire.start_station_id = cycle_stations.id

This model results in a mean absolute error of 898 seconds, an improvement over the 901 seconds we saw earlier. However, the improvement is relatively minor. Because of these diminishing returns, it might be time to move on. 

Filtering out previously rated movies

Of course, this includes movies the user has already seen and rated in the past. Let’s remove them:

SELECT * FROM
ML.PREDICT(MODEL ch09eu.movie_recommender_16, (
  WITH seen AS (
     SELECT ARRAY_AGG(movieId) AS movies 
     FROM ch09eu.movielens_ratings
     WHERE userId = 903
  )
  SELECT 
     movieId, title, 903 AS userId
  FROM ch09eu.movielens_movies, UNNEST(genres) g, seen
  WHERE g = 'Comedy' AND movieId NOT IN UNNEST(seen.movies)
))
ORDER BY predicted_rating DESC
LIMIT 5

For this user, this happens to yield the same set of movies—the top predicted ratings didn’t include any of the movies the user has already seen.

Customer targeting

In the previous section, we looked at how to identify the top-rated movies for a specific user. Sometimes we have a product and need to find the customers who are likely to appreciate it. Suppose, for example, you want to get more reviews for movieId=96481, which has only one rating, and you want to send coupons to the 100 users who are likely to rate it the highest. We can identify those users by using the following:

SELECT * FROM
ML.PREDICT(MODEL ch09eu.movie_recommender_16, (
  WITH allUsers AS (
      SELECT DISTINCT userId
      FROM ch09eu.movielens_ratings
  )
  SELECT 
     96481 AS movieId, 
     (SELECT title FROM ch09eu.movielens_movies WHERE movieId=96481) title,
     userId
  FROM
     allUsers
))
ORDER BY predicted_rating DESC
LIMIT 100

The result gives us 100 users to target, the top 5 of whom we list here:

Batch predictions for all users and movies

What if you want to carry out predictions for every user and movie combination? Instead of having to pull distinct users and movies as in the previous query, a convenient function is provided to carry out batch predictions for all movieId and userId encountered during training:

SELECT * 
FROM ML.RECOMMEND(MODEL ch09eu.movie_recommender_16)

As seen in an earlier section, it is possible to filter out movies that the user has already seen and rated in the past. The reason previously viewed movies aren’t filtered out by default is that there are situations (think of restaurant recommendations, for example) for which it is perfectly expected that we would need to recommend restaurants the user has liked in the past.

Obtaining user and product factors

You can get the user factors or product factors from ML.WEIGHTS. For example, here’s how to get the product factors for movieId=96481 and user factors for userId=54192:

SELECT 
   processed_input
   , feature
   , TO_JSON_STRING(factor_weights)
   , intercept
FROM ML.WEIGHTS(MODEL ch09eu.movie_recommender_16)
WHERE
(processed_input = 'movieId' AND feature = '96481')
OR
(processed_input = 'userId' AND feature = '54192')

The result is as follows:

Multiplying these weights and adding the intercept is how you get the predicted rating for this combination of movieId and userId in the matrix factorization approach.

These weights also serve as a low-dimensional representation of the movie and user behavior. You can create a regression model to predict the rating given the user factors, product factors, and any other information that we know about our users and products.

Creating input features

The MovieLens dataset does not have any user information and has very little information about the movies themselves. To illustrate the concept, therefore, let’s create some synthetic information about users:

CREATE OR REPLACE TABLE ch09eu.movielens_users AS
SELECT
  userId
  , RAND() * COUNT(rating) AS loyalty
  , CONCAT(SUBSTR(CAST(userId AS STRING), 0, 2)) AS postcode
FROM
  ch09eu.movielens_ratings
GROUP BY userId

Input features about users can be obtained by joining the user table with the machine learning weights and selecting all of the user information and the user factors from the weights array:

WITH userFeatures AS (
  SELECT 
      u.*,
      (SELECT ARRAY_AGG(weight) FROM UNNEST(factor_weights)) AS user_factors
  FROM
      ch09eu.movielens_users u
  JOIN
      ML.WEIGHTS(MODEL ch09eu.movie_recommender_16) w
   ON
      processed_input = 'userId' AND feature = CAST(u.userId AS STRING)
)
 
SELECT * FROM userFeatures
LIMIT 5

This yields user features like these (you will need to remove the userId itself before feeding it into the regression model):

Similarly, you can get product features for the movies data, except that you need to decide how to handle the genre because a movie could have more than one. If you decide to create a separate training row for each genre, you can construct the product features using the following:

WITH productFeatures AS (
  SELECT 
      p.* EXCEPT(genres)
      , g
      , (SELECT ARRAY_AGG(weight) FROM UNNEST(factor_weights)) AS product_factors
  FROM
      ch09eu.movielens_movies p, UNNEST(genres) g
  JOIN
      ML.WEIGHTS(MODEL ch09eu.movie_recommender_16) w
  ON
      processed_input = 'movieId' AND feature = CAST(p.movieId AS STRING)
)
 
SELECT * FROM productFeatures
LIMIT 5

This yields rows of the following form:

By combining these two WITH clauses and pulling in the rating corresponding to the movieId-userId combination (if it exists in the ratings table), you can create the training dataset:²²

CREATE OR REPLACE TABLE ch09eu.movielens_hybrid_dataset AS
 
WITH userFeatures AS (
  SELECT 
      u.*,
      (SELECT ARRAY_AGG(weight) FROM UNNEST(factor_weights)) AS user_factors
  FROM
      ch09eu.movielens_users u
  JOIN
      ML.WEIGHTS(MODEL ch09eu.movie_recommender_16) w
  ON
      processed_input = 'userId' AND feature = CAST(u.userId AS STRING)
),
 
productFeatures AS (
  SELECT 
      p.* EXCEPT(genres)
      , g
      , (SELECT ARRAY_AGG(weight) FROM UNNEST(factor_weights)) AS product_factors
  FROM
      ch09eu.movielens_movies p, UNNEST(genres) g
  JOIN
      ML.WEIGHTS(MODEL ch09eu.movie_recommender_16) w
  ON
      processed_input = 'movieId' AND feature = CAST(p.movieId AS STRING)
)
 
SELECT p.* EXCEPT(movieId), u.* EXCEPT(userId), rating 
FROM productFeatures p, userFeatures u
JOIN
   ch09eu.movielens_ratings r
ON
   r.movieId = p.movieId AND r.userId = u.userId

One of the rows of this table looks like this:

Essentially, you have a couple of attributes about the movie, the product factors array corresponding to the movie, a couple of attributes about the user, and the user factors array corresponding to the user. These form the inputs to the “hybrid” recommendations model that builds off the matrix factorization model and adds in metadata about users and movies.

Training hybrid recommendation model

As of this writing, BigQuery ML cannot handle arrays as inputs to a regression model. Let’s therefore define a function to convert arrays to a struct for which the array elements are its fields:

CREATE OR REPLACE FUNCTION ch09eu.arr_to_input_3(a ARRAY<FLOAT64>)
RETURNS STRUCT<a1 FLOAT64, a2 FLOAT64, a3 FLOAT64> AS (
STRUCT(
    a[OFFSET(0)]
    , a[OFFSET(1)]
    , a[OFFSET(2)]
));

Now you can do the following:

SELECT 
  ch09eu.arr_to_input_3(a).*
FROM
(SELECT [34.23, 43.21, 63.21] AS a)

And here’s your result:

You can create a similar function named ch09eu.arr_to_input_16_users to convert the user factor array into named columns, and a similar function for the product factor arrays.²³ Then you can tie together metadata about users and products with the user factors and product factors obtained from the matrix factorization approach to create a regression model to predict the rating:

CREATE OR REPLACE MODEL ch09eu.movielens_recommender_hybrid 
OPTIONS(model_type='linear_reg', input_label_cols=['rating'])
AS
 
SELECT
  * EXCEPT(user_factors, product_factors)
  , ch09eu.arr_to_input_16_users(user_factors).*
  , ch09eu.arr_to_input_16_products(product_factors).*
FROM
  ch09eu.movielens_hybrid_dataset

There is no point in looking at the evaluation metrics of this model, because the user information we used to create the training dataset was fake (note the RAND() in the creation of the loyalty column)—we did this exercise to demonstrate how it could be done. And of course, we could train a dnn_regressor model and optimize the hyperparameters if we want a more sophisticated model. But if we are going to go that far, it might be better to consider using AutoML tables, which we cover in the next section.

Hyperparameter tuning using scripting

Take the k-means clustering model. The evaluation tab in the BigQuery web UI (as well as SELECT * from ML.EVALUATE) shows the Davies-Bouldin index, which is useful for determining the optimal number of clusters supported by the data (the lower the number, the better the clustering).

For example, here’s a script to try varying the number of clusters:

DECLARE NUM_CLUSTERS INT64 DEFAULT 3;
DECLARE MIN_ERROR FLOAT64 DEFAULT 1000.0;
DECLARE BEST_NUM_CLUSTERS INT64 DEFAULT -1;
DECLARE MODEL_NAME STRING;
 
WHILE NUM_CLUSTERS < 8 DO
 
  SET MODEL_NAME = CONCAT('ch09eu.london_station_clusters_', 
                          CAST(NUM_CLUSTERS AS STRING));
 
  CREATE OR REPLACE MODEL MODEL_NAME
  OPTIONS(model_type='kmeans', 
           num_clusters=NUM_CLUSTERS, 
           standardize_features = true) AS
  SELECT * except(station_name)
  from ch09eu.stationstats;
 
  SET error = (SELECT davies_bouldin_index FROM ML.EVALUATE(MODEL MODEL_NAME));
  IF error < MIN_ERROR THEN
      SET MIN_ERROR = error;
      SET BEST_NUM_CLUSTERS = NUM_CLUSTERS;
  END IF;
  
 
  SET NUM_CLUSTERS = NUM_CLUSTERS + 1;
 
END WHILE

Hyperparameter tuning in Python

Alternatively, you could do this using Python and its multithreading capability to limit the number of concurrent queries:²⁵

def train_and_evaluate(num_clusters: Range, max_concurrent=3):
    # grid search means to try all possible values in range
    params = []
    for k in num_clusters.values():
        params.append(Params(k))
     
    # run all the jobs
    print('Grid search of {} possible parameters'.format(len(params)))
    pool = ThreadPool(max_concurrent)
    results = pool.map(lambda p: p.run(), params)
    
    # sort in ascending order
    return sorted(results, key=lambda p: p._error)

In this code, the run() method of the Params class invokes the appropriate training and evaluation queries:

class Params:
    def __init__(self, num_clusters):
        self._num_clusters = num_clusters
        self._model_name = (
            'ch09eu.london_station_clusters_{}'.format(num_clusters))
        self._train_query = """
          CREATE OR REPLACE MODEL {}
          OPTIONS(model_type='kmeans', 
                  num_clusters={}, 
                  standardize_features = true) AS
          SELECT * except(station_name)
          from ch09eu.stationstats
        """.format(self._model_name, self._num_clusters)
        self._eval_query = """
          SELECT davies_bouldin_index AS error
          FROM ML.EVALUATE(MODEL {});
        """.format(self._model_name)
       self._error = None
        
    def run(self):
        bq = bigquery.Client(project=PROJECT)
        job = bq.query(self._train_query, location='EU')
        job.result() # wait for job to finish
        evaldf = bq.query(self._eval_query, location='EU').to_dataframe()
        self._error = evaldf['error'][0]
        return self

When searching in the range [3,9], you find that the number of clusters at which the error is minimized is 7:

ch09eu.london_station_clusters_7           1.551265     7
ch09eu.london_station_clusters_9           1.571020     9
ch09eu.london_station_clusters_6           1.571398     6
ch09eu.london_station_clusters_4           1.596398     4
ch09eu.london_station_clusters_8           1.621974     8
ch09eu.london_station_clusters_5           1.660766     5
ch09eu.london_station_clusters_3           1.681441     3

Hyperparameter tuning using AI Platform

In both of the hyperparameter tuning methods that we’ve considered so far, we tried out every possible value of a parameter that fell within a range. As the number of possible parameters grows, a grid search becomes increasingly wasteful. It is better to use a more efficient search algorithm, and that’s where Cloud AI Platform’s hyperparameter tuning can be helpful. You can use the hyperparameter tuning service for any model (not just TensorFlow). Let’s apply it to tuning the feature engineering and number of nodes of a DNN model.²⁶

First, create a configuration file that specifies the ranges for each of the parameters, the number of concurrent queries, and the total number of trials:

trainingInput:
  scaleTier: CUSTOM
  masterType: standard   # See: https://cloud.google.com/ml-
engine/docs/tensorflow/machine-types
  hyperparameters:
     goal: MINIMIZE
     maxTrials: 50
     maxParallelTrials: 2
     hyperparameterMetricTag: mean_absolute_error
     params:
     - parameterName: afternoon_start
       type: INTEGER
       minValue: 9
       maxValue: 12
       scaleType: UNIT_LINEAR_SCALE
     - parameterName: afternoon_end
       type: INTEGER
       minValue: 15
       maxValue: 19
       scaleType: UNIT_LINEAR_SCALE
     - parameterName: num_nodes_0
       type: INTEGER
       minValue: 10
       maxValue: 100
       scaleType: UNIT_LOG_SCALE
     - parameterName: num_nodes_1
       type: INTEGER
       minValue: 3
       maxValue: 10
       scaleType: UNIT_LINEAR_SCALE

Note that we have specified minimum and maximum values for each of the parameters and the metric (mean absolute error) to be minimized. We are asking for optimization to happen using just 50 trials, whereas a grid search would have required trying out 4×4×90×7, or more than 10,000 options. So using the AI Platform hyperparameter tuning service results in a 200-fold savings!

Next, you create a Python program that invokes BigQuery to train and evaluate the model given a single set of these parameters:

def train_and_evaluate(args):
    model_name = "ch09eu.bicycle_model_dnn_{}_{}_{}_{}".format(
        args.afternoon_start, args.afternoon_end, args.num_nodes_0,
args.num_nodes_1
    )
    train_query = """
         CREATE OR REPLACE MODEL {}
         TRANSFORM(* EXCEPT(start_date)
                   , IF(EXTRACT(dayofweek FROM start_date) BETWEEN 2 and 6,
'weekday', 'weekend') as dayofweek
                   , ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [5, {}, {}]) AS
hourofday
         )
         OPTIONS(input_label_cols=['duration'], 
                 model_type='dnn_regressor',
                 hidden_units=[{}, {}])
         AS
 
         SELECT 
           duration
           , start_station_name
           , start_date
         FROM `bigquery-public-data`.london_bicycles.cycle_hire
     """.format(model_name, 
                args.afternoon_start, 
                args.afternoon_end,
                args.num_nodes_0,
                args.num_nodes_1)
     logging.info(train_query)
     bq = bigquery.Client(project=args.project, 
                          location=args.location, 
                          credentials=get_credentials())
     job = bq.query(train_query)
     job.result() # wait for job to finish
     
     eval_query = """
         SELECT mean_absolute_error 
         FROM ML.EVALUATE(MODEL {})
     """.format(model_name)
     logging.info(eval_info)
     evaldf = bq.query(eval_query).to_dataframe()
     return evaldf['mean_absolute_error'][0]   

Note that this code uses a specific value for each of the tunable parameters and returns the mean absolute error, which is the metric being minimized.

This error value is then written out:

hpt.report_hyperparameter_tuning_metric(
       hyperparameter_metric_tag='mean_absolute_error',
       metric_value=error,
       global_step=1)

The training program is submitted to the AI Platform Training service:

gcloud ai-platform jobs submit training $JOBNAME 
  --runtime-version=1.13 
  --python-version=3.5 
  --region=$REGION 
  --module-name=trainer.train_and_eval 
  --package-path=$(pwd)/trainer 
  --job-dir=gs://$BUCKET/hparam/ 
  --config=hyperparam.yaml 
  —
  --project=$PROJECT --location=EU

The resulting output, shown in the AI Platform console, contains the best parameters.

TensorFlow’s BigQueryReader

A TensorFlow input pipeline can read from a BigQuery table into keyed TensorFlow Examples using BigQueryReader. First, create a features dictionary of the columns of interest:

features = dict(
  start_station_name=tf.FixedLenFeature([1], tf.string),
  duration=tf.FixedLenFeature([1], tf.int32))

Then create a reader specifying the timestamp at which the data is to be read (because the BigQuery table could be receiving streamed data while we are reading it) and the number of threads (partitions) in which the table is to be read:

reader = tf.contrib.cloud.BigQueryReader(project_id=PROJECT,
            dataset_id=DATASET,
            table_id=TABLE,
            timestamp_millis=TIME,
            num_partitions=NUM_PARTITIONS,
            features=features)

Finally, populate a queue with the BigQuery Table partitions, and use it to read the TensorFlow examples:

queue = tf.train.string_input_producer(reader.partitions())
row_id, examples_serialized = reader.read(queue)
examples = tf.parse_example(examples_serialized, features=features)

Although this works, there are several problems with this approach. In machine learning training, you will need to read batch_size records at once, shuffle the read order across workers, prefetch records, and so on. Hence, we recommend that you do not follow this approach.

Using pandas

If the BigQuery table is small enough, read it directly into an in-memory pandas DataFrame:

query = """
SELECT 
  start_station_name 
  , duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire 
GROUP BY start_station_name
"""
df = bq.query(query, location='EU').to_dataframe()

Use the tf.data API to read from pandas:

tf.estimator.inputs.pandas_input_fn(
    df,
    batch_size=128,
    num_epochs=10,
    shuffle=True,
    num_threads=8,
    target_column='duration'
)

Apache Beam/Cloud Dataflow

If the table is too large to fit into memory, export the BigQuery data into TensorFlow records on Google Cloud Storage using Cloud Dataflow (see Chapter 5 for more details):

_ = (
           examples
           | 'get_tfrecords' >> beam.Map(lambda x: x['tfrecord'])
           | 'writetfr' >> beam.io.tfrecordio.WriteToTFRecord(
               os.path.join(options['outdir'], 'tfrecord', step)))

Each of the previous examples is created by pulling the necessary records from BigQuery:

 tfexample = tf.train.Example(
         features=tf.train.Features(
             feature={
                 'start_station_name': _bytes_feature(row['start_station_name']),
                 'duration': _int64_feature(row['duration']),
           }))

Along the way, if necessary, you can transform the records using tf.transform. Then, in TensorFlow, you can use the high-throughput methods provided by tf.data.tfrecorddataset to read in the data.

Exporting to TensorFlow

The TensorFlow ecosystem for serving is very powerful—it is possible to carry out predictions of TensorFlow models in a web browser using JavaScript and tensorflow.js, on an embedded device or mobile application using TensorFlow Lite, in Kubernetes clusters using Kubeflow, as a REST API using AI Platform Predictions, and more. Therefore, you might find it advantageous to export your BigQuery ML model as a TensorFlow SavedModel. After the BigQuery ML model has been exported, you can use it in any of the environments that can serve TensorFlow models.

Predicting with TensorFlow models

If you have trained a model in TensorFlow and exported it as a SavedModel, you can import the TensorFlow model into BigQuery and use the ML.PREDICT SQL function in BigQuery to make predictions. This is very useful if you want to make batch predictions (e.g., to make predictions for all the data collected in the past hour), given that any SQL query can be scheduled in BigQuery.

Importing the model into BigQuery is simply a matter of specifying a different model_type and pointing it at the model_path from which the SavedModel was exported (note the wildcard at the end to pick up the assets, vocabulary, etc.):

CREATE OR REPLACE MODEL ch09eu.txtclass_tf
OPTIONS (model_type='tensorflow',
         model_path='gs://bucket/some/dir/1549825580/*')

This creates a model in BigQuery that works like any built-in model, as illustrated in Figure 9-11. Here, the schema indicates that the required input to the model is called “input” and is a string.

Figure 9-11. The schema of the imported TensorFlow model

Given this schema, we can now do a prediction:

SELECT
  input,
  (SELECT AS STRUCT(p, ['github', 'nytimes', 'techcrunch'][ORDINAL(s)])
          prediction 
FROM
    (SELECT p, ROW_NUMBER() OVER() AS s FROM
      (SELECT * FROM UNNEST(dense_1) AS p)) 
  ORDER BY p DESC LIMIT 1).*
FROM ML.PREDICT(MODEL advdata.txtclass_tf,
(
SELECT 'Unlikely Partnership in House Gives Lawmakers Hope for Border Deal' AS
input
UNION ALL SELECT "Fitbit's newest fitness tracker is just for employees and
health insurance members"
UNION ALL SELECT "Show HN: Hello, a CLI tool for managing social media"
))

This is very powerful because we can now train a machine learning model, save it to Google Cloud Storage, import it into BigQuery, and carry out periodic predictions without the need to move the data for predictions out of the data warehouse.