Présentation de BI Engine

BigQuery BI Engine est un service rapide d'analyse en mémoire qui accélère de nombreuses requêtes SQL dans BigQuery, en assurant une mise en cache intelligente des données que vous utilisez le plus fréquemment. Cette mise en cache vous permet d'améliorer les performances des requêtes sans réglage manuel ni aucune hiérarchisation des données. Vous pouvez mettre en cluster des tables et partitionner des tables pour optimiser davantage les performances de BI Engine pour les grandes tables.

Par exemple, si votre tableau de bord n'affiche que les données du dernier trimestre, vous pouvez partitionner vos tables par heure afin que seules les dernières partitions soient chargées en mémoire. Vous pouvez ensuite utiliser des vues matérialisées pour joindre et aplatir vos données, puis marquer la vue et la table de base résultantes comme tables préférées pour vous assurer que l'accélération de BI Engine n'est appliquée qu'aux données dont vous avez besoin.

BI Engine offre les avantages suivants :

  • Compatibilité avec l'API BigQuery : BI Engine s'intègre directement à l'API BigQuery. Toute solution BI ou application personnalisée qui fonctionne avec l'API BigQuery via des mécanismes standards tels que l'API REST ou les pilotes JDBC et ODBC peut utiliser BI Engine sans modification.
  • Exécution vectorisée : l'utilisation du traitement vectorisé dans un moteur d'exécution permet d'utiliser plus efficacement les architectures de processeur modernes, en effectuant des opérations sur des lots de données à la fois. BI Engine utilise également des encodages de données avancés, en particulier l'encodage par plages de dictionnaire, pour compresser davantage les données stockées dans la couche en mémoire.
  • Intégration parfaite : BI Engine fonctionne avec les fonctionnalités et les métadonnées BigQuery, y compris les vues autorisées, la sécurité au niveau des colonnes et le masquage des données.
  • Allocations de réservation : les réservations BI Engine gèrent séparément l'allocation de mémoire pour chaque projet et chaque région. BI Engine ne met en cache que les parties interrogées des colonnes et des partitions. Vous pouvez spécifier les tables qui utilisent l'accélération de BI Engine avec les tables préférées.

Dans la plupart des organisations, BI Engine est activé par un administrateur de la facturation qui réserve de la capacité pour l'accélération de BI Engine avec une édition appropriée. Pour en savoir plus, consultez la section Réserver de la capacité BI Engine.

Architecture BI Engine

BI Engine s'intègre aux outils de BI (tels que Looker, Data Studio, Tableau et Power BI), ainsi qu'aux applications personnalisées via l'API BigQuery pour accélérer l'exploration et l'analyse des données :

Composants de l'architecture BI Engine.

Cas d'utilisation de BI Engine

BI Engine peut considérablement accélérer de nombreuses requêtes SQL, y compris celles utilisées pour les tableaux de bord BI. L'accélération est plus efficace si vous identifiez les tables essentielles à vos requêtes, puis que vous les désignez comme tables préférées. Pour utiliser BI Engine, créez une réservation dans une région et spécifiez sa taille. Vous pouvez laisser BigQuery déterminer les tables à mettre en cache en fonction des modèles d'utilisation du projet ou spécifier des tables pour éviter qu'un autre trafic n'interfère avec leur accélération.

BI Engine est utile dans les cas d'utilisation suivants :

  • Vous utilisez des outils de BI pour analyser vos données : BI Engine accélère les requêtes BigQuery, qu'elles soient exécutées dans la console BigQuery, dans un outil de BI tel que Data Studio ou Tableau, dans une bibliothèque cliente, dans une API ou dans un connecteur ODBC ou JDBC. Cela peut améliorer considérablement les performances des tableaux de bord connectés à BigQuery via une connexion intégrée (API) ou des connecteurs.
  • Vous disposez de tables interrogées fréquemment : BI Engine vous permet de désigner des tables préférées à accélérer. Cela est utile si vous disposez d'un sous-ensemble de tables qui sont interrogées plus fréquemment ou qui sont utilisées pour des tableaux de bord très visibles.

Dans les cas suivants, BI Engine peut ne pas répondre à vos besoins :

  • Vous utilisez des caractères génériques dans vos requêtes : les requêtes faisant référence à des tables génériques ne sont pas compatibles avec BI Engine et ne bénéficient pas de l'accélération.
  • Vous avez besoin de fonctionnalités BigQuery non compatibles avec BI Engine: bien que BI Engine soit compatible avec la plupart des fonctions et opérateurs SQL, les fonctionnalités non compatibles incluent les tables externes, la sécurité au niveau des lignes et les fonctions non définies par l'utilisateur.

Remarques sur BI Engine

Tenez compte des points suivants lorsque vous décidez de la configuration de BI Engine :

Garantir l'accélération pour des requêtes spécifiques

Pour vous assurer qu'un ensemble de requêtes est accéléré, créez un projet distinct avec une réservation BI Engine dédiée. Commencez par, estimer la capacité de calcul requise pour vos requêtes, puis désignez ces tables comme tables préférées pour BI Engine.

Réduire les jointures

BI Engine fonctionne mieux avec des données préjointes ou pré-agrégées, et avec des requêtes comportant un petit nombre de jointures. Ce comportement est particulièrement vrai lorsque l'un des côtés de la jointure est volumineux et que les autres sont beaucoup plus petits, par exemple lorsque vous interrogez une grande table de faits jointe à des tables de dimensions plus petites. Vous pouvez combiner BI Engine avec des vues matérialisées qui effectuent des jointures pour produire une seule grande table plate. Cette approche évite d'effectuer les mêmes jointures pour chaque requête. Les vues matérialisées obsolètes sont recommandées pour des performances de requête optimales.

Comprendre l'impact de BI Engine

Pour comprendre votre utilisation de BI Engine, consultez la page Surveiller BI Engine avec Cloud Monitoring, ou interrogez les vues INFORMATION_SCHEMA.BI_CAPACITIES et INFORMATION_SCHEMA.BI_CAPACITY_CHANGES. Veillez à désactiver l'option Utiliser les résultats mis en cache dans BigQuery pour obtenir la comparaison la plus précise. Pour en savoir plus, consultez l'article Utiliser les résultats de requête mis en cache.

Tables préférées

Les tables préférées de BI Engine vous permettent de limiter l'accélération de BI Engine à un ensemble spécifié de tables. Les requêtes adressées à toutes les autres tables utilisent des emplacements BigQuery standards. Par exemple, avec les tables préférées, vous pouvez accélérer uniquement les tables et les tableaux de bord que vous identifiez comme importants pour votre entreprise.

Si le projet ne dispose pas de suffisamment de mémoire pour stocker toutes les tables préférées, BI Engine décharge les partitions et colonnes qui n'ont pas été consultées récemment. Ce processus libère de la mémoire pour les nouvelles requêtes nécessitant une accélération.

Limites des tables préférées

Les tables préférées de BI Engine présentent les limites suivantes :

  • Vous ne pouvez pas ajouter de vues logiques à la liste des réservations de tables préférées. Les tables préférées de BI Engine ne sont compatibles qu'avec les tables.
  • Les requêtes sur les vues matérialisées ne sont accélérées que si les vues matérialisées et leurs tables de base figurent dans la liste des tables préférées.
  • La spécification de partitions ou de colonnes pour l'accélération n'est pas acceptée.
  • Les colonnes de type JSON ne sont pas prises en charge et ne sont pas accélérées par BI Engine.
  • Les requêtes qui accèdent à plusieurs tables ne sont accélérées que si toutes les tables sont des tables préférées. Par exemple, toutes les tables d'une requête avec une JOIN doivent figurer dans la liste des tables préférées pour être accélérées. Si une seule table ne figure pas dans la liste des tables préférées, la requête ne peut pas utiliser BI Engine.
  • Les ensembles de données publics ne sont pas compatibles avec la Google Cloud console. Pour ajouter une table publique en tant que table préférée, utilisez l'API ou le LDD.

Optimisation et accélération des requêtes

BigQuery, et par extension BI Engine, divise un plan de requête en plusieurs sous-requêtes. Une sous-requête se compose d'opérations telles que l'analyse, le filtrage, le calcul ou l'agrégation de données, et sert d'unité d'exécution.

Bien que toutes les requêtes SQL BigQuery compatibles s'exécutent correctement avec BI Engine, BI Engine optimise de manière sélective des étapes spécifiques :

  • Sous-requêtes au niveau des feuilles : BI Engine est surtout optimisé pour les sous-requêtes au niveau des feuilles qui analysent les données depuis l'espace de stockage et effectuent des opérations telles que le filtrage, le calcul, l'agrégation, le tri (ORDER BY) et les jointures compatibles.
  • Exécution de secours : les étapes de requête ou les sous-requêtes qui ne peuvent pas être accélérées par BI Engine reviennent automatiquement aux emplacements d'exécution BigQuery standards sans échouer la requête.

En raison de cette optimisation sélective, les requêtes d'informatique décisionnelle ou de type tableau de bord simplifiées sont celles qui bénéficient le plus de BI Engine, car la majorité de leur temps d'exécution est consacrée au traitement des données brutes dans les sous-requêtes au niveau des feuilles.

Limites

Pour utiliser BI Engine, votre organisation doit réserver de la capacité BI Engine avec une édition compatible. Pour en savoir plus, consultez la section Comprendre les éditions BigQuery.

De plus, BI Engine présente des limites décrites dans les sections suivantes.

Jointures

BI Engine accélère certains types de requêtes de jointure. L'accélération se produit sur les sous-requêtes au niveau des feuilles avec les jointures INNER et LEFT OUTER, où une grande table de faits est jointe à un maximum de quatre tables de dimensions plus petites. Les petites tables de dimensions sont soumises aux restrictions suivantes :

  • Moins de 5 millions de lignes
  • Limites de taille :
    • Tables non partitionnées : 5 Gio ou moins
    • Tables partitionnées : partitions référencées de 1 Gio ou moins

Fonctions de fenêtrage

Le fenêtrage, terme qui désigne également les fonctions analytiques, présente les limites suivantes lorsqu'il bénéficie de l'accélération BI Engine :

  • Les étapes d'entrée sans fenêtrage sont accélérées par BI Engine. Dans ce cas, la vue INFORMATION_SCHEMA.JOBS indique bi_engine_statistics.acceleration_mode comme FULL_INPUT.
  • Les étapes d'entrée des requêtes avec fenêtrage sont accélérées par BI Engine si elles sont conformes aux limites applicables au fenêtrage BI Engine. Dans ce cas, les étapes d'entrée ou la requête complète sont exécutées dans BI Engine, et la vue INFORMATION_SCHEMA.JOBS indique bi_engine_statistics.acceleration_mode comme FULL_INPUT ou FULL_QUERY.

Pour en savoir plus sur le champ BiEngineStatistics, consultez la documentation de référence relative aux tâches.

Limites applicables au fenêtrage BI Engine

Les requêtes avec fenêtrage ne s'exécutent dans BI Engine que si toutes les conditions suivantes sont remplies :

  • La requête analyse exactement une table.
    • La table n'est pas partitionnée.
    • La table comporte moins de 5 millions de lignes.
  • La requête ne comporte aucun opérateur JOIN.
  • La taille de la table analysée multipliée par le nombre d'opérateurs de fenêtrage ne dépasse pas 300 Mio.

Deux fonctions de fenêtrage ayant des clauses OVER identiques et les mêmes entrées directes peuvent partager le même opérateur de fonction de fenêtrage. Exemple :

  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (ORDER BY x) FROM my_table ne comporte qu'un seul opérateur de fenêtrage.
  • SELECT ROW_NUMBER() OVER (ORDER BY x), SUM(x) OVER (PARTITION BY y ORDER BY x) FROM my_table comporte deux opérateurs de fenêtrage, car les deux fonctions ont des clauses OVER différentes.
  • SELECT ROW_NUMBER() OVER (ORDER BY x) FROM (SELECT SUM(x) OVER (ORDER BY x) AS x FROM my_table) comporte deux opérateurs de fenêtrage, car les deux fonctions ont des entrées directes différentes, même si leurs clauses OVER semblent identiques.

Fonctions analytiques compatibles

Les fonctions analytiques suivantes sont compatibles :

  • ANY_VALUE
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • CORR
  • COUNT
  • COUNTIF
  • COVAR_POP
  • COVAR_SAMP
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • PERCENTILE_CONT
  • PERCENTILE_DISC
  • RANK
  • ROW_NUMBER
  • ST_CLUSTERDBSCAN
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • STRING_AGG
  • SUM
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

Si les fonctions analytiques ne sont pas compatibles, le message d'erreur suivant peut s'afficher :

Analytic function is incompatible with other operators or its inputs are too large

Limites non compatibles avec BI Engine

L'accélération de BI Engine n'est pas disponible pour les fonctionnalités suivantes :

  • Fonctions distantes et UDF JavaScript.
  • Tables externes, y compris les tables BigLake.
  • Interrogation des données JSON (message d'erreur : JSON native type is not supported.).
  • Interrogation des données RANGE (message d'erreur : RANGE native type is not supported.).
  • Écriture des résultats dans une table BigQuery permanente.
  • Tables contenant des opérations upsert qui utilisent l'ingestion de capture de données modifiées de BigQuery.
  • Transactions.
  • Requêtes renvoyant plus de 1 Gio de données (pour les applications sensibles à la latence, nous vous recommandons d'utiliser une taille de réponse inférieure à 1 Mio).
  • Sécurité au niveau des lignes.
  • Requêtes qui utilisent des fonctions de recherche et de recherche vectorielle (telles que la SEARCH fonction ou VECTOR_SEARCH fonction) ou qui sont optimisées par des index de recherche ou index vectoriels.
  • Requêtes récursives utilisant RECURSIVE.
  • Requêtes BigQuery ML.

Solution pour les fonctionnalités non compatibles

Si votre requête utilise des fonctionnalités SQL non compatibles, vous pouvez utiliser la solution de contournement suivante :

  1. Écrivez une requête dans BigQuery.
  2. Enregistrez les résultats de la requête dans une table.
  3. Programmez votre requête pour mettre à jour la table régulièrement. Une fréquence d'actualisation horaire ou quotidienne fonctionne mieux. Actualiser toutes les minutes peut rendre le cache non valide trop fréquemment.
  4. Référencez cette table dans vos requêtes critiques.

Quotas et limites

Pour connaître les limites et les quotas qui s'appliquent à BI Engine, consultez la page Quotas et limites de BigQuery.

Tarifs

Des frais sont facturés pour la réservation que vous créez pour la capacité BI Engine. Pour en savoir plus sur les tarifs de BI Engine, consultez la section Tarifs de BigQuery.

Étape suivante