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

1- Introduction

Lors de l'intégration de données, comme les données de marché etc..., depuis des 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.

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 »).

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 N et un fichier 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 l'utilitaire comm, disponible sur la plupart des plateformes Unix/Linux, à travers un cas pratique.

2- L'utilitaire comm

2-1- Syntaxe et usage

La syntaxe de comm est très basique

comm [-123] file1 file2

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

2-2- Pré-requis

Quelques pré-requis sont indispensables :

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.

3- 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.bcp

1|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 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.

Annexe