Besoin : pouvoir comparer un « checksum » des données de certaines tables (en base de données, ou dans un MDM), pour s’assurer que les données y sont fonctionnellement identiques.

Contexte : certaines données (dites de référentiel) sont dupliquées et synchronisées entre plusieurs systèmes/applications. Ce contrôle permet de s’assurer que la synchronisation fonctionne bien.

Contraintes : la structure des tables peut être légèrement différente d’un système à l’autre, et elles ne sont pas toujours accessibles directement en SQL (cas du MDM : progiciel EBX dans notre cas)

Ca n’existe pas déjà?

J’ai d’abord recherché s’il existait des solutions toutes prêtes pour ce type de besoin.

Sur la base de données Oracle, il y a la fonction DBMS_SQLHASH.GETHASH : elle correspond plutôt bien au besoin, mais a l’inconvénient d’être une fonction spécifique Oracle, dont on n’a pas le code source ou l’algorithme exact. On ne peut donc pas l’implémenter à l’identique dans un autre type de base, ni dans EBX.

Pour nous, cette contrainte était rédhibitoire. Mais, si vous n’avez pas la contrainte multi-base ou multi-produit, cette fonction est probablement une meilleure solution que ce que je vais décrire ci-dessous.

Objectif

L’idée est d’avoir un traitement qu’on peut lancer sur chaque base (MDM compris), et qui donnerait le checksum d’une table passée en paramètre (en précisant la liste des colonnes à prendre en compte).

En appelant ce traitement à intervalle régulier (une fois par nuit) sur les différents applicatifs, et en comparant les résultats, on peut avertir quand il y a désynchronisation.

Idéalement, on pourrait aussi passer des options de filtrage sur la table (pour ne prendre en compte que certains enregistrements). Et aussi avoir des informations plus détaillées pour aider au diagnostic si nécessaire.

Checksum par ligne ou par colonne?

Puisqu’on veut un checksum global pour une table, il y a deux approches : faire un checksum par ligne (puis globaliser sur toutes les lignes) ou faire un checksum par colonne (puis globaliser sur toutes les colonnes).

Dans mes premiers tests, je suis plutôt parti sur un checksum par colonne, car l’information de la colonne en erreur me paraissait pertinente (un bug de synchronisation concerne souvent une colonne particulière). Mais cela m’a posé des problèmes de taille de chaîne à concaténer : la concaténation est soit faite à la main donc très lente, soit faite via des fonctions Oracle qui limitent la taille du résultat à du VARCHAR2 (cf http://www.oraclecafe.com/2012/07/string-aggregation-function-oracle/). Au final, je me suis donc ré-orienté vers un checksum par ligne.

En creusant ce sujet, je suis tombé sur https://stackoverflow.com/questions/33824571/fastest-way-to-compute-for-hash-of-a-whole-table/33836522#33836522 qui propose une implémentation efficace d’un checksum par ligne sur une base Oracle. Le temps de réponse m’a paru correct : 95 secondes pour parcourir 300 000 enregistrements, avec 32 colonnes.

J’ai utilisé un hash SHA1, qui est disponible à partir de Oracle 11g dans le package DBMS_CRYPTO, et dans quasiment n’importe quel langage.

Implémentation dans EBX

Il m’a fallu implémenter le même algorithme en Java pour EBX, en choisissant la même manière de concaténer les différents champs d’un enregistrement.

J’arrive à obtenir le même résultat, avec des performances plutôt meilleures qu’en Oracle (surtout après validation, quand il a tout monté en mémoire). Et ces résultats peuvent être exposés via des APIs HTTP.

Algorithme de comparaison

On a donc deux APIs qui permettent de sortir les checksums : une en SQL pour Oracle, une en HTTP pour EBX.

Il ne reste plus qu’à comparer intelligemment le résultat des deux APIs. Oui, parce que si les checksums sont différents, on a en général envie d’avoir plus de détail : quelles lignes sont différentes? Pourquoi? Sauf que, si on a un peu de volumétrie, on ne peut pas se permettre de lister les checksums de toutes les lignes.

Voici l’algorithme que j’ai implémenté (en Perl) :

• D’abord comparer le nombre de lignes (simple et rapide)
• S’il y a le même nombre de lignes, comparer le checksum de la table (un peu plus long)
• Si le nombre de lignes ou le checksum sont différents, afficher le détail :
  • Parcourir les lignes par petits paquets (ex: 10 000 lignes), et comparer le checksum de chaque ligne, pour lister les identifiants des lignes qui sont différentes. Cela peut être long en fonction de la volumétrie de la table, mais ne devrait pas se produire souvent
    • Pour faire les paquets de lignes, le plus sûr m’a semblé de filtrer sur les identifiants (qui sont systématiquement des séquences dans notre cas) : where id>=n and id <n+10000
    • J’affiche d’abord la liste des identifiants qui ne sont que d’un côté
    • Puis les identifiants qui sont des deux côtés mais avec un checksum différent. Dans ce cas, j’en profite pour afficher la chaîne concaténée des valeurs des champs, pour qu’on puisse facilement y voir la différence (attention à ne pas le faire si les champs peuvent avoir une taille importante)

Cas particuliers

Pour concaténer tous les champs d’un enregistrement, il faut d’abord les sérialiser en chaîne de caractère :

• pour les dates, j’ai choisi de les mettre au format ISO : il est verbeux, mais facilement lisible, et facilement implémentable dans n’importe quel langage
• pour les numériques non entiers, il faut faire attention à utiliser les mêmes séparateurs partout
• pour les chaînes de caractères multi-lignes, il faut remplacer le passage à la ligne par un « \n » par exemple

La lecture des checksums de ligne par petit paquet s’appuie sur le fait que la table a une clé primaire composée d’un seul champ. Ce n’est pas le cas pour les tables de jointure, mais, comme elles ont en général très peu de champs (souvent 2), on a pris l’ensemble des lignes d’un coup.

Performances

Il faut environ 15 minutes pour parcourir l’ensemble du référentiel, mais cela dépendra évidemment de beaucoup de facteurs.

Si on veut faire la comparaison avec plusieurs schemas Oracle, les appels peuvent être lancés en parallèle pour gagner du temps.

Une piste d’optimisation serait de pré-calculer les checksums et les stocker : dans une colonne dédiée (mais attention à être sûr qu’elle soit toujours tenue à jour), une vue matérialisée (si la base le supporte), ou autre. Mais je n’ai pas testé.

Bilan

Cet outil nous a permis de découvrir qu’il y avait un grand nombre d’écarts de données en production. La grande majorité de ces écarts s’expliquait et n’avait aucune conséquence fonctionnelle (ex: des approximations lors de l’initialisation des données), mais cela a également révélé quelques bugs dans notre chaîne référentielle, qu’on n’avait jamais remarqués jusqu’ici.

Cela nous sert également en phase de recette, que ce soit pour vérifier la non-régression ou pour chasser les bugs dans les nouveaux développements.