Migrer des entrepôts de données vers BigQuery : optimisation des performances

Ce document fait partie d'une série qui vous aide à passer d'un entrepôt de données sur site à BigQuery sur Google Cloud. Il décrit des techniques essentielles permettant d'optimiser les performances des requêtes dans votre entrepôt de données fourni par BigQuery. Nous partons ici du principe que vous avez migré le schéma et les données depuis votre ancien entrepôt de données vers BigQuery, et que vous avez traduit les requêtes de vos anciennes tâches en langage SQL standard de BigQuery.

Tout au long de votre lecture, concentrez-vous sur les raisons pour lesquelles vous souhaitez optimiser les performances dans le contexte de votre effort de migration. Cet article n'a pas pour vocation de proposer une liste exhaustive des techniques d'optimisation disponibles (vous pouvez retrouver ces dernières dans la documentation officielle concernant les bonnes pratiques pour BigQuery). Il cherche plutôt à vous aider à déterminer si vous devriez utiliser ou non ces techniques, et quand.

La série sur la migration comprend les parties suivantes :

Considérations générales sur les performances

BigQuery traite efficacement les données de tous les ensembles de données, qu'ils soient de faible taille ou atteignent l'échelle du pétaoctet. Cet outil vous permet d'exécuter vos tâches d'analyse de données en toute fluidité, sans avoir à modifier votre entrepôt de données fraîchement migré. Si, dans certaines situations, les performances des requêtes ne correspondent pas à vos attentes, consultez les principes clés décrits sur cette page afin de comprendre la façon dont certains facteurs peuvent affecter les performances.

Compromis en matière d'optimisation

Comme pour n'importe quel système, l'optimisation des performances implique parfois des compromis. Par exemple, l'utilisation de la syntaxe SQL avancée peut occasionnellement complexifier les requêtes et les rendre moins claires aux yeux des utilisateurs moins expérimentés. En outre, la micro-optimisation de charges de travail non critiques peut également monopoliser vos ressources, vous empêchant ainsi de développer de nouvelles fonctionnalités ou d'optimiser plus efficacement votre infrastructure. Pour vous aider à dégager le meilleur retour sur investissement possible, nous vous recommandons donc de vous concentrer sur l'optimisation des charges de travail les plus importantes pour vos pipelines d'analyse de données.

Pour savoir si des requêtes spécifiques posent problème, vous pouvez utiliser Cloud Monitoring pour surveiller la manière dont vos tâches BigQuery consomment des ressources au fil du temps. Si vous identifiez une requête lente ou exigeante en ressources, vous pouvez vous focaliser sur celle-ci afin d'en optimiser les performances.

Capacité et simultanéité

BigQuery décompose la capacité de calcul nécessaire pour exécuter des requêtes SQL en unités appelées emplacements. Il calcule ensuite automatiquement le nombre d'emplacements requis par chaque requête, en fonction de la taille et de la complexité de cette dernière.

BigQuery gère automatiquement le quota d'emplacements partagé par vos requêtes actuelles, en fonction de l'historique, de l'utilisation et des dépenses du client. Le nombre d'emplacements par défaut par projet offre une capacité amplement suffisante pour la plupart des utilisateurs. L'accès à davantage d'emplacements ne garantit pas des performances plus élevées pour une requête déterminée. Une augmentation du nombre d'emplacements peut cependant améliorer les performances pour des requêtes volumineuses ou complexes, ou à l'échelle d'un grand nombre de charges de travail simultanées. Pour améliorer davantage les performances des requêtes, envisagez d'acheter un plus grand nombre d'emplacements réservés, en plus d'optimiser les requêtes et le modèle de données.

BigQuery propose deux modèles de tarification pour les requêtes : à la demande et au forfait. Les tarifs à la demande sont basés sur la quantité de données traitées par chaque requête exécutée. Les tarifs forfaitaires conviennent davantage aux clients dont les dépenses mensuelles en analyse sont constantes. Lorsque vous souscrivez à la tarification forfaitaire, vous achetez une capacité dédiée au traitement de requêtes, qui est mesurée en emplacements BigQuery. Le coût de tous les octets traités est inclus dans le prix forfaitaire mensuel. Si vos requêtes dépassent votre capacité forfaitaire, elles sont mises en file d'attente jusqu'à ce que vos ressources forfaitaires soient disponibles.

Plan et chronologie de requête

L'UI Web de BigQuery vous permet d'inspecter visuellement le plan et la chronologie de vos requêtes. Vous pouvez utiliser la méthode API jobs.get pour récupérer des informations sur le plan et la chronologie des requêtes. L'outil BigQuery Visualiser pourrait également vous intéresser : il s'agit d'un outil Open Source qui représente visuellement le déroulement des phases d'exécution d'une tâche BigQuery.

Lorsque BigQuery exécute une tâche de requête, il convertit l'instruction SQL déclarative en un graphe d'exécution. Ce graphe est divisé en une série de phases de requête, elles-mêmes composées d'ensembles d'étapes d'exécution plus précis. BigQuery exploite une architecture parallèle fortement distribuée pour exécuter ces requêtes. Les phases modélisent les unités de travail que de nombreux nœuds de calcul potentiels peuvent exécuter en parallèle. Les phases communiquent entre elles via une architecture de brassage distribuée rapide.

Plan d'exécution de requête

En plus du plan de requête, les tâches de requête présentent également une chronologie d'exécution, qui fournit le compte des unités de travail réalisées, en attente et actives au sein des nœuds de calcul de la requête. Une requête peut comporter simultanément plusieurs phases avec des nœuds de calcul actifs, de sorte que la chronologie permet de montrer la progression globale de la requête.

Statistiques de la chronologie

Pour estimer le coût en ressources d'une requête, vous pouvez consulter le nombre de secondes d'emplacement qu'elle utilise. Plus ce nombre est faible, moins une requête est exigeante en ressources, car cela indique que davantage de ressources sont disponibles au même moment pour les autres requêtes du projet.

Les statistiques du plan et de la chronologie de requête peuvent vous aider à comprendre comment BigQuery exécute les requêtes et à savoir si certaines phases utilisent davantage de ressources. Par exemple, une phase JOIN qui génère beaucoup plus de lignes de sortie que de lignes d'entrée peut indiquer la possibilité de filtrer plus tôt dans la requête. La gestion du service limite toutefois l'exploitation directe de certaines informations. Si vous souhaitez améliorer les performances et l'exécution des requêtes, nous vous conseillons d'appliquer les techniques décrites dans la section Stratégies d'optimisation des requêtes ainsi que dans la documentation sur les bonnes pratiques de BigQuery.

Sources de données externes

Une source de données externe (également appelée source de données fédérée) peut être interrogée directement depuis BigQuery, même si les données n'y sont pas stockées. Au lieu de charger ou de diffuser les données, vous créez une table qui référence la source de données externe. BigQuery est compatible avec plusieurs sources de données externes, dont Cloud Bigtable, Cloud Storage et Google Drive.

Les requêtes portant sur des sources de données externes peuvent s'avérer moins performantes que l'interrogation de données contenues dans une table BigQuery native. Si la vitesse des requêtes est une priorité, nous vous conseillons de charger les données dans BigQuery au lieu de configurer une source de données externe. Les performances d'une requête portant sur une source de données externe dépendent du type de stockage externe. Par exemple, il est plus rapide d'interroger des données stockées dans Google Cloud Storage que d'interroger des données stockées dans Google Drive. En général, les performances des requêtes portant sur des sources de données externes sont équivalentes à celles de la lecture directe de données depuis le stockage externe.

Lorsque vous interrogez une source de données externe, les résultats ne sont pas mis en cache. Chaque requête effectuée sur une table externe vous sera facturée, même si vous lancez plusieurs fois la même requête. Si vous devez effectuer une même requête à plusieurs reprises sur une table externe qui ne change pas fréquemment, envisagez plutôt d'écrire les résultats de la requête dans une table permanente et d'exécuter les requêtes sur celle-ci.

En règle générale, l'utilisation d'une source de données externe est conseillée pour les cas d'utilisation suivants :

  • Opérations d'extraction, de transformation et de chargement (ETL) lors du chargement de données
  • Interrogation de données changeant fréquemment
  • Chargements périodiques, telles que l'ingestion récurrente de données depuis Bigtable

Pour en savoir plus sur les possibilités, les limites et les bonnes pratiques liées à l'utilisation de sources de données externes, consultez la documentation officielle de BigQuery.

Stratégies d'optimisation des requêtes

Si, après avoir pris en compte les considérations de la section précédente, vous estimez que les performances de certaines requêtes ne correspondent pas à vos attentes, envisagez de les optimiser en suivant les stratégies présentées dans cette section.

Partitionnement

Le partitionnement de table consiste à diviser une table en segments appelés partitions. En divisant une grande table en partitions plus petites, vous pouvez améliorer les performances des requêtes et maîtriser les coûts en réduisant le nombre d'octets lus par une requête. Nous recommandons le partitionnement pour les tables d'une taille supérieure à 10 GB, si leur schéma le permet.

Il existe deux types de tables partitionnées dans BigQuery :

  • Tables partitionnées par date d'ingestion : il s'agit de tables partitionnées en fonction de la date d'ingestion (chargement) ou d'arrivée des données.
  • Tables partitionnées : tables partitionnées en fonction d'une colonne DATE ou TIMESTAMP.

Vous pouvez créer une table partitionnée dans BigQuery :

Par exemple, supposons que vous ayez migré une table orders de votre ancien entrepôt de données vers BigQuery, avec le schéma suivant :

orders
Nom de la colonne Type
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER

Vous pouvez maintenant partitionner la table orders par date dans la colonne orderDate comme suit :

CREATE TABLE `your-project.sales.orders_partitioned` (
  salesOrderKey STRING,
  orderDate TIMESTAMP,
  customerKey STRING,
  totalSale FLOAT64,
  currencyKey INT64
)
PARTITION BY DATE(orderDate)
AS
SELECT * FROM `your-project.sales.orders`

La requête suivante devrait s'exécuter bien plus rapidement sur la table partitionnée que sur l'autre table, car la clause WHERE indique à BigQuery de n'analyser que les données d'une partition unique :

SELECT
 DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
 currencyKey,
 COUNT(*) AS numberOfOrders,
 SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_partitioned`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-01-01'
GROUP BY DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

Pour savoir quand envisager le partitionnement dans le contexte de l'amélioration d'un schéma, consultez la présentation du transfert de schéma et de données.

Filtrage par cluster

Le clustering peut améliorer les performances de certains types de requêtes, telles que les requêtes utilisant des clauses de filtre et celles agrégeant des données. Lorsqu'une tâche de requête ou de chargement écrit des données dans une table en cluster, BigQuery trie les données à l'aide des valeurs des colonnes de clustering. Ces valeurs permettent d'organiser les données en plusieurs blocs dans le stockage BigQuery. Lorsque vous soumettez une requête contenant une clause filtrant les données en fonction des colonnes de clustering, BigQuery utilise les blocs triés pour éviter l'analyse de données inutiles, accélérant ainsi les requêtes et réduisant leur coût.

À l'heure actuelle, BigQuery ne permet d'utiliser le clustering que sur des tables partitionnées. Le clustering de tables est compatible avec les tables partitionnées par date d'ingestion et avec les tables partitionnées sur une colonne DATE ou TIMESTAMP.

Vous pouvez utiliser le clustering sur une table partitionnée dans les cas suivants :

  • Vos données sont déjà partitionnées sur une colonne de date ou d'horodatage.
  • Vous utilisez couramment des filtres ou des agrégations sur des colonnes spécifiques de vos requêtes.

Lorsque vous combinez le clustering et le partitionnement, vous pouvez partitionner les données en fonction d'une colonne de date ou d'horodatage, puis les mettre en cluster sur un ensemble de colonnes différent. Dans ce cas, les données de chaque partition sont mises en cluster en fonction des valeurs des colonnes de clustering. Le partitionnement permet d'obtenir des estimations de coûts précises pour vos requêtes (basées sur les partitions analysées).

Pour améliorer davantage l'exemple présenté dans la section précédente, vous pouvez créer une table nommée orders_clustered, qui est à la fois partitionnée et mise en cluster, basée sur la table orders d'origine :

CREATE TABLE
 `your-project.sales.orders_clustered` (
   salesOrderKey STRING,
   orderDate TIMESTAMP,
   customerKey STRING,
   totalSale FLOAT64,
   currencyKey INT64 )
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT * FROM `your-project.sales.orders`

La requête suivante devrait s'exécuter bien plus rapidement sur la table orders_clustered que sur la table orders d'origine grâce à la combinaison du partitionnement et du filtre WHERE. Elle devrait également s'exécuter plus rapidement que sur la table orders_partitioned grâce à la combinaison du clustering et des clauses customerKey IN [...] et GROUP BY customerKey.

SELECT
  customerKey,
  DATE_TRUNC(DATE(orderDate), MONTH) AS firstOfMonth,
  currencyKey,
  COUNT(*) AS numberOfOrders,
  SUM(totalSale) AS totalSales
FROM `your-project.sales.orders_clustered`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
  AND customerKey IN (
    '1292803200000-402',
    '1298764800000-414',
    '1267401600000-212',
    '1267488000000-209')
GROUP BY customerKey, DATE_TRUNC(DATE(orderDate), MONTH), currencyKey

Pour savoir quand envisager le partitionnement dans le contexte de l'amélioration d'un schéma, consultez la présentation du transfert de schéma et de données.

Dénormalisation

La dénormalisation est une stratégie courante qui permet d'améliorer les performances de lecture sur des ensembles de données relationnels auparavant normalisés. La méthode recommandée pour dénormaliser des données dans BigQuery consiste à utiliser des champs imbriqués et répétés. Il est préférable d'employer cette stratégie lorsque vous avez affaire à des relations hiérarchiques souvent interrogées ensemble (par exemple, dans le cas de relations parent-enfant).

L'utilisation de champs imbriqués et répétés permet de localiser les données dans des emplacements individuels, et ainsi d'obtenir une exécution en parallèle. Cette approche maintient les relations sans complètement aplatir les données relationnelles dénormalisées, ce qui améliore encore davantage les performances en réduisant la communication réseau lors de la phase de brassage.

La dénormalisation des données requiert davantage d'espace de stockage. Cette augmentation n'est toutefois pas un problème dans la plupart des cas d'utilisation, grâce au stockage à faible coût de BigQuery. Concédant des ressources de calcul au profit de ressources de stockage, la dénormalisation vous aide à réduire le coût global lié aux applications ayant un taux de lecture élevé. Cette diminution des coûts est due à l'augmentation globale de la vitesse ainsi qu'à l'utilisation réduite du réseau et des ressources de calcul.

Les données imbriquées sont représentées par un type STRUCT en langage SQL standard de BigQuery. Les données répétées sont représentées par un type ARRAY, et une fonction ARRAY peut être utilisée. L'imbrication et la répétition se complètent (un tableau ARRAY comporte des objets STRUCT). Pour en savoir plus, consultez les bonnes pratiques de BigQuery concernant la dénormalisation.

Prenons l'exemple de deux tables orders_clustered et order_lines_clustered ayant le schéma suivant :

orders_clustered
Nom de la colonne Type
salesOrderKey STRING
orderDate TIMESTAMP
customerKey STRING
totalSale FLOAT
currencyKey INTEGER
order_lines_clustered
Nom de la colonne Type
salesOrderKey STRING
salesOrderLineKey STRING
productKey STRING
quantity INTEGER
unitPrice FLOAT
orderDate TIMESTAMP
totalSale FLOAT

Vous pouvez créer une table dénormalisée (orders_nested) qui pré-fusionne les données des deux autres tables en champs imbriqués et répétés :

CREATE TABLE `your-project.sales.orders_denormalized`
PARTITION BY DATE(orderDate)
CLUSTER BY customerKey
AS
SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  o.customerKey,
  o.totalSale,
  ARRAY_AGG(
    STRUCT(
      ol.salesOrderLineKey,
      ol.productKey,
      ol.totalSale
  )) AS lineItem
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
GROUP BY 1,2,3,4,5

Prenez maintenant les deux requêtes SELECT suivantes :

SELECT
  o.salesOrderKey,
  o.orderDate,
  o.currencyKey,
  ol.salesOrderLineKey,
  ol.productKey,
  ol.totalSale
FROM `your-project.sales.orders_clustered` AS o
  JOIN `your-project.sales.order_lines_clustered` AS ol
    ON ol.orderDate = o.orderDate
    AND ol.salesOrderKey = o.salesOrderKey
WHERE DATE_TRUNC(DATE(o.orderDate), MONTH) = '2015-02-01'
  AND o.customerKey = '1201392000000-325'

SELECT
 salesOrderKey,
 orderDate,
 currencyKey,
 lineItem
FROM `your-project.sales.orders_denormalized`
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

La deuxième requête devrait s'exécuter bien plus rapidement que la première, car elle agit sur une table dénormalisée dont les données ont déjà été pré-fusionnées.

La deuxième requête renvoie les valeurs lineItem dans le type de données ARRAY(STRUCT()) :

Résultats de la deuxième requête

Vous pouvez aplatir le tableau à l'aide de l'opérateur UNNEST :

SELECT
 o.salesOrderKey,
 o.orderDate,
 o.currencyKey,
 ol.*
FROM `your-project.sales.orders_denormalized` AS o
  JOIN UNNEST(lineItem) AS ol
WHERE DATE_TRUNC(DATE(orderDate), MONTH) = '2015-02-01'
 AND customerKey = '1201392000000-325'

La requête renvoie le résultat au format aplati, comme illustré ci-dessous :

Résultat de la requête au format aplati

Pour savoir quand envisager la dénormalisation dans le contexte de l'amélioration d'un schéma, consultez le guide sur le transfert de schéma et de données.

Fonctions approximatives

L'agrégation approximative correspond à l'estimation des résultats des fonctions d'agrégation (cardinalité et quantiles, par exemple). BigQuery accepte toute une gamme de fonctions d'agrégation approximative qui exploitent l'algorithme HyperLogLog++ pour estimer le nombre de valeurs distinctes présentes dans un ensemble de données volumineux.

Les fonctions d'agrégation approximative requièrent moins de mémoire que les fonctions d'agrégation exacte correspondantes, mais elles engendrent également une incertitude statistique. Vous pouvez utiliser des fonctions d'agrégation approximative avec des flux de données volumineux pour lesquels l'utilisation linéaire de la mémoire est peu pratique, ainsi que pour les données qui sont déjà approximatives.

Prenez par exemple les deux requêtes suivantes :

SELECT
  COUNT(DISTINCT salesOrderKey)
FROM `your-project.sales.orders`

SELECT
 APPROX_COUNT_DISTINCT(salesOrderKey)
FROM `your-project.sales.orders`

La première renvoie la valeur exacte. La deuxième renvoie une valeur approximative, mais avec un écart négligeable par rapport à la valeur exacte. Néanmoins, cette deuxième requête devrait s'exécuter bien plus rapidement que la première.

Anti-modèles

Pour améliorer davantage les performances, consultez la documentation BigQuery sur les anti-modèles afin d'éviter de commettre des erreurs fréquentes (par exemple, les jointures générant plus de sorties que d'entrées, l'utilisation d'instructions LMD spécifiques à un point ou l'interrogation de données asymétriques non filtrées).

Autres bonnes pratiques

Cet article ne présente que quelques-unes des techniques principales qui permettent d'améliorer les performances des requêtes pour votre entrepôt de données migré. Pour découvrir d'autres améliorations possibles ainsi que d'autres bonnes pratiques, consultez la documentation complète sur l'optimisation des performances des requêtes.

Étape suivante

Explorez des architectures de référence, des schémas, des tutoriels et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.