Optimiser le calcul des requêtes

Ce document présente les bonnes pratiques à adopter pour optimiser les performances de vos requêtes.

Une fois la requête terminée, vous pouvez afficher le plan de requête dans la console Google Cloud. Vous pouvez également demander des détails d'exécution à l'aide des vues INFORMATION_SCHEMA.JOBS* ou de la méthode de l'API REST jobs.get.

Le plan de requête fournit des détails sur les phases et les étapes de la requête. Ces informations peuvent vous aider à identifier les moyens d'améliorer les performances des requêtes. Par exemple, si vous remarquez une étape qui écrit beaucoup plus de résultats que d'autres, vous devrez peut-être filtrer plus tôt la requête.

Pour en savoir plus sur le plan de requête et consulter des exemples d'amélioration des performances des requêtes, consultez la page Obtenir des insights sur les performances des requêtes. Après avoir traité les insights sur les performances des requêtes, vous pouvez les optimiser davantage en effectuant les tâches suivantes :

Réduire la quantité de données traitées

Vous pouvez réduire les données à traiter à l'aide des options décrites dans les sections suivantes.

Éviter la requête SELECT *

Bonne pratique : Contrôlez la projection en interrogeant uniquement les colonnes dont vous avez besoin.

La projection fait référence au nombre de colonnes lues par la requête. La projection d'un trop grand nombre de colonnes entraîne des E/S et une matérialisation (écriture des résultats) accrues (gaspillage).

  • Utilisez les options d'aperçu des données. Si vous testez ou explorez les données, utilisez l'une des options d'aperçu de données plutôt que SELECT *.
  • Interrogez des colonnes spécifiques. L'application d'une clause LIMIT à une requête SELECT * n'a aucune incidence sur la quantité de données lues. Vous êtes facturé pour la lecture de tous les octets de la table, et la requête est comptabilisée dans votre quota de niveau gratuit. Nous recommandons plutôt d'interroger uniquement les colonnes dont vous avez besoin. Par exemple, utilisez SELECT * EXCEPT pour exclure une ou plusieurs colonnes des résultats.
  • Utilisez des tables partitionnées. Si vous devez effectuer des requêtes sur toutes les colonnes d'une table, mais seulement sur un sous-ensemble de données, prenez en compte les points suivants :

    • Matérialisez les résultats dans une table de destination et interrogez plutôt cette table.
    • Partitionnez vos tables, puis interrogez la partition concernée. Par exemple, utilisez la requête WHERE _PARTITIONDATE="2017-01-01" pour n'interroger que la partition du 1er janvier 2017.

  • Utilisez SELECT * EXCEPT. Interroger un sous-ensemble de données ou utiliser SELECT * EXCEPT peut réduire considérablement la quantité de données lues par une requête. Outre les économies de coûts, les performances sont améliorées en réduisant la quantité d'E/S de données et le volume de matérialisation requis pour obtenir les résultats de la requête.

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

Éviter l'utilisation excessive des tables de caractères génériques

Bonne pratique : Lorsque vous interrogez des tables de caractères génériques, vous devez utiliser le préfixe le plus précis.

Utilisez des caractères génériques pour interroger plusieurs tables à l'aide d'instructions SQL concises. Les tables de caractères génériques sont une union de tables correspondant à l'expression générique. Les tables de caractères génériques sont utiles si votre ensemble de données contient les ressources suivantes :

  • Plusieurs tables portant le même nom avec des schémas compatibles
  • Tables segmentées

Lorsque vous interrogez une table générique, spécifiez un caractère générique (*) après le préfixe de table commun. Par exemple, FROM bigquery-public-data.noaa_gsod.gsod194* interroge toutes les tables des années 1940.

Les préfixes précis sont plus performants que les préfixes courts. Par exemple, FROM bigquery-public-data.noaa_gsod.gsod194* est plus performant que FROM bigquery-public-data.noaa_gsod.*, car moins de tables correspondent au caractère générique.

Évitez les tables segmentées par date

Bonne pratique : N'utilisez pas de tables segmentées par date (également appelées tables nommées par date) à la place des tables partitionnées par date.

Les tables partitionnées offrent de meilleures performances que les tables nommées par date. Lorsque vous créez des tables segmentées par date, BigQuery doit conserver une copie du schéma et des métadonnées pour chacune de ces tables. De plus, leur utilisation peut obliger BigQuery à valider les autorisations pour chaque table interrogée. Cette pratique peut également alourdir le traitement des requêtes et affecter leurs performances.

Évitez de trop segmenter les tables

Bonne pratique : Évitez de créer trop de segments dans vos tables. Si vous segmentez des tables par date, utilisez plutôt des tables partitionnées par période.

La segmentation des tables consiste à diviser de grands ensembles de données en tables séparées et à ajouter un suffixe à chaque nom de table. Si vous segmentez des tables par date, utilisez plutôt des tables partitionnées par période.

En raison du faible coût du stockage BigQuery, vous n'avez pas besoin d'optimiser vos tables comme vous le feriez dans un système de base de données relationnelle. La création d'un grand nombre de segments a un impact négatif sur les performances qui l'emporte sur les avantages en termes de coûts.

Avec des tables segmentées, BigQuery doit gérer le schéma, les métadonnées et les autorisations pour chaque segment. En raison de la charge supplémentaire que cela représente, une segmentation excessive peut affecter les performances des requêtes.

La quantité et la source des données lues par une requête peuvent avoir un impact sur les performances et le coût de la requête.

Limiter les requêtes partitionnées

Bonne pratique : Lorsque vous interrogez une table partitionnée, utilisez les colonnes suivantes pour filtrer les partitions sur les tables partitionnées :

  • Pour les tables partitionnées par date d'ingestion, utilisez la pseudo-colonne _PARTITIONTIME.
  • Pour les tables partitionnées, telles que les colonnes de temps et les unités temporelles, utilisez la colonne de partitionnement.

Pour les tables partitionnées par unité de temps, le filtrage des données avec _PARTITIONTIME ou la colonne de partitionnement vous permet de spécifier une date ou une plage de dates. Par exemple, la clause WHERE ci-dessous utilise la pseudo-colonne _PARTITIONTIME pour spécifier des partitions entre le 1er janvier 2016 et le 31 janvier 2016 :

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

La requête ne traite que les données des partitions spécifiées par la plage de dates. Filtrer les partitions améliore les performances des requêtes et réduit les coûts.

Réduisez les données avant d'utiliser une clause JOIN

Bonne pratique : Réduisez la quantité de données traitées avant une clause JOIN en effectuant des agrégations.

L'utilisation d'une clause GROUP BY avec des fonctions d'agrégation nécessite un calcul important, car ces types de requêtes utilisent le brassage. Étant donné que ces requêtes utilisent beaucoup de ressources de calcul, vous ne devez utiliser une clause GROUP BY que si nécessaire.

Pour les requêtes contenant GROUP BY et JOIN, effectuez une agrégation en amont de la requête pour réduire la quantité de données traitées. Par exemple, la requête suivante effectue une opération JOIN sur deux grandes tables sans filtrage préalable :

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

Cette requête pré-agrège le nombre de commentaires, ce qui réduit la quantité de données lues pour l'opération JOIN :

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

Utiliser la clause WHERE

Bonne pratique : Limitez la quantité de données renvoyées par une requête à l'aide d'une clause WHERE. Dans la mesure du possible, utilisez les colonnes BOOL, INT, FLOAT ou DATE dans la clause WHERE.

Les opérations sur les colonnes BOOL, INT, FLOAT et DATE sont généralement plus rapides que les opérations sur les colonnes STRING ou BYTE. Dans la mesure du possible, utilisez une colonne qui utilise l'un de ces types de données dans la clause WHERE pour réduire la quantité de données renvoyées par la requête.

Optimiser les opérations de requête

Vous pouvez optimiser vos opérations de requête à l'aide des options décrites dans les sections suivantes.

Éviter de transformer les données à plusieurs reprises

Bonne pratique : Si vous utilisez SQL pour effectuer des opérations ETL, puis évitez les situations vous amenant à transformer les mêmes données de façon répétée.

Par exemple, si vous utilisez SQL pour raccourcir des chaînes ou extraire des données à l'aide d'expressions régulières, matérialisez les résultats transformés dans une table de destination : vous gagnerez en efficacité. Les fonctions telles que les expressions régulières nécessitent des calculs supplémentaires. Il vaut mieux donc interroger la table de destination sans ajouter la surcharge de la transformation.

Éviter plusieurs évaluations des mêmes CTE

Bonne pratique : Utilisez le langage procédural, des variables, des tables temporaires et des tables qui expirent automatiquement pour conserver les calculs et les utiliser ultérieurement dans la requête.

Lorsque votre requête contient des expressions de table courantes (CTE) utilisées à plusieurs endroits de la requête, elles peuvent être évaluées chaque fois qu'elles sont référencées. L'optimiseur de requêtes tente de détecter des parties de la requête qui ne peuvent être exécutées qu'une seule fois, mais cela n'est pas toujours possible. Ainsi, l'utilisation d'une CTE peut ne pas aider à réduire la complexité des requêtes internes et la consommation des ressources.

Vous pouvez stocker le résultat d'une CTE dans une variable scalaire ou dans une table temporaire en fonction des données renvoyées par la CTE.

Évitez les jointures et les sous-requêtes répétées

Bonne pratique : Évitez de fusionner plusieurs fois les mêmes tables et d'utiliser les mêmes sous-requêtes.

Au lieu de fusionner les données à plusieurs reprises, il est plus efficace d'utiliser des données répétées imbriquées pour représenter les relations. Ces données ont moins d'incidence sur les performances de la bande passante de communication requise par une jointure. Vous limitez également les coûts E/S qui vous sont facturés en cas de lectures et d'écritures répétées des mêmes données. Pour plus d'informations, consultez la section Utiliser des champs imbriqués et répétés.

De même, la répétition de sous-requêtes identiques a un impact sur les performances en raison du traitement répétitif des requêtes. Si vous utilisez les mêmes sous-requêtes dans plusieurs requêtes, pensez à matérialiser les résultats des sous-requêtes dans une table. Ensuite, utilisez les données matérialisées dans vos requêtes.

La matérialisation des résultats de vos sous-requêtes améliore les performances, et réduit la quantité globale de données lues et écrites par BigQuery. L'impact sur les performances du traitement répété des E/S et des requêtes est largement compensé par le faible coût de stockage des données matérialisées.

Optimisez vos modèles de jointure

Bonne pratique : Pour les requêtes qui associent des données provenant de plusieurs tables, optimisez vos modèles de jointure en commençant par la plus grande table.

Lorsque vous créez une requête à l'aide de la clause JOIN, tenez compte de l'ordre dans lequel vous fusionnez les données. L'optimiseur de requête GoogleSQL détermine quelle table doit figurer sur tel côté de la jointure. Il est recommandé de faire apparaître en premier la table avec le plus grand nombre de lignes, suivie de la table avec le moins de lignes, puis de placer les tables restantes en ordre décroissant de taille.

Lorsqu'une grande table constitue le côté gauche de la jointure JOIN et une petite le côté droit de la jointure JOIN, une jointure de diffusion est créée. Celle-ci envoie toutes les données de la plus petite table à chaque emplacement qui traite la plus grande table. Il est conseillé d'effectuer d'abord la jointure de diffusion.

Pour visualiser la taille des tables dans votre jointure JOIN, consultez la section Obtenir des informations sur les tables.

Optimiser la clause ORDER BY

Bonne pratique : Lorsque vous utilisez la clause ORDER BY, veillez à suivre les bonnes pratiques :

  • Utilisez ORDER BY dans la requête de plus haut niveau ou dans les clauses de fenêtrage. Transférez les opérations complexes en fin de requête. Placer une clause ORDER BY au milieu d'une requête a un impact considérable sur les performances, à moins de l'utiliser dans une fonction de fenêtrage.

    Une autre technique d'ordonnancement des requêtes consiste à reporter les opérations complexes, telles que les expressions régulières et les fonctions mathématiques, à la fin de la requête. Cette technique réduit les données à traiter avant l'exécution d'opérations complexes.

  • Utiliser une clause LIMIT Si vous triez un grand nombre de valeurs, mais que vous n'avez pas besoin de les renvoyer toutes, utilisez une clause LIMIT. Par exemple, la requête suivante ordonne un ensemble de résultats très volumineux et génère une erreur Resources exceeded. La requête est triée en fonction de la colonne title dans mytable. La colonne title contient des millions de valeurs.

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    Pour supprimer l'erreur, envoyez une requête semblable à celle-ci :

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • Utilisez un fenêtrage. Si vous triez un très grand nombre de valeurs, utilisez une fonction de fenêtrage et limitez les données avant d'appeler la fonction de fenêtrage. Par exemple, la requête suivante répertorie les dix utilisateurs Stack Overflow les plus anciens et leur classement, le compte le plus ancien étant classé le plus bas:

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    L'exécution de cette requête prend environ 15 secondes. Cette requête utilise LIMIT à la fin de la requête, mais pas dans la fonction de fenêtrage DENSE_RANK() OVER. De ce fait, la requête nécessite que toutes les données soient triées sur un seul nœud de calcul.

    Vous devez plutôt limiter l'ensemble de données avant de calculer la fonction de fenêtrage afin d'améliorer les performances:

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    L'exécution de cette requête prend environ deux secondes, tout en renvoyant les mêmes résultats que la requête précédente.

    Avertissement : la fonction DENSE_RANK() classe les données par années. Par conséquent, pour le classement de données couvrant plusieurs années, ces requêtes ne fournissent pas de résultats identiques.

Diviser les requêtes complexes en requêtes plus petites

Bonne pratique : Exploitez les fonctionnalités de requête à plusieurs instructions et les procédures stockées pour effectuer les calculs conçus comme une requête complexe en plusieurs requêtes plus petites et plus simples.

L'exécution de requêtes complexes, de fonctions REGEX et de sous-requêtes ou de jointures en couches peut être lente et gourmande en ressources. Essayer de regrouper tous les calculs dans une seule très grande instruction SELECT, par exemple pour en faire une vue, est parfois un antimodèle. Cela peut entraîner une requête lente et gourmande en ressources. Dans les cas extrêmes, le plan de requête interne devient si complexe que BigQuery ne peut pas l'exécuter.

La division d'une requête complexe permet de matérialiser les résultats intermédiaires dans des variables ou des tables temporaires. Vous pouvez ensuite utiliser ces résultats intermédiaires dans d'autres parties de la requête. Elle est de plus en plus utile lorsque ces résultats sont nécessaires à plusieurs endroits de la requête.

Il est souvent possible de mieux exprimer l'intention réelle de certaines parties de la requête grâce aux tables temporaires qui constituent les points de matérialisation des données.

Utiliser des champs imbriqués et répétés

Pour en savoir plus sur la dénormalisation du stockage de données à l'aide de champs imbriqués et répétés, consultez la page Utiliser des champs imbriqués et répétés.

Utiliser des types de données INT64 dans les jointures

Bonne pratique : Utilisez des types de données INT64 dans les jointures plutôt que les types de données STRING pour réduire les coûts et améliorer les performances de comparaison.

BigQuery n'indexe pas les clés primaires comme les bases de données traditionnelles. Par conséquent, plus la colonne de jointure est large, plus la comparaison est longue. Par conséquent, l'utilisation des types de données INT64 dans les jointures est moins coûteuse et plus efficace que l'utilisation des types de données STRING.

Réduire les résultats de requête

Vous pouvez réduire les résultats de la requête en utilisant les options décrites dans les sections suivantes.

Matérialiser de grands ensembles de résultats

Bonne pratique : Envisagez de matérialiser de grands ensembles de résultats sur une table de destination. En effet, l'écriture de ces volumes importants de données a un impact sur les performances et les coûts.

BigQuery limite les résultats mis en cache à environ 10 Go compressés. Les requêtes qui renvoient des résultats plus volumineux dépassent cette limite et provoquent fréquemment l'erreur suivante : Response too large.

Cette erreur est souvent renvoyée lors de la sélection d'un grand nombre de champs dans une table contenant une quantité considérable de données. Des problèmes d'écriture des résultats mis en cache peuvent également se produire dans les requêtes de type ETL qui normalisent les données sans réduction ni agrégation.

Vous pouvez contourner la limite de taille des résultats mis en cache à l'aide des options suivantes :

  • Utiliser des filtres pour limiter l'ensemble de résultats
  • Utiliser une clause LIMIT pour réduire l'ensemble de résultats, en particulier si vous incluez une clause ORDER BY
  • Écrire les données de sortie dans une table de destination

Vous pouvez parcourir les résultats à l'aide de l'API REST de BigQuery. Pour en savoir plus, consultez la section Parcourir les donnée de table.

Utiliser BI Engine

Pour accélérer encore vos requêtes SQL en mettant en cache les données que vous utilisez le plus fréquemment, envisagez d'ajouter une réservation BI Engine au projet dans lequel les requêtes sont calculées. BigQuery BI Engine utilise un moteur de requêtes vectorisés pour accélérer les performances de requête SELECT.

Éviter les modèles anti-SQL

Les bonnes pratiques suivantes fournissent des conseils pour éviter les antipatrons de requête qui ont un impact sur les performances de BigQuery.

Éviter les autojointures

Bonne pratique : Au lieu d'utiliser des autojointures, utilisez une fonction de fenêtrage (analytique) ou l'opérateur PIVOT..

En règle générale, les autojointures sont utilisées pour calculer les relations dépendantes des lignes. L'utilisation d'une autojointure peut potentiellement doubler le nombre de lignes de sortie. Cette augmentation des données de sortie peut entraîner de mauvaises performances.

Éviter les jointures croisées

Bonne pratique : Évitez les jointures qui génèrent plus de sorties que d'entrées. Lorsqu'une jointure croisée (CROSS JOIN) est requise, pré-agrégez vos données.

Les jointures croisées sont des requêtes pour lesquelles chaque ligne de la première table est jointe aux lignes de la seconde table, des clés non uniques sont présentes des deux côtés. Une sortie est défavorable lorsque le nombre de lignes dans la table de gauche est multiplié par le nombre de lignes dans la table de droite. Dans les cas extrêmes, la requête peut ne pas aboutir.

Si la requête aboutit, l'explication du plan de requête affiche les lignes de sortie par rapport aux lignes d'entrée. Vous avez la possibilité de confirmer un produit cartésien en modifiant la requête pour imprimer le nombre de lignes de chaque côté de la clause JOIN, regroupées par la clé de jointure.

Pour éviter les problèmes de performances associés aux jointures qui génèrent plus de sorties que d'entrées :

  • Recourez à une clause GROUP BY pour pré-agréger les données.
  • Utilisez un fenêtrage. Les fenêtrages s'avèrent souvent plus efficaces que les jointures croisées. Pour en savoir plus, consultez la page Fonctions de fenêtrage.

Éviter les instructions LMD qui mettent à jour ou insèrent des lignes simples

Bonne pratique : Évitez les instructions LMD qui mettent à jour ou insèrent des lignes simples. Organisez vos mises à jour et insertions par lots.

L'utilisation d'instructions LMD spécifiques à un point constitue une tentative de traiter BigQuery comme un système de traitement transactionnel en ligne (OLTP). BigQuery se concentre sur le traitement analytique en ligne (OLAP) et utilise des analyses de table et non des recherches de points. Si vous avez besoin d'un comportement de type OLTP (mises à jour ou insertions sur une seule ligne), envisagez une base de données compatible avec les cas d'utilisation OLTP tels que Cloud SQL.

Les instructions LMD de BigQuery sont destinées aux mises à jour groupées. Les instructions LMD UPDATE et DELETE de BigQuery sont orientées vers des réécritures périodiques de vos données, et non des mutations de ligne unique. L'instruction LMD INSERT doit être utilisée avec parcimonie. Les insertions consomment les mêmes quotas de modification que les tâches de chargement. Si votre cas d'utilisation implique de fréquentes insertions de ligne unique, envisagez plutôt de diffuser vos données par flux.

Si le traitement par lots de vos instructions UPDATE génère de nombreux tuples dans des requêtes très longues, vous pouvez vous rapprocher de la limite de longueur de requête de 256 Ko. Pour contourner la limite de longueur de requête, déterminez si vos mises à jour peuvent être gérées sur la base de critères logiques et non d'une série de remplacements directs de tuples.

Par exemple, vous pouvez charger votre ensemble d'enregistrements de remplacement dans une autre table, puis écrire l'instruction LMD pour mettre à jour toutes les valeurs de la table d'origine si les colonnes non mises à jour correspondent. Ainsi, en supposant que les données d'origine se trouvent dans la table t et que les mises à jour sont appliquées dans la table u, la requête se présente comme suit :

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

Utiliser des noms d'alias pour les colonnes portant un nom similaire

Bonne pratique : Utilisez des alias de colonnes et de tables lorsque vous utilisez des colonnes portant le même nom sur plusieurs requêtes, y compris des sous-requêtes.

Les alias aident à identifier les colonnes et les tables référencées en plus de votre référence initiale à la colonne. L'utilisation d'alias peut vous aider à comprendre et à résoudre les problèmes dans votre requête SQL, y compris à trouver les colonnes utilisées dans les sous-requêtes.

Spécifier des contraintes dans le schéma de la table

Lorsque les données de table contiennent des contraintes, spécifiez les contraintes dans le schéma de la table. Le moteur de requêtes peut optimiser les plans de requête à l'aide de contraintes de table.

Spécifier les contraintes de clé primaire et de clé étrangère

Vous devez spécifier des contraintes de clé dans le schéma de la table lorsque les données de la table répondent aux exigences d'intégrité des données des contraintes de clé primaire ou de clé étrangère. Le moteur de requêtes peut utiliser les contraintes de clé pour optimiser les plans de requête. Vous trouverez des informations détaillées dans l'article de blog Associer des optimisations avec des clés primaires BigQuery et des clés étrangères.

BigQuery ne vérifie pas automatiquement l'intégrité des données. Vous devez donc vous assurer que vos données répondent aux contraintes spécifiées dans le schéma de la table. Si vous ne conservez pas l'intégrité des données dans les tables avec des contraintes spécifiées, les résultats de votre requête risquent d'être inexacts.

Étapes suivantes