Tag #sql
Analyser efficacement un fichier CSV
Je travaille régulièrement sur des traitements de fichiers CSV. J'apprécie beaucoup ce format de fichier car il se génère et se lit facilement, qu'on soit informaticien ou non. J'ai d'ailleur écrit 2 librairies PHP pour lire, générer et valider des CSV : deblan/csv et deblan/csv-validator.
Lors des mes analyses, je me confronte parfois à des fichiers qui comportent beaucoup de colonnes et c'est un enfer d'utiliser un tableur pour visualiser et filtrer ces tableaux très larges.
Après quelques recherches, j'ai découvert deux projets qui se complètent à merveille ! Le premier est un outil qui transforme un fichier CSV en une base de données SQLite : csvs-to-sqlite. Le second génère une interface web (avec un serveur web intégré) pour faire des requêtes SQL sur une base de données SQLite : datasette.
Ce sont des outils écrits en Python qui s'installent et s'utilisent en une poignée de secondes.
$ sudo pip3 install csvs-to-sqlite datasette
Pour illustrer, je vais traiter le fichier CSV de la base officielle des codes postaux disponible sur data.gouv.fr.
$ wget -O codes_postaux.csv https://datanova.legroupe.laposte.fr/explore/dataset/laposte_hexasmal/download/\?format\=csv\&timezone\=Europe/Berlin\&use_labels_for_header\=true
$ csvs-to-sqlite -s ";" codes_postaux.csv codes_postaux.db
$ datasette serve codes_postaux.db
Serve! files=('codes_postaux.db',) (immutables=()) on port 8001
INFO: Started server process [8550]
INFO: Waiting for application startup.
INFO: Application startup complete.
INFO: Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit)
On peut maintenant accéder à http://127.0.0.1:8001 depuis un navigateur et commencer à analyer et traiter les données :
Et le plus intéressant pour moi, c'est de pouvoir écrire des requêtes SQL affiner vraiment les données affichées :
Enfin, on peut générer un nouveau fichier CSV (ou JSON) avec les données filtrées.
Simple, rapide et efficace !
Équivalent du MATCH AGAINST de MySQL sur PostgreSQL
Le blog est propulsé sur un système de gestion de contenu écrit sur Symfony. Les données sont gérées dans une base MariaDB et ça tourne très très bien :)
Pour apprendre à utiliser PostgreSQL, je me suis donné comme défi de rendre compatible ce blog avec PostgreSQL. Fort heureusement, j'ai un ORM et 90% du boulot est géré par 3 lignes de configuration.
Le moteur de recherche est un peu plus compliqué à migrer puisque j'ai généré des requêtes en dehors de l'ORM. Son fonctionnement est relativement standard car quand un utilisateur saisi des mots clés, une première requête SQL va donner un score aux articles du blog publiés et je vais afficher ceux qui dépassent une valeur donnée.
Pour ce faire, j'utilise MATCH AGAINST
de MySQL/MariaDB et la requête donne ça :
SELECT
post.id,
post.title,
post.tags,
MATCH(post.title) AGAINST(:search) AS MATCH_TITLE,
MATCH(post.content) AGAINST(:search) AS MATCH_CONTENT,
MATCH(post.tags) AGAINST(:search) AS MATCH_TAGS
FROM post
WHERE
post.active = 1 AND
post.published_at < :date
ORDER BY
MATCH_TITLE DESC,
MATCH_TAGS DESC,
MATCH_CONTENT DESC
Pour obtenir des résultats équivalents avec PostgreSQL, la requête doit
changer car MATCH AGAINST
n'existe pas et comme PostgreSQL offre des outils
beaucoup plus complets, c'est moins évident. Je trouve d'ailleurs que la
documentation est assez peu claire à ce sujet. J'ai mis du temps à
pondre une requête qui fonctionnait. La voici :
SELECT
post.id,
ts_rank(to_tsvector(post.title), query) as match_title,
ts_rank(to_tsvector(post.tags), query) as match_tags,
ts_rank(to_tsvector(post.content), query) as match_content
FROM
post,
plainto_tsquery(:search) query
WHERE
post.active = true AND
post.published_at < :date
ORDER BY
match_title DESC,
match_tags DESC,
match_content DESC
Dans les 2 cas, :search
correspond aux mots clés et :date
représente la
date où la recherche est faite.
Les scores ne sont pas du même ordre de grandeur mais je retrouve des résultats équivalents sur les 2 moteurs de base de données.