L’utilitaire comm sous Unix. Intégration de données en delta dans une base de données

Logo

Introduction

Lors de l’intégration de données (données de marché, etc.) à partir de fichiers plats reçus à intervalles réguliers, les progiciels et les développements avec les outils ETL comme Informatica, Genio, DataStage… ont souvent tendance pour détecter les nouvelles données et les données modifiées entre le fichier N-1 et le fichier N à réaliser l’intégration selon deux méthodes :

  • Méthode 1 : l’intégralité du fichier est chargé en base dans une table tampon avec les outils natifs comme bcp pour Sybase, SQL*Loader pour Oracle ou avec des outils ETL. Les données de la table tampon sont ensuite comparées avec les données déjà en base. La plupart du temps les comparaisons sont réalisées avec des traitements lourds, complexes et en ligne à ligne (curseurs, etc.).
  • Méthode 2 : le fichier est lu ligne à ligne en comparant en parallèle avec les données en base. Dans cette méthode, la base de données subit des interrogations intensives en ligne à ligne également.
integration de donnees en delta de fichiers

Grossièrement la cinématique de la comparaison est la suivante :

  • La ligne est lue.
  • Existe-t-elle déjà en base ?
    • Non : la ligne est insérée.
    • Oui : la ligne existante est mise à jour si les données sont différentes.
  • Lecture de la ligne suivante, etc.

Ces traitements sont encore "acceptables" lorsque la volumétrie des fichiers reçus est faible (quelques dizaines ou centaines de lignes). Lorsque les fichiers contiennent des dizaines ou centaines de milliers de lignes, ces opérations ligne à ligne en base de données deviennent très coûteuses en performances (voir la métaphore de la boulangère sur les traitements en ligne à ligne : Performances SQL, traitements ensemblistes et ligne à ligne, la métaphore de la boulangère ).

Citation d’un argument avancé pour ne pas optimiser un traitement batch : "c’est pas grave c’est la nuit que ça se passe". Même si le traitement se déroule la nuit, pourquoi ne pas faire efficace ?

Il existe un utilitaire Unix trop peu utilisé mais très puissant pour générer un fichier ne contenant que le delta entre un fichier du jour N et un fichier de la veille N-1 (nouvelles données et données modifiées entre N et N-1). Cet utilitaire est le binaire comm.

Lorsque les données modifiées ou nouvelles sont relativement faibles entre 2 fichiers, l’utilitaire comm va soulager fortement les données lues par le programme et/ou le traitement réalisé en base : le gain en performances est exceptionnel, quelques heures parfois.

Cet article présente à travers un cas pratique rencontré l’utilitaire comm, disponible sur la plupart des plateformes Unix/Linux, mais aussi avec MingW ou Cygwin sous Windows.

L’utilitaire comm

Syntaxe et usage

La syntaxe de comm est très basique :

comm [-123] file1 file2
  • -1 : Supprime dans la sortie les lignes n’existant que dans le fichier 1.
  • -2 : Supprime dans la sortie les lignes n’existant que dans le fichier 2.
  • -3 : Supprime dans la sortie les lignes identiques existant dans le fichier 1 et le ficher 2.

Code retour : 0 en cas de succès, >0 en cas d’erreur rencontrée.

Pré-requis

Quelques pré-requis sont indispensables :

  • Les structures des fichiers à traiter sont identiques (nombre de colonnes, séparateur, ordre des données, etc.).
  • Les données dans les fichiers sont déjà triées.

Lorsque les données dans le fichier ne sont pas déjà triées, 2 options :

  • Si la génération du fichier est interne à l’entreprise et provient d’une base de données (bcp ou autres), rajouter la clause ORDER BY nécessaire pour obtenir un fichier trié. Les clauses ORDER BY n’étant pas supportées dans les vues Sybase, remplacer la vue par une table proxy attachée à une procédure stockée, procédure stockée dans laquelle il sera possible d’implémenter la clause ORDER BY (voir article sur les tables proxy attachées à des procédures stockées : Sybase Component Integration Services CIS, tables proxy attachées à des jeux de résultats de procédures stockées ).
  • Si le fichier est envoyé par un éditeur ou un fournisseur externe et que l’obtention d’un fichier trié n’est pas négociable, il est alors nécessaire d’appliquer le binaire sort sur le fichier en entrée pour trier les données. 
    %> cat 1.txt | sort > 2.txt
    Contraintes à surveiller dans l’utilisation du binaire sort : consommation mémoire et espace disque. Ces contraintes sont dépendantes de la plateforme Unix/Linux utilisée. La taille des fichiers en entrée est à étudier.

Les fichiers traités étant déjà triés, la consommation mémoire utilisée par l’utilitaire comm est quasi négligeable. Les fichiers sont lus et le fichier résultant est écrit par paquets.

Exemple pratique

Dans l’exemple pratique, la plateforme est un OS Sun SPARC Solaris 9 32 bits. Les fichiers previous.bcp et new.bcp sont respectivement les fichiers de données reçus la veille et le jour en cours.

La structure du fichier est un fichier ASCII avec comme séparateur de colonnes le caractère |

%> tail -1 previous.bcp1|Jun  6 2009 12:00AM|OTCCHARAC_INTERFACES|SOPCHAR060|3| |1|3|Jun  6 2009  4:14PM

Pour détecter les nouvelles lignes et les lignes modifiées dans le fichier new.bcp par rapport au fichier previous.bcp :

%> comm -32 new.bcp previous.bcp > load.bcp
%> cat load.bcp
          1|Jun  6 2009 12:00AM|OTCSCHEDL_INTERFACES|SOPSCHE063|0| |0|1|                             
1|Jun  6 2009 12:00AM|OTCCHARAC_INTERFACES|SOPCHAR060|3| |1|3|Jun  6 2009  7:19PMM

L’option -3 retire les lignes identiques dans les fichiers previous.bcp (fichier 2) et new.bcp (fichier 1).

L’option -2 retire les lignes uniques au fichier previous.bcp (fichier 2).

Le fichier résultat load.bcp obtenu avec comm ne contient ainsi que le delta et les données nécessaires à traiter. Au lieu de charger et traiter 600 000 lignes, il n’y a bien souvent qu’une petite dizaine ou une petite centaine de lignes à charger en base pour traitement.