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. BI Engine peut accélérer les requêtes SQL depuis n'importe quelle source, y compris celles écrites par des outils de visualisation de données, et peut gérer les tables mises en cache pour une optimisation continue. Vous pouvez ainsi améliorer les performances des requêtes sans réglage manuel ni hiérarchisation des données. Vous pouvez mettre en cluster 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 pour que seules les dernières partitions soient chargées en mémoire. Vous pouvez également combiner les avantages des vues matérialisées et de BI Engine. Cette approche est particulièrement adaptée lorsque les vues matérialisées sont utilisées pour joindre et aplatir les données afin d'optimiser leur structure pour BI Engine.

BI Engine offre les avantages suivants :

  • Compatibilité avec l'API BigQuery:BI Engine s'intègre directement à l'API BigQuery. Toute solution d'informatique décisionnelle ou application personnalisée qui fonctionne avec l'API BigQuery via des mécanismes standards tels que REST ou les pilotes JDBC et ODBC peut utiliser BI Engine sans aucune modification.
  • Environnement d'exécution vectorisé:le 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 des lignes, ainsi que 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 requises et 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 facturation qui doit réserver de la capacité pour l'accélération de BI Engine. Pour en savoir plus, consultez la section Réserver de la capacité 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 marquez comme tables préférées. Pour utiliser BI Engine, vous devez créer une réservation dans une région et spécifier 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 d'informatique décisionnelle 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 d'informatique décisionnelle tel que Looker Studio ou Tableau, ou dans une bibliothèque cliente, une API ou un connecteur ODBC ou JDBC. Cela peut considérablement améliorer les performances des tableaux de bord connectés à BigQuery via une connexion intégrée (API) ou des connecteurs.
  • Vous avez interrogé des tables fréquemment : BI Engine vous permet de désigner des tables préférées à accélérer. Ces options sont utiles si un sous-ensemble de tables est interrogé plus fréquemment ou est utilisé pour des tableaux de bord à visibilité élevée.

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 avec BI Engine incluent les tables externes, la sécurité au niveau des lignes et les fonctions définies par l'utilisateur autres que SQL.

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. Cela 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 dimension plus petites. Vous pouvez combiner BI Engine avec des vues matérialisées qui effectuent des jointures pour produire une seule grande table plate. De cette manière, les mêmes jointures ne sont pas effectuées pour chaque requête. Les vues matérialisées obsolètes sont recommandées pour optimiser les performances des requêtes.

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 RAM 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 à 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 jointure 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 préférée, la requête ne peut pas utiliser BI Engine.
  • Les ensembles de données publics ne sont pas compatibles avec la console Google Cloud. Pour ajouter une table publique en tant que table préférée, utilisez l'API ou le LDD.

Limites

BigQuery BI Engine est soumis aux limites 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 INNER et LEFT OUTER JOINS, où une grande table de faits est jointe à un maximum de quatre tables de "dimension" plus petites. Les tables à petites dimensions sont soumises aux restrictions suivantes:

  • Moins de cinq millions de lignes
  • Limite de taille :
    • Tables non partitionnées: 5 Go ou moins
    • Tables partitionnées: partitions référencées de 1 Go 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 BigQuery BI Engine :

  • Les étapes d'entrée sont accélérées par BigQuery BI Engine si elles ne comportent pas de fenêtrage. Dans ce cas, la vue INFORMATION_SCHEMA.JOBS signale bi_engine_statistics.acceleration_mode comme étant défini sur FULL_INPUT.
  • Les étapes d'entrée des requêtes avec des fonctions de fenêtre dans leurs étapes d'entrée sont accélérées par BI Engine, mais ne sont pas soumises aux limites décrites dans la section 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 dans ce cas, la vue INFORMATION_SCHEMA.JOBS signale bi_engine_statistics.acceleration_mode comme étant défini sur 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 des fonctions de 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 contient moins de cinq 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 Mo.

Deux fonctions analytiques ayant des clauses OVER identiques et les mêmes entrées directes peuvent partager le même opérateur de fonction analytique. 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 ces 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 de fenêtrage compatibles

Les fonctions analytiques référencées suivantes sont prises en charge:

  • 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 le fenêtrage n'est pas compatible, l'erreur suivante peut s'afficher :

La fonction analytique est incompatible avec d'autres opérateurs ou ses entrées sont trop volumineuses

Autres limites de BI Engine

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

  • Fonctions JavaScript définies par l'utilisateur
  • Tables externes
  • Interroger des données JSON - message d'erreur: Le type natif JSON n'est pas compatible
  • Écrire des résultats dans une table BigQuery permanente
  • Tables contenant des opérations upsert qui utilisent la capture de données modifiées de BigQuery
  • Transactions
  • Requêtes renvoyant plus de 1 GiB de données. Pour les applications sensibles à la latence, nous vous recommandons d'utiliser une taille de réponse inférieure à 1 Mo.
  • Sécurité au niveau des lignes

Solution pour les fonctionnalités non compatibles

Bien que certaines fonctionnalités SQL ne soient pas compatibles avec BigQuery BI Engine, une solution de contournement est disponible :

  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 risque d'invalider le cache trop fréquemment.
  4. Référencez cette table dans vos requêtes critiques.

Quotas et limites

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

Tarifs

Des frais sont facturés pour la réservation que vous créez pour la capacité BI Engine. Pour plus d'informations sur la tarification de BI Engine, consultez la page Tarifs de BigQuery.

Étapes suivantes