Quand est-il judicieux d'utiliser Google BigQuery ?

Au cours de ma carrière, j'ai dû rédiger des requêtes analytiques complexes pour différents types de rapports et de graphiques. Le plus souvent, il s'agissait d'un graphique qui affichait des données agrégées par date, semaine, trimestre, etc. Habituellement, ces rapports sont créés pour aider les clients à identifier les tendances et illustrer les performances de leur entreprise à un niveau élevé. Mais que se passe-t-il lorsque les data scientists et les ingénieurs doivent créer un rapport beaucoup plus complet, basé sur un ensemble de données volumineuses ?

Dans le cas où le rapport est basé sur un petit ensemble de données, la tâche peut être résolue en écrivant une requête SQL sous une base de données relationnelle. Dans cette étape, il est important de connaître les bases pour écrire des requêtes et comment les rendre plus rapides et efficaces. Cependant, le rapport dépend parfois d'un plus grand ensemble de données (par exemple, des millions de lignes et plus dans un tableau), mais le rapport ne dépend pas des variables d'entrée (paramètres), ou vous pouvez constater que le nombre de valeurs est assez petit. Dans de tels scénarios, une requête SQL peut être lente, il n'est donc pas optimal pour les utilisateurs d'attendre que la requête soit exécutée. La pratique la plus courante dans de tels cas consiste à exécuter une requête à l'avance, avant que le client ne demande un rapport.

En outre, cela nécessite la mise en œuvre de certaines fonctionnalités de mise en cache, afin que le client puisse prendre des données du cache au lieu d'exécuter une requête en temps réel. Cette approche fonctionne parfaitement, à condition que vous n'ayez pas besoin d'afficher des données en temps réel. Il peut afficher des données calculées une heure ou même un jour plus tôt. Ainsi, le rapport/graphique réel est affiché à l'aide de données mises en cache et non basées sur des données en temps réel.

Se tourner vers Google BigQuery

Pendant que je travaillais sur un projet d'analyse dans l'industrie pharmaceutique, j'avais besoin de graphiques prenant le code postal et le nom du médicament comme paramètres d'entrée. J'avais également besoin de montrer des comparaisons entre des médicaments dans des régions spécifiques des États-Unis.

La requête analytique était très complexe et a duré environ 50 minutes sur notre serveur Postgres (processeur quadricœur avec 16 Go de RAM). Je n'ai pas pu l'exécuter à l'avance et mettre en cache les résultats, car la requête prenait les codes postaux et les médicaments comme paramètres d'entrée, il y avait donc des milliers de combinaisons, et il était impossible de prédire quel client choisirait.

Même si je voulais essayer d'exécuter toutes les combinaisons de paramètres d'entrée, ma base de données aurait très probablement planté. Il était donc temps de choisir une approche différente et de choisir une solution facile à utiliser. Ce tableau était important pour le client, cependant, le client n'était pas prêt à s'engager à apporter de grands changements à l'architecture ou à migrer entièrement vers une autre base de données.

Sur ce projet particulier, nous avons essayé quelques approches différentes :

• Mise à l'échelle verticale du serveur (ajout de RAM et de CPU au serveur Postgres)
• Utilisation de bases de données alternatives telles qu'Amazon Redshift et autres.
• Nous avons également recherché une solution NoSQL, mais la plupart d'entre elles sont assez complexes et nécessitent de nombreux changements d'architecture, dont beaucoup auraient été trop importants pour le client.

Enfin, nous avons essayé Google BigQuery. Il a répondu à nos attentes et nous a permis de faire le travail sans faire d'énormes changements que le client serait réticent à approuver. Mais qu'est-ce que Google BigQuery et comment fonctionne-t-il ?

BigQuery est un service Web basé sur REST qui vous permet d'exécuter des requêtes SQL analytiques complexes sur de grands ensembles de données. Après avoir importé les données dans BigQuery et exécuté la même requête que Postgres (la syntaxe est étrangement similaire), notre requête s'exécutait beaucoup plus rapidement et prenait environ une minute. En fin de compte, nous nous sommes retrouvés avec une amélioration des performances 50x simplement en utilisant un service différent. Il convient de noter que les autres bases de données n'offraient pas le même gain de performances, et soyons généreux et disons simplement qu'elles n'étaient même pas proches. Pour être honnête, j'ai été vraiment impressionné par le gain de performances fourni par BigQuery, car les chiffres étaient meilleurs que nous ne l'avions espéré.

Malgré cela, je ne présenterais pas BigQuery comme la meilleure solution de base de données au monde. Bien que cela ait bien fonctionné pour notre projet, il présente encore de nombreuses limitations, telles qu'un nombre limité de mises à jour dans la table par jour, des limitations sur la taille des données par demande, etc. Vous devez comprendre que BigQuery ne peut pas être utilisé pour remplacer une base de données relationnelle et qu'il est orienté sur l'exécution de requêtes analytiques, et non sur de simples opérations et requêtes CRUD.

Dans cet article, je vais essayer de comparer l'utilisation de Postgres (ma base de données relationnelle préférée) et de BigQuery pour des scénarios de cas d'utilisation réels. En outre, je voudrais fournir quelques suggestions en cours de route, à savoir mon avis sur le moment où il est réellement judicieux d'utiliser BigQuery.

Exemple de données

Pour comparer Postgres et Google BigQuery, j'ai pris des informations démographiques publiques pour chaque pays regroupées par pays, âge, année et sexe (vous pouvez télécharger les mêmes données à partir de ce lien).

J'ai ajouté les données à quatre tables :

1. populations
2. locations
3. age_groups
4. populations_aggregated

Le dernier tableau ne contient que les données agrégées des trois tableaux précédents. Voici le schéma DB :

La table populations avec laquelle j'ai fini contient plus de 6,9 ​​millions de lignes. Ce n'est pas trop, mais c'était suffisant pour mon test.

Sur la base d'exemples de données, j'ai essayé de créer des requêtes pouvant être utilisées pour créer des rapports et des graphiques analytiques réels. J'ai donc préparé des requêtes pour les prochains rapports :

1. Population aux États-Unis agrégée par années.
2. Population en 2019 pour tous les pays en commençant par les plus grands pays.
3. Les cinq nations les plus « plus anciennes » par an. « Les plus âgés » désigne les pays où le pourcentage de personnes de plus de 60 ans par rapport au nombre total de personnes est le plus élevé. La requête doit donner cinq résultats par année.
4. Les cinq premières nations agrégées par année, où la différence entre la population masculine et féminine est la plus grande.
5. Obtenez l'âge médian (moyen) par pays pour chaque année en partant des pays « les plus âgés » vers les « plus jeunes ».
6. Trouvez les cinq premiers pays "mourants" chaque année. «Mourir» désigne les pays dans lesquels la population diminue (le dépeuplement est le plus élevé).

Les requêtes #1, #2 et #6 sont assez simples et directes, mais les requêtes #3, #4 et #5 n'étaient pas si faciles à écrire, du moins pour moi. Veuillez noter que je suis un ingénieur back-end et que l'écriture de requêtes SQL complexes n'est pas exactement une de mes spécialités, donc quelqu'un avec plus d'expérience SQL pourrait probablement créer des requêtes plus intelligentes. Cependant, pour le moment, nous devons vérifier comment Postgres et BigQuery traitent les mêmes requêtes avec les mêmes données.

J'ai construit 24 requêtes au total :

• 6 pour Postgres DB, qui utilisent des tables non agrégées ( populations , locations , age_groups )
• 6 pour Postgres DB, qui utilisent la table populations_aggregated
• 6+6 requêtes pour BigQuery qui utilisent des tables agrégées et non agrégées.

Permettez-moi de partager les requêtes BigQuery n° 1 et n° 5 pour les données agrégées afin que vous puissiez comprendre la complexité des requêtes simples (n° 1) et complexes n° 5.

Requête sur la population aux États-Unis agrégée par années :

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

Requête sur l'âge médian par pays pour chaque année triée du plus ancien au plus jeune :

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

Remarque : Vous pouvez trouver toutes les requêtes dans mon référentiel bitbucket (le lien se trouve à la fin de l'article).

Résultats de test

Pour exécuter des requêtes, j'ai utilisé deux serveurs Postgres différents. Le premier a 1 cœur de processeur et 4 Go de RAM, soutenu par un disque SSD. Le second a 16 cœurs de processeur, 64 Go de RAM et utilisait également un lecteur SSD (le deuxième serveur a 16 fois le potentiel du processeur et de la RAM).

Notez également qu'il n'y a pas eu de charge sur les bases de données lors de l'exécution du test. Je les ai créés uniquement pour exécuter des requêtes. Dans des situations réelles, les requêtes prendront plus de temps, car d'autres requêtes peuvent s'exécuter en même temps. De plus, ces requêtes exécutées en parallèle peuvent verrouiller des tables. Pour vérifier la vitesse des requêtes, j'utilisais pgAdmin3 et l'interface Web BigQuery.

Dans mon test, j'ai obtenu ces résultats:

Permettez-moi de montrer ces résultats dans un graphique à barres pour la requête n ° 1 et la requête n ° 5.

Remarque : La base de données Postgres était située sur un serveur basé aux États-Unis et je suis basé en Europe. Il y a donc eu un délai supplémentaire dans la transmission des données par Postgres.

Performances et conclusions de BigQuery

Sur la base des résultats que j'ai obtenus, j'ai tiré les conclusions suivantes:

• En cas de mise à l'échelle verticale de Postgres, même à 16 fois, il ne donne que 10 à 25 % de performances en exécutant une seule requête. En d'autres termes, un serveur Postgres avec un seul cœur de processeur et 4 Go de RAM exécutait des requêtes avec un temps très similaire au temps nécessaire pour un serveur avec 16 cœurs de processeur et 64 Go de RAM. Bien sûr, des serveurs plus grands peuvent traiter des ensembles de données beaucoup plus volumineux, mais cela n'améliore pas beaucoup le temps d'exécution des requêtes.
• Pour Postgres, les jointures avec de petites tables (la table locations a environ 400 lignes et age_groups a 100 lignes) ne produisent pas une différence énorme par rapport aux requêtes en cours d'exécution sous des données agrégées situées dans une table. De plus, j'ai constaté que pour les requêtes qui s'exécutent en une à deux secondes, les requêtes avec des jointures internes sont plus rapides, mais pour les requêtes de longue durée, la situation est différente.
• Dans BigQuery, la situation avec les jointures est totalement différente. BigQuery n'aime pas les jointures. La différence de temps entre les requêtes, qui utilisent des données agrégées et non agrégées, est assez importante (pour les requêtes #3 et $5, elle était d'environ deux secondes). Cela signifie que, pour BigQuery, vous pouvez effectuer autant de sous-requêtes que vous le souhaitez, mais pour de bonnes performances, la requête doit utiliser une table.
• Postgres est plus rapide pour les requêtes qui utilisent une simple agrégation ou un filtrage ou qui utilisent un petit ensemble de données. J'ai constaté que les requêtes qui prennent moins de cinq secondes dans Postgres fonctionnent plus lentement dans BigQuery.
• BigQuery fonctionne bien mieux pour les requêtes longues. À mesure que la différence de taille d'ensemble de données augmente, la différence de temps nécessaire à l'exécution de ces requêtes augmente également.

Quand il est judicieux d'utiliser BigQuery

Revenons maintenant au problème central abordé dans cet article : quand devez-vous réellement utiliser Google BigQuery ? Sur la base de mes conclusions, je suggérerais d'utiliser BigQuery lorsque les conditions suivantes sont remplies :

• Utilisez-le lorsque vous avez des requêtes qui s'exécutent plus de cinq secondes dans une base de données relationnelle. L'idée de BigQuery est d'exécuter des requêtes analytiques complexes, ce qui signifie qu'il est inutile d'exécuter des requêtes qui effectuent une simple agrégation ou un filtrage. BigQuery convient aux requêtes « lourdes », celles qui fonctionnent à l'aide d'un grand ensemble de données. Plus l'ensemble de données est volumineux, plus vous êtes susceptible de gagner en performances en utilisant BigQuery. L'ensemble de données que j'ai utilisé ne faisait que 330 Mo (mégaoctets, pas même gigaoctets).
• BigQuery n'aime pas les jointures, vous devez donc fusionner vos données dans une seule table pour obtenir un meilleur temps d'exécution. BigQuery permet d'enregistrer les résultats de la requête dans une nouvelle table. Pour créer une nouvelle table agrégée, importez simplement toutes vos données dans BigQuery, exécutez une requête qui consolidera toutes les données et enregistrez-la simplement dans une nouvelle table.
• BigQuery convient aux scénarios dans lesquels les données ne changent pas souvent et où vous souhaitez utiliser le cache, car il dispose d'un cache intégré. Qu'est-ce que ça veut dire? Si vous exécutez la même requête et que les données des tables ne sont pas modifiées (mises à jour), BigQuery utilisera simplement les résultats mis en cache et n'essaiera pas d'exécuter à nouveau la requête. De plus, BigQuery ne facture pas d'argent pour les requêtes mises en cache. Remarque : Même les requêtes mises en cache prennent 1 à 1,2 seconde pour renvoyer des résultats.
• Vous pouvez également utiliser BigQuery lorsque vous souhaitez réduire la charge sur votre base de données relationnelle. Les requêtes analytiques sont « lourdes » et leur utilisation excessive dans une base de données relationnelle peut entraîner des problèmes de performances. Ainsi, vous pourriez éventuellement être obligé de penser à faire évoluer votre serveur. Cependant, avec BigQuery, vous pouvez déplacer ces requêtes en cours d'exécution vers un service tiers, afin qu'elles n'affectent pas votre base de données relationnelle principale.

Enfin, quelques mots supplémentaires sur l'utilisation de BigQuery dans la vraie vie. Dans notre projet réel, les données des rapports changeaient sur une base hebdomadaire ou mensuelle, nous pouvions donc importer manuellement les données dans BigQuery. Cependant, si vos données changent fréquemment, la synchronisation des données entre votre base de données relationnelle et BigQuery peut ne pas être aussi simple, et c'est une mise en garde qu'il convient de garder à l'esprit.

Liens

Vous pouvez trouver les exemples de données utilisés dans cet article ici, tandis que les requêtes et les données au format CSV sont accessibles ici.