Réduire les coûts de BigQuery et Looker grâce à ClickHouse - Partie 1

Cet article s'inscrit dans une série en plusieurs volets, découpée selon les étapes logiques de mise en place du processus. Le sujet comportant de nombreuses conditions si-alors-sinon liées à la complexité de l'écosystème et des technologies, j'ai choisi de scinder l'article afin que chacune soit traitée séparément, sans alourdir l'ensemble. La première partie couvre l'essentiel de la théorie ; la seconde se concentrera sur une implémentation de base.

D'emblée, pour les non-initiés : ClickHouse est un data warehouse concurrent de BigQuery. Sa marque de fabrique : un datastore extrêmement performant, capable d'exécuter des opérations bien plus rapidement que la plupart des autres data warehouses du marché. Une solution idéale, donc, pour stocker et servir des données à des workloads qui les interrogent en permanence, comme les outils de BI tels que Looker.

Cet article a été rédigé avec l'assistance technique de notre partenaire Aiven, fournisseur de référence d'offres DBaaS, qui propose notamment un service ClickHouse managé. Pour plus d'exhaustivité, j'inclus également des informations sur ClickHouse, qui propose son propre service managé.

BigQuery est une excellente plateforme pour les analyses à grande échelle ou les workloads ML, et il est souvent associé à des outils de BI comme Looker pour la visualisation ou le reporting. Malheureusement, ce type de requêtes se heurte fréquemment à des problèmes de performance liés aux ressources ou aux contraintes de coûts qui pèsent sur celles-ci. S'ajoute à cela l'éléphant — parfois irritable — au milieu de la pièce : le coût par requête facturé par BigQuery.

De plus, après les récentes hausses tarifaires sur la facturation à la requête, BigQuery est devenu plus onéreux, en particulier lorsqu'il est connecté à un outil qui interroge les données en permanence. C'est pourquoi de nombreux clients cherchent à réduire les coûts de leurs workloads et redécouvrent ce que les Customer Reliability Engineers de DoiT International répètent depuis des années : leurs outils de BI figurent parmi les principaux contributeurs aux coûts BigQuery, voire en tête, et constituent l'une des plus belles cibles d'optimisation.

Lors de Google Next 2023, dans ma présentation (l'enregistrement a malheureusement été retiré, mais les diapositives restent disponibles), j'ai abordé un sujet d'optimisation des coûts que de nombreux clients ont mis en œuvre avec d'excellents résultats : migrer certains workloads coûteux hors de BigQuery, en particulier ceux à forte charge de calcul, vers d'autres outils moins chers et mieux adaptés.

Dans le prolongement direct de cette idée, je propose ici une méthode alternative de présentation des données : utiliser le data warehouse ClickHouse comme couche de cache et de service entre BigQuery et les outils de BI tels que Looker. Les workloads de reporting et de visualisation peuvent ainsi être déplacés hors de BigQuery vers une plateforme plus abordable et plus performante.

Remarque importante : j'utilise ici ClickHouse, mais la même logique s'applique à d'autres outils ou bases de données selon les besoins. Je m'appuie sur ClickHouse parce qu'il excelle à servir rapidement des données aux outils de visualisation, et parce que je le rencontre suffisamment souvent pour justifier cet article.

Vous avez peut-être vu passer ces derniers temps de nombreuses publicités sur LinkedIn ou ailleurs vantant ClickHouse comme moins cher ou plus rapide que BigQuery. Les esprits brillants derrière cette campagne s'appuient sur les mêmes fondations que le concept proposé dans cet article. Nos data architects chez DoiT International ont observé à grande échelle l'impact des évolutions tarifaires sur une très large partie des clients GCP, tout en imaginant des moyens créatifs de leur faire économiser de l'argent.

ClickHouse est un data warehouse comparable à BigQuery, mais avec des différences architecturales majeures. Il repose sur une architecture plus monolithique, articulée autour de modules définis par l'utilisateur pour booster massivement les performances. Grâce à cette infrastructure modulaire, il peut s'avérer nettement plus performant que BigQuery ou que d'autres solutions de data warehousing du marché pour de nombreuses tâches.

Cette citation de PostHog résume parfaitement la situation :

" L'écart de performance entre BigQuery et ClickHouse peut être considérable. BigQuery peut mettre plusieurs dizaines de secondes à exécuter une requête. ClickHouse, correctement réglé, peut exécuter la même requête sur des téraoctets de données en moins d'une seconde. "

" Élémentaire, mon cher Watson, pour faire des économies " – Sherlock Holmes (revisité version cloud)

La réponse, c'est la réduction des coûts ! Ou, pour être plus précis : un potentiel de réduction des coûts.

Pourquoi ? Parce que vous interrogez un data warehouse qui ne facture pas l'usage à la requête, mais à un tarif forfaitaire : vous pouvez donc requêter autant que les ressources le permettent, sans facturation à l'usage.

Dans cet article, je m'appuie sur l'offre ClickHouse managée d'Aiven pour la grille tarifaire. C'est le service avec lequel j'ai testé cet article, et la connexion de ClickHouse — comme des autres offres Aiven — à un environnement GCP s'y fait très simplement.

Les forfaits business d'Aiven démarrent autour de 500 $/mois pour le niveau startup et 2 000 $/mois pour le niveau business — autant de repères tarifaires pour évaluer la pertinence de cette approche dans votre environnement.

Plusieurs seuils de rentabilité conditionnent l'intérêt économique de cette solution.

Si vous dépensez moins de 500 $/mois sur BigQuery ou pour votre outil de visualisation, il y a de fortes chances que cette approche ne génère aucune économie ; en revanche, vous bénéficierez très probablement de gains de performance significatifs.

En gardant à l'esprit les seuils de 500 $ et 2 000 $ d'Aiven, cela correspond respectivement à 1 Tio et 321 Tio (320 Tio + 1 Tio offert) de données traitées par mois selon le modèle de facturation à la demande de BigQuery. Vous pouvez aussi exécuter cette requête [1] sur le projet qui exécute les jobs de votre outil de BI.

À titre de comparaison, ClickHouse propose des plans dotés d'autoscaling et d'instances un peu plus grandes à des tarifs très proches. Selon le dimensionnement et vos besoins en développement/production, ils peuvent constituer une alternative plus économique.

Pour les Editions BigQuery, il n'existe pas de valeur simple indiquant le seuil de rentabilité, car les Editions reposent sur un autoscaler — autrement dit, sur une tarification à barème glissant. La méthode la plus simple consiste à exécuter la requête [1] mentionnée plus haut sur le projet où Looker exécute ses requêtes : elle calculera les coûts approximatifs liés à Looker.

Remarque sur cette requête :

Vous devrez peut-être ajuster la regex ou la remplacer entièrement par le nom de votre service account Looker pour obtenir des coûts précis, en particulier si vous utilisez un service account Looker non standard ou plusieurs comptes.

Une fois cette valeur calculée, vous pourrez déterminer si elle se situe au-dessus ou en dessous des seuils de 500 $ et 2 000 $ évoqués plus haut. Et si la performance est un enjeu, le surcoût peut tout à fait se justifier pour ne plus avoir à patienter 20 secondes avant qu'un dashboard Looker n'affiche des données en temps réel.

L'idée : répliquer les données entre BigQuery et ClickHouse, puis connecter à ClickHouse les outils de BI gourmands en requêtes — ou tout autre outil intensif en requêtes. En théorie, cela élimine les coûts par requête liés à BigQuery et réduit considérablement la facture, puisque vous utilisez désormais une ressource à prix fixe pour vos requêtes.

Cette approche peut aussi servir si vous avez besoin de temps réel ou de capacités de requêtage bien plus rapides. ClickHouse peut en effet être un moteur de requêtage BIEN plus performant lorsqu'il est correctement réglé.

Toute démarche susceptible de générer des économies importantes commence par quelques étapes préliminaires, et celle-ci ne fait pas exception.

Première chose à déterminer : quel volume de données et quelles tables votre outil de BI consulte-t-il dans BigQuery ? La question paraît générique, mais elle n'a rien de trivial dans BigQuery ; comme par le passé, je vous fournis une requête pour vous y aider [2]. Attention, cette requête peut potentiellement coûter cher : pensez à vérifier l'estimation dans l'UI de BQ avant de l'exécuter.

Deuxième tâche, plus détaillée : mener une analyse de votre processus d'ingestion. Vous devez comprendre ses rouages et examiner la manière dont les données sont actuellement ingérées dans BigQuery. La raison : nous allons modifier ce processus afin de dédoubler les données entre BigQuery et la nouvelle instance ClickHouse, pour qu'elles soient propagées aux deux.

Pour clore cette première partie, je souhaite donner quelques repères sur le dimensionnement et la création de l'instance ClickHouse qui servira tout au long de cette série.

Ayant mené plusieurs migrations BigQuery -> * (un autre système de base de données), on me demande souvent comment bien dimensionner le système cible. La réponse, malheureusement, c'est qu'il n'existe pas vraiment de méthode infaillible.

J'ai mené des analyses sur les données extraites de BigQuery par les requêtes, sur le volume de cache hits et sur l'utilisation des slots — mais BigQuery est un système si différent de la plupart des autres bases de données qu'on ne peut pas vraiment établir une comparaison sur la même base. Ces exercices m'ont montré que la chose est faisable, mais BigQuery n'expose pas certaines métriques nécessaires, comme les données uniques requêtées ou la consommation CPU/mémoire des slots.

Cela dit, ces exercices m'ont appris une chose : démarrez toujours avec au moins 8 Go de RAM pour une base sur laquelle vous effectuez des requêtes basiques de manière régulière ; et si vous mettez en place une véritable base de visualisation en production avec plus de 10 utilisateurs effectuant des requêtes lourdes en calcul tout au long de la journée, alors 16 Go constituent un minimum. Plus la complexité des requêtes augmente, plus l'ajout de CPU est bénéfique, mais une machine bi-CPU est un bon point de départ quel que soit le workload, la mémoire étant bien plus déterminante que le CPU pour la plupart des capacités de requêtage.

En suivant ces repères, je commencerais un proof-of-concept avec une instance très modeste — par exemple le tier hobbyist d'Aiven ou l'instance development de ClickHouse — pour mettre les choses en route, puis je monterais en gamme afin d'ajuster le dimensionnement selon les besoins.

Plutôt que de proposer une procédure complète qui sera inévitablement obsolète à la prochaine évolution de l'UI, je renvoie directement à la documentation Aiven.

Première étape : créer un compte Aiven, comme indiqué ici.

Deuxième étape : créer un VPC Aiven, comme expliqué ici, puis le mettre en peering avec votre VPC GCP, comme décrit ici.

Étape suivante : créer le service ClickHouse, comme indiqué ici. Cela peut prendre quelques minutes : profitez-en pour aller chercher un café ou un thé.

Vérifiez ensuite que tout fonctionne, soit via le conteneur Docker, soit via l'outil CLI depuis votre VPC en peering, et vous serez prêt à l'utiliser.

Comme précédemment, je me contente de renvoyer à la documentation de l'éditeur, car tout ce que j'écrirais ici serait inévitablement périmé au prochain changement.

Voici le guide de démarrage rapide de l'éditeur pour créer une instance.

Je recommande de configurer GCP Private Service Connect vers votre instance ClickHouse, comme indiqué ici. C'est ce qui garantit le plus haut niveau de sécurité avec un minimum de configuration pour exploiter votre instance.

Cette première partie n'était qu'une introduction à ce que nous allons mettre en place. Dans la prochaine, j'expliquerai comment intégrer vos données dans ClickHouse et configurer la réplication entre ClickHouse et BigQuery.