Créer des modèles de machine learning dans BigQuery ML

SQL

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, exécutez l'instruction suivante :

   CREATE OR REPLACE MODEL `bqml_tutorial.sample_model`
   OPTIONS(model_type='logistic_reg') AS
   SELECT
   IF(totals.transactions IS NULL, 0, 1) AS label,
   IFNULL(device.operatingSystem, "") AS os,
   device.isMobile AS is_mobile,
   IFNULL(geoNetwork.country, "") AS country,
   IFNULL(totals.pageviews, 0) AS pageviews
   FROM
   `bigquery-public-data.google_analytics_sample.ga_sessions_*`
   WHERE
   _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'

   L'exécution de la requête prend plusieurs minutes. Une fois la première itération terminée, votre modèle (sample_model) s'affiche dans le panneau de navigation. Étant donné que la requête utilise une instruction CREATE MODEL pour créer un modèle, les résultats de la requête ne sont pas affichés.

Détails de la requête

L'instruction CREATE MODEL crée le modèle, puis l'entraîne à l'aide des données récupérées par l'instruction SELECT de votre requête.

La clause OPTIONS(model_type='logistic_reg') crée un modèle de régression logistique. Un modèle de régression logistique divise les données d'entrée en deux classes, puis estime la probabilité que les données appartiennent à l'une d'elles. Ce que vous essayez de déterminer (par exemple, si un e-mail est un spam), est représenté par un 1, et les autres valeurs sont représentées par un 0. La probabilité qu'une valeur donnée appartienne à la classe que vous essayez de détecter est indiquée par une valeur comprise entre 0 et 1. Par exemple, si la probabilité estimée d'un e-mail est de 0,9, il y a 90 % de chances qu'il s'agisse d'un spam.

L'instruction SELECT de cette requête extrait les colonnes suivantes qui permettent au modèle de réaliser une prédiction sur la probabilité qu'un client effectue une transaction :

• totals.transactions : nombre total de transactions e-commerce au cours de la session. Si le nombre de transactions est NULL, la valeur de la colonne label est définie sur 0. Sinon, la valeur est égale à 1. Ces valeurs correspondent aux résultats possibles. Plutôt que de définir l'option input_label_cols= dans l'instruction CREATE MODEL, vous pouvez créer un alias nommé label.
• device.operatingSystem : système d'exploitation de l'appareil du visiteur.
• device.isMobile : indique si l'appareil du visiteur est un appareil mobile.
• geoNetwork.country : pays d'origine des sessions, sur la base de l'adresse IP.
• totals.pageviews : nombre total de pages vues au cours de la session.

La clause FROM entraîne l'entraînement du modèle à l'aide des exemples de tables bigquery-public-data.google_analytics_sample.ga_sessions. Ces tables sont segmentées par date. Vous devez donc les agréger à l'aide d'un caractère générique dans le nom de la table : google_analytics_sample.ga_sessions_*.

La clause WHERE – _TABLE_SUFFIX BETWEEN '20160801' AND '20170630' – limite le nombre de tables analysées par la requête. La plage de dates analysée est comprise entre le 1er août 2016 et le 30 juin 2017.

BigQuery DataFrames

Avant d'essayer cet exemple, suivez les instructions de configuration pour BigQuery DataFrames du guide de démarrage rapide de BigQuery DataFrames. Pour en savoir plus, consultez la documentation de référence sur BigQuery DataFrames.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour un environnement de développement local.

from bigframes.ml.linear_model import LogisticRegression
import bigframes.pandas as bpd

# Start by selecting the data you'll use for training. `read_gbq` accepts
# either a SQL query or a table ID. Since this example selects from multiple
# tables via a wildcard, use SQL to define this data. Watch issue
# https://github.com/googleapis/python-bigquery-dataframes/issues/169
# for updates to `read_gbq` to support wildcard tables.

df = bpd.read_gbq_table(
    "bigquery-public-data.google_analytics_sample.ga_sessions_*",
    filters=[
        ("_table_suffix", ">=", "20160801"),
        ("_table_suffix", "<=", "20170630"),
    ],
)

# Extract the total number of transactions within
# the Google Analytics session.
#
# Because the totals column is a STRUCT data type, call
# Series.struct.field("transactions") to extract the transactions field.
# See the reference documentation below:
# https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.operations.structs.StructAccessor#bigframes_operations_structs_StructAccessor_field
transactions = df["totals"].struct.field("transactions")

# The "label" values represent the outcome of the model's
# prediction. In this case, the model predicts if there are any
# ecommerce transactions within the Google Analytics session.
# If the number of transactions is NULL, the value in the label
# column is set to 0. Otherwise, it is set to 1.
label = transactions.notnull().map({True: 1, False: 0}).rename("label")

# Extract the operating system of the visitor's device.
operating_system = df["device"].struct.field("operatingSystem")
operating_system = operating_system.fillna("")

# Extract whether the visitor's device is a mobile device.
is_mobile = df["device"].struct.field("isMobile")

# Extract the country from which the sessions originated, based on the IP address.
country = df["geoNetwork"].struct.field("country").fillna("")

# Extract the total number of page views within the session.
pageviews = df["totals"].struct.field("pageviews").fillna(0)

# Combine all the feature columns into a single DataFrame
# to use as training data.
features = bpd.DataFrame(
    {
        "os": operating_system,
        "is_mobile": is_mobile,
        "country": country,
        "pageviews": pageviews,
    }
)

# Logistic Regression model splits data into two classes, giving the
# a confidence score that the data is in one of the classes.
model = LogisticRegression()
model.fit(features, label)

# The model.fit() call above created a temporary model.
# Use the to_gbq() method to write to a permanent location.
model.to_gbq(
    your_model_id,  # For example: "bqml_tutorial.sample_model",
    replace=True,
)

SQL

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, exécutez l'instruction suivante :

   SELECT
   *
   FROM
   ML.EVALUATE(MODEL `bqml_tutorial.sample_model`, (
   SELECT
   IF(totals.transactions IS NULL, 0, 1) AS label,
   IFNULL(device.operatingSystem, "") AS os,
   device.isMobile AS is_mobile,
   IFNULL(geoNetwork.country, "") AS country,
   IFNULL(totals.pageviews, 0) AS pageviews
   FROM
   `bigquery-public-data.google_analytics_sample.ga_sessions_*`
   WHERE
   _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))

   Les résultats doivent se présenter sous la forme suivante :

     +--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+
     |     precision      |       recall        |      accuracy       |      f1_score       |      log_loss       | roc_auc                   |
     +--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+
     | 0.468503937007874  | 0.11080074487895716 | 0.98534315834767638 | 0.17921686746987953 | 0.04624221101176898    | 0.98174125874125873 |
     +--------------------+---------------------+---------------------+---------------------+---------------------+--------------------+
     

   Étant donné que vous avez effectué une régression logistique, les résultats incluent les colonnes suivantes :

   • precision : métrique des modèles de classification. La précision correspond à la fréquence à laquelle le modèle prédit correctement la classe positive.

   • recall : métrique des modèles de classification qui répond à la question suivante : parmi toutes les étiquettes positives possibles, combien d'entre elles le modèle a-t-il correctement identifiées ?

   • accuracy : la justesse correspond à la fraction de prédictions correctement identifiées par un modèle de classification.

   • f1_score : mesure de la justesse du modèle. Le score f1 est la moyenne harmonique de la précision et du rappel. La meilleure valeur d'un score f1 est 1. La pire valeur est 0.

   • log_loss : fonction de perte utilisée dans une régression logistique. Il s'agit de la mesure de l'écart entre les prédictions du modèle et les étiquettes correctes.

   • roc_auc : aire sous la courbe ROC. Elle correspond à la probabilité que le niveau de confiance d'un classificateur soit supérieur pour un exemple positif choisi aléatoirement comparé à un exemple négatif choisi aléatoirement. Pour en savoir plus, consultez la page Classification dans le Cours d'initiation au Machine Learning.

Détails de la requête

La toute première instruction SELECT récupère les colonnes de votre modèle.

La clause FROM utilise la fonction ML.EVALUATE basée sur votre modèle.

L'instruction SELECT et la clause FROM imbriquées de cette requête sont identiques à celles de la requête CREATE MODEL.

La clause WHERE – _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' – limite le nombre de tables analysées par la requête. La plage de dates analysée est comprise entre le 1er juillet 2017 et le 1er août 2017. Il s'agit des données dont vous vous servez pour évaluer les prévisions de performances du modèle. Elles ont été recueillies dans le mois qui a suivi la période couverte par les données d'entraînement.

BigQuery DataFrames

Avant d'essayer cet exemple, suivez les instructions de configuration pour BigQuery DataFrames du guide de démarrage rapide de BigQuery DataFrames. Pour en savoir plus, consultez la documentation de référence sur BigQuery DataFrames.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour un environnement de développement local.

import bigframes.pandas as bpd

# Select model you'll use for evaluating. `read_gbq_model` loads model data from a
# BigQuery, but you could also use the `model` object from the previous steps.
model = bpd.read_gbq_model(
    your_model_id,  # For example: "bqml_tutorial.sample_model",
)

# The filters parameter limits the number of tables scanned by the query.
# The date range scanned is July 1, 2017 to August 1, 2017. This is the
# data you're using to evaluate the predictive performance of the model.
# It was collected in the month immediately following the time period
# spanned by the training data.
df = bpd.read_gbq_table(
    "bigquery-public-data.google_analytics_sample.ga_sessions_*",
    filters=[
        ("_table_suffix", ">=", "20170701"),
        ("_table_suffix", "<=", "20170801"),
    ],
)

transactions = df["totals"].struct.field("transactions")
label = transactions.notnull().map({True: 1, False: 0}).rename("label")
operating_system = df["device"].struct.field("operatingSystem")
operating_system = operating_system.fillna("")
is_mobile = df["device"].struct.field("isMobile")
country = df["geoNetwork"].struct.field("country").fillna("")
pageviews = df["totals"].struct.field("pageviews").fillna(0)
features = bpd.DataFrame(
    {
        "os": operating_system,
        "is_mobile": is_mobile,
        "country": country,
        "pageviews": pageviews,
    }
)

# Some models include a convenient .score(X, y) method for evaluation with a preset accuracy metric:

# Because you performed a logistic regression, the results include the following columns:

# - precision — A metric for classification models. Precision identifies the frequency with
# which a model was correct when predicting the positive class.

# - recall — A metric for classification models that answers the following question:
# Out of all the possible positive labels, how many did the model correctly identify?

# - accuracy — Accuracy is the fraction of predictions that a classification model got right.

# - f1_score — A measure of the accuracy of the model. The f1 score is the harmonic average of
# the precision and recall. An f1 score's best value is 1. The worst value is 0.

# - log_loss — The loss function used in a logistic regression. This is the measure of how far the
# model's predictions are from the correct labels.

# - roc_auc — The area under the ROC curve. This is the probability that a classifier is more confident that
# a randomly chosen positive example
# is actually positive than that a randomly chosen negative example is positive. For more information,
# see ['Classification']('https://developers.google.com/machine-learning/crash-course/classification/video-lecture')
# in the Machine Learning Crash Course.

model.score(features, label)
#    precision    recall  accuracy  f1_score  log_loss   roc_auc
# 0   0.412621  0.079143  0.985074  0.132812  0.049764  0.974285
# [1 rows x 6 columns]

SQL

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, exécutez l'instruction suivante :

   SELECT
   country,
   SUM(predicted_label) as total_predicted_purchases
   FROM
   ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
   SELECT
   IFNULL(device.operatingSystem, "") AS os,
   device.isMobile AS is_mobile,
   IFNULL(totals.pageviews, 0) AS pageviews,
   IFNULL(geoNetwork.country, "") AS country
   FROM
   `bigquery-public-data.google_analytics_sample.ga_sessions_*`
   WHERE
   _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
   GROUP BY country
   ORDER BY total_predicted_purchases DESC
   LIMIT 10

   Les résultats doivent se présenter sous la forme suivante :

   +----------------+---------------------------+
   |    country     | total_predicted_purchases |
   +----------------+---------------------------+
   | United States  |                       220 |
   | Taiwan         |                         8 |
   | Canada         |                         7 |
   | India          |                         2 |
   | Turkey         |                         2 |
   | Japan          |                         2 |
   | Italy          |                         1 |
   | Brazil         |                         1 |
   | Singapore      |                         1 |
   | Australia      |                         1 |
   +----------------+---------------------------+
   

Détails de la requête

L'instruction SELECT initiale récupère la colonne country et ajoute la colonne predicted_label. Cette colonne predicted_label est générée par la fonction ML.PREDICT. Lorsque vous utilisez la fonction ML.PREDICT, le nom de la colonne de résultats pour le modèle est predicted_<label_column_name>. Pour les modèles de régression linéaire, predicted_label est la valeur estimée de label. Pour les modèles de régression logistique, predicted_label est l'étiquette qui décrit le mieux la valeur des données d'entrée donnée, soit 0, soit 1.

La fonction ML.PREDICT sert à prédire les résultats à l'aide de votre modèle.

L'instruction SELECT et la clause FROM imbriquées de cette requête sont identiques à celles de la requête CREATE MODEL.

La clause WHERE – _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' – limite le nombre de tables analysées par la requête. La plage de dates analysée est comprise entre le 1er juillet 2017 et le 1er août 2017. Il s'agit des données pour lesquelles vous réalisez des prédictions. Elles ont été recueillies dans le mois qui a suivi la période couverte par les données d'entraînement.

Les clauses GROUP BY et ORDER BY regroupent les résultats par pays, puis les trient en fonction de la somme d'achats prévus par ordre décroissant.

La clause LIMIT permet ici de n'afficher que les 10 premiers résultats.

BigQuery DataFrames

Avant d'essayer cet exemple, suivez les instructions de configuration pour BigQuery DataFrames du guide de démarrage rapide de BigQuery DataFrames. Pour en savoir plus, consultez la documentation de référence sur BigQuery DataFrames.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour un environnement de développement local.

import bigframes.pandas as bpd

# Select model you'll use for predicting.
# `read_gbq_model` loads model data from
# BigQuery, but you could also use the `model`
# object from the previous steps.
model = bpd.read_gbq_model(
    your_model_id,  # For example: "bqml_tutorial.sample_model",
)

# The filters parameter limits the number of tables scanned by the query.
# The date range scanned is July 1, 2017 to August 1, 2017. This is the
# data you're using to make the prediction.
# It was collected in the month immediately following the time period
# spanned by the training data.
df = bpd.read_gbq_table(
    "bigquery-public-data.google_analytics_sample.ga_sessions_*",
    filters=[
        ("_table_suffix", ">=", "20170701"),
        ("_table_suffix", "<=", "20170801"),
    ],
)

operating_system = df["device"].struct.field("operatingSystem")
operating_system = operating_system.fillna("")
is_mobile = df["device"].struct.field("isMobile")
country = df["geoNetwork"].struct.field("country").fillna("")
pageviews = df["totals"].struct.field("pageviews").fillna(0)
features = bpd.DataFrame(
    {
        "os": operating_system,
        "is_mobile": is_mobile,
        "country": country,
        "pageviews": pageviews,
    }
)
# Use Logistic Regression predict method to predict results
# using your model.
# Find more information here in
# [BigFrames](https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.ml.linear_model.LogisticRegression#bigframes_ml_linear_model_LogisticRegression_predict)

predictions = model.predict(features)

# Call groupby method to group predicted_label by country.
# Call sum method to get the total_predicted_label by country.
total_predicted_purchases = predictions.groupby(["country"])[
    ["predicted_label"]
].sum()

# Call the sort_values method with the parameter
# ascending = False to get the highest values.
# Call head method to limit to the 10 highest values.
total_predicted_purchases.sort_values(ascending=False).head(10)

# country
# United States    220
# Taiwan             8
# Canada             7
# India              2
# Japan              2
# Turkey             2
# Australia          1
# Brazil             1
# Germany            1
# Guyana             1
# Name: predicted_label, dtype: Int64

SQL

Cette requête est identique à celle de la section précédente, à l'exception de la clause GROUP BY. La clause GROUP BY – GROUP BY fullVisitorId – saisie ici permet de regrouper les résultats par ID de visiteur.

1. Dans la console Google Cloud, accédez à la page BigQuery.

   Accéder à BigQuery

2. Dans l'éditeur de requête, exécutez l'instruction suivante :

   SELECT
   fullVisitorId,
   SUM(predicted_label) as total_predicted_purchases
   FROM
   ML.PREDICT(MODEL `bqml_tutorial.sample_model`, (
   SELECT
   IFNULL(device.operatingSystem, "") AS os,
   device.isMobile AS is_mobile,
   IFNULL(totals.pageviews, 0) AS pageviews,
   IFNULL(geoNetwork.country, "") AS country,
   fullVisitorId
   FROM
   `bigquery-public-data.google_analytics_sample.ga_sessions_*`
   WHERE
   _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'))
   GROUP BY fullVisitorId
   ORDER BY total_predicted_purchases DESC
   LIMIT 10

   Les résultats doivent se présenter sous la forme suivante :

     +---------------------+---------------------------+
     |    fullVisitorId    | total_predicted_purchases |
     +---------------------+---------------------------+
     | 9417857471295131045 |                         4 |
     | 112288330928895942  |                         2 |
     | 2158257269735455737 |                         2 |
     | 489038402765684003  |                         2 |
     | 057693500927581077  |                         2 |
     | 2969418676126258798 |                         2 |
     | 5073919761051630191 |                         2 |
     | 7420300501523012460 |                         2 |
     | 0456807427403774085 |                         2 |
     | 2105122376016897629 |                         2 |
     +---------------------+---------------------------+
     

BigQuery DataFrames

Avant d'essayer cet exemple, suivez les instructions de configuration pour BigQuery DataFrames du guide de démarrage rapide de BigQuery DataFrames. Pour en savoir plus, consultez la documentation de référence sur BigQuery DataFrames.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour un environnement de développement local.

import bigframes.pandas as bpd

# Select model you'll use for predicting.
# `read_gbq_model` loads model data from
# BigQuery, but you could also use the `model`
# object from the previous steps.
model = bpd.read_gbq_model(
    your_model_id,  # For example: "bqml_tutorial.sample_model",
)

# The filters parameter limits the number of tables scanned by the query.
# The date range scanned is July 1, 2017 to August 1, 2017. This is the
# data you're using to make the prediction.
# It was collected in the month immediately following the time period
# spanned by the training data.
df = bpd.read_gbq_table(
    "bigquery-public-data.google_analytics_sample.ga_sessions_*",
    filters=[
        ("_table_suffix", ">=", "20170701"),
        ("_table_suffix", "<=", "20170801"),
    ],
)

operating_system = df["device"].struct.field("operatingSystem")
operating_system = operating_system.fillna("")
is_mobile = df["device"].struct.field("isMobile")
country = df["geoNetwork"].struct.field("country").fillna("")
pageviews = df["totals"].struct.field("pageviews").fillna(0)
full_visitor_id = df["fullVisitorId"]

features = bpd.DataFrame(
    {
        "os": operating_system,
        "is_mobile": is_mobile,
        "country": country,
        "pageviews": pageviews,
        "fullVisitorId": full_visitor_id,
    }
)

predictions = model.predict(features)

# Call groupby method to group predicted_label by visitor.
# Call sum method to get the total_predicted_label by visitor.
total_predicted_purchases = predictions.groupby(["fullVisitorId"])[
    ["predicted_label"]
].sum()

# Call the sort_values method with the parameter
# ascending = False to get the highest values.
# Call head method to limit to the 10 highest values.
total_predicted_purchases.sort_values(ascending=False).head(10)

# fullVisitorId
# 9417857471295131045    4
# 0376394056092189113    2
# 0456807427403774085    2
# 057693500927581077     2
# 112288330928895942     2
# 1280993661204347450    2
# 2105122376016897629    2
# 2158257269735455737    2
# 2969418676126258798    2
# 489038402765684003     2
# Name: predicted_label, dtype: Int64