Utiliser BigQuery ML pour prédire les résultats de matchs de basketball

Ce tutoriel utilise BigQuery ML pour prédire le taux de réussite de paniers à trois points au basketball. BigQuery ML permet aux utilisateurs de créer et d'exécuter des modèles de machine learning dans BigQuery à l'aide de requêtes SQL. L'objectif est de démocratiser le machine learning en permettant aux utilisateurs SQL de créer des modèles à l'aide de leurs propres outils et d'accélérer le rythme de développement en leur évitant d'avoir à transférer des données.

Dans ce tutoriel, vous allez exploiter l'exemple d'ensemble de données public NCAA Basketball Data pour BigQuery afin de créer un modèle de prédiction du nombre de paniers à trois points réalisés.

Objectifs

Dans ce tutoriel, vous allez utiliser :

  • BigQuery ML pour créer un modèle de régression linéaire à l'aide de l'instruction CREATE MODEL ;
  • la fonction ML.EVALUATE pour évaluer le modèle de ML ;
  • la fonction ML.PREDICT pour effectuer des prédictions à l'aide du modèle de ML.

Coûts

Ce tutoriel utilise des composants facturables de Cloud Platform, y compris :

  • BigQuery
  • BigQuery ML
  • Datalab

Pour en savoir plus sur le coût de BigQuery, consultez la page Tarifs de BigQuery.

Pour en savoir plus sur le coût de BigQuery ML, consultez la page Tarifs de BigQuery ML.

Pour plus d'informations sur les coûts liés à Cloud Datalab, consultez la page des tarifs de Cloud Datalab.

Avant de commencer

  1. Connectez-vous à votre compte Google.

    Si vous n'en possédez pas déjà un, vous devez en créer un.

  2. Dans Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Cloud.

    Accéder à la page de sélection du projet

  3. Vérifiez que la facturation est activée pour votre projet Google Cloud. Découvrez comment vérifier que la facturation est activée pour votre projet.

  4. BigQuery est automatiquement activé dans les nouveaux projets. Pour activer BigQuery dans un projet préexistant, accédez à Activer l'APIBigQuery

    Activer l'API

Étape 1 : Configurer Cloud Datalab

Dans ce tutoriel, vous allez configurer Cloud Datalab à l'aide de Cloud Shell. Cloud Datalab fournit un environnement notebook basé sur Jupyter, qui vous permet d'utiliser BigQuery ML et BigQuery.

Avant de configurer Cloud Datalab, vous devez activer l'API Compute Engine et l'API Cloud Source Repositories. Pour plus d'informations sur l'activation de ces API, consultez la section Activer et désactiver des services.

Pour configurer Cloud Datalab sur une VM Compute Engine, procédez comme suit :

  1. Accédez à Cloud Console.

    Cloud Console

  2. Cliquez sur Activer Cloud Shell.

    Icône Activer Cloud Shell

  3. Dans la fenêtre Cloud Shell, saisissez la commande suivante pour configurer l'outil gcloud afin d'utiliser la zone us-central1-a comme zone par défaut pour le SDK Cloud. Cette zone contiendra votre instance Cloud Datalab.

    gcloud config set compute/zone us-central1-a
    
  4. Saisissez la commande suivante pour créer une VM Compute Engine (nommée mltutorial) utilisée pour exécuter le conteneur Cloud Datalab. Cette commande crée également une connexion SSH à votre VM et associe l'interface Web distante de Cloud Datalab au port 8081 de l'hôte local. Si vous y êtes invité, saisissez le numéro correspondant à votre zone Compute Engine par défaut.

    datalab create mltutorial
    

    Lorsque vous voyez le message The connection to Datalab is now open and will remain until this command is killed, vous êtes connecté à l'instance. Une fois la connexion SSH établie, il se peut que l'interface de ligne de commande n'affiche plus de résultats.

  5. Dans la fenêtre Cloud Shell, cliquez sur l'icône icône Aperçu sur le Web Aperçu sur le Web.

  6. Sélectionnez Modifier le port.

  7. Dans la boîte de dialogue Modifier le port d'aperçu, saisissez 8081, puis cliquez sur Modifier et prévisualiser. Cloud Datalab s'ouvre alors dans un nouvel onglet.

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. Cliquez sur Bloc-notes pour créer un bloc-notes. Celui-ci s'ouvre dans un nouvel onglet.

  9. Dans la barre de titre, cliquez sur Bloc-notes sans titre pour modifier le nom du bloc-notes.

  10. Dans la boîte de dialogue Renommer le notebook, saisissez BigQuery ML tutorial, puis cliquez sur OK.

  11. Dans la première cellule de code, saisissez les informations suivantes pour obtenir la dernière version de la bibliothèque cliente Python BigQuery.

    !pip install --upgrade google-cloud-bigquery
    
  12. Cliquez sur Exécuter > Exécuter à partir de cette cellule. Les résultats de la requête apparaissent sous le bloc de code. Lorsque l'installation est terminée, le message suivant s'affiche :

    Successfully installed ...

  13. Le code affiche un certain nombre de résultats. Pour les masquer et réduire la cellule, cliquez sur le menu de la cellule et sélectionnez Collapse (Réduire).

    Menu de la cellule

  14. Cliquez sur Ajouter du code pour créer une cellule de code.

  15. Saisissez le code suivant pour importer la bibliothèque cliente Python BigQuery et initialiser un client. Ce dernier permet d'envoyer des messages à l'API BigQuery et d'en recevoir.

    from google.cloud import bigquery
    client = bigquery.Client()
    # We use a unique dataset ID for this example to avoid collisions with
    # other invocations of this tutorial.  In practice, you could leverage
    # a persistent dataset and not create/destroy it with each invocation.
    dataset_id = "bqml_tutorial_{}".format(str(uuid.uuid4().hex))
  16. Cliquez sur Exécuter > Exécuter à partir de cette cellule. Cette commande n'affiche aucun résultat.

Étape 2 : Créer votre ensemble de données

Vous allez maintenant créer un ensemble de données BigQuery pour stocker votre modèle de ML. Procédez comme suit :

  1. Cliquez sur Ajouter du code pour créer une cellule de code.

  2. Saisissez la commande suivante pour créer votre ensemble de données.

    dataset = bigquery.Dataset(client.dataset(dataset_id))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. Cliquez sur Exécuter > Exécuter à partir de cette cellule. Le résultat de la commande devrait ressembler à ce qui suit :

    Dataset '[project_ID]:bqml_tutorial' successfully created.

Étape 3 : Générez vos données d'entraînement et de caractéristiques

Vous allez ensuite générer vos données d'entraînement et de caractéristiques. Tout d'abord, vous générez les fonctionnalités d'entrée du modèle en interrogeant la table bigquery-public-data.ncaa_basketball.mbb_teams_games_sr de l'ensemble de données public NCAA Basketball Data. Une fois ceci fait, vous allez générer vos données d'entraînement.

Générer les caractéristiques en entrée (extraction de caractéristiques)

Les caractéristiques en entrée incluent l'écart moyen et l'écart type des statistiques du match précédent pour les équipes à domicile et les équipes en extérieur sur différentes périodes (10 matchs, 5 matchs ou 1 match précédant le match en cours). La colonne team-id (pour les équipes à domicile et celles en extérieur) et la colonne season sont également utilisées en tant que caractéristiques uniques, ce qui contribue à la moyenne statistique du modèle.

En raison de la longueur de la requête utilisée pour générer les caractéristiques d'entrée, vous allez utiliser une requête enregistrée dans l'interface utilisateur Web classique de BigQuery pour créer la table.

Pour générer les données des caractéristiques en entrée :

  1. Cliquez sur le bouton ci-dessous pour ouvrir la requête de données en entrée.

    Ouvrir la requête de saisie des caractéristiques

    Si vous en avez le temps, examinez la requête.

  2. Sous la fenêtre de requête, cliquez sur Afficher les options.

  3. Dans la section Table de destination, cliquez sur Sélectionner une table.

  4. Dans la boîte de dialogue Sélectionner une table de destination :

    1. Pour Project, sélectionnez votre projet.
    2. Dans le champ Ensemble de données, choisissez bqml_tutorial.
    3. Dans le champ ID de la table, saisissez cume_games.
    4. Cliquez sur OK.
  5. Cliquez sur Exécuter la requête. Cette action crée une tâche de requête qui écrit les résultats dans la table spécifiée.

    Si vous oubliez de spécifier une table de destination avant d'exécuter la requête, vous pouvez copier la table temporaire dans une table permanente. Pour ce faire, cliquez sur le bouton Enregistrer en tant que table dans la fenêtre des résultats.

  6. L'exécution de la requête prend quelques secondes. Une fois la requête exécutée, la table cume_games apparaît dans le panneau de navigation de l'interface utilisateur Web de BigQuery.

Générer vos données d'entraînement

Après avoir obtenu les caractéristiques en entrée, vous allez générer vos données d'entraînement. Celles-ci sont écrites dans la table bqml_tutorial.wide_games. Lors de la création des données d'entraînement, vous allez exclure un match des éléments à utiliser pour prédire les résultats.

Dans cette requête, vous réalisez une autojointure de la table cume_games que vous avez créée en exécutant la requête précédente. Cette opération permet de générer à la fois les statistiques de l'équipe locale et celles de l'équipe en extérieur avant le match en cours.

En raison de la longueur de la requête utilisée pour générer vos données d'entraînement, vous allez utiliser une requête enregistrée dans l'interface utilisateur Web classique de BigQuery pour créer la table.

Pour générer vos données d'entraînement :

  1. Cliquez sur le bouton ci-dessous pour ouvrir la requête de données en entrée.

    Ouvrir la requête de données d'entraînement

    Si vous en avez le temps, examinez la requête.

  2. Sous la fenêtre de requête, cliquez sur Afficher les options.

  3. Dans la section Table de destination, cliquez sur Sélectionner une table.

  4. Dans la boîte de dialogue Sélectionner une table de destination :

    1. Pour Project, sélectionnez votre projet.
    2. Dans le champ Ensemble de données, choisissez bqml_tutorial.
    3. Dans le champ ID de la table, saisissez wide_games.
    4. Cliquez sur OK.
  5. Cliquez sur Exécuter la requête. Cette action crée une tâche de requête qui écrit les résultats dans la table spécifiée.

    Si vous oubliez de spécifier une table de destination avant d'exécuter la requête, vous pouvez copier la table temporaire dans une table permanente. Pour ce faire, cliquez sur le bouton Enregistrer en tant que table dans la fenêtre des résultats.

  6. L'exécution de la requête prend quelques secondes. Une fois la requête exécutée, la table wide_games apparaît dans le panneau de navigation de l'interface utilisateur Web de BigQuery.

Étape 4 : Créez votre modèle

Vous allez maintenant créer un modèle de régression linéaire. Il permet de prédire le taux combiné de réussite de paniers à trois points en fonction des statistiques du match précédent. La requête SQL standard utilise une instruction CREATE MODEL pour créer le modèle. L'instruction CREATE MODEL peut créer et entraîner votre modèle, mais dans ce tutoriel, vous l'entraînez séparément.

Pour exécuter la requête CREATE MODEL afin de créer votre modèle, procédez comme suit :

  1. Basculez vers votre notebook Cloud Datalab.

  2. Cliquez sur Ajouter du code pour créer une cellule de code.

  3. La bibliothèque cliente Python BigQuery propose une commande magique qui vous permet d'exécuter des requêtes avec le moins de code possible. Pour charger les commandes magiques à partir de la bibliothèque cliente, saisissez le code suivant :

    %load_ext google.cloud.bigquery
    
  4. Cliquez sur Exécuter > Exécuter à partir de cette cellule. Cette commande n'affiche aucun résultat.

  5. Cliquez sur Ajouter du code pour créer une cellule de code.

  6. La bibliothèque cliente BigQuery propose une cellule magique, %%bigquery, qui exécute une requête SQL et renvoie les résultats sous la forme d'un objet DataFrame Pandas. Entrez la requête SQL standard suivante dans la cellule. Vous n'avez pas besoin d'utiliser le préfixe #standardSQL avec la bibliothèque cliente. Le langage SQL standard est la syntaxe de requête par défaut.

Jupyter

%%bigquery
CREATE OR REPLACE MODEL bqml_tutorial.ncaa_model OPTIONS(
  model_type='linear_reg', max_iteration=50) AS
SELECT
  * EXCEPT(
    game_id, season, scheduled_date,
    total_three_points_made,
    total_three_points_att),
  total_three_points_att as label
FROM
  bqml_tutorial.wide_games
WHERE
  # remove the game to predict
  game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'

Python

sql = """
    CREATE OR REPLACE MODEL `{0}.ncaa_model`
    OPTIONS (
        model_type='linear_reg',
        max_iteration=50 ) AS
    SELECT
        * EXCEPT (
            game_id, season, scheduled_date,
            total_three_points_made,
            total_three_points_att),
        total_three_points_att as label
    FROM
        `{0}.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Cliquez sur Exécuter > Exécuter à partir de cette cellule.

  2. L'exécution de la requête prend plusieurs minutes. Une fois la première itération terminée, votre modèle ncaa_model apparaît dans le panneau de navigation de l'interface utilisateur Web de BigQuery. Comme la requête utilise une instruction CREATE MODEL, les résultats de la requête ne sont pas affichés. La sortie prend la forme d'une chaîne vide.

Étape 5 : Générez des statistiques d'entraînement

Pour afficher les résultats de l'entraînement du modèle, vous pouvez vous servir de la fonction ML.TRAINING_INFO. Vous avez également la possibilité d'afficher les statistiques dans l'interface utilisateur Web de BigQuery. Cette fonctionnalité n'est actuellement pas disponible dans l'UI Web classique de BigQuery. Dans ce tutoriel, vous utilisez la fonction ML.TRAINING_INFO.

Pour créer un modèle, un algorithme de machine learning examine de nombreux exemples et essaie de trouver un modèle qui minimise la perte. Ce processus est appelé "minimisation du risque empirique".

La perte est la pénalité relative à une mauvaise prédiction. Ce nombre indique à quel point la prédiction du modèle est fausse pour un exemple donné. Si la prédiction du modèle est parfaite, la perte est nulle. Sinon, la perte est supérieure à zéro. L'objectif de l'entraînement d'un modèle est de trouver un ensemble de pondérations et de biais présentant une faible perte pour tous les exemples (en moyenne).

Pour afficher les statistiques d'entraînement du modèle qui ont été générées lors de l'exécution de la requête CREATE MODEL, procédez comme suit :

  1. Cliquez sur Ajouter du code pour créer une cellule de code.

  2. Entrez la requête SQL standard suivante dans la cellule.

Jupyter

%%bigquery
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `{}.ncaa_model`)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Cliquez sur Exécuter > Exécuter à partir de cette cellule.

  2. Une fois la requête terminée, les résultats apparaissent sous celle-ci. Ils devraient apparaître comme suit :

    Résultat ML.TRAINING_INFO

    La colonne Perte de données d'entraînement représente la métrique de perte calculée après l'itération donnée dans l'ensemble de données d'entraînement. Étant donné que vous avez effectué une régression linéaire, cette colonne correspond à l'erreur quadratique moyenne. La colonne Evaluation Data Loss (Perte de données d'évaluation) représente la même métrique de perte calculée sur l'ensemble de données exclues (données bloquées lors de l'entraînement pour valider le modèle).

    Pour en savoir plus sur la fonction ML.TRAINING_INFO, consultez la documentation de référence sur la syntaxe BigQuery ML.

Étape 6 : Évaluez votre modèle

Après avoir créé votre modèle, vous évaluez ses performances à l'aide de la fonction ML.EVALUATE.

Pour exécuter la requête ML.EVALUATE permettant d'évaluer le modèle, procédez comme suit :

  1. Cliquez sur Ajouter du code pour créer une cellule de code.

  2. Entrez la requête SQL standard suivante dans la cellule.

Jupyter

%%bigquery
WITH eval_table AS (
SELECT
  *,
  total_three_points_att AS label
FROM
  `bqml_tutorial.wide_games` )
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
    TABLE eval_table)

Python

sql = """
    WITH eval_table AS (
        SELECT
            *,
            total_three_points_att AS label
        FROM
            `{0}.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `{0}.ncaa_model`,
            TABLE eval_table)
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Cliquez sur Exécuter > Exécuter à partir de cette cellule.

  2. Une fois la requête terminée, les résultats apparaissent sous celle-ci. Ils devraient apparaître comme suit :

    Résultat de ML.EVALUATE

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

    • mean_absolute_error
    • mean_squared_error
    • mean_squared_log_error
    • median_absolute_error
    • r2_score
    • explained_variance

L'erreur absolue moyenne est une métrique importante dans les résultats de l'évaluation. L'erreur absolue moyenne représente la différence moyenne entre l'étiquette prévue et l'étiquette réelle. Dans ce cas, elle est d'environ 7.

Étape 7 : Utilisez votre modèle pour prédire les résultats

Maintenant que vous avez évalué votre modèle, la prochaine étape consiste à utiliser la fonction ML.PREDICT pour prédire le taux de réussite de paniers à trois points lors de la finale 2018, Michigan contre Villanova.

Pour exécuter la requête qui utilise la fonction ML.PREDICT, procédez comme suit :

  1. Cliquez sur Ajouter du code pour créer une cellule de code.

  2. Entrez la requête SQL standard suivante dans la cellule.

Jupyter

%%bigquery
  WITH game_to_predict AS (
  SELECT
    *
  FROM
    `bqml_tutorial.wide_games`
  WHERE
    game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
SELECT
  truth.game_id AS game_id,
  total_three_points_att,
  predicted_total_three_points_att
FROM (
  SELECT
    game_id,
    predicted_label AS predicted_total_three_points_att
  FROM
    ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
      table game_to_predict) ) AS predict
JOIN (
  SELECT
    game_id,
    total_three_points_att AS total_three_points_att
  FROM
    game_to_predict) AS truth
ON
  predict.game_id = truth.game_id

Python

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `{0}.wide_games`
        WHERE
            game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
    SELECT
        truth.game_id AS game_id,
        total_three_points_att,
        predicted_total_three_points_att
    FROM (
        SELECT
            game_id,
            predicted_label AS predicted_total_three_points_att
        FROM
            ML.PREDICT(MODEL `{0}.ncaa_model`,
            table game_to_predict) ) AS predict
    JOIN (
        SELECT
            game_id,
            total_three_points_att AS total_three_points_att
        FROM
            game_to_predict) AS truth
    ON
        predict.game_id = truth.game_id
""".format(dataset_id)
df = client.query(sql).to_dataframe()
print(df)

  1. Cliquez sur Exécuter > Exécuter à partir de cette cellule.

  2. Une fois la requête terminée, les résultats apparaissent sous celle-ci. Ils doivent se présenter sous la forme suivante. Comme l'entraînement du modèle n'est pas déterministe, vous pouvez obtenir des résultats différents.

    Résultat de ML.PREDICT

La valeur total_three_points_att correspond au nombre réel de paniers réussis dans le match final, soit 50. La prédiction du modèle est de 43,41.

Nettoyage

Afin d'éviter que des frais ne soient facturés sur votre compte Google Cloud Platform pour les ressources utilisées dans ce tutoriel, procédez comme suit :

  • Supprimez le projet que vous avez créé.
  • Vous pouvez également conserver le projet et supprimer la VM Cloud Datalab.

Supprimer votre VM Cloud Datalab

La suppression de votre projet entraîne la suppression de la VM Cloud Datalab. Si vous ne souhaitez pas supprimer le projet Cloud Platform, vous pouvez supprimer la VM Cloud Datalab.

Pour supprimer la VM Cloud Datalab, procédez comme suit :

  1. Ouvrez la page "Instances de VM" dans Compute Engine.

    Accéder à la page Instances de VM

  2. Vérifiez l'instance mltutorial, puis cliquez sur Supprimer.

  3. Lorsque vous y êtes invité, cliquez sur Supprimer.

Supprimer votre projet

Pour supprimer le projet :

  1. Dans Cloud Console, accédez à la page Gérer les ressources.

    Accéder à la page Gérer les ressources

  2. Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
  3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

Étapes suivantes