- Le eurofxref-hist.zip de la BCE n’est qu’un ensemble simple de CSV de taux de change, mais avec
curl, gunzip et sqlite3, on peut trouver immédiatement la date 2000-10-26 où le dollar a été le plus fort face à l’euro.
- La source est au format large (wide format), avec des colonnes de devises après
Date, ce qui est peu pratique pour l’analyse, et il faut la remettre en forme en format long (long format) de type Date,Currency,Rate.
- À cause d’une virgule finale (trailing comma) à la fin de chaque ligne, le parseur CSV lit une colonne vide, et il faut supprimer la dernière colonne avec
.iloc[:,:-1] dans Pandas pour obtenir un résultat melt propre.
- Le CSV nettoyé peut être envoyé sur csvbase via HTTP PUT, puis enchaîné avec des outils comme
gnuplot, DuckDB ou sqlite3 pour tracer des graphes, calculer des moyennes mobiles ou charger des CSV via HTTP.
- Des données publiques qu’on peut récupérer sans négociation d’accès, authentification, quota ni documentation API complexe se comportent comme une open API, et même un simple fichier zip peut servir de base d’échange de données pour des applications financières.
Interroger des taux de change avec un seul fichier zip
- La BCE publie les données historiques de taux de change entre l’euro et d’autres devises sous la forme d’un fichier zip officiel.
- Le pipeline ci-dessous télécharge les données, les décompresse, charge le CSV dans une base SQLite en mémoire, trie selon la valeur du USD, puis récupère la première date.
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip \
| gunzip \
| sqlite3 ':memory:' '.import /dev/stdin stdin' \
"select Date from stdin order by USD asc limit 1;"
- La sortie est
2000-10-26.
curl -s réduit le bruit sur la sortie d’erreur standard, et gunzip décompresse le fichier zip.- Sur Mac OS ou BSD, le
gunzip de la famille BSD ne prend pas en charge les fichiers zip, il faut donc utiliser bsdtar -xOf - à la place.
sqlite3 ':memory:' utilise une base en mémoire, et .import /dev/stdin stdin charge l’entrée standard dans une table stdin.
Remettre le CSV en forme et utiliser Pandas melt
- L’en-tête du CSV source est au format large (wide format), du type
Date,USD,JPY,BGN,CYP,CZK,DKK,..., avec des colonnes de devises après la date.
- Pour filtrer et agréger, un format long (long format) de type
Date,Currency,Rate est beaucoup plus simple à manipuler.
- La transformation du format large vers le format long est souvent appelée melt.
- La plupart des bases de données SQL n’ont pas d’opération équivalente à
melt, ce qui rend Pandas utile pour la préparation des données.
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).melt("Date").to_csv(sys.stdout, index=False)'
- Le fichier de la BCE contient une virgule finale (trailing comma) à la fin de chaque ligne, ce qui amène le parseur CSV à ajouter une colonne vide à la fin.
- Cette colonne vide produit des lignes inutiles à la fin du résultat de
melt, il faut donc la supprimer.
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)'
.iloc[:, :-1] sélectionne toutes les lignes et toutes les colonnes sauf la dernière.- Les données de change de la BCE demandent un peu de remise en forme, mais on peut les utiliser immédiatement sans négociation d’accès, paiement, discussion avec un commercial, envoi d’un email, d’un nom d’entreprise ou d’un poste, ni quota, authentification ou lecture de documentation API.
- Comme il suffit de traiter le format de base et la forme des données, cela reste relativement bon parmi les publications de données ouvertes.
Envoyer les données nettoyées sur csvbase
- Le CSV nettoyé peut être envoyé dans une table csvbase pour éviter de répéter le travail de remise en forme.
- Il suffit d’ajouter un
curl à la fin du pipeline existant pour téléverser le CSV en HTTP PUT.
curl -s https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip | \
gunzip | \
python3 -c 'import sys, pandas as pd
pd.read_csv(sys.stdin).iloc[:, :-1].melt("Date")\
.to_csv(sys.stdout, index=False)' | \
curl -n --upload-file - \
'https://csvbase.com/calpaterson/eurofxref-hist?public=yes'
--upload-file - téléverse à l’URL indiquée les données reçues sur l’entrée standard.- Si la table n’existe pas sur csvbase, elle est créée ; sinon, les données sont insérées dans cette table.
-n utilise les identifiants du fichier ~/.netrc.
Tracer un graphe de taux de change avec gnuplot
- La table csvbase nettoyée peut être récupérée en CSV avec
curl, puis reliée à grep, cut et gnuplot.
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
grep USD | \
cut -d, -f 2,4 | \
gnuplot -e "set datafile separator ','; set term dumb; \
plot '-' using 1:2 with lines title 'usd'"
- Cette commande affiche plus de 6 000 points de données sous forme d’art ASCII relativement lisible dans un terminal texte 80x25.
- La configuration
gnuplot est réglée pour lire une entrée CSV et tracer la date et le taux de change sous forme de courbe.
set datafile separator ',' : indique que l’entrée est un CSVset term dumb : dessine en art ASCIIplot - : lit les données depuis l’entrée standardusing 1:2 with lines : trace une ligne à partir des colonnes 1 et 2, c’est-à-dire la date et le tauxtitle 'usd' : nomme la courbe usd
- On peut aussi produire une image SVG, et pour obtenir un rendu de type série temporelle, il faut indiquer que l’axe des x représente le temps, définir le format temporel et régler la rotation des graduations de l’axe des x.
- Pour un usage répété, on peut encapsuler cela dans une fonction Bash
plot_timeseries_to_svg.
Calculer une moyenne mobile avec DuckDB
- Pour visualiser la tendance du taux USD, on peut calculer une moyenne mobile avec DuckDB.
curl -s https://csvbase.com/calpaterson/eurofxref-hist | \
duckdb -csv -c "select Date, avg(value) over \
(order by date rows between 100 preceding and current row) \
as rolling from read_csv_auto('/dev/stdin')
where variable = 'USD';" | \
plot_timeseries_to_svg rolling
- Si
duckdb n’est pas disponible, il n’est pas difficile d’adapter la même requête pour sqlite3.
- DuckDB ressemble à SQLite, mais il est orienté colonnes plutôt qu’orienté lignes.
- DuckDB peut lire directement un CSV via HTTP et en faire une table.
CREATE TABLE eurofxref_hist AS SELECT * FROM
read_csv_auto("https://csvbase.com/calpaterson/eurofxref-hist";);
- DuckDB infère assez bien les types, détecte la taille du terminal et compacte par défaut l’affichage des gros résultats.
- Il peut afficher une barre de progression pour les grosses requêtes et produire des tableaux Markdown.
Quand des données publiques se comportent comme une open API
- Un CSV dans un fichier zip, combiné à des outils faciles à installer avec
brew install ou apt install, permet déjà de faire beaucoup de choses.
eurofxref-hist.zip est une forme extrêmement simple de protocole d’échange de données entre organisations.- Ce fichier zip paraît modeste, mais de nombreuses applications financières l’utilisent chaque jour.
- On peut penser que la BCE conserve la virgule finale parce que la supprimer maintenant casserait beaucoup de code existant.
- Quand des données publiques sont fournies de façon très simple, elles peuvent aussi jouer le rôle d’une open API.
- Si beaucoup d’API ressemblent davantage à de l’échange de données qu’à des appels de fonctions distants, elles ne sont alors pas si différentes, sur le plan fonctionnel, de données publiques faciles à récupérer.
Les URL simples et les verbes HTTP de csvbase
- csvbase attribue une URL à chaque table.
https://csvbase.com/<username>/<table_name>;
https://csvbase.com/calpaterson/eurofxref-hist
- Chaque URL prend en charge quatre principaux verbes HTTP.
GET : récupère le CSV ; dans un navigateur, on peut obtenir une page webPUT : crée une nouvelle table avec un nouveau CSV ou écrase une table existantePOST : ajoute en masse des lignes CSV à une table existanteDELETE : supprime la table concernée
- L’authentification utilise le HTTP Basic Auth.
Notes sur la préparation des données et les pipelines
- Parmi les bases de données SQL qui proposent une fonction équivalente à melt, on peut citer UNPIVOT de Snowflake et PIVOT/UNPIVOT de MS SQL Server.
- L’une des raisons importantes pour lesquelles R et Pandas sont utilisés est la puissance de leurs fonctions de préparation des données.
- Les pipelines Bash fonctionnent en multiprocessus, chaque programme s’exécutant en parallèle dans son propre processus.
- Pendant que
curl télécharge les données depuis le web, grep filtre, sqlite interroge, et un autre curl peut les téléverser à nouveau.
- Cette structure peut offrir des performances compétitives face au cloud.
- En octobre 2000, le taux du dollar face à l’euro était de
0.8252, ce qui signifie qu’avec 1 dollar on pouvait acheter 1,21 euro.
- L’euro a été lancé en janvier 1999 sans billets ni pièces ; au départ, il n’existait qu’au sein du système bancaire, les billets et pièces étant arrivés plus tard.
1 commentaires
Avis sur Hacker News
Je me souviens de ce fichier quand je travaillais à la BCE il y a environ 15 ans.
C’était, de très loin, le fichier le plus téléchargé du site de la BCE, et beaucoup de personnes et d’institutions financières le récupéraient chaque jour pour mettre à jour leurs systèmes.
Chaque jour, pendant les quelques minutes qui suivaient l’heure de publication prévue, le trafic faisait un gros pic, et le fait qu’une fois décompressé ce soit un simple fichier CSV était un choix délibéré.
Cela permettait de servir le fichier de manière fiable et rapide, avec peu de ressources, et la petite équipe qui gérait alors le site public de la BCE pouvait être très fière de cette décision technique consistant à fournir ces données sous forme d’un unique fichier statique.
Ce n’est pas tape-à-l’œil, et il n’y a pas de framework.
Il y a une quinzaine d’années, dans une vieille grande entreprise dont à peu près tout le monde a probablement déjà acheté un produit, je m’occupais des échanges de données entre le système d’historique des produits et des sous-systèmes/parallèles hérités de fusions-acquisitions ; c’était surtout des imports/exports en masse de fichiers à largeur fixe ou délimités, échangés via des serveurs SFTP.
À l’époque, le produit avait déjà 15 ans, et il y avait une vingtaine ou une trentaine de ces sources de données ou exports qui circulaient, mais tout fonctionnait très bien.
Il est très probable que ce soit encore utilisé aujourd’hui sans grands changements, et à l’époque le frontend était en cours de réécriture pour remplacer une ancienne version en Smalltalk.
Parmi les sources de données que nous utilisions, c’était la plus agréable à manipuler.
L’architecte dirait que ZIP n’est pas un format adapté à la spécification pour cet usage, la conformité dirait qu’il faut vérifier les fuites de données personnelles, et l’équipe risques dirait qu’il faut empêcher des acteurs malveillants de télécharger le fichier.
Les responsables web diraient probablement qu’ajouter quoi que ce soit au site nécessite une procédure de changement approuvée.
Les simples téléchargements de fichiers et les fichiers CSV, c’est excellent.
J’aimerais que davantage d’acteurs publient leurs données dans ce genre de format simple, et je meurs un peu intérieurement chaque fois que je dois remplir un « panier » pour télécharger des données du gouvernement américain.
Il existe aussi beaucoup d’outils wrappers qui facilitent ce pipeline précis, et si l’on a besoin d’une vue web et de fonctions un peu plus avancées, des outils comme Datasette sont très bien.
On peut lire le fichier ZIP en flux, traiter le CSV ligne par ligne pour le transformer, puis, avec Postgres, utiliser COPY FROM stdin pour charger les données dans la base.
Cela paraît tellement logique et utile que je suis surpris de ne jamais être tombé dessus jusqu’ici.
Comme j’ai beaucoup de rapports au format CSV, j’ai hâte de l’utiliser pour lancer rapidement des requêtes.
Par exemple, la gestion des guillemets peut diverger entre
"Look, this contains \"quotes\"!",012345et"Look, this contains ""quotes""!",012345, et on peut même voir des exemples encore plus cassés comme"Look, this contains "quotes"!",012345ouLook, this contains "quotes"!,012345.On peut aussi retrouver des traces de tableur avec un zéro initial supprimé, comme dans
"Look, this contains ""quotes""!",12345.En théorie, JSON aussi peut être modifié à la main et devenir un fichier à moitié cassé, mais en pratique je vois très rarement des gens faire ça à des fichiers JSON, et les valeurs comme les numéros de série ont davantage tendance à rester des chaînes en JSON plutôt que des entiers dont une application « serviable » supprimerait les zéros initiaux.
Pourquoi font-ils ça au juste, y a-t-il une raison valable ?
Remplacer le CSV par un document JSON empaqueté en ZIP offrirait les mêmes avantages.
Le vrai problème, c’est qu’il y a trop d’obstacles pour télécharger simplement un fichier unique servi statiquement.
J’ai déjà créé une API pour une administration, dont les données ne changeaient qu’une fois par an, ou étaient révisées très rarement.
L’ensemble du jeu de données pouvait tenir dans un seul fichier ZIP de moins de 1 Mo, mais le solution architect a fait grossir le projet en définissant les exigences.
Il a interdit l’usage du cache au motif que les données auraient pu changer exactement au moment de la requête, ce qui a donné une API lente, et un système de webhooks excessivement complexe pour notifier les abonnés des changements de données.
Un simple fichier ZIP aurait peut-être été trop simpliste, mais ce n’était pas très éloigné de ce dont on avait réellement besoin.
Si l’on veut faire plus sophistiqué, on peut ajouter un webhook déclenché quand le fichier change, afin que le client sache quand le retélécharger sans avoir à le sonder une fois par jour.
Ou bien un simple script qui envoie un e-mail à une mailing list prédéfinie lorsqu’il y a un changement suffit largement.
S’il n’a pas changé, on reçoit une réponse HTTP 304 vide ; s’il a changé, on récupère à nouveau le ZIP de moins de 1 Mo avec un nouvel ETag. Je ne vois pas ce qui manque ici.
Le cache ajoute de la complexité et crée aussi le risque de devoir le revalider manuellement ; il est donc possible que le solution architect ait eu raison.
Si l’on doit télécharger un fichier de 565 Ko pour obtenir un seul résultat,
2000-10-26, c’est une API horrible.Si l’on veut récupérer beaucoup de données pour les resservir aux utilisateurs, un CSV empaqueté en ZIP est excellent, et je le préfère de loin à du protobuf pour des horaires de trains en temps réel de transports publics, souvent mal pris en charge dans plusieurs langages.
Mais si on le traite comme une API destinée à obtenir une valeur unique, c’est un gaspillage énorme, et j’espère que personne n’intègre ça dans une appli de cette manière.
L’article lui-même est chouette, mais le titre donne trop l’impression d’une affirmation provocatrice.
Il n’y a absolument aucune raison de les demander plus d’une fois par jour, et les personnes qui utilisent ces données veulent probablement des filtres ou agrégations très différents les uns des autres.
Si l’objectif est d’obtenir le taux de change actuel, alors oui, c’est une mauvaise conception, mais il existe d’autres services pour cet usage, et ce fichier correspond bien aux cas d’usage typiques.
Ce n’est pas directement lié aux API, mais à une époque où je faisais du support pour une application de gestion foncière, elle fonctionnait bien dans des bureaux satellites lents, avec des lignes qui pouvaient être du niveau de l’ISDN, jusqu’à la sortie d’une nouvelle version ; avec cette nouvelle version, plus rien ne marchait.
Le fournisseur disait de la faire tourner sur un serveur RDP, ce qui me semblait absurde ; en enquêtant, j’ai découvert qu’un appel faisait, sans aucune raison, un
SELECT * FROM sometable, alors que les autres appels dans la même exécution utilisaient de vraies clauses SQL select appropriées.Quand nous l’avons signalé au fournisseur, il a d’abord été très perplexe sur la manière dont nous avions pu le découvrir, puis il a fini par sortir une nouvelle version corrigée, utilisable même sur des lignes lentes.
J’ai du mal à comprendre pourquoi leurs propres tests ne l’avaient pas détecté et pourquoi ils avaient poussé une solution coûteuse chez le client.
Si vous avez regardé un tant soit peu de JavaScript moderne, 565 Ko et la logique pour y trouver une grosse valeur sont, selon n’importe quel critère raisonnable, minuscules.
Certains considèrent qu’une API, c’est « une manière d’obtenir des données, même si l’on reçoit tout sans filtrage » ; personnellement, je vois plutôt le téléchargement de toute une table comme le téléchargement d’un modèle de données sur lequel aucune logique n’agit, tandis qu’une API est une logique qui filtre et renvoie une partie du modèle selon la façon qui m’intéresse.
J’ai beaucoup construit de logiciels financiers, côté backend comme frontend, et côté frontend il est malheureusement courant de transférer ce volume de « données » avant même d’atteindre les vraies données.
Côté backend, ce n’est qu’une décision de conception, et rien n’est plus rapide qu’une tâche cron nocturne qui parse les taux de change, génère un
todays-rates.jsonadapté à l’usage prévu, puis le sert comme fichier statique à des applis mobiles, web et microservices.Rien ne dit qu’une appli mobile doive forcément consommer directement ce ZIP-CSV-over-HTTP.
Il existe une optimisation très simple pour ceux qui se plaignent de devoir récupérer un gros fichier chaque fois qu’ils ont besoin d’un petit bout de données.
Si l’on garantit que le fichier est append-only et qu’on utilise une compression de type HTTP gzip/brotli au lieu d’un fichier ZIP, on peut récupérer uniquement les nouvelles données depuis la dernière mise à jour via des requêtes par plage.
Ajoutez à cela un en-tête de somme de contrôle pour se rassurer, et vous obtenez une API incrémentale assez efficace tout en restant très simple.
Bien sûr, il faut conserver un état, payer le coût du premier téléchargement et de la maintenance de cet état, et c’est inefficace si l’on a besoin, une seule fois, exactement du taux EUR/JPY du 22/08/2007.
C’est encore très en chantier, mais le code actuel de « qualité recherche » est ici : https://pypi.org/project/csvbase-client/
https://github.com/gtsystem/python-remotezip
Rien qu’un patch quotidien permettrait de réduire fortement la bande passante nécessaire pour maintenir le fichier à jour de mon côté.
Cela vaut si télécharger quelques centaines de Ko de plus par jour a de l’importance, ce qui n’est probablement pas le cas la plupart du temps.
Il y a une coquille dans l’exemple
sqlite.Ce n’est pas visible sur la capture d’écran, mais il faut ajouter l’argument -csv à
sqlite.Je vais le rajouter et invalider le cache. Je vérifierai ce qui cloche après avoir couché les enfants.
Modification : la raison pour laquelle cela fonctionnait chez moi est que j’avais
.separator ','dans~/.sqliterc.J’ai dû le définir par défaut après avoir réalisé qu’à l’époque j’importais surtout des fichiers CSV.
Petite digression : même si l’euro n’a d’abord existé que sous forme électronique, il avait des taux de change fixes avec les anciennes monnaies des pays membres de la zone euro.
Il était notamment arrimé au Deutsche Mark allemand, une monnaie établie et digne de confiance.
Donc, pour expliquer « pourquoi l’euro initial était faible », il faut aussi expliquer pourquoi le DEM était faible à l’époque, et l’explication de ce paragraphe ne semble pas passer ce test.
Pour les petits problèmes où l’on peut télécharger toute la base de données à chaque fois et la traiter en lecture seule, il ne faut pas sous-estimer la valeur de la simplicité.
J’aime bien SQLite parce qu’il est aussi portable qu’un fichier
.jsonou.csv, tout en étant mieux préparé à interagir comme une base de données.clickhouse-local, on peut aussi traiter de vieux fichiers CSV comme une base de données.L’essentiel est là.
Ce qu’il n’a pas fallu faire dans ce cas : négocier des droits d’accès, par exemple payer ou parler à un commercial, mettre son adresse e-mail, le nom de son entreprise et son poste dans la base de prospects de quelqu’un, respecter des quotas, s’authentifier, lire la documentation d’une API, traiter des problèmes plus sérieux que le format et la structure de base.
La bande passante n’est pas gratuite.
SQLite peut lire et écrire des fichiers ZIP.
https://sqlite.org/zipfile.html
Je me demande s’il est possible de décompresser avec
sqlite3au lieu degunzip.Si l’on peut enregistrer le fichier sur disque, on peut faire comme ceci :
sqlite3 -newline '' ':memory:' "SELECT data FROM zipfile('eurofxref-hist.zip')" \| sqlite3 -csv ':memory:' '.import /dev/stdin stdin' \"select ...;"