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 utiliserez les éléments suivants :

  • 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 ML
  • la fonction ML.PREDICT pour réaliser des prédictions à l'aide du modèle de ML.

Coûts

Ce tutoriel utilise des composants facturables de Cloud Platform, dont :

  • BigQuery
  • BigQuery ML
  • Cloud Datalab

Pour plus d'informations sur le coût de BigQuery, consultez la page Tarifs de BigQuery.

Pour plus d'informations sur le coût de BigQuery ML, consultez la page Tarifs de BigQuery ML.

Pour plus d'informations sur le coût de Cloud Datalab, consultez la page 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. Sélectionnez ou créez un projet Google Cloud Platform.

    Accéder à la page "Gérer les ressources"

  3. Assurez-vous que la facturation est activée pour votre projet Google Cloud Platform.

    Découvrir comment activer la facturation

  4. BigQuery est automatiquement activé dans les nouveaux projets. Pour activer BigQuery dans un projet existant, accédez à Activez BigQueryl'API requise.

    Activer l'API.

Étape 1 : Configurez Cloud Datalab

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

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

Pour configurer Cloud Datalab sur une VM Compute Engine :

  1. Accédez à la console GCP.

    Console GCP

  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 de façon à 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 (appelée mltutorial) qui permet d'exécuter le conteneur Cloud Datalab. Cette commande crée également une connexion SSH à votre VM et mappe l'interface Web distante de Cloud Datalab sur le 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
    

    Le message The connection to Datalab is now open and will remain until this command is killed vous indique que 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 Aperçu sur le Web icône 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 bloc-notes, 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()
  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('bqml_tutorial'))
    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. Vous allez commencer par interroger la table bigquery-public-data.ncaa_basketball.mbb_teams_games_sr dans l'ensemble de données public NCAA Basketball Data afin de générer les caractéristiques du modèle en entrée. 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. Pour Ensemble de données, sélectionnez bqml_tutorial.
    3. Dans le champ ID de 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. La table (cume_games) s'affiche alors 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 allez réaliser une jointure réflexive de la table cume_games que vous avez créée avec 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. Pour Ensemble de données, sélectionnez bqml_tutorial.
    3. Dans le champ ID de 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. La table (wide_games) s'affiche alors 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 en SQL standard crée le modèle à l'aide d'une instruction CREATE MODEL. 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 qui permet de créer votre modèle :

  1. Basculez vers le bloc-notes 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'une structure de données Pandas. Saisissez la requête en 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 `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'
"""
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. À la fin de la première itération, votre modèle (ncaa_model) s'affiche dans le panneau de navigation de l'interface utilisateur Web de BigQuery. Comme la requête crée un modèle à l'aide d'une instruction CREATE MODEL, elle n'affiche aucun résultat. La sortie prend la forme d'une chaîne vide.

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

Pour consulter les résultats de l'entraînement du modèle, vous pouvez utiliser la fonction ML.TRAINING_INFO ou afficher les statistiques dans l'interface utilisateur Web de BigQuery. Cette fonctionnalité n'est actuellement pas disponible dans l'interface utilisateur Web classique de BigQuery. Dans ce tutoriel, vous allez utiliser 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 générées lors de l'exécution de la requête CREATE MODEL :

  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 `bqml_tutorial.ncaa_model`)
"""
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.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 allez évaluer ses performances à l'aide de la fonction ML.EVALUATE.

Pour exécuter la requête ML.EVALUATE permettant d'évaluer le modèle :

  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
            `bqml_tutorial.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
            TABLE eval_table)
"""
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 global de réussite de paniers à trois points dans le match final NCAA 2018, Michigan contre Villanova.

Pour exécuter la requête qui utilise la fonction ML.PREDICT :

  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
            `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
"""
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 :

  • Vous pouvez supprimer 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 également celle de la VM Cloud Datalab. Si vous ne souhaitez pas supprimer le projet Cloud Platform, vous pouvez simplement supprimer la VM Cloud Datalab.

Pour supprimer la VM Cloud Datalab :

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

    Accédez à la page des 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, procédez comme suit :

  1. Dans la console GCP, accédez à la page "Projets".

    Accéder à la page Projets

  2. Dans la liste des projets, sélectionnez celui 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

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Documentation sur BigQuery ML