Chargement de 1 trillion de données météo dans TimescaleDB

Construire un entrepôt de données météorologiques, partie 1 : charger 1 trillion de lignes de données météo dans TimescaleDB

Le sens de ce que nous faisons

Pourquoi construire un entrepôt de données météorologiques

• L’idée est de rassembler et d’analyser les données météorologiques historiques du monde entier afin d’étudier les signes du changement climatique
• Avec un entrepôt de données météo à grande échelle, on peut déterminer par région si Jakarta s’est réellement réchauffée, si les tempêtes se sont intensifiées, ou si le Chili est devenu globalement plus chaud ou plus nuageux
• Cela permet d’identifier quelles régions de la planète ont subi le plus de changements climatiques et de quel type ils sont
• Pour effectuer ce type d’analyse à l’échelle mondiale, il faut accélérer les requêtes de l’entrepôt de données, et le volume de données est immense
• La première étape consiste à charger les données dans PostgreSQL. Utiliser TimescaleDB pour accélérer les requêtes temporelles et PostGIS pour accélérer les requêtes géospatiales semble prometteur

Présentation des données

• Utilisation des données du produit de réanalyse climatique ERA5, et non de données d’observation directes
• ERA5 correspond aux résultats d’exécution d’un modèle climatique contraint par des données d’observation : dans les zones riches en observations, il ressemble aux observations, et dans les zones sans observations, il reste physiquement cohérent et conforme aux statistiques climatiques
• ERA5 fournit, depuis 1940, des données horaires couvrant l’ensemble du globe à une résolution de 0,25 degré. Pour des variables comme la température, les précipitations, la nébulosité ou la vitesse du vent, chaque variable représente plus de 750 millions de lignes
• Insérer rapidement ces données dans une base relationnelle n’est pas simple

Méthodes d’insertion des données

Instruction insert sur une seule ligne

• C’est la méthode la plus simple, mais elle est très lente. À 3 000 insertions par seconde, il faudrait environ 8 ans pour charger l’ensemble des données
• Le surcoût est important : analyse syntaxique, vérification des tables et colonnes, plan d’exécution, verrouillage de table, écriture dans les buffers, écriture disque, commit, etc.

insert à valeurs multiples

• Insère plusieurs lignes avec une seule instruction insert. Réduction du surcoût réseau, de l’analyse syntaxique et du plan d’exécution
• psycopg3 est le plus rapide avec 25 000 à 30 000 insertions par seconde
• Mais il faut encore environ 10 mois pour charger l’ensemble des données

Instruction copy

• Méthode optimisée pour le chargement massif de données. Lecture directe depuis des fichiers CSV ou binaires avec optimisation de l’analyse, du plan et de l’utilisation du WAL
• Si les données sont déjà en CSV, il suffit d’utiliser l’instruction copy
• Le copy de psycopg3 permet d’insérer plus de 100 000 lignes par seconde. Même en tenant compte du surcoût, l’ensemble du chargement peut être terminé en moins de 3 mois
• Lors d’insertions rapides sur une longue durée avec copy, il faut surveiller les goulots d’étranglement

copy en parallèle

• Effectuer plusieurs opérations copy en parallèle permet d’augmenter la vitesse
• Pour l’insertion dans une table unique, l’effet de la parallélisation reste limité : au-delà de 16 workers, il n’y a plus de gain notable

Utilisation d’outils externes

• Benchmarks de pg_bulkload et timescaledb-parellel-copy
• pg_bulkload est rapide, mais n’est pas sûr par défaut car il contourne le WAL
• timescaledb-parallel-copy permet une insertion sûre à plus de 300 000 lignes par seconde avec plusieurs workers

Ajustement des paramètres PostgreSQL

• Désactiver fsync et full_page_writes permet d’éviter les écritures disque et d’aller plus vite, mais c’est risqué
• Les tables unlogged n’utilisent pas non plus le WAL et sont donc plus rapides, mais elles sont tronquées en cas de crash. Une hypertable ne peut pas être unlogged

Quelle est la meilleure méthode ?

• Le mieux est d’utiliser psycopg3 pour faire un copy directement vers l’hypertable. Si les données sont en fichiers CSV, utiliser timescaledb-parallel-copy
• Pour la parallélisation, 12 à 16 workers constituent un bon compromis
• En désactivant certaines protections, on peut monter jusqu’à 460 000 lignes par seconde, mais c’est risqué
• Une mise à niveau matérielle permettrait d’aller encore plus vite
• ClickHouse pourrait être plus rapide, mais l’auteur a choisi TimescaleDB pour apprendre PostgreSQL
• À 460 000 lignes par seconde, l’ensemble des données peut être chargé en moins de 20 jours

L’avis de GN⁺

• La tentative d’analyser des données ERA5 en les intégrant dans une base relationnelle est intéressante. Jusqu’ici, il était plus courant d’analyser directement des données NetCDF avec xarray ou dask, mais construire un entrepôt de données permettrait d’exécuter des requêtes plus complexes.
• Il est impressionnant que l’auteur puisse atteindre 460 000 lignes par seconde avec une configuration matérielle vieille de 5 ans. Avec du matériel récent, atteindre 1 million de lignes par seconde semble possible. Mais désactiver fsync et full_page_writes peut compromettre l’intégrité de la base, donc prudence.
• Les capacités de traitement parallèle de PostgreSQL ne semblent pas très utiles pour une table unique. En combinant traitement parallèle et partitionnement, on pourrait obtenir de meilleures performances. Des solutions d’extension horizontale de Postgres comme Citus méritent aussi d’être envisagées.
• Le fait qu’ERA5 puisse être utilisé pour analyser le changement climatique est intéressant. Cela permettrait d’étudier le climat passé dans des régions où les données d’observation manquent. Mais ERA5 reste fondamentalement le résultat d’un modèle. Même s’il est corrigé par des observations, il faut garder à l’esprit cette incertitude.
• Pour une plateforme d’analyse, il est courant d’utiliser des entrepôts de données cloud comme Snowflake ou BigQuery. Mais apprendre en manipulant son propre matériel, comme l’auteur, a aussi beaucoup de valeur. Les données climatiques étant particulièrement volumineuses, les déplacer vers le cloud n’est pas simple. Les résultats réels de l’analyse seront intéressants à suivre.

[jangsc0000]

Les avis GN+ sont rédigés au vouvoiement.. ?

[GN⁺]

Avis Hacker News

En résumé :

• Lors de l’analyse de données géospatiales, il est important de comprendre les systèmes de coordonnées (CRS) et les projections cartographiques. Pour les traitements géospatiaux à grande échelle, Google BigQuery est le plus performant.

• Il faut vérifier par l’expérimentation si une base de données relationnelle est adaptée aux données météorologiques en grille.

• Si les Hypertables de Timescale sont lentes, cela peut être dû à l’index de la colonne timestamp créé par défaut. Il est préférable d’ignorer la création de l’index avec l’option create_default_indexes=>false ou de créer l’index après l’insertion des données.

• L’analyse manque d’explications sur les avantages concrets du transfert des données météo vers un SGBDR. Une architecture serverless + stockage objet peut aussi offrir des temps de réponse très rapides.

• La plupart des jeux de données météo/climat, comme ERA5, sont constitués d’une grille régulière de latitude/longitude, et il n’est pas souhaitable d’en détruire complètement la structure. Il vaut mieux utiliser des versions optimisées pour le cloud, comme ARCO-ERA5.

• Dans PostgreSQL, désactiver le WAL et exécuter périodiquement la commande VACUUM FREEZE peut encore améliorer les performances lors du chargement de très gros volumes de données.

• S’il n’est pas possible d’utiliser COPY, une bonne méthode consiste aussi à encoder les lignes en chaînes JSON, à les envoyer comme paramètre d’une requête unique, puis à utiliser json_to_recordset.