Charger les données de StackOverflow dans PostgreSQL 11

mar. 02 octobre 2018 (PostgreSQL)

PostgreSQL 11

La version Beta 4 de PostgreSQL 11 est sortie le 20 septembre. Cette version nous apporte son lot de nouvelles fonctionnalités et amélioration de performances.

Nouveautés

Pour commencer, le partitionnement natif progresse encore :

mise à jour de la clé de partition,
support de clé primaires et clé étrangères,
création d'index automatique,
partition par défaut,
partitionnement par hachage.

Une amélioration des performances en sélection sur les tables partitionnées est également attendue grâce à l'élagage dynamique des partitions.

Au niveau des performances, le parallélisme progresse encore et la compilation Just In Time (JIT) fait son apparition.

Côté programmation côté serveur, une belle nouveauté arrive avec le contrôle transactionnel en PL et les objets PROCEDURE.

Index couvrants

Une des nouveautés de cette version, les index couvrants, permet, grâce au mot clé INCLUDE de stocker des colonnes supplémentaires dans un index B-tree sans ordonner les entrées de l'index sur ces données.
L'objectif est de pouvoir effectuer des accès par index direct (INDEX ONLY SCAN) sans trop dégrader les performances en insertion et mise à jour.

Pour tester cette fonctionnalité, j'avais besoin de tables assez volumineuses contenant des colonnes trop large pour être habituellement stockées dans des index B-tree : des données en texte, XML ou JSON.

Pour permettre des recherches en direct sur ce type de données, on utilise des habituellement des index spécialisés en recherche plein texte de type GIN ou GiST.

Ici, l'objectif n'est pas de rechercher des données dans le champ volumineux. Il est de travailler sur des recherches sur des colonnes classiques de type entier ou date et de récupérer plus rapidement le champ volumineux.

Créer un jeu de données ressemblant à la réalité est compliqué. Adrien Nayrat dans une belle conférence sur la recherche plein texte avait expliqué comment récupérer les données de [StackOverflow](https://stackoverflow.com/].

Chargement des données de stackoverflow

Un projet python permet de charger des données de stackoverflow dans une base de données PostgreSQL.

Toutes les colonnes du dump ne sont pas récupérées et les commentaires sont stockés dans des colonnes texte.
Je voulais pouvoir stocker la ligne originale en XML ou JSON mais le projet ne permettait pas cela.

Et c'est parti pour la réinvention de la roue

Je pensais utiliser les fonctions XML intégrées à PostgreSQL pour faire le chargement des données. Cependant, les fichiers n'étaient pas reconnus comme des documents XML valides par la fonction xmlparse :

so=# SELECT xmlroot(
       xmlparse(document pg_read_file('/var/lib/postgresql/dba/Badges.xml')),
       version '1.0', standalone yes);
ERROR:  invalid XML document
DETAIL : line 1: Start tag expected, '<' not found
<?xml version="1.0" encoding="utf-8"?>
^

Je suis alors parti dans des choses de plus en plus compliquées en insérant uniquement les données de chaques lignes.
Donc, suppression des 2 premières et de la dernière ligne du fichier. Puis insertion par COPY dans la table :

$ awk 'NR>3 {print last} {last=$0}' dba/Badges.xml \
        |  psql -d so -c "COPY dba.badges (field) FROM STDIN"
COPY 233456

Malheureusement, des tabulations et des backslash empêchaient la récupération des données des commentaires :

$ awk 'NR>3 {print last} {last=$0}' dba/Comments.xml \
        |  psql -d so -c "COPY dba.comments (field) FROM STDIN"
ERROR:  extra data after last expected column
CONTEXT : COPY test, line 1121: "  <row Id="1512" PostId="1224" Score="0"
          Text="Thanks Justin,following is order by cpu time.09-Feb-2..."

Qu'à cela ne tienne, awk à la rescousse et jouons à remplacer du texte...

$ awk 'NR>3 {print last} {last=$0}' dba/Comments.xml \
       | awk '{gsub(/\\/,"\//backslash//",$0);print $0}' \
       | awk '{gsub(/\t/,"\//tab//",$0);print $0}' \
       | psql -d so -c "COPY dba.comments (field) FROM STDIN"
$ psql -d so -c "update test set field = xmlparse(DOCUMENT 
    REPLACE(REPLACE(field::text,'//tab//',chr(9)),'//backslash//','\')) 
    where field::text like '%//tab//%' or field::text like '%//backslash//%'"

Bon, ça marche... par contre, en voulant utiliser xpath pour sortir certains champs du XML, je me suis rendu compte que je n'avais plus qu'à recoder le projet initial...

En passant, j'ai compris que les fichiers XML originaux posaient problème à cause du BOM. Un petit sed et ça repart !

$ sed -i '1s/^\xEF\xBB\xBF//' dba/Tags.xml
$ psql -d so -x -c "SELECT xmlroot(
>       xmlparse(document pg_read_file('/var/lib/postgresql/dba/Tags.xml'))
>       , version '1.0', standalone yes);"
-[ RECORD 1 ]--------------------------------------------------------------+
xmlroot | <?xml version="1.0" standalone="yes"?>\r                         +
        | \r                                                               +
        | <tags>\r                                                         +
        |  <row Id="1" TagName="esri-company" (...) WikiPostId="5398" />\r +
        |  (...)                                                           +
        |  <row Id="2077" TagName="arcobjects" (...) WikiPostId="7682" />\r+
        | </tags>\r                                                        +
        | \r                                                               +

Tout cela m'a permis de jouer un peu avec XML. Mais le parsing de tous ces champs allait être fastidieux à programmer, sans compter que les performances n'allaient pas être bonnes.
Il était temps d'améliorer le projet initial !

3 nouvelles fonctionnalités

Chargement d'un projet complet en une commande

La procédure de chargement actuelle est de récupérer tous les fichiers nécessaires de StackOverflow ou l'archive d'un projet dédié sur archive.org. Puis on décompresse le tout et on lance le script.

Étant de nature feignante et n'ayant pas assez d'espace disque pour stocker le projet StackOverflow, j'ai proposé une petite évolution permettant de télécharger et de charger directement via le script un projet StackExchange dédié (option -s).

Chargement de chaque ligne dans un champ jsonb

Les informations des archives sont stockées en XML. Le script actuel vient récupérer des informations spécifiques dans chaque entrée et les insèrent dans des colonnes spécifiques. Les informations non traitées sont perdues.

Mon envie de départ était de charger des données réelles dans des champs XML ou jsonb. Il manquait donc une brique dans le projet.

En étudiant le code, j'ai remarqué que pour fonctionner avec Psycopg, chaque entrée était transformée en json. Il était donc aussi simple d'insérer directement les informations dans un champ jsonb que de devoir retraiter du XML.

La Pull Request en question devrait être intégrée à la fin du support de la version 9.3 de PostgreSQL. En effet, le jsonb ne fait son apparition qu'en version 9.4.

[Update 21/01/2019] La PR a été mergée. L'option -j permet d'insérer les données dans un champ jsonb.

Ajout des clés étrangères

Un problème récurrent dans la gestion des données réelles est leur non-intégrité. Un schéma mal conçu ou un bug logiciel peut conduire à la conservation d'informations orphelines.

Cela peut-être un manque de contrainte sur une colonne. Par exemple, une date de naissance dans le futur aboutissant à un âge négatif.
Un autre genre de problème est, si un post est supprimé, que l'historique de ce post ne doit plus pointer vers un enregistrement inexistant. Il peut au choix devrait pas être conservé et automatiquement supprimé.

L'utilisation de clés étrangères avec des règles précises sur mise à jour ou suppression d'un enregistrement dans une table donnée permet d'éviter ce genre de problème.

Les données de StackOverflow présente ce genre de problème. Une première version de la PR proposait de supprimer les lignes incriminées. Le côté destructeur était gênant, une seconde version a été proposée permettant de conserver les lignes n'ayant pas de références vers une ligne de la table référencée. Ceci est possible en notant les contraintes comme NOT VALID. Toute nouvelle insertion devra vérifier la contrainte mais les lignes existantes ne sont pas vérifiées.

Les fichiers SQL PostLinks_fk.sql et Votes_fk.sql précisent, dans les commentaires, les commandes nécessaires pour nettoyer les lignes problématiques et valider les contraintes d'intégrité référentielles pour toutes les clés étrangères.

Geeker, c'est bien !

La découverte des nouveautés de la version 11 m'a conduit, de fil en aiguille, à contribuer à un projet porté par un développeur très sympa, qui a pris le temps de relire et valider mes PR. Merci à lui et merci à dalibo pour les temps de CCC !

DBAのトトロ

Totoro chez les DBA