Gérer les données d'entrée et les sources de données

Lors de l'évaluation de vos données d'entrée, considérez les E/S requises. Combien d'octets votre requête lit-elle? Limitez-vous correctement la quantité de données d'entrée ? Vos données sont-elles stockées dans un stockage BigQuery natif ou dans une source de données externe ? La quantité de données lues par une requête et la source de ces données ont un impact sur les performances et le coût de la requête.

Vous pouvez examiner la manière dont les données d'entrée sont lues par une requête à l'aide de l'explication du plan de requête.

Les bonnes pratiques présentées ci-dessous vous aideront à contrôler vos données d'entrée et à choisir une source de données.

Contrôler la projection - Éviter SELECT *

Bonne pratique : Contrôlez la projection - Interrogez 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).

Utiliser SELECT * est le moyen le plus coûteux d'interroger des données. Lorsque vous utilisez SELECT *, BigQuery effectue une analyse complète de chaque colonne de la table.

Si vous effectuez des tests ou explorez les données, utilisez l'une des options de prévisualisation des données plutôt que SELECT *.

Appliquer une clause LIMIT à une requête SELECT * n'affecte pas 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.

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 par date, puis interrogez la partition concernée. Par exemple, WHERE _PARTITIONDATE="2017-01-01" n'analyse que la partition du 1er janvier 2017.

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.

Limiter les requêtes partitionnées

Bonne pratique : Lorsque vous interrogez une table partitionnée, utilisez la pseudo-colonne _PARTITIONTIME pour filtrer les partitions.

Lorsque vous interrogez des tables partitionnées, utilisez la pseudo-colonne _PARTITIONTIME. Filtrer les données à l'aide de _PARTITIONTIME vous permet de spécifier une date ou une plage de dates. Par exemple, la clause WHERE suivante 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 indiquées par la plage de dates, ce qui réduit la quantité de données d'entrée. Filtrer les partitions améliore les performances des requêtes et réduit les coûts.

Dénormaliser les données autant que possible

Bonne pratique : BigQuery fonctionne mieux lorsque les données sont dénormalisées. Plutôt que de conserver un schéma relationnel tel qu'un schéma en étoile ou en flocon, vous pouvez améliorer les performances en dénormalisant vos données et en tirant parti des champs imbriqués et répétés. Les champs imbriqués et répétés permettent de conserver des relations sans l'impact sur les performances de la préservation d'un schéma relationnel (normalisé).

Les économies de stockage rendues possibles par la normalisation des données ont moins d'impact sur les systèmes modernes. L'augmentation des coûts de stockage est justifiée par les gains de performance découlant de la dénormalisation des données. Les jointures nécessitent une coordination des données (synonyme de bande passante de communication). La dénormalisation localise les données dans des emplacements individuels, ce qui permet une exécution en parallèle.

Pour maintenir des relations tout en dénormalisant vos données, utilisez des champs imbriqués et répétés au lieu d'aplatir complètement vos données. Lorsque les données relationnelles sont complètement aplaties, la communication réseau (brassage) peut avoir un impact négatif sur les performances des requêtes.

Par exemple, dénormaliser un schéma de commandes sans utiliser de champs imbriqués et répétés peut nécessiter un regroupement par champ tel que order_id (lorsqu'il existe une relation de un à plusieurs). En raison du brassage qu'il implique, le regroupement des données est moins performant que la dénormalisation des données à l'aide de champs imbriqués et répétés.

Dans certaines circonstances, la dénormalisation des données et l'utilisation de champs imbriqués et répétés peuvent ne pas améliorer les performances. Évitez la dénormalisation dans les cas suivants :

  • Vous avez un schéma en étoile dont les dimensions changent fréquemment.
  • BigQuery complète un système de traitement des transactions en ligne (OLTP, Online Transaction Processing) avec une mutation au niveau de la ligne, mais ne peut pas le remplacer.

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

BigQuery ne nécessite pas une dénormalisation complètement plate. Vous pouvez utiliser des champs imbriqués et répétés pour maintenir les relations.

  • Données d'imbrication (STRUCT)

    • Les données d'imbrication vous permettent de représenter des entités étrangères de façon intégrée.
    • Interroger des données imbriquées utilise la syntaxe "dot" pour référencer des champs feuille, ce qui est semblable à la syntaxe utilisée pour une jointure.
    • Les données imbriquées sont représentées sous la forme d'un type STRUCT en langage SQL standard.
  • Données répétées (ARRAY)

    • Créer un champ de type RECORD avec le mode défini sur REPEATED vous permet de conserver une relation de un à plusieurs de façon intégrée (à condition que la relation ne soit pas à cardinalité élevée).
    • Avec des données répétées, le brassage n'est pas nécessaire.
    • Les données répétées sont représentées sous la forme d'un tableau ARRAY. Vous pouvez utiliser une fonction ARRAY en langage SQL standard lorsque vous interrogez les données répétées.
  • Données imbriquées et répétées (ARRAY of STRUCTs)

    • L'imbrication et la répétition se complètent.
    • Par exemple, dans une table d'enregistrements de transactions, vous pouvez inclure un tableau de lignes STRUCT.

Utiliser des sources de données externes de manière appropriée

Bonne pratique : Si les performances de la requête sont une priorité, n'utilisez pas de source de données externe.

Interroger des tables dans le stockage géré BigQuery est généralement beaucoup plus rapide que d'interroger des tables externes dans Cloud Storage, Google Drive ou Cloud Bigtable.

Utilisez une source de données externe pour les cas d'utilisation suivants :

  • Opérations d'extraction, de transformation et de chargement (ETL) lors du chargement de données
  • Changement fréquent de données
  • Chargements périodiques, comme par exemple l'ingestion récurrente de données depuis Cloud Bigtable

É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, utilisez le préfixe le plus précis possible.

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 :

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

Lorsque vous interrogez une table de caractères génériques, 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* fonctionne mieux que FROM bigquery-public-data.noaa_gsod.* car moins de tables correspondent au caractère générique.

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

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.