L’utilisation de BigQuery est quelque chose d’incontournable si vous utilisez la plateforme Google Cloud. Il s’agit probablement de la solution de DataWarehouse la plus performante du marché tant par sa simplicité d’utilisation que par ses performances.
Cet outil vous permet de charger des centaines de Téraoctets sur une table et de les lire en un rien de temps via du SQL classique sans jamais avoir besoin de faire autre chose que de charger ses données et de les lire.
Chose qui est moins facile à mettre en œuvre sur ses principaux concurrents comme SnowFlake qui lui demande de comprendre le concept de virtual warehouse, jusqu’à aller sur des solutions “on premise” qui vont demander la formation d’une équipe d’Avengers rien que pour faire tourner la base de données.
Mais l’utilisation de BigQuery ne se fait pas sans contrepartie, ce service peut rapidement devenir onéreux si l’on ne comprend pas bien son fonctionnement ainsi que ses subtilités.
Dans cet article, je vais décrire 7 techniques pour réduire sa facture BigQuery, toutes ces méthodes sont classées de la plus facile à mettre en œuvre à la moins simple.
1 - Bannir le SELECT *
La première manière de réduire sa facture est probablement la plus simple et efficace, il s’agit de bannir définitivement le SELECT * de tous vos scripts SQL.
Contrairement à la plupart des bases relationnelles qui stockent leurs données au format ligne, BigQuery les stocke en utilisant le format colonne. Avec les avantages de ce mode de stockage, il est possible de réduire le nombre de bytes lus simplement en réduisant le nombre de colonnes dans le SELECT.
Comme la facturation à la demande sur BigQuery est en fonction du nombre de bytes lus depuis le stockage, lire moins de colonnes vous permettra de lire moins de bytes, comme illustré dans les requêtes d’exemple ci dessous :
En supposant que nous souhaitons récupérer le nom de l’auteur et le message de chaque commit sur la plateforme GitHub, comme le Téraoctet lu est facturé 5$, nous passons d’une requête d’environ 4$ pour le SELECT * à une requête à 15 centimes.
Je vous laisse imaginer l’économie réalisée lorsque plusieurs utilisateurs font ce genre de requêtes plusieurs fois par jour sur une table d’une centaine de Téraoctets.
Notez que l’utilisation du mot clé LIMIT ne réduit pas le nombre de données scannées dans le storage, contrairement aux bases relationnelles, BigQuery n’est pas capable de ramener uniquement un nombre donné de lignes directement depuis le storage, il est uniquement capable de ramener un nombre fixe de colonnes ou alors de filtrer sur une partition.
2 - Partitionner ses données
Une autre manière de réduire ses coûts sur BigQuery est de partitionner les tables par date ou par intervalle de nombres.
En plus de stocker ses données au format colonne, BigQuery est capable de répartir les données dans des partitions afin de lire uniquement une partie des données stockées plutôt que l'entièreté de la table.
Dans cet exemple, si nous souhaitons récupérer les Tags du 1er Mars 2018 sur la table questions_2018, le moteur BigQuery devra obligatoirement lire l'entièreté de la colonne Tags du stockage pour filtrer celles qui ne sont pas à la bonne date.
En partitionnant la table d’origine par le champ creation_date (illustré dans questions_2018_partitioned), BigQuery va stocker les données dans une partition associée à chaque valeur différente de creation_date afin que le moteur puisse lire uniquement la portion de donnée nécessaire lorsque vous utilisez une WHERE clause sur le champ creation_date.
Nous pouvons voir le bénéfice de l’utilisation d’une partition en utilisant la table publique
geo_openstreetmap.history_layers qui est partitionnée par entiers sur le champ layer_code. Si la requête exécutée n’utilise pas la partition, BigQuery devra quand-même scanner l'entièreté de la table.
L’autre avantage du partitionnement est de savoir à l’avance si votre partition contient de la donnée ou non, dans les captures d’écran ci-dessus, on constate que la valeur 110 pour layer_code ne contient aucune donnée, contrairement à la valeur 1101 qui contient 10 Mo de données.
Notez qu’il n’est pas possible à l’heure actuelle de partitionner une table BigQuery par chaîne de caractères, de même, il est important de partitionner par un champ suffisamment discriminant afin d’éviter que toutes vos données se retrouvent dans une même partition, ce qui serait contre productif.
3 - Abuser du cache
Le cache mémoire interne de BigQuery est une fonctionnalité que l’on retrouve dans une grande majorité des DataWarehouse managés et son implémentation varie selon la solution que l’on utilise.
Le cache mémoire de BigQuery est une implémentation très simple, lorsqu'une requête est lancée par un utilisateur, le résultat de la requête va être stocké dans ce cache mémoire pendant 24H. Ainsi si n’importe quel autre utilisateur va lancer une requête identique, BigQuery va comprendre qu’il n’a pas besoin de lire son stockage interne et de refaire toutes les transformations. Il va lire le résultat de la requête stocké dans le cache, puis l’afficher à l’utilisateur, cette opération est extrêmement rapide et n’est pas facturée à l’utilisateur.
Cependant, pour pouvoir bénéficier du cache, plusieurs conditions doivent être réunies :
- La donnée de la table source n’a pas changée
- La requête n’utilise pas de fonctions non-déterministe comme NOW()
- Le cache mémoire est activé sur le résultat de la requête
En prenant en compte ces conditions, vous pourrez créer un modèle de données permettant à vos utilisateurs d’utiliser le cache sans qu’ils aient à configurer quoi que ce soit.
4 - Utiliser un TTL
Ce point me permet d’aborder le deuxième concept de facturation de BigQuery qui est la taille des données stockées dans votre projet. A l’heure actuelle BigQuery facture 0,020$ par Gb de données stockées dans BigQuery dans la région Europe.
Afin de réduire les coûts associés au stockage, il existe plusieurs méthodes. La plus drastique est de mettre place un TTL au niveau d’un dataset ou d’une table pour que toutes les données d’une table disparaissent après une période donnée.
Dans cet exemple, nous spécifions une période d’expiration de 90 jours pour chacune des tables créées dans le dataset my_dataset. Ce qui signifie que chaque nouvelle table contenue dans ce dataset sera automatiquement supprimée au bout de 90 jours.
Vous pouvez aussi spécifier cette période sur chacune des tables afin d’avoir plus de flexibilité que de spécifier cette option sur l'entièreté du dataset.
Enfin, il est aussi possible de définir cette option au niveau de la partition via l’outil en ligne de commande bq :
bq mk --time_partitioning_type=DAY --time_partitioning_expiration=259200 project_id:dataset.table
Dans cet exemple, BigQuery va supprimer une partition lorsque la date actuelle du système dépasse celle de la partition + 3 jours.
Ce type d’expiration ne fonctionne pas sur les tables partitionnées par entiers.
5 - Exporter les archives sur Cloud Storage
Par défaut, si les données de votre table ou d’une partition ne sont pas éditées au bout de 90 jours, BigQuery va automatiquement diviser le coût de stockage de la table ou de la partition par deux en le plaçant dans l’espace de stockage long terme, mais il est possible d’obtenir une réduction plus importante.
Pour ce faire, il suffit d’exporter manuellement ces données dans un bucket cloud storage de type Archive afin de bénéficier de coûts de stockage encore plus faibles que celui de BigQuery long term storage.
Cette méthode n’est utilisable que si vous avez des données que les utilisateurs ne vont jamais lire sauf dans des cas exceptionnels. Il est recommandé d’utiliser cette solution uniquement lorsque l’accès à ces données se fait au maximum une fois par an. Dans le cas contraire, il sera plus judicieux de rester sur le BigQuery long term storage.
6 - Eviter les streaming insert
Pour charger de la donnée dans BigQuery, on passe généralement par un load job qui va charger un ensemble de données comme un fichier, une base ou une application vers une table. Cette opération est gratuite, mais est limitée en termes de quotas, vous avez droit à un nombre limité de load dans la journée pour une table, ce qui peut-être contraignant si vous avez beaucoup de données qui arrivent sur une file de message comme Pub/Sub.
C'est pour cette raison que les streamings inserts ont été inventés, ils permettent de charger de la donnée dans BigQuery tout en les rendant immédiatement disponibles, mais contrairement au load job, cette opération est payante.
Afin de limiter les coûts, il est recommandé de vérifier si l’utilisation des streamings inserts est pertinente car si vous n’avez pas besoin de lire les données dès lors que celles-ci arrivent dans BigQuery, il serait préférable de passer par des load job.
7 - Limiter les erreurs humaines
La sixième solution pour optimiser ses coûts est de mettre en place des quotas personnalisés au niveau du projet ou des utilisateurs afin de contrôler le nombre de bytes scannés au maximum à la journée.
Par défaut, il n’y a pas de limite au nombre de bytes scannés à la journée sur BigQuery et il est arrivé chez un client qu’une personne non expérimentée fasse plusieurs grosses requêtes sur une table de plusieurs Téraoctets entraînant une facture 10x supérieure à la normale pour une journée, voici une capture d’écran illustrant la courbe des coûts au mois.
En utilisant des quotas personnalisés, vous pouvez éviter ces erreurs tout en prédisant le coût maximum de BigQuery à la journée.
Prenez garde cependant que lorsqu’un utilisateur va atteindre son quota personnalisé à la journée, celui-ci ne pourra plus utiliser BigQuery jusqu’à la fin de la journée. De même, si vous définissez un quota sur l’entièreté du projet, lorsque celui-ci sera atteint, plus personne ne pourra utiliser BigQuery à l’intérieur du projet, il faudra passer par un autre projet ayant accès aux tables souhaitées.
À l'heure actuelle, il n’existe pas de manière documentée de définir les quotas en utilisant l’API gcp, tout doit se faire manuellement via la console, ce qui empêche de sauvegarder ces paramètres via de l’infra as code ou de les appliquer facilement sur plusieurs environnements.
Attention aussi à l’utilisation de ce quota qui à l’heure actuelle ne prend pas en compte la réduction des bytes scannés en moins grâce à l’utilisation des partitions.
Il est aussi possible de spécifier le paramètre --maximim_bytes_billed sur l’outil en ligne de commande bq pour qu’une requête ne s’exécute pas si celle-ci dépasse un certain nombre de bytes défini :
bq query --maximim_bytes_billed=10000 `SELECT * FROM table`
Cette option ne peut pas s’appliquer par défaut au niveau du projet, ceci doit être fait manuellement par chaque utilisateur sur chaque requête, rendant cette option difficilement exploitable.
8 - Passer à la facturation forfaitaire
En utilisant le mode de facturation forfaitaire, vous appliquez un tarif fixe sur une période donnée plutôt que d’avoir un prix variant en fonction du nombre de Téraoctets traités, rendant ainsi la facturation beaucoup plus prévisible.
Lorsque vous passez à ce mode de facturation, vous allez réserver au mois ou à l’année un nombre fixe d’emplacements BigQuery que vous pouvez utiliser à tout moment. Un emplacement peut être interprété comme une unité de mesure permettant d’exécuter des requêtes. Il est possible de réserver des tranches de 100 emplacements pour 2000$ par mois.
Avec la facturation à la demande, BigQuery utilise le maximum des ressources disponibles pour exécuter ses requêtes, mais en utilisant le mode forfaitaire, BigQuery va utiliser les réservations disponibles pour pouvoir l’exécuter. Ceci limitera les performances d’une requête dans le cas où celle-ci a besoin de plus d’emplacements que ceux actuellement disponibles, mais réduira aussi les performances des autres utilisateurs qui devront se partager un nombre limité de ressources.
Il n’est pas possible de connaître le nombre exact d’emplacements qu’une requête va demander avant de l’avoir exécutée, cette information est obtenable après exécution de la requête.
Il est néanmoins possible d’obtenir une moyenne à la journée de l’utilisation des slots de BigQuery en utilisant la table `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
Ce mode de facturation est le plus efficace en termes de contrôle de coût, mais n’oubliez pas que les coûts liés au stockage et à l'utilisation des streaming inserts s’appliquera en plus de votre forfait au mois.
