GSP461

Présentation

BigQuery est la base de données d'analyse NoOps, économique et entièrement gérée de Google. Avec BigQuery, vous pouvez interroger des téraoctets et des téraoctets de données sans avoir à gérer d'infrastructure ni à faire appel aux services d'un administrateur de base de données. Basé sur le langage SQL et le modèle de paiement à l'usage, BigQuery vous permet de vous concentrer sur l'analyse des données pour en dégager des insights pertinents.

BigQuery ML permet aux analystes de données d'utiliser leur connaissance de SQL pour créer rapidement des modèles de machine learning directement à l'emplacement où sont stockées les données dans BigQuery.

BigQuery comporte un ensemble de données public regroupant des informations sur les matchs, les équipes et les joueurs de basket-ball de la NCAA. Les données sur les matchs couvrent les statistiques générales et par match depuis 2009, ainsi que les scores finaux depuis 1996. On y trouve des données supplémentaires sur les victoires et les défaites pouvant remonter à la saison 1894-1895 pour certaines équipes.

Dans cet atelier, vous allez créer un prototype de modèle puis l'entraîner et l'évaluer avec BigQuery ML, afin de prédire les équipes victorieuses et perdantes des matchs d'un tournoi de basket-ball de la NCAA.

Objectifs de l'atelier

Dans cet atelier, vous allez apprendre à effectuer les tâches suivantes :

• Accéder à l'ensemble de données public de la NCAA à l'aide de BigQuery
• Explorer l'ensemble de données de la NCAA pour vous familiariser avec le schéma et le champ d'application des données disponibles
• Préparer les données existantes et les convertir en caractéristiques et en étiquettes
• Diviser l'ensemble de données en sous-ensembles destinés à l'entraînement et à l'évaluation
• Utiliser BigQuery ML pour créer un modèle basé sur l'ensemble de données du tournoi de la NCAA
• Prédire le tableau et le vainqueur du tournoi de la NCAA avec le modèle que vous avez créé

Prérequis

Il s'agit d'un atelier de niveau intermédiaire. Pour suivre cet atelier, vous devez connaître le langage et les mots clé SQL. Il est également recommandé de savoir utiliser BigQuery. Si vous n'avez pas l'expérience requise dans ces domaines, suivez au minimum l'un des ateliers ci-dessous avant de vous lancer dans celui-ci :

• Présentation de SQL pour BigQuery et Cloud SQL
• BigQuery : Qwik Start - Console

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Pour réaliser cet atelier :

• vous devez avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;

Remarque : Ouvrez une fenêtre de navigateur en mode incognito/navigation privée pour effectuer cet atelier. Vous éviterez ainsi les conflits entre votre compte personnel et le temporaire étudiant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.

• vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.

Remarque : Si vous possédez déjà votre propre compte ou projet Google Cloud, veillez à ne pas l'utiliser pour réaliser cet atelier afin d'éviter que des frais supplémentaires ne vous soient facturés.

Démarrer l'atelier et se connecter à la console Google Cloud

1. Cliquez sur le bouton Démarrer l'atelier. Si l'atelier est payant, un pop-up s'affiche pour vous permettre de sélectionner un mode de paiement. Sur la gauche, vous trouverez le panneau Détails concernant l'atelier, qui contient les éléments suivants :

   • Le bouton Ouvrir la console Google Cloud
   • Le temps restant
   • Les identifiants temporaires que vous devez utiliser pour cet atelier
   • Des informations complémentaires vous permettant d'effectuer l'atelier

2. Cliquez sur Ouvrir la console Google Cloud (ou effectuez un clic droit et sélectionnez Ouvrir le lien dans la fenêtre de navigation privée si vous utilisez le navigateur Chrome).

   L'atelier lance les ressources, puis ouvre la page Se connecter dans un nouvel onglet.

   Conseil : Réorganisez les onglets dans des fenêtres distinctes, placées côte à côte.

   Remarque : Si la boîte de dialogue Sélectionner un compte s'affiche, cliquez sur Utiliser un autre compte.

3. Si nécessaire, copiez le nom d'utilisateur ci-dessous et collez-le dans la boîte de dialogue Se connecter.

   {{{user_0.username | "Username"}}}

   Vous trouverez également le nom d'utilisateur dans le panneau Détails concernant l'atelier.

4. Cliquez sur Suivant.

5. Copiez le mot de passe ci-dessous et collez-le dans la boîte de dialogue Bienvenue.

   {{{user_0.password | "Password"}}}

   Vous trouverez également le mot de passe dans le panneau Détails concernant l'atelier.

6. Cliquez sur Suivant.

   Important : Vous devez utiliser les identifiants fournis pour l'atelier. Ne saisissez pas ceux de votre compte Google Cloud. Remarque : Si vous utilisez votre propre compte Google Cloud pour cet atelier, des frais supplémentaires peuvent vous être facturés.

7. Accédez aux pages suivantes :

   • Acceptez les conditions d'utilisation.
   • N'ajoutez pas d'options de récupération ni d'authentification à deux facteurs (ce compte est temporaire).
   • Ne vous inscrivez pas à des essais gratuits.

Après quelques instants, la console Cloud s'ouvre dans cet onglet.

Remarque : Pour afficher un menu contenant la liste des produits et services Google Cloud, cliquez sur le menu de navigation en haut à gauche.

Ouvrir la console BigQuery

1. Dans la console Google Cloud, sélectionnez le menu de navigation > BigQuery.

Le message Bienvenue sur BigQuery dans Cloud Console s'affiche. Il contient un lien vers le guide de démarrage rapide et les notes de version.

2. Cliquez sur OK.

La console BigQuery s'ouvre.

Tâche 1 : Ouvrir la console BigQuery

1. Dans la console Cloud, ouvrez le menu de navigation, puis sélectionnez BigQuery.

2. Cliquez sur OK pour accéder à la version bêta de l'UI. Assurez-vous que votre ID de projet est défini dans l'onglet "Explorateur", qui doit ressembler à l'image ci-dessous :

Si vous cliquez sur la flèche "Développer le nœud" à côté de votre projet, vous ne verrez aucune base de données ni aucune table, car vous n'en avez pas ajouté pour le moment.

Par chance, BigQuery propose de nombreux ensembles de données publics et libres d'accès avec lesquels travailler. Nous allons maintenant parler plus en détail de l'ensemble de données de la NCAA, puis voir comment l'ajouter à votre projet BigQuery.

Tâche 2 : Tournoi NCAA March Madness

La NCAA (National Collegiate Athletic Association) organise deux tournois de basket-ball universitaires majeurs tous les ans aux États-Unis, l'un féminin et l'autre masculin. Lors du tournoi masculin de la NCAA en mars, 68 équipes s'affrontent dans des matchs à élimination directe. La dernière équipe est la grande gagnante du championnat March Madness.

La NCAA met à disposition un ensemble de données public qui contient les statistiques des matchs de basket-ball masculin et féminin ainsi que les joueurs et joueuses sélectionnés pour la saison et les tournois finaux. Les données sur les matchs couvrent les statistiques générales et par match depuis 2009, ainsi que les scores finaux depuis 1996. On y trouve des données supplémentaires sur les victoires et les défaites pouvant remonter à la saison 1894-1895 pour certaines équipes.

• Vous pouvez consulter la campagne publicitaire marketing Google Cloud sur la prédiction des insights en temps réel pour en savoir un peu plus sur cet ensemble de données, voir comment il a été utilisé et suivre le tournoi de cette année sur la page Insights March Madness Google Cloud.

Tâche 3 : Trouver l'ensemble de données public de la NCAA dans BigQuery

1. Avant d'effectuer cette étape, vérifiez que vous êtes toujours dans la console BigQuery. Dans l'onglet "Explorateur", cliquez sur le bouton + AJOUTER, puis sélectionnez Ensembles de données publics.

2. Dans la barre de recherche, saisissez NCAA Basketball et appuyez sur Entrée. Lorsque l'ensemble de données apparaît, sélectionnez-le et cliquez sur AFFICHER L'ENSEMBLE DE DONNÉES :

Un nouvel onglet BigQuery présentant l'ensemble de données chargé s'ouvre. Vous pouvez continuer à travailler dans cet onglet, ou bien le fermer et actualiser votre console BigQuery dans l'autre onglet pour afficher votre ensemble de données public.

Remarque : Si l'ensemble de données "ncaa_basketball" n'apparaît pas, cliquez sur + AJOUTER > Ajouter un projet aux favoris en saisissant son nom. Saisissez le nom de projet bigquery-public-data, puis cliquez sur AJOUTER AUX FAVORIS.

3. Développez l'ensemble de données bigquery-public-data > ncaa_basketball pour afficher ses tables :

L'ensemble de données doit comporter dix tables.

4. Cliquez sur mbb_historical_tournament_games puis sur APERÇU pour afficher un échantillon des lignes de données.

5. Cliquez ensuite sur DÉTAILS pour consulter les métadonnées de la table.

La page qui s'affiche doit ressembler à celle-ci :

Tâche 4 : Écrire une requête pour déterminer le nombre de saisons et de matchs disponibles

Vous allez à présent écrire une requête SQL simple pour déterminer le nombre de saisons et de matchs disponibles dans la table mbb_historical_tournament_games.

1. Copiez les commandes suivantes et collez-les dans la zone de saisie de l'éditeur de requête, qui se trouve au-dessus de la section des détails de la table :

SELECT season, COUNT(*) as games_per_tournament FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` GROUP BY season ORDER BY season # default is Ascending (low to high)

2. Cliquez sur EXÉCUTER. Peu après, vous devriez obtenir un résultat semblable à celui-ci :

3. Parcourez les résultats et notez le nombre de saisons ainsi que le nombre de matchs joués par saison. Ces informations vous seront utiles pour répondre aux questions qui suivent. Pour identifier rapidement le nombre de lignes renvoyées, vous pouvez regarder en bas à droite, près des flèches de pagination.

Cliquez sur Vérifier ma progression pour valider l'objectif. Écrire une requête pour déterminer le nombre de saisons et de matchs disponibles

Tester vos connaissances

Répondez aux questions à choix multiples ci-dessous pour réviser les concepts abordés jusqu'ici. Tentez d'y répondre du mieux que vous le pouvez.

Tâche 5 : Comprendre le rôle des caractéristiques et étiquettes en ML

L'objectif de cet atelier est de prédire l'équipe gagnante d'un match de basket-ball masculin de la NCAA en s'appuyant sur les données historiques des matchs. En machine learning, une colonne de données permettant de déterminer un résultat (la victoire ou la défaite pour un match du tournoi) s'appelle une caractéristique.

La colonne comportant les données que vous souhaitez prédire s'appelle l'étiquette. Les modèles de machine learning apprennent à associer les caractéristiques entre elles pour prédire le résultat d'une étiquette.

Exemples de caractéristiques que votre ensemble de données pourrait comporter :

• Saison
• Nom de l'équipe
• Nom de l'équipe adverse
• Place de l'équipe au classement des têtes de série
• Place de l'équipe adverse au classement des têtes de série

L'étiquette que vous essayez de prédire pour les matchs à venir est le résultat du match, c'est-à-dire la victoire ou la défaite d'une équipe.

Tester vos connaissances

Répondez aux questions à choix multiples ci-dessous pour réviser les concepts abordés jusqu'ici. Tentez d'y répondre du mieux que vous le pouvez.

Tâche 6 : Créer un ensemble de données de machine learning étiqueté

Créer un modèle de machine learning nécessite un grand volume de données d'entraînement de bonne qualité. Par chance, l'ensemble de données de la NCAA est assez fourni pour permettre de créer un modèle efficace.

1. Revenez à la console BigQuery. Elle devrait encore afficher les résultats de la requête que vous avez exécutée précédemment.

2. Dans le menu de gauche, ouvrez la table mbb_historical_tournament_games en cliquant sur son nom. Une fois qu'elle est chargée, cliquez sur APERÇU. La page qui s'affiche doit ressembler à celle-ci :

Tester vos connaissances

Répondez aux questions à choix multiples ci-dessous pour réviser les concepts abordés jusqu'ici. Tentez d'y répondre du mieux que vous le pouvez.

3. Après avoir parcouru l'ensemble de données, vous remarquerez qu'une ligne comporte à la fois les colonnes win_market et lose_market. Vous devez diviser le registre unifié des résultats des matchs de manière à séparer les données par équipe, et ainsi attribuer une étiquette "gagnante" ou "perdante" à chaque ligne.

4. Copiez la requête suivante et collez-la dans l'éditeur de requête, puis cliquez sur EXÉCUTER :

# create a row for the winning team SELECT # features season, # ex: 2015 season has March 2016 tournament games round, # sweet 16 days_from_epoch, # how old is the game game_date, day, # Friday 'win' AS label, # our label win_seed AS seed, # ranking win_market AS market, win_name AS name, win_alias AS alias, win_school_ncaa AS school_ncaa, # win_pts AS points, lose_seed AS opponent_seed, # ranking lose_market AS opponent_market, lose_name AS opponent_name, lose_alias AS opponent_alias, lose_school_ncaa AS opponent_school_ncaa # lose_pts AS opponent_points FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` UNION ALL # create a separate row for the losing team SELECT # features season, round, days_from_epoch, game_date, day, 'loss' AS label, # our label lose_seed AS seed, # ranking lose_market AS market, lose_name AS name, lose_alias AS alias, lose_school_ncaa AS school_ncaa, # lose_pts AS points, win_seed AS opponent_seed, # ranking win_market AS opponent_market, win_name AS opponent_name, win_alias AS opponent_alias, win_school_ncaa AS opponent_school_ncaa # win_pts AS opponent_points FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games`

Vous devez obtenir le résultat suivant :

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer un ensemble de données de machine learning étiqueté

Maintenant que vous connaissez les caractéristiques disponibles via les résultats, vous pouvez répondre à la question suivante pour améliorer votre compréhension de l'ensemble de données.

Tâche 7 : Créer un modèle de machine learning pour prédire l'équipe gagnante à partir de la place dans le classement des têtes de série et du nom de l'équipe

Nous avons pris le temps de parcourir les données. Passons maintenant à l'entraînement de notre modèle de machine learning.

• Répondez aux questions suivantes du mieux possible, et utilisez-les pour vous guider dans cette section.

Choisir un type de modèle

Dans cette mise en situation, vous allez créer un modèle de classification. Comme nous avons deux classes, victoire ou défaite, on peut dire qu'il s'agit d'un modèle de classification binaire. Une équipe peut soit gagner un match, soit le perdre.

Lorsque cet atelier sera terminé, vous pourrez effectuer une prévision du nombre de points marqués par une équipe à l'aide d'un modèle de prévision, mais ce n'est pas l'objectif de ce chapitre.

Pour facilement déterminer si vous faites une prévision ou une classification, vous pouvez vérifier le type d'étiquette (colonne) de données que vous êtes en train de prédire :

• Si la colonne contient des valeurs numériques (un nombre d'unités vendues ou de points dans un match, par exemple), vous effectuez une prévision.
• S'il s'agit d'une valeur de chaîne, vous effectuez une classification (cette ligne correspond à une classe ou à l'autre).
• En présence de plus de deux classes (victoire, défaite et match nul, par exemple), vous effectuez une classification à classes multiples.

Notre modèle de classification appliquera le machine learning avec un modèle statistique largement répandu, la régression logistique.

Nous avons besoin d'un modèle capable de générer une probabilité pour chaque valeur d'étiquette distincte possible, une "victoire" ou une "défaite" dans le cas présent. La régression logistique est le type de modèle idéal pour ce cas de figure. Bonne nouvelle : le modèle de ML effectuera automatiquement toutes les opérations mathématiques et optimisations nécessaires pendant l'entraînement. Les ordinateurs sont vraiment efficaces pour cela !

Remarque : De nombreux autres modèles de machine learning plus ou moins complexes permettent d'effectuer des tâches de classification. Google a souvent recours au deep learning via des réseaux de neurones.

Créer un modèle de machine learning avec BigQuery ML

Pour créer notre modèle de classification dans BigQuery, il nous suffit d'écrire l'instruction SQL CREATE MODEL et d'ajouter quelques options.

Cependant, avant de créer le modèle, nous allons commencer par lui trouver un emplacement dans notre projet.

1. Dans l'onglet "Explorateur", cliquez sur l'icône Afficher les actions située à côté de l'ID de votre projet, puis sélectionnez Créer un ensemble de données.

2. La boîte de dialogue "Créer un ensemble de données" s'ouvre. Définissez l'ID de votre ensemble de données sur bracketology, puis cliquez sur CRÉER UN ENSEMBLE DE DONNÉES.

3. Exécutez les commandes suivantes dans l'éditeur de requête :

CREATE OR REPLACE MODEL `bracketology.ncaa_model` OPTIONS ( model_type='logistic_reg') AS # create a row for the winning team SELECT # features season, 'win' AS label, # our label win_seed AS seed, # ranking win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ranking lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` WHERE season <= 2017 UNION ALL # create a separate row for the losing team SELECT # features season, 'loss' AS label, # our label lose_seed AS seed, # ranking lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ranking win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` # now we split our dataset with a WHERE clause so we can train on a subset of data and then evaluate and test the model's performance against a reserved subset so the model doesn't memorize or overfit to the training data. # tournament season information from 1985 - 2017 # here we'll train on 1985 - 2017 and predict for 2018 WHERE season <= 2017

Comme vous pouvez le constater, créer un modèle ne nécessite que quelques lignes de code SQL. En revanche, il est très important de choisir le type de modèle logistic_reg pour notre tâche de classification.

Remarque : Reportez-vous au guide de documentation BigQuery ML pour consulter la liste de toutes les options de modèle et paramètres disponibles. Ici, nous disposons déjà d'un champ nommé "label" (étiquette), nous utilisons donc l'option de modèle input_label_cols pour éviter d'avoir à spécifier notre colonne d'étiquette.

L'entraînement du modèle prend entre 3 et 5 minutes. À la fin du job d'entraînement, vous devez recevoir le résultat suivant :

4. Cliquez sur le bouton Accéder au modèle situé sur le côté droit de la console.

Cliquez sur Vérifier ma progression pour valider l'objectif. Créer un modèle de machine learning

Consulter les détails de l'entraînement du modèle

• Dans la fenêtre des informations du modèle, faites défiler l'affichage jusqu'à la section Options d'entraînement et observez les itérations effectuées par le modèle au cours de l'entraînement.

Les utilisateurs expérimentés en machine learning noteront qu'ils peuvent personnaliser tous ces hyperparamètres (options définies avant l'exécution du modèle) en configurant leur valeur dans l'instruction OPTIONS.

Si vous débutez en machine learning, BigQuery ML attribuera des valeurs par défaut appropriées pour les options que vous n'avez pas définies.

Reportez-vous à la liste des options de modèle de BigQuery ML pour plus d'informations.

Consulter les statistiques de l'entraînement du modèle

Les modèles de machine learning "apprennent" à associer les caractéristiques connues à des étiquettes inconnues. Comme vous l'aurez peut-être déjà remarqué, certaines caractéristiques sont plus utiles que d'autres pour déterminer une victoire ou une défaite. Par exemple, la place au classement des têtes de série et le nom de l'école seront plus pertinentes que le jour de programmation du match.

Lorsqu'ils démarrent leur processus d'entraînement, les modèles de machine learning n'ont pas cette intuition et attribuent généralement une pondération aléatoire à chaque caractéristique.

Au cours du processus d'entraînement, le modèle va optimiser le niveau d'importance accordé à chaque caractéristique. À chaque exécution, il essaye de minimiser la perte de données d'entraînement et la perte de données d'évaluation.

Si vous vous rendez compte que le taux de perte de données d'évaluation final est significativement plus élevé que celui des données d'entraînement, cela signifie que votre modèle est en surapprentissage ou qu'il mémorise vos données d'entraînement au lieu d'apprendre quelles sont les relations généralisables.

Pour voir combien d'entraînements votre modèle a suivis, cliquez sur l'onglet ENTRAÎNEMENT et sélectionnez Table sous l'option "Afficher en tant que".

Au cours de cette exécution spécifique, le modèle a terminé trois itérations d'entraînement en environ 20 secondes. Il est possible que vos statistiques soient différentes de celles-ci.

Voir ce que le modèle a appris au sujet des caractéristiques

Après l'entraînement, vous pourrez déterminer quelles caractéristiques se sont avérées les plus utiles pour votre modèle en analysant les pondérations.

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT category, weight FROM UNNEST(( SELECT category_weights FROM ML.WEIGHTS(MODEL `bracketology.ncaa_model`) WHERE processed_input = 'seed')) # try other features like 'school_ncaa' ORDER BY weight DESC

Le résultat doit se présenter comme suit :

Comme vous pouvez le voir, si une équipe figure parmi les premières (1, 2, 3) ou les dernières (14, 15, 16) têtes de série, le modèle accorde plus d'importance à la caractéristique dans la détermination de la victoire ou de la défaite. En effet, les premières têtes de série sont censées obtenir de bons résultats au cours du tournoi.

L'essence même du machine learning est d'être capable, sans abreuver le modèle d'instructions SQL IF THEN codées en dur, de lui faire comprendre que si (IF) l'équipe est tête de série n° 1, alors (THEN) il faut estimer qu'elle a 80 % de chances supplémentaires de l'emporter. Le machine learning apprend les relations de manière autonome, sans s'appuyer sur une logique ou des règles codées en dur. Consultez la documentation sur les pondérations pour la syntaxe BQML pour plus d'informations.

Tâche 8 : Évaluer les performances du modèle

Pour évaluer les performances de votre modèle, il suffit d'exécuter la commande ML.EVALUATE sur le modèle entraîné.

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT * FROM ML.EVALUATE(MODEL `bracketology.ncaa_model`)

Vous devez obtenir un résultat semblable à celui-ci :

La valeur sera juste à environ 69 %. C'est plus précis que de jouer à pile ou face, mais cela mériterait d'être amélioré.

Remarque : Dans les modèles de classification, la justesse n'est pas la seule métrique de sortie à prendre en compte.

Comme vous avez effectué une régression logistique, vous pouvez évaluer les performances de votre modèle en fonction de toutes les métriques suivantes (sachant que plus la valeur est proche de 1.0, mieux c'est) :

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

Rappel : métrique des modèles de classification qui répond à la question "Parmi toutes les étiquettes positives possibles, combien ont été correctement identifiées par le modèle ?"

Justesse : la justesse correspond à la fraction de prédictions correctes émises 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. Il s'agit de la probabilité qu'un classificateur soit plus confiant dans le fait qu'un exemple positif choisi au hasard soit réellement positif que dans le fait qu'un exemple négatif choisi au hasard soit positif.

Tâche 9 : Générer des prédictions

Votre modèle ayant été entraîné sur des données historiques jusqu'à la saison 2017 incluse (ce qui constituait toutes les données en votre possession), il est maintenant possible de faire des prédictions pour la saison 2018. Votre équipe de science des données vient de vous fournir les résultats du tournoi de l'année 2018, qui se trouvent dans une table distincte de votre ensemble de données d'origine.

Pour générer des prédictions, il suffit d'appeler ML.PREDICT sur un modèle entraîné et de transmettre l'ensemble de données pour lequel vous voulez générer une prédiction.

• Exécutez les commandes suivantes dans l'éditeur de requête :

CREATE OR REPLACE TABLE `bracketology.predictions` AS ( SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model`, # predicting for 2018 tournament games (2017 season) (SELECT * FROM `data-to-insights.ncaa.2018_tournament_results`) ) )

Peu après, vous devriez obtenir un résultat semblable à celui-ci :

Cliquez sur Vérifier ma progression pour valider l'objectif. Évaluer les performances du modèle et créer une table

Remarque : Vos prédictions sont conservées dans une table pour que vous puissiez récupérer les insights par la suite sans avoir à exécuter à nouveau la requête ci-dessus.

Votre ensemble de données d'origine comporte à présent trois colonnes supplémentaires :

• Étiquette prédite
• Options des étiquettes prédites
• Probabilité des étiquettes prédites

Comme vous connaissez les résultats du tournoi March Madness 2018, vous allez rapidement voir si les prédictions du modèle étaient justes. (Conseil : Si vous voulez générer des prédictions pour le tournoi March Madness de cette année, utilisez un ensemble de données contenant le classement des têtes de série et le nom des équipes de l'année 2019. Bien entendu, la colonne d'étiquette sera vide, puisque les matchs concernés n'ont pas encore eu lieu. C'est là qu'intervient la prédiction !)

Tâche 10 : Quel a été le niveau de réussite du modèle pour le tournoi 2018 de la NCAA ?

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT * FROM `bracketology.predictions` WHERE predicted_label <> label

Vous devez obtenir un résultat semblable à celui-ci :

Sur 134 prédictions (67 matchs de tournoi), notre modèle s'est trompé 38 fois. Il obtient un score global de 70 % concernant le tournoi 2018.

Tâche 11 : Les modèles ne font pas de miracles...

Beaucoup d'autres facteurs et caractéristiques entrent en ligne de compte dans les victoires très serrées et les défaites surprises du tournoi March Madness, ce qui en fait un événement très difficile à prédire pour les modèles.

Essayons de trouver les résultats les plus surprenants du tournoi 2017, d'après le modèle. Nous allons chercher des prédictions pour lesquelles le niveau de confiance du modèle était supérieur à 80 %, mais qui se sont révélées ERRONÉES.

1. Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT model.label AS predicted_label, model.prob AS confidence, predictions.label AS correct_label, game_date, round, seed, school_ncaa, points, opponent_seed, opponent_school_ncaa, opponent_points FROM `bracketology.predictions` AS predictions, UNNEST(predicted_label_probs) AS model WHERE model.prob > .8 AND predicted_label <> predictions.label

Vous devriez obtenir un résultat semblable à celui-ci :

Prédiction : le modèle prédit la victoire de l'équipe Virginia (tête de série n° 1) contre UMBC (tête de série n° 16) avec un niveau de confiance de 87 %. Cette prédiction semble logique. Et pourtant...

Regardez la vidéo sur la victoire miraculeuse de la tête de série n° 16 (UMBC) contre la tête de série n° 1 (Virginia) pour découvrir ce qui s'est réellement passé.

À la fin du match, Barry Odom, entraîneur de l'UMBC, a déclaré : "Incroyable, c'est tout ce que je peux dire." Pour en savoir plus, consultez l'article sur le match de basket-ball masculin du tournoi 2018 entre l'UMBC et l'équipe de Virginia.

Récapitulatif

• Vous avez créé un modèle de machine learning servant à prédire les résultats d'un match.
• Vous avez évalué ses performances et avez obtenu une justesse de 69 % en utilisant le nom des équipes et leur place au classement des têtes de série comme caractéristiques principales.
• Vous avez prédit les résultats d'un tournoi de 2018.
• Vous avez analysé les résultats pour en tirer des informations.

Notre prochain défi sera de créer un modèle plus performant SANS utiliser les caractéristiques basées sur le nom des équipes et leur place dans le classement des têtes de série.

Tâche 12 : Utiliser des caractéristiques de modèle de ML plus pertinentes

Dans la deuxième partie de cet atelier, vous allez créer un autre modèle de ML en utilisant de nouvelles caractéristiques plus détaillées.

Vous savez désormais comment créer des modèles de ML avec BigQuery ML. Votre équipe de science des données vous a fourni un nouvel ensemble de données fournissant des détails sur chaque match, qui contient de nouvelles statistiques d'équipes que votre modèle pourra apprendre. Exemples :

• L'efficacité à marquer des paniers dans la durée, d'après une analyse historique réalisée pour chaque match
• La possession du ballon dans la durée

Créer un nouvel ensemble de données de ML avec ces caractéristiques avancées

• Exécutez les commandes suivantes dans l'éditeur de requête :

# create training dataset: # create a row for the winning team CREATE OR REPLACE TABLE `bracketology.training_new_features` AS WITH outcomes AS ( SELECT # features season, # 1994 'win' AS label, # our label win_seed AS seed, # ranking # this time without seed even win_school_ncaa AS school_ncaa, lose_seed AS opponent_seed, # ranking lose_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # create a separate row for the losing team SELECT # features season, # 1994 'loss' AS label, # our label lose_seed AS seed, # ranking lose_school_ncaa AS school_ncaa, win_seed AS opponent_seed, # ranking win_school_ncaa AS opponent_school_ncaa FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` t WHERE season >= 2014 UNION ALL # add in 2018 tournament game results not part of the public dataset: SELECT season, label, seed, school_ncaa, opponent_seed, opponent_school_ncaa FROM `data-to-insights.ncaa.2018_tournament_results` ) SELECT o.season, label, # our team seed, school_ncaa, # new pace metrics (basketball possession) team.pace_rank, team.poss_40min, team.pace_rating, # new efficiency metrics (scoring over time) team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # opposing team opponent_seed, opponent_school_ncaa, # new pace metrics (basketball possession) opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # new efficiency metrics (scoring over time) opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # a little feature engineering (take the difference in stats) # new pace metrics (basketball possession) opp.pace_rank - team.pace_rank AS pace_rank_diff, opp.poss_40min - team.poss_40min AS pace_stat_diff, opp.pace_rating - team.pace_rating AS pace_rating_diff, # new efficiency metrics (scoring over time) opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff, opp.pts_100poss - team.pts_100poss AS eff_stat_diff, opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff FROM outcomes AS o LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS team ON o.school_ncaa = team.team AND o.season = team.season LEFT JOIN `data-to-insights.ncaa.feature_engineering` AS opp ON o.opponent_school_ncaa = opp.team AND o.season = opp.season

Peu après, vous devriez obtenir un résultat semblable à celui-ci :

Cliquez sur Vérifier ma progression pour valider l'objectif. Utiliser des caractéristiques de modèle de ML plus pertinentes

Tâche 13 : Prévisualiser les nouvelles caractéristiques

• Cliquez sur le bouton Accéder à la table sur le côté droit de la console. Cliquez sur l'onglet Aperçu.

La table doit ressembler à celle-ci :

Si vous n'obtenez pas le même résultat que sur la capture d'écran ci-dessus, ne vous inquiétez pas.

Tâche 14 : Interpréter les métriques sélectionnées

• Vous allez maintenant découvrir des étiquettes importantes qui peuvent être très utiles pour effectuer des prédictions.

opp_efficiency_rank

Classement de l'efficacité de l'adversaire : parmi toutes les équipes, quel est le classement de notre adversaire par rapport au nombre de paniers marqués dans la durée (points par 100 possessions du ballon) ? Une valeur faible est préférable.

opp_pace_rank

Classement du rythme de l'adversaire : parmi toutes les équipes, quel est le classement de l'adversaire pour la possession du ballon (nombre de possessions en 40 minutes) ? Une valeur faible est préférable.

Vous disposez dorénavant de caractéristiques pertinentes sur l'efficacité des équipes en termes de paniers marqués et de capacité à rester en possession du ballon. Nous allons les utiliser pour entraîner notre deuxième modèle.

Pour éviter que ce nouveau modèle "se souvienne des bonnes équipes", effacez les noms des équipes et leur place au classement des têtes de série des caractéristiques pour travailler uniquement avec les nouvelles métriques.

Tâche 15 : Entraîner le nouveau modèle

• Exécutez les commandes suivantes dans l'éditeur de requête :

CREATE OR REPLACE MODEL `bracketology.ncaa_model_updated` OPTIONS ( model_type='logistic_reg') AS SELECT # this time, don't train the model on school name or seed season, label, # our pace poss_40min, pace_rank, pace_rating, # opponent pace opp_poss_40min, opp_pace_rank, opp_pace_rating, # difference in pace pace_rank_diff, pace_stat_diff, pace_rating_diff, # our efficiency pts_100poss, efficiency_rank, efficiency_rating, # opponent efficiency opp_pts_100poss, opp_efficiency_rank, opp_efficiency_rating, # difference in efficiency eff_rank_diff, eff_stat_diff, eff_rating_diff FROM `bracketology.training_new_features` # here we'll train on 2014 - 2017 and predict on 2018 WHERE season BETWEEN 2014 AND 2017 # between in SQL is inclusive of end points

Peu après, vous devriez obtenir un résultat semblable à ceci :

Tâche 16 : Évaluer les performances du nouveau modèle

• Pour évaluer les performances de votre modèle, exécutez la commande suivante dans l'éditeur de requête :

SELECT * FROM ML.EVALUATE(MODEL `bracketology.ncaa_model_updated`)

Vous devriez obtenir un résultat semblable à ceci :

Félicitations ! Vous venez d'entraîner un modèle avec des caractéristiques différentes et avez fait augmenter sa justesse à environ 75 %, soit une hausse de 5 % par rapport au modèle d'origine.

Il est indispensable de comprendre qu'avec le machine learning, la qualité des caractéristiques de l'ensemble de données a une influence majeure sur la justesse du modèle.

Cliquez sur Vérifier ma progression pour valider l'objectif. Entraîner le nouveau modèle et effectuer l'évaluation

Tâche 17 : Identifier ce que le modèle a appris

• Quelles sont les caractéristiques auxquelles le modèle accorde le plus d'importance pour prédire une victoire ou une défaite ? Pour le savoir, exécutez la commande suivante dans l'éditeur de requête :

SELECT * FROM ML.WEIGHTS(MODEL `bracketology.ncaa_model_updated`) ORDER BY ABS(weight) DESC

Vous devez normalement obtenir le résultat suivant :

Nous avons utilisé la valeur absolue des pondérations pour les classer, de sorte à les faire apparaître par ordre d'importance (pour la détermination d'une victoire ou d'une défaite).

Comme vous pouvez le constater dans les résultats, les trois premières sont pace_stat_diff, eff_stat_diff et eff_rating_diff. Prenons le temps de mieux les comprendre.

pace_stat_diff

Différence entre les statistiques (possessions/40 minutes) des deux équipes. D'après ce modèle, il s'agit du facteur le plus important pour déterminer l'issue du match.

eff_stat_diff

Différence entre les statistiques (points nets/100 possessions) des deux équipes.

eff_rating_diff

Différence entre les classements normalisés portant sur l'efficacité à marquer des paniers pour les deux équipes.

Quelles caractéristiques le modèle a-t-il négligées dans ses prédictions ? Saison. Il s'agissait du dernier élément de la liste des pondérations énumérées ci-dessus. Ce modèle met en avant le fait que la saison (2013, 2014, 2015) n'est pas un élément utile dans la prédiction de l'issue du match. L'année 2014 n'avait rien de particulièrement magique pour les équipes.

Il est intéressant de voir que le modèle a accordé plus d'importance à la dynamique d'une équipe (sa tendance à conserver la possession du ballon) qu'à son efficacité à marquer.

Tâche 18 : Passons aux prédictions !

• Exécutez les commandes suivantes dans l'éditeur de requête :

CREATE OR REPLACE TABLE `bracketology.ncaa_2018_predictions` AS # let's add back our other data columns for context SELECT * FROM ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( SELECT * # include all columns now (the model has already been trained) FROM `bracketology.training_new_features` WHERE season = 2018 ))

Vous devriez obtenir un résultat semblable à celui-ci :

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter une requête pour créer la table "ncaa_2018_predictions"

Tâche 19 : Analyse des prédictions

Comme vous connaissez le résultat réel des matchs, vous pouvez facilement identifier les prédictions incorrectes en vous appuyant sur le nouvel ensemble de données de test.

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT * FROM `bracketology.ncaa_2018_predictions` WHERE predicted_label <> label

Vous voyez dans le nombre d'enregistrements renvoyés par la requête que le modèle s'est trompé à 48 reprises (24 matchs) sur le nombre total d'associations possibles pour le tournoi de 2018, ce qui donne une justesse de 64 %. Découvrons les surprises qui ont fait de 2018 une année mémorable.

Tâche 20 : Quelles ont été les surprises de March Madness 2018 ?

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT CONCAT(school_ncaa, " was predicted to ",IF(predicted_label="loss","lose","win")," ",CAST(ROUND(p.prob,2)*100 AS STRING), "% but ", IF(n.label="loss","lost","won")) AS narrative, predicted_label, # what the model thought n.label, # what actually happened ROUND(p.prob,2) AS probability, season, # us seed, school_ncaa, pace_rank, efficiency_rank, # them opponent_seed, opponent_school_ncaa, opp_pace_rank, opp_efficiency_rank FROM `bracketology.ncaa_2018_predictions` AS n, UNNEST(predicted_label_probs) AS p WHERE predicted_label <> n.label # model got it wrong AND p.prob > .75 # by more than 75% confidence ORDER BY prob DESC

Vous devriez obtenir le résultat suivant :

La plus grande surprise est celle que notre modèle précédent avait déjà trouvée : le match entre UMBC et Virginia. Pour découvrir l'ampleur des bouleversements de la hiérarchie auxquels nous avons assisté lors du tournoi 2018, consultez l'article Has This Been the “Maddest” March?. L'année 2019 nous réserve-t-elle autant de surprises ?

Tâche 21 : Comparer les performances des modèles

Que peut-on dire des cas où le modèle plus simpliste (avec comparaison des têtes de série) s'est trompé tandis que le modèle avancé a effectué une prédiction juste ?

• Exécutez les commandes suivantes dans l'éditeur de requête :

SELECT CONCAT(opponent_school_ncaa, " (", opponent_seed, ") was ",CAST(ROUND(ROUND(p.prob,2)*100,2) AS STRING),"% predicted to upset ", school_ncaa, " (", seed, ") and did!") AS narrative, predicted_label, # what the model thought n.label, # what actually happened ROUND(p.prob,2) AS probability, season, # us seed, school_ncaa, pace_rank, efficiency_rank, # them opponent_seed, opponent_school_ncaa, opp_pace_rank, opp_efficiency_rank, (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) AS seed_diff FROM `bracketology.ncaa_2018_predictions` AS n, UNNEST(predicted_label_probs) AS p WHERE predicted_label = 'loss' AND predicted_label = n.label # model got it right AND p.prob >= .55 # by 55%+ confidence AND (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) > 2 # seed difference magnitude ORDER BY (CAST(opponent_seed AS INT64) - CAST(seed AS INT64)) DESC

Vous devriez obtenir le résultat suivant :

Le modèle a prédit une victoire surprise des Florida St. (09) dans le match qui les a opposés à Xavier (01), et c'est bien ce qui s'est produit !

Ce résultat inattendu a été correctement prédit par le nouveau modèle qui s'est basé sur les nouvelles caractéristiques plus avancées, comme la dynamique et l'efficacité à marquer (alors même que la place au classement des têtes de série suggérait l'inverse). Regardez les moments forts du match sur YouTube.

Tâche 22 : Prédictions pour le tournoi March Madness 2019

Nous connaissons maintenant les équipes et les têtes de série pour mars 2019, ce qui nous permet de prédire le résultat des matchs à venir.

Explorer les données de 2019

• Exécutez la requête suivante pour identifier les meilleures têtes de série :

SELECT * FROM `data-to-insights.ncaa.2019_tournament_seeds` WHERE seed = 1

Vous devriez obtenir le résultat suivant :

Créer une matrice comportant tous les matchs possibles

Étant donné que nous ignorons quelles équipes s'affronteront durant le tournoi, nous allons toutes les opposer les unes aux autres.

Dans SQL, la fonction de jointure croisée (CROSS JOIN) permet de représenter le fait que chaque équipe mentionnée dans la table affronte toutes les autres.

• Exécutez la requête ci-dessous pour créer tous les cas de figure pouvant se présenter pendant le tournoi.

SELECT NULL AS label, team.school_ncaa AS team_school_ncaa, team.seed AS team_seed, opp.school_ncaa AS opp_school_ncaa, opp.seed AS opp_seed FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp # teams cannot play against themselves :) WHERE team.school_ncaa <> opp.school_ncaa

Ajouter les statistiques d'équipes de 2018 (dynamique, efficacité)

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament` AS WITH team_seeds_all_possible_games AS ( SELECT NULL AS label, team.school_ncaa AS school_ncaa, team.seed AS seed, opp.school_ncaa AS opponent_school_ncaa, opp.seed AS opponent_seed FROM `data-to-insights.ncaa.2019_tournament_seeds` AS team CROSS JOIN `data-to-insights.ncaa.2019_tournament_seeds` AS opp # teams cannot play against themselves :) WHERE team.school_ncaa <> opp.school_ncaa ) , add_in_2018_season_stats AS ( SELECT team_seeds_all_possible_games.*, # bring in features from the 2018 regular season for each team (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE school_ncaa = team AND season = 2018) AS team, (SELECT AS STRUCT * FROM `data-to-insights.ncaa.feature_engineering` WHERE opponent_school_ncaa = team AND season = 2018) AS opp FROM team_seeds_all_possible_games ) # Preparing 2019 data for prediction SELECT label, 2019 AS season, # 2018-2019 tournament season # our team seed, school_ncaa, # new pace metrics (basketball possession) team.pace_rank, team.poss_40min, team.pace_rating, # new efficiency metrics (scoring over time) team.efficiency_rank, team.pts_100poss, team.efficiency_rating, # opposing team opponent_seed, opponent_school_ncaa, # new pace metrics (basketball possession) opp.pace_rank AS opp_pace_rank, opp.poss_40min AS opp_poss_40min, opp.pace_rating AS opp_pace_rating, # new efficiency metrics (scoring over time) opp.efficiency_rank AS opp_efficiency_rank, opp.pts_100poss AS opp_pts_100poss, opp.efficiency_rating AS opp_efficiency_rating, # a little feature engineering (take the difference in stats) # new pace metrics (basketball possession) opp.pace_rank - team.pace_rank AS pace_rank_diff, opp.poss_40min - team.poss_40min AS pace_stat_diff, opp.pace_rating - team.pace_rating AS pace_rating_diff, # new efficiency metrics (scoring over time) opp.efficiency_rank - team.efficiency_rank AS eff_rank_diff, opp.pts_100poss - team.pts_100poss AS eff_stat_diff, opp.efficiency_rating - team.efficiency_rating AS eff_rating_diff FROM add_in_2018_season_stats

Générer des prédictions

CREATE OR REPLACE TABLE `bracketology.ncaa_2019_tournament_predictions` AS SELECT * FROM # let's predicted using the newer model ML.PREDICT(MODEL `bracketology.ncaa_model_updated`, ( # let's predict on March 2019 tournament games: SELECT * FROM `bracketology.ncaa_2019_tournament` ))

Cliquez sur Vérifier ma progression pour valider l'objectif. Exécuter des requêtes pour créer les tables "ncaa_2019_tournament" et "ncaa_2019_tournament_predictions"

Obtenir vos prédictions

SELECT p.label AS prediction, ROUND(p.prob,3) AS confidence, school_ncaa, seed, opponent_school_ncaa, opponent_seed FROM `bracketology.ncaa_2019_tournament_predictions`, UNNEST(predicted_label_probs) AS p WHERE p.prob >= .5 AND school_ncaa = 'Duke' ORDER BY seed, opponent_seed

• Ici, nous avons filtré les résultats du modèle pour afficher tous les matchs possibles pour l'équipe de Duke. Faites défiler la page pour trouver le match opposant Duke à North Dakota St.

Statistique : Duke (1) est 88,5 % plus susceptible de gagner que North Dakota St. (16) le 22 mars 2019.

Essayez de modifier le filtre school_ncaa au-dessus pour prédire les matchs de votre tableau. Notez le niveau de confiance du modèle et profitez des matchs !

Félicitations !

Vous avez utilisé BigQuery ML pour prédire quelles seraient les équipes victorieuses lors du tournoi de basket-ball masculin de la NCAA.

Étapes suivantes et informations supplémentaires

• Vous voulez en savoir plus sur les statistiques et l'analyse des matchs de basket-ball ? Consultez d'autres analyses réalisées par l'équipe à l'origine des publicités et des prédictions Google Cloud sur le tournoi de la NCAA.
• Découvrez l'atelier suivant :
  • Explorer les données NCAA à l'aide de BigQuery

Formations et certifications Google Cloud

Les formations et certifications Google Cloud vous aident à tirer pleinement parti des technologies Google Cloud. Nos cours portent sur les compétences techniques et les bonnes pratiques à suivre pour être rapidement opérationnel et poursuivre votre apprentissage. Nous proposons des formations pour tous les niveaux, à la demande, en salle et à distance, pour nous adapter aux emplois du temps de chacun. Les certifications vous permettent de valider et de démontrer vos compétences et votre expérience en matière de technologies Google Cloud.

Dernière mise à jour du manuel : 19 mars 2024

Dernier test de l'atelier : 19 mars 2024

Copyright 2024 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits peuvent être des marques des entreprises auxquelles ils sont associés.