Présentation

Le réglage des performances de BigQuery a généralement pour but de réduire les temps d'exécution ou le coût des requêtes. Lors de cet atelier, nous verrons plusieurs exemples d'optimisations de performances qui pourraient vous être utiles dans votre cas d'utilisation. Le réglage des performances ne doit être effectué qu'à la fin de la phase de développement, et seulement si l'on constate que les requêtes courantes prennent trop de temps pour s'exécuter.

Il est de loin préférable d'avoir des schémas de table flexibles et des requêtes intéressantes, lisibles et faciles à entretenir, plutôt que de les alourdir uniquement pour accroître légèrement la performance. Dans certains cas cependant, vous devrez améliorer la performance de vos requêtes, parfois parce qu'elles sont exécutées si souvent que de petites améliorations font toute la différence. De plus, connaître les compromis en termes de performances peut vous aider à faire le choix entre différents modèles.

Objectifs

Dans cet atelier, vous découvrirez les techniques suivantes pour réduire les coûts et les délais d'exécution de BigQuery :

• Réduire les E/S
• Mettre en cache les résultats des requêtes précédentes
• Effectuer des jointures efficaces
• Éviter de surcharger les nœuds de calcul uniques
• Utiliser des fonctions d'agrégation approximatives

Préparation

Pour chaque atelier, nous vous attribuons un nouveau projet Google Cloud et un nouvel ensemble de ressources pour une durée déterminée, sans frais.

1. Connectez-vous à Qwiklabs dans une fenêtre de navigation privée.

2. Vérifiez le temps imparti pour l'atelier (par exemple : 01:15:00) : vous devez pouvoir le terminer dans ce délai.
   Une fois l'atelier lancé, vous ne pouvez pas le mettre en pause. Si nécessaire, vous pourrez le redémarrer, mais vous devrez tout reprendre depuis le début.

3. Lorsque vous êtes prêt, cliquez sur Démarrer l'atelier.

4. Notez vos identifiants pour l'atelier (Nom d'utilisateur et Mot de passe). Ils vous serviront à vous connecter à Google Cloud Console.

5. Cliquez sur Ouvrir la console Google.

6. Cliquez sur Utiliser un autre compte, puis copiez-collez les identifiants de cet atelier lorsque vous y êtes invité.
   Si vous utilisez d'autres identifiants, des messages d'erreur s'afficheront ou des frais seront appliqués.

7. Acceptez les conditions d'utilisation et ignorez la page concernant les ressources de récupération des données.

Ouvrir la console BigQuery

1. Dans Google Cloud Console, sélectionnez le menu de navigation > BigQuery :

Le message Welcome to BigQuery in the Cloud Console (Bienvenue sur BigQuery dans Cloud Console) s'affiche. Il contient un lien vers le guide de démarrage rapide et répertorie les mises à jour de l'interface utilisateur.

2. Cliquez sur Done (OK).

Tâche 1 : Réduire les E/S

Une requête qui calcule la somme de trois colonnes sera plus lente qu'une requête qui calcule la somme de deux colonnes, mais la principale différence de performance sera due au plus grand nombre de données à lire et non aux additions supplémentaires. Par conséquent, une requête qui calcule la moyenne d'une colonne sera presque aussi rapide qu'une requête dont la méthode d'agrégation consiste à calculer la variance des données (même si le calcul de la variance exige que BigQuery garde une trace à la fois de la somme et de la somme des carrés), car la surcharge des requêtes simples est le plus souvent causée par les E/S, et non par le calcul.

Utiliser SELECT de façon pertinente

Comme BigQuery utilise des formats de fichiers en colonnes, moins il y a de colonnes lues dans un SELECT, moins il y a de données à lire. C'est d'autant plus vrai pour les SELECT *, dont l'exécution implique de lire chaque colonne de chaque ligne de la table, ce qui est assez lent et coûteux.

Il y a exception lorsque vous utilisez un SELECT * dans une sous-requête, puis que vous faites référence à seulement quelques champs dans une requête externe. L'optimiseur BigQuery est alors assez intelligent pour ne lire que les colonnes qui sont absolument nécessaires.

1. Exécutez la requête suivante dans la fenêtre de l'éditeur BigQuery :

SELECT bike_id, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

Dans la fenêtre Résultats de la requête, vous remarquerez que la requête s'est terminée en ~1,2 seconde et a traité ~372 Mo de données.

2. Exécutez la requête suivante dans la fenêtre de l'éditeur BigQuery :

SELECT * FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

Dans la fenêtre Résultats de la requête, vous remarquerez que la requête s'est terminée en ~4,5 secondes et a traité ~2,6 Go de données. C'est bien plus long.

Si vous avez besoin de presque toutes les colonnes d'une table, pensez à utiliser SELECT * EXCEPT afin de ne pas lire celles dont vous n'avez pas besoin.

Remarque : BigQuery met en cache les résultats de requête pour accélérer les requêtes répétées. Pour connaître les performances de traitement des requêtes réelles, désactivez cette mise en cache en cliquant sur Plus -> Paramètres de requête et en décochant l'option Utiliser les résultats mis en cache.

Réduire les données lues

Lors du réglage d'une requête, il est important de commencer par les données qui sont lues et de voir dans quelle mesure elles peuvent être réduites. Supposons que nous souhaitons connaître la durée moyenne des locations en aller simple les plus courantes.

1. Exécutez la requête suivante dans la fenêtre de l'éditeur BigQuery :

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

2. Cliquez sur l'onglet Détails de l'exécution de la fenêtre Résultats de la requête.

Les détails de la requête indiquent que le tri (pour les quantiles approximatifs de chaque paire de stations) a nécessité une répartition des résultats de l'étape d'entrée, mais c'est le calcul qui prend le plus de temps.

3. En filtrant et en regroupant les données par nom de station plutôt que par ID de station, nous pouvons réduire les frais généraux liés aux E/S de la requête, puisqu'il y aura moins de colonnes à lire. Exécutez la requête suivante :

SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_name != end_station_name GROUP BY start_station_name, end_station_name ORDER BY num_trips DESC LIMIT 10

La requête ci-dessus évite d'avoir à lire les deux colonnes d'ID et se termine en 10,8 secondes environ. Ce gain de temps s'explique par le plus petit nombre de données à lire.

Le résultat de la requête reste identique puisque le nom de station et l'ID de station ont une relation 1:1.

Réduire le nombre de calculs coûteux

Supposons que nous souhaitons connaître la distance totale parcourue par chaque vélo dans notre ensemble de données.

1. Une manière simple de le faire est d'additionner la distance de chaque trajet en vélo :

WITH trip_distance AS ( SELECT bike_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e WHERE start_station_id = s.id AND end_station_id = e.id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

La requête ci-dessus prend environ 9,8 secondes (soit à peu près 55 secondes de durée d'utilisation des emplacements) et brasse près de 1,22 Mo. Le résultat révèle que certains vélos ont parcouru près de 6 000 kilomètres.

2. Le calcul de la distance est une opération assez coûteuse. Nous pouvons éviter de joindre la table cycle_stations à la table cycle_hire table si nous précalculons les distances entre toutes les paires de stations à l'aide de la commande suivante :

WITH stations AS ( SELECT s.id AS start_id, e.id AS end_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e ), trip_distance AS ( SELECT bike_id, distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, stations WHERE start_station_id = start_id AND end_station_id = end_id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

Cette requête effectue seulement 600 000 calculs de distance géographique, contre 24 millions précédemment. La durée d'utilisation des emplacements est cette fois à peu près de 31,5 secondes (30 % plus rapide), malgré le brassage d'environ 33,05 Mo de données.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Réduire les E/S

Tâche 2 : Mettre en cache les résultats des requêtes précédentes

Le service BigQuery met automatiquement en cache les résultats des requêtes dans une table temporaire. Si la même requête est envoyée dans un délai de 24 heures, les résultats sont pris de cette table sans aucun recalcul. Les résultats mis en cache sont extrêmement rapides et n'engendrent pas de frais.

Il y a cependant quelques points à noter. La mise en cache des requêtes est basée sur une comparaison stricte des chaînes de caractères. Ainsi, même les espaces blancs peuvent entraîner un défaut de cache. Les requêtes ne sont jamais mises en cache dans les cas suivants : les requêtes ont un comportement non déterministe (par exemple, elles utilisent CURRENT_TIMESTAMP ou RAND) ; la table ou la vue interrogée a changé (même si les colonnes/lignes concernées par la requête restent inchangées) ; la table est associée à un tampon de flux (même s'il n'y a pas de nouvelles lignes) ; la requête utilise des instructions LMD ou interroge des sources de données externes.

Mettre en cache les résultats intermédiaires

Il est possible d'améliorer les performances globales sans augmenter le nombre d'I/O, grâce aux tables temporaires et aux vues matérialisées.

Par exemple, supposons que vous ayez un certain nombre de requêtes qui commencent par calculer la durée moyenne des trajets entre deux stations. La clause WITH (également appelée expression de table commune) améliore la lisibilité mais pas la vitesse ou le coût des requêtes puisque les résultats ne sont pas mis en cache. Il en va de même pour les vues et les sous-requêtes. Si vous utilisez souvent une clause WITH, une vue ou une sous-requête, vous pouvez améliorer les performances en stockant le résultat dans une table ou une vue matérialisée.

1. Vous devrez d'abord créer un ensemble de données appelé mydataset dans la région UE (plusieurs régions dans l'Union européenne) d'où les données des vélos proviennent, sous votre projet dans BigQuery.

• Dans le volet gauche de la section Explorateur, cliquez sur l'icône Afficher les actions (trois points) en regard de votre projet BigQuery (qwiklabs-gcp-xxxx) et sélectionnez Créer un ensemble de données.

Dans la boîte de dialogue Créer un ensemble de données :

• Définissez l'ID de l'ensemble de données sur mydataset.

• Définissez le Type d'emplacement sur EU (plusieurs régions dans l'Union européenne).

• Conservez les valeurs par défaut des autres options.

• Pour terminer, cliquez sur le bouton bleu Créer l'ensemble de données.

• Vous pouvez maintenant exécuter la requête suivante :

  CREATE OR REPLACE TABLE mydataset.typical_trip AS SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name

2. Utilisez la table créée pour trouver les jours où les trajets à vélo sont beaucoup plus longs que d'habitude :

SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[OFFSET(5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN mydataset.typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

3. Utilisez la clause WITH pour trouver les jours où les trajets à vélo sont beaucoup plus longs que d'habitude :

WITH typical_trip AS ( SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name ) SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[ OFFSET (5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

Remarquez que la vitesse augmente d'environ 50 % en raison de l'absence de calcul de la durée moyenne du trajet. Les deux requêtes renvoient le même résultat, à savoir que les trajets le jour de Noël prennent plus de temps que d'habitude. La table mydataset.typical_trip n'est pas actualisée lorsque des données sont ajoutées dans la table cycle_hire.

Vous pouvez résoudre ce problème d'actualisation des données en utilisant une vue matérialisée ou en programmant des requêtes pour mettre régulièrement la table à jour. Vous devez évaluer le coût de ces mises à jour pour déterminer si l'amélioration des performances des requêtes compense le coût supplémentaire de la mise à jour de la table ou de la vue matérialisée.

Accélérer les requêtes avec BI Engine

Si vous accédez fréquemment à certaines tables dans des environnements d'informatique décisionnelle (BI), comme des tableaux de bord avec des agrégations et des filtres, vous pouvez accélérer vos requêtes grâce à BI Engine. Il gardera automatiquement en mémoire les données pertinentes (soit des colonnes de la table, soit des résultats dérivés), et utilisera un processeur de requêtes spécialisé, réglé pour traiter des données principalement en mémoire. Sous BI Engine, vous pouvez réserver la quantité de mémoire (maximum actuel de 10 Go) que BigQuery doit utiliser pour son cache à partir de la console d'administration BigQuery.

Réservez cette mémoire dans la même région que l'ensemble de données que vous interrogez. Ensuite, BigQuery met en cache les tables, les parties de tables et les agrégations en mémoire, et renvoie plus rapidement les résultats.

Un des cas d'utilisation principaux de BI Engine concerne les tables accessibles depuis des outils de tableaux de bord tels que Google Data Studio. Allouer de la mémoire pour une réservation BI Engine permet de rendre les tableaux de bord qui s'appuient sur un backend BigQuery beaucoup plus réactifs.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Mettre en cache les résultats des requêtes précédentes

Tâche 3 : Jointures efficaces

Soumise aux limitations imposées par la largeur de la bande passante entre les emplacements, la jointure de deux tables nécessite également une coordination des données. S'il est possible d'éviter une jointure ou de réduire la quantité de données jointes, n'hésitez pas à le faire.

Dénormalisation

Une manière d'améliorer les performances de lecture et d'éviter les jointures est de renoncer à un stockage efficace des données et d'augmenter le nombre de copies redondantes des données. Ce processus s'appelle la dénormalisation.

• Ainsi, au lieu de stocker les latitudes et longitudes des stations de vélos séparément des informations sur les locations de vélos, nous pouvons créer une table dénormalisée :

  CREATE OR REPLACE TABLE mydataset.london_bicycles_denorm AS SELECT start_station_id, s.latitude AS start_latitude, s.longitude AS start_longitude, end_station_id, e.latitude AS end_latitude, e.longitude AS end_longitude FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s ON h.start_station_id = s.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS e ON h.end_station_id = e.id

  Nous n'avons alors plus besoin d'effectuer de jointure dans les requêtes suivantes, car la table contient les données d'emplacement de tous les trajets.

  Dans ce cas, le stockage et la lecture des données supplémentaires remplacent les frais de calcul d'une jointure. Il est tout à fait possible que le coût de la lecture d'un plus grand nombre de données sur le disque soit supérieur au coût de la jointure. Vous devez évaluer si la dénormalisation présente des avantages en termes de performances.

  Cliquez sur Vérifier ma progression pour valider l'objectif.

  Dénormalisation

Éviter les autojointures de grandes tables

Les autojointures se produisent lorsqu'une table est jointe à elle-même. Bien que BigQuery accepte les autojointures, celles-ci peuvent entraîner une baisse des performances si la table jointe avec elle-même est très grande. Dans bien des cas, vous pouvez éviter l'autojointure grâce aux fonctionnalités SQL telles que l'agrégation et le fenêtrage.

Voyons un exemple : Les prénoms de bébé publiés par l'Administration de la sécurité sociale des États-Unis constituent l'un des ensembles de données publics de BigQuery.

1. Vous pouvez également interroger cet ensemble des données pour découvrir quels sont les prénoms masculins les plus couramment attribués en 2015 dans l'État du Massachusetts. Veillez à ce que votre requête s'exécute dans la région US (plusieurs régions aux États-Unis). Cliquez sur +Créer une requête SQL, puis sélectionnez Plus > Paramètres de requête > Options avancées, décochez Sélection automatique de l'emplacement, sélectionnez Multirégional, puis US (plusieurs régions aux États-Unis), ou quittez la requête pour utiliser la Sélection automatique de l'emplacement et cliquez sur Enregistrer :

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

2. Interrogez également cet ensemble des données pour découvrir quels sont les prénoms féminins les plus couramment attribués en 2015 dans l'État du Massachusetts :

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

3. Quels prénoms ont été les plus attribués aux bébés garçons et aux bébés filles dans le pays durant les années couvertes par l'ensemble de données ? Une manière simple de résoudre ce problème consiste à lire deux fois la table d'entrée et à faire une autojointure :

WITH male_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' ), female_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' ), both_genders AS ( SELECT name, SUM(m.num_babies) + SUM(f.num_babies) AS num_babies, SUM(m.num_babies) / (SUM(m.num_babies) + SUM(f.num_babies)) AS frac_male FROM male_babies AS m JOIN female_babies AS f USING (name) GROUP BY name ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

4. Une solution plus rapide et plus intéressante (mais surtout, correcte !) consiste à reformuler la requête afin de lire les données une seule fois et d'éviter d'effectuer une autojointure :

WITH all_babies AS ( SELECT name, SUM( IF (gender = 'M', number, 0)) AS male_babies, SUM( IF (gender = 'F', number, 0)) AS female_babies FROM `bigquery-public-data.usa_names.usa_1910_current` GROUP BY name ), both_genders AS ( SELECT name, (male_babies + female_babies) AS num_babies, SAFE_DIVIDE(male_babies, male_babies + female_babies) AS frac_male FROM all_babies WHERE male_babies > 0 AND female_babies > 0 ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

Cette requête a pris à peu près 2,4 secondes, soit environ 30 fois moins de temps.

Réduire le nombre de données de jointure

Il est possible d'effectuer la requête ci-dessus avec une jointure efficace, à condition de réduire la quantité des données de jointure en regroupant dès le début les données par nom et par genre :

• Essayez d'exécuter la requête suivante :

  WITH all_names AS ( SELECT name, gender, SUM(number) AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current GROUP BY name, gender ), male_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'M' ), female_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'F' ), ratio AS ( SELECT name, (f.num_babies + m.num_babies) AS num_babies, m.num_babies / (f.num_babies + m.num_babies) AS frac_male FROM male_names AS m JOIN female_names AS f USING (name) ) SELECT * FROM ratio WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

  Le regroupement précoce a permis de réduire les données tôt dans la requête, avant qu'elle n'effectue une jointure. Ainsi, le brassage et les autres opérations complexes s'exécutent uniquement sur des données de taille beaucoup plus petite et restent efficaces. La requête ci-dessus s'est terminée en 2 secondes environ et a renvoyé le bon résultat.

Utiliser un fenêtrage au lieu d'une autojointure

Supposons que vous souhaitez connaître la durée entre le moment où un vélo est déposé et celui où il est à nouveau loué, c'est-à-dire la durée de stationnement d'un vélo. Il s'agit d'un exemple de relation de dépendance entre les lignes. On pourrait penser que la seule façon de résoudre ce problème est de joindre la table à elle-même, en faisant correspondre la date de fin (end_date) d'un trajet à la date de début (start_date) du trajet suivant. Assurez-vous que votre requête s'exécute dans la région EU (plusieurs régions dans l'Union européenne). Cliquez sur +Créer une requête SQL, puis sélectionnez Plus > Paramètres de requête > Options supplémentaires et vérifiez que la case "Sélection automatique de l'emplacement" est cochée.

1. Vous pouvez cependant éviter l'autojointure grâce à un fenêtrage :

SELECT bike_id, start_date, end_date, TIMESTAMP_DIFF( start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire LIMIT 5

2. Nous pouvons ainsi calculer la durée moyenne de non-utilisation d'un vélo dans chaque station, puis classer les stations selon ce critère :

WITH unused AS ( SELECT bike_id, start_station_name, start_date, end_date, TIMESTAMP_DIFF(start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire ) SELECT start_station_name, AVG(time_at_station) AS unused_seconds FROM unused GROUP BY start_station_name ORDER BY unused_seconds ASC LIMIT 5

Effectuer des jointures avec des valeurs précalculées

Il peut parfois être utile de précalculer les fonctions dans des tables plus petites, puis de les joindre aux valeurs précalculées, plutôt que de répéter plusieurs fois un calcul coûteux.

Supposons, par exemple, que nous souhaitons trouver les deux stations entre lesquelles nos clients roulent à vélo le plus rapidement. Pour calculer le rythme (minutes par kilomètre) auquel ils roulent, nous devons diviser la durée du trajet par la distance entre les stations.

1. Nous pourrions créer une table dénormalisée avec les distances entre les stations et calculer ensuite le rythme moyen :

WITH denormalized_table AS ( SELECT start_station_name, end_station_name, ST_DISTANCE(ST_GeogPoint(s1.longitude, s1.latitude), ST_GeogPoint(s2.longitude, s2.latitude)) AS distance, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s1 ON h.start_station_id = s1.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s2 ON h.end_station_id = s2.id ), durations AS ( SELECT start_station_name, end_station_name, MIN(distance) AS distance, AVG(duration) AS duration, COUNT(*) AS num_rides FROM denormalized_table WHERE duration > 0 AND distance > 0 GROUP BY start_station_name, end_station_name HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM durations ORDER BY pace ASC LIMIT 5

La requête ci-dessus appelle la fonction géospatiale ST_DISTANCE une fois par ligne de la table cycle_hire (24 millions de fois), dure environ 14,7 secondes et traite à peu près 1,9 Go de données.

2. Nous pouvons également utiliser la table cycle_stations pour pré-calculer la distance entre chaque paire de stations (il s'agit d'une autojointure) et la joindre ensuite à la table plus petite de la durée moyenne de trajet entre les stations :

WITH distances AS ( SELECT a.id AS start_station_id, a.name AS start_station_name, b.id AS end_station_id, b.name AS end_station_name, ST_DISTANCE(ST_GeogPoint(a.longitude, a.latitude), ST_GeogPoint(b.longitude, b.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations a CROSS JOIN `bigquery-public-data`.london_bicycles.cycle_stations b WHERE a.id != b.id ), durations AS ( SELECT start_station_id, end_station_id, AVG(duration) AS duration, COUNT(*) AS num_rides FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE duration > 0 GROUP BY start_station_id, end_station_id HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM distances JOIN durations USING (start_station_id, end_station_id) ORDER BY pace ASC LIMIT 5

La requête reformulée avec les jointures plus efficaces ne dure qu'environ 8,2 secondes, soit une vitesse presque 1,8 fois supérieure, et traite à peu près 554 Mo, ce qui se traduit par un coût presque 4 fois inférieur.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Jointures

Tâche 4 : Éviter de surcharger un nœud de calcul

Certaines opérations (celles de tri, par exemple) doivent être effectuées sur un seul nœud de calcul. Un trop grand volume de données à trier peut surcharger la mémoire d'un nœud de calcul et entraîner un message d'erreur lié à un dépassement de ressources. Évitez de surcharger un nœud de calcul avec trop de données. La définition de ce "trop" évolue au fur et à mesure des améliorations du matériel des centres de données Google. Actuellement, cela correspond à environ un gigaoctet.

Limiter les tris importants

1. Supposons que nous souhaitons passer en revue les locations et les numéroter dans l'ordre de fin de location. Nous pourrions le faire en utilisant la fonction ROW_NUMBER() :

SELECT rental_id, ROW_NUMBER() OVER(ORDER BY end_date) AS rental_number FROM `bigquery-public-data.london_bicycles.cycle_hire` ORDER BY rental_number ASC LIMIT 5

34,5 secondes sont nécessaires pour traiter seulement 372 Mo parce qu'il faut trier sur un seul nœud de calcul l'ensemble des données sur les vélos de Londres. Si nous avions traité un ensemble de données plus important, le nœud de calcul aurait été surchargé.

2. Voyons s'il est possible de limiter les tris importants et de les répartir. Il est effectivement possible d'extraire la date des locations et de trier ensuite les trajets de chaque journée :

WITH rentals_on_day AS ( SELECT rental_id, end_date, EXTRACT(DATE FROM end_date) AS rental_date FROM `bigquery-public-data.london_bicycles.cycle_hire` ) SELECT rental_id, rental_date, ROW_NUMBER() OVER(PARTITION BY rental_date ORDER BY end_date) AS rental_number_on_day FROM rentals_on_day ORDER BY rental_date ASC, rental_number_on_day ASC LIMIT 5

Cela prend environ 15,1 secondes (soit une vitesse 2 fois supérieure), car le tri peut être effectué sur la base de données d'une seule journée à la fois.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Éviter de surcharger un nœud de calcul

Décalage de données

Le même problème de surcharge d'un nœud de calcul, en l'occurrence de sa mémoire, peut se produire pendant l'exécution d'un ARRAY_AGG avec GROUP BY si l'une des clés est beaucoup plus courante que les autres.

1. Comme il existe plus de 3 millions de dépôts GitHub et que les commits sont bien répartis, cette requête aboutit. Assurez-vous d'exécuter la requête dans le centre de traitement US (plusieurs régions des États-Unis) :

SELECT repo_name, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name

La requête va aboutir, mais elle peut prendre jusqu'à 30 minutes. Si vous comprenez son fonctionnement, poursuivez l'atelier.

2. La majorité des utilisateurs GitHub sont répartis sur seulement quelques fuseaux horaires. Le regroupement par fuseau horaire aboutit donc à un échec puisque nous demandons à un seul nœud de calcul de trier une fraction importante de 750 Go :

SELECT author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits` GROUP BY author.tz_offset

3. Si vous avez besoin de trier toutes les données, utilisez des clés plus précises en répartissant les données du groupe sur un plus grand nombre de nœuds de calcul, puis agrégez les résultats correspondant à la clé souhaitée. Par exemple, au lieu de regrouper les résultats uniquement par fuseau horaire, vous pouvez les regrouper à la fois par timezone (fuseau horaire) et par repo_name (nom de dépôt), puis agréger les dépôts pour obtenir la réponse pour chaque fuseau horaire :

SELECT repo_name, author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name, author.tz_offset

La requête va aboutir, mais elle peut prendre plus de 15 minutes. Si vous comprenez son fonctionnement, poursuivez l'atelier.

Tâche 5 : Fonctions d'agrégation approximative

BigQuery fournit des approximations rapides et peu gourmandes en mémoire des fonctions d'agrégation. Au lieu d'utiliser COUNT(DISTINCT […]), on peut utiliser APPROX_COUNT_DISTINCT sur des flux de données volumineux lorsqu'on peut tolérer une légère incertitude statistique dans le résultat.

Nombre approximatif

1. Découvrons le nombre de dépôts GitHub uniques à l'aide de la commande suivante :

SELECT COUNT(DISTINCT repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La requête ci-dessus prend 8,3 secondes pour calculer le résultat correct de 3347770.

2. L'utilisation de la fonction approximative :

SELECT APPROX_COUNT_DISTINCT(repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

La requête ci-dessus prend à peu près 3,9 secondes (soit une vitesse 2 fois supérieure) et renvoie un résultat approximatif de 3399473, d'environ 1,5 % supérieur à la bonne réponse.

Dans le cas des ensembles de données volumineux, mais uniquement dans ce cas, l'algorithme approximatif est beaucoup plus efficace que l'algorithme exact. Il est recommandé dans les cas d'utilisation où une marge d'erreur d'environ 1 % est tolérable. Avant d'utiliser la fonction approximative, évaluez votre cas d'utilisation.

Il existe d'autres fonctions approximatives : par exemple, APPROX_QUANTILES sert à calculer les percentiles, APPROX_TOP_COUNT à trouver les premiers éléments et APPROX_TOP_SUM à calculer les premiers éléments sur la base de la somme d'un élément.

Cliquez sur Vérifier ma progression pour valider l'objectif.

Fonctions d'agrégation approximative

Félicitations !

Vous avez appris un certain nombre de techniques pour améliorer les performances de vos requêtes. Lorsque vous envisagerez certaines de ces techniques, souvenez-vous de la citation du célèbre informaticien Donald Knuth : "L'optimisation prématurée est la source de tous les maux".

Étapes suivantes et informations supplémentaires

• Google Cloud Platform – Documentation sur l'optimisation de la performance des requêtes
• BigQuery – Bonnes pratiques de contrôle des coûts

Terminer l'atelier

Une fois l'atelier terminé, cliquez sur Terminer l'atelier. Google Cloud Skills Boost supprime les ressources que vous avez utilisées, puis efface le compte.

Si vous le souhaitez, vous pouvez noter l'atelier. Sélectionnez un nombre d'étoiles, saisissez un commentaire, puis cliquez sur Envoyer.

Le nombre d'étoiles correspond à votre degré de satisfaction :

• 1 étoile = très insatisfait(e)
• 2 étoiles = insatisfait(e)
• 3 étoiles = ni insatisfait(e), ni satisfait(e)
• 4 étoiles = satisfait(e)
• 5 étoiles = très satisfait(e)

Si vous ne souhaitez pas donner votre avis, vous pouvez fermer la boîte de dialogue.

Pour soumettre des commentaires, suggestions ou corrections, veuillez accéder à l'onglet Assistance.

Copyright 2020 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.