BigQuery est un data warehouse polyvalent qui transforme vos big data en insights précieux, mais la facture peut vite s'envoler. Premier volet d'une série de guides détaillés : comment l'utiliser efficacement.
Nouveau guide : optimisez les coûts et les performances de BigQuery
Les hausses soudaines de coûts ne sont pas rares quand plusieurs équipes d'analystes interrogent plusieurs datasets BigQuery. Mais elles n'ont rien d'une fatalité.
Dans notre dernier ebook, The BigQuery Optimization Handbook: Preparing to Save, Sayle Matthews, Senior Cloud Architect chez DoiT et spécialiste des données, signe le premier volet d'une série d'analyses pointues : comment dépenser moins pour faire plus avec BigQuery, et rendre vos factures Google Cloud plus prévisibles.
Au sommaire :
- Maîtriser les fondamentaux
- Éviter les erreurs courantes dans les requêtes
- Identifier vos requêtes les plus coûteuses
Maîtriser les fondamentaux
Le data warehouse BigQuery est entièrement managé et embarque des fonctionnalités natives comme le machine learning, l'analyse géospatiale et la business intelligence pour gérer et analyser vos big data. Mais sans préparation, les mauvaises surprises arrivent vite. Avant de vous attaquer à vos coûts BigQuery, quelques notions s'imposent. Commençons par les slots et les modèles de tarification :
Les slots
Le calcul dans BigQuery repose sur une unité appelée slot, soit un vCPU associé à de la mémoire. En théorie, plus vous disposez de slots alloués, plus vos requêtes s'exécutent rapidement. Les slots standards s'achètent toujours par lots de 100, dans le cadre d'un commitment d'un mois ou d'un an.
Les slots assurent une fonction non documentée : le shuffling des données. En clair, le shuffling consiste à redistribuer les données traitées vers un nouvel emplacement pour accélérer l'étape en cours ou la suivante du plan de requête. Jusqu'à 60 % des slots alloués à votre projet peuvent servir de shuffle slots à un instant donné. Le shuffling améliore l'efficacité des requêtes, mais il mobilise de précieux slots pour des opérations qui ne font pas progresser directement leur exécution.
Les modèles de tarification
La tarification est soit à la demande, soit forfaitaire. Le modèle par défaut est la tarification à la demande, facturée 5 $ par To de données scannées. Pour la plupart des entreprises, c'est le principal poste de coûts dans BigQuery.
La tarification forfaitaire (ou par slot) fixe un prix unique pour le scan BigQuery, au prix éventuel d'une perte de performance. Elle plafonne le nombre de slots disponibles à ceux que vous achetez à l'avance et supprime le coût de 5 $ par To scanné.
Avec la tarification forfaitaire, vous pouvez ajuster votre architecture en ajoutant un pool de slots appelés Flex slots, en complément ou en remplacement de vos slots existants. Les Flex slots offrent des engagements plus courts et plus souples, avec une durée minimale de 60 secondes et la possibilité de les supprimer à tout moment par simple annulation.
Pour égaler les performances du mode à la demande, il faut acheter 20 lots de 100 slots, soit 40 000 $ par mois ou 34 000 $ pour un an. Acheter 2 000 slots n'est rentable que si vos coûts de scan BigQuery atteignent au moins ce niveau.
Évaluer les coûts
Avant d'optimiser vos coûts, vous devez analyser l'utilisation des données dans vos projets. Cela suppose un accès aux données de requêtes de vos projets et datasets.
Deux méthodes s'offrent à vous : les audit log sinks et les tables INFORMATION_SCHEMA. L'audit log sink est la méthode à privilégier, car les données qu'il fournit sont bien plus riches . Les clients DoiT qui utilisent la fonctionnalité BigQuery Lens dans DoiT Cloud Intelligence™ disposent déjà d'un audit log sink configuré dans leur environnement. Plus d'informations ici.
Éviter les erreurs courantes dans les requêtes
Avant d'aborder les requêtes qui vous livreront les données recherchées, passons en revue quelques erreurs classiques dans l'écriture de requêtes BigQuery. Elles allongent les temps d'exécution et alourdissent inutilement la facture.
1. SELECT *
C'est sans doute la principale source de coûts superflus dans les requêtes BigQuery. Sélectionner toutes les colonnes d'une table ou d'une vue est rarement utile. Rappelez-vous : la facturation BigQuery dépend du volume de données scannées. Limitez votre sélection pour réduire ce coût.
2. Jointures inutiles ou trop larges
Dans les data warehouses orientés OLAP (comme BigQuery), la bonne pratique consiste à dénormaliser les schémas de la base. Cette approche aplatit les structures de données et réduit le nombre de jointures nécessaires par rapport à une base relationnelle traditionnelle.
3. Cross joins
Les cross joins ont leur utilité dans plusieurs cas d'usage de BigQuery, mais ils posent problème lorsqu'on les place comme opération la plus interne d'une requête : on charge alors bien plus de données que ce qui sera réellement renvoyé en sortie.
4. Mauvaise utilisation des Common Table Expressions (CTE)
Les Common Table Expressions (CTE) sont des outils formidables qui simplifient considérablement le code SQL. Mais référencer plusieurs fois la même CTE dans une requête revient à l'exécuter plusieurs fois — et donc à payer plusieurs fois la lecture des données.
5. Ne pas utiliser les partitions dans les clauses WHERE
Les partitions comptent parmi les fonctionnalités les plus importantes de BigQuery pour réduire les coûts et optimiser les performances de lecture. Pourtant, elles sont fréquemment omises, ce qui alourdit inutilement la facture.
6. Vues trop complexes
Des vues trop complexes peuvent dégrader les performances. Si la logique d'une vue est trop lourde, mieux vaut envisager un précalcul dans une autre table ou la transformer en vue matérialisée.
7. Petits inserts
Si vous devez insérer un petit nombre d'enregistrements dans une table, faites-le par lot plutôt qu'avec une multitude de petits inserts.
8. Abus des instructions DML
On abuse souvent des instructions DML lorsqu'on traite BigQuery comme un SGBD relationnel classique en recréant les données à volonté. Une meilleure approche : le "modèle additif", qui insère de nouvelles lignes horodatées pour identifier les plus récentes et purge périodiquement les anciennes si l'historique n'est pas nécessaire.
Identifier vos requêtes les plus coûteuses
L'ebook renvoie vers un dépôt GitHub contenant les fichiers SQL nécessaires pour repérer vos requêtes les plus coûteuses. Voici les trois requêtes principales à utiliser :
- Les requêtes les plus coûteuses au global
- Les requêtes individuelles les plus coûteuses
- Les utilisateurs les plus coûteux
Autres requêtes du dépôt
Le dépôt GitHub propose aussi d'autres requêtes à usage plus spécialisé : repérer les requêtes provenant de Looker, compter le nombre d'exécutions d'une requête, calculer le coût des requêtes portant un label spécifique, etc.
Repérer les requêtes problématiques côté performance
Une fois les requêtes les plus coûteuses identifiées, il reste à débusquer celles qui consomment plus de ressources que nécessaire et n'offrent pas les performances attendues. Elles recoupent souvent les plus coûteuses : attendez-vous à des chevauchements.
L'optimisation des performances sera approfondie dans un prochain volet de cette série, consacré aux pièges méconnus de la performance BigQuery et aux moyens de les contourner.
Dernier sujet abordé dans ce volet : un ensemble de requêtes du dépôt GitHub qui fournissent des informations plus générales ou des métadonnées :
- Requêtes par type de job
- Requêtes simultanées
- Comptage des requêtes
Et maintenant ?
Téléchargez The BigQuery Optimization Handbook: Preparing to Save. Ce volet et les suivants contenant un volume conséquent de contenu détaillé, nous vous recommandons de mettre en place dès maintenant un audit log sink pour vos projets BigQuery, afin de collecter des données dans la durée et de tirer le meilleur parti de ces requêtes et des prochains volets. Pour rappel : les clients DoiT qui utilisent déjà la fonctionnalité BigQuery Lens dans DoiT Cloud Intelligence disposent déjà d'un audit log sink configuré dans leur environnement.