Importation et exportation massives de données sous InterBase 6.0 / Firebird 1.5.x

Voici un tutoriel permettant de réaliser des importations et exportations massives de données en évitant tous les pièges.

Ce tutoriel nécessite quelques notions sur le langage SQL et InterBase/Firebird.

Article lu   fois.

L'auteur

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction.

Généralement, lors d'un développement de logiciel de gestion, il est nécessaire de récupérer des données en masse (venant d'un fichier). De même, afin d'avoir la possibilité de migrer vers un autre SGBD dans le futur, il peut être intéressant de pouvoir exporter toutes ces données.

Cependant, si l'on ne s'y prend pas avec précaution, une insertion de quelques milliers d'enregistrements peut vite devenir un véritable enfer (ralentissements importants, plantage ...).

C'est pourquoi je vous propose ce tutoriel qui, dans un premier temps, décrira un cas particulier en détail, et, dans un deuxième temps, récapitulera les pièges a éviter.

Ce tutoriel est basé uniquement sur des scripts SQL afin de pouvoir les utiliser quelque soit le langage de programmation.

Ces scripts sont utilisables notamment avec isql (fournit avec InterBase et Firebird).

II. Étude de cas.

A. Conditions initiales.

Afin de bien étudier le sujet, nous allons prendre une base de données relativement simple mais utilisant des clés primaires (PRIMARY KEY), des clés étrangères (FOREIGN KEY) et des index.

Soit deux tables CLIENT et CIVILITE :

 CLIENT
 Nom de champ  Type de données
 IDCLIENT  BigInt not null + Primary Key Asc + Index Desc
 CIVILID  SmallInt + Foreign Key Asc sur CIVILITE(IDCIVILITE)
 NOM  Varchar(40) not null + Index Asc + Index Desc
 PRENOM  Varchar(30)
 DESCRIPT  Varchar(60)
 CIVILITE
 Nom de champ  Type de données
 IDCIVILITE  SmallInt not null + Primary Key Asc
 LABEL  Varchar(10) not null

Nous déclarons IDCLIENT et IDCIVILITE comme clés primaires de leur table respective et CIVILID comme clé étrangère sur IDCLIENT dont la référence est CIVILITE(IDCIVILITE).

Afin d'avoir une base de données réaliste, nous avons aussi plusieurs index : NOM (Ascendant et Descendant), IDCLIENT (Descendant), un générateur pour la clé primaire de CLIENT avec son trigger et sa procédure stockée.

Nous ajoutons aussi trois enregistrements dans la table CIVILITE.

Si vous le désirez, voici le script de création des tables de la base : Creer_base.sql.
(selon les cas, il peut être nécessaire de modifier le début du fichier en fonction de votre installation, notamment le chemin de connexion).

Nous disposons maintenant d'une base de données opérationnelle mais vide.
La table sur laquelle nous allons travailler est CLIENT.

B. Importer des données.

L'importation de données ne pose aucun problème lorsqu'il n'existe que très peu d'enregistrements (< 1 000), on peut alors pratiquer plusieurs insertions consécutives. Mais cela se complique lorsque l'on doit incorporer beaucoup plus de données (100 000, 1 000 000, ...).

Pourquoi ?

Tel qu'est notre base de données, pour chaque insertion, le SGBDR vérifie toutes les contraintes.
Ainsi nous obtenons l'algorithme suivant :

 
Sélectionnez

Pour chaque insertion dans CLIENT
{
	si IDCLIENT est nul alors on appelle le trigger.
	Vérification de l'unicité de la clé primaire PK_CLIENT (et donc de IDCLIENT).
	Vérification de l'existence de la clé étrangère FK_CLIENT_CIV (et donc de CIVILID dans CIVILITE(IDCIVILITE)).
	Ajout de la ligne dans CLIENT.
	Mise à jour de tous les index.				
}
				

Nous constatons que le cache mémoire sera vite saturé et la transaction grossira également si on ne commit pas régulièrement, sans compter que les emplacements réservés pour les insertions sont vites comblés, obligeant Interbase/Firebird à désorganiser les données.

Cela fait pas mal de chose à vérifier à chaque insertion en plus des ressources utilisées, pour peu qu'il y ait un grand volume de données à insérer et la ... c'est le drame : Tout est planté.

Comment résoudre le problème ?

En retirant provisoirement les contrôles de validité (clés primaires, étrangères, index) lors des insertions.
Ainsi on obtient :

 
Sélectionnez

Pour chaque insertion dans CLIENT
{
	si IDCLIENT est nul alors on appelle le trigger (cela nous évite de gérer les identifiants).
	Ajout de la ligne dans CLIENT.
}
				

Oui mais alors les clés et les index ?

Il nous suffit de les détruire avant le traitement des insertions massives et de les reconstruire en fin de traitement.
En plus de limiter le nombre de tâches à effectuer, ces clés et index seront optimisés car tout frais tout neuf.

D'accord mais n'y a-t-il pas un risque d'incohérence de données ?

Oui. Donc pour éviter cela, nous avons laissé le trigger (on suppose que les civilités sont correctes).
Nous en arrivons donc à ceci :

 
Sélectionnez

Suppression de la clé primaire PK_CLIENT.
Suppression de la clé étrangère FK_CLIENT_CIV.
Suppression des index.
Pour chaque insertion
{
	si IDCLIENT est nul alors on appelle le trigger (cela nous évite de gérer les identifiants).
	Ajout de la ligne dans CLIENT.
}
Création des index.
Création de la clé étrangère FK_CLIENT_CIV.
Création de la clé primaire PK_CLIENT.
				

La création de la clé étrangère peut 'bugger' (message d'erreur). Dans ce cas, arrêtez et redémarrez le gardien IB/FB avant de relancer le script.

Donc il nous suffit maintenant de réaliser des INSERT INTO successifs ?

Pas tout à fait, car faire des milliers ou millions d'INSERT INTO n'est pas ce qu'il y a de plus rapide. En effet, à chaque ordre envoyé au serveur, celui-ci doit l'interpréter et le vérifier avant de l'exécuter, pour lui éviter ce travail, une petite astuce ... passer par une table externe.
Ceci est beaucoup plus rapide à traiter.
Pour cela, nous avons besoin d'avoir nos données sous forme d'un fichier texte dont les enregistrements sont de longueurs fixes (fichier séquentiel).

Pour autoriser l'usage de tables externes, il est nécessaire de modifier 'firebird.conf'. Modifiez la ligne ExternalFileAccess en ExternalFileAccess = Restrict c:\MonRepAutorise\ ou ExternalFileAccess = Full. Le redémarrage du gardien IB/FB peut être nécessaire. N'oubliez pas de rétablir les paramètres après car ceci entraine une certaine vulnérabilité

Exemple :
Soit un fichier dont chaque ligne comprend :
1 caractère définissant la Civilité,
40 pour le nom,
30 pour le prénom,
60 pour la description,
et un retour à la ligne.

Voici un exemple de contenu du fichier :

 
Sélectionnez

1MONNOM                                  MonPrénom                     Ma Description
2MONNOM1                                 MonPrénom                     Ma Description
2MONNOM2                                 MonPrénom                     Ma Description
1MONNOM3                                 MonPrénom                     Ma Description
				

(Les espaces à droite de "Ma Description" ne sont pas affichés pour des raisons de mise en page)

Nous avons donc notre fichier contenant les données à insérer dans notre table CLIENT.
Voici un exemple de fichier permettant de tester cet exemple : donnees.txt.

La table externe permettant de traiter le fichier serait donc de ce type :

 
Sélectionnez

CREATE TABLE T_EXTERNAL EXTERNAL FILE 'DONNEES.TXT' 
(
	CIV             char( 1),
	NAME            char(40),
	FIRSTNAME       char(30),
	DESCRIPT        char(60),
	RT              char(2)
);

(Le champs RT correspond aux deux caractères de retour à la ligne)

Reprenons maintenant l'algorithme décrit auparavant afin d'en établir un script (on y ajoute bien évidemment la création de la table externe au début) :

 Algorithme  Code correspondant
 
Sélectionnez
Suppression de la clé
  primaire PK_CLIENT
 
Sélectionnez
ALTER TABLE CLIENT
  DROP CONSTRAINT PK_CLIENT;
 
Sélectionnez
Suppression de la clé
  étrangère FK_CLIENT_CIV
 
Sélectionnez
ALTER TABLE CLIENT
  DROP CONSTRAINT FK_CLIENT_CIV;
 
Sélectionnez
Suppression des index
 
Sélectionnez
DROP INDEX IDX_NOM_ASC;
DROP INDEX IDX_NOM_DESC;
DROP INDEX IDX_IDCLT_DESC;
 
Sélectionnez
Pour chaque insertion
{
  si IDCLIENT est nul alors on appelle 
  le trigger (cela nous évite de gérer
  les identifiants).
 
  Ajout de la ligne dans CLIENT.
}
 
Sélectionnez
INSERT INTO 
  CLIENT (IDCLIENT,CIVILID,NOM,PRENOM,DESCRIPT)
  SELECT null, CIV,NAME,FIRSTNAME,DESCRIPT
  FROM T_EXTERNAL;

Mais si l'on souhaite en profiter pour vérifier ou mettre en forme des données, il est préférable d'utiliser une procédure stockée qui réalisera le transfert de la table externe vers la table CLIENT.

On obtient alors :

 Algorithme  Code correspondant
 
Sélectionnez
Pour chaque insertion
{
  si IDCLIENT est nul alors on appelle 
    le trigger (cela nous évite de gérer
    les identifiants).
 
  On met la valeur de NOM en majuscule 
    (pour exemple de mise en forme
    des données)
 
  Ajout de la ligne dans CLIENT.
}
 
Sélectionnez
CREATE PROCEDURE PS_TRANSFERT
AS
  DECLARE VARIABLE CIV             Char(1) ;
  DECLARE VARIABLE NAME            Char(40);
  DECLARE VARIABLE FIRSTNAME       Char(30);
  DECLARE VARIABLE DESCRIPT        Char(60);
BEGIN
  FOR
    SELECT CIV,NAME,FIRSTNAME,DESCRIPT
    FROM T_EXTERNAL
    INTO :CIV, :NAME, :FIRSTNAME, :DESCRIPT 
  DO
  BEGIN
    /*Insérer ici les Vérifications/mises en forme */
    NAME = UPPER(NAME) ;
 
    INSERT INTO CLIENT 
        (IDCLIENT,CIVILID,NOM,PRENOM,DESCRIPT)
    VALUES (null,:CIV,:NAME,:FIRSTNAME,:DESCRIPT);
  END
END;

Nous mettrons donc à la suite, afin de lancer la procédure stockée :

 
Sélectionnez

EXECUTE PROCEDURE PS_TRANSFERT;
				

A la fin de ce traitement notre table externe ainsi que notre procédure stockée ne sont plus utiles, nous pouvons donc les supprimer (ceci n'altère pas notre fichier de données) :

 
Sélectionnez

DROP PROCEDURE	PS_TRANSFERT;
DROP TABLE	T_EXTERNAL;
				

Puis nous reprenons notre algorithme :

 Algorithme  Code correspondant
 
Sélectionnez
Création des index
 
Sélectionnez
CREATE DESCENDING INDEX IDX_IDCLT_DESC ON CLIENT (IDCLIENT);
CREATE ASCENDING INDEX IDX_NOM_ASC ON CLIENT (NOM);
CREATE DESCENDING INDEX IDX_NOM_DESC ON CLIENT (NOM);
 
Sélectionnez
Création de la clé
  étrangère FK_CLIENT_CIV
 
Sélectionnez
ALTER TABLE CLIENT
  ADD CONSTRAINT FK_CLIENT_CIV
  FOREIGN KEY (CIVILID) REFERENCES CIVILITE (IDCIVILITE);

La création de la clé étrangère peut 'bugger'. Dans ce cas, arrêtez et redémarrez le gardien IB/FB.

 Algorithme  Code correspondant
 
Sélectionnez
Création de la clé primaire PK_CLIENT
 
Sélectionnez
ALTER TABLE CLIENT
  ADD CONSTRAINT PK_CLIENT
  PRIMARY KEY (IDCLIENT);

Et voilà le travail !

Notre importation de données est maintenant optimisée et peut donc être utilisée sur un nombre d'enregistrements quelconque (faible ou élevé).

Voici le script d'importation de données : Importation.sql

C. Exporter des données.

L'exportation de données est beaucoup plus facile à réaliser. En effet, les index de la table CLIENT ne seront utilisés que s'ils permettent de trouver les enregistrements correspondants. Ainsi, les index accélèrent la recherche comme dans une requête SQL.

Il peut être intéressant d'avoir a chaque fin de ligne du fichier de données exportées, un retour à la ligne. Ainsi, la présentation des données sera plus lisible pour l'utilisateur.

Il est alors nécessaire d'avoir recourt à la fonction UDF ASCII_CHAR.

UDF : User Defined Functions. Il s'agit de fonctions de bibliothèques déclarées par le développeur.
Les fonctions utilisées sont incluses dans InterBase/Firebird, cependant, il est possible d'en créer soit-même

Dans le cas présent, nous exporterons l'intégralité de la table CLIENT.

L'algorithme utilisé est donc beaucoup plus léger :

 Algorithme  Code correspondant
 
Sélectionnez
Déclaration de la fonction UDF ASCII_CHAR
  (permettra l'ajout d'un retour à la ligne).
 
Déclaration de la fonction UDF LOWER
  (pour la mise en forme des données).
 
Création de la table externe.
 
Sélectionnez
DECLARE EXTERNAL FUNCTION ASCII_CHAR INTEGER
RETURNS CHAR(1)
ENTRY_POINT 'IB_UDF_ascii_char' MODULE_NAME 'ib_udf';
 
DECLARE EXTERNAL FUNCTION LOWER CSTRING(80)
RETURNS CSTRING(80) FREE_IT 
ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';
 
CREATE TABLE T_EXTERNAL EXTERNAL FILE 'EXPORT.TXT' (
  CIV            char( 1),
  NAME           char(40),
  FIRSTNAME      char(30),
  DESCRIPT       char(60),
  RT             char(2)
);

Nous n'exporterons pas le champ IDCLIENT afin d'obtenir un fichier identique à celui de l'importation.

 Algorithme  Code correspondant
 
Sélectionnez
Pour chaque enregistrements
  de la table CLIENT
{
  Ajout de la ligne dans la table externe.
}
 
Sélectionnez
INSERT INTO T_EXTERNAL (CIV,NAME,FIRSTNAME,DESCRIPT,RT)
  SELECT CIVILID,NOM,PRENOM,DESCRIPT, 
    ASCII_CHAR(13)||ASCII_CHAR(10)
  FROM CLIENT;

S'il y a manipulation de données, alors on utilise une procédure stockée :

 
Sélectionnez

CREATE PROCEDURE PS_TRANSFERT
AS
    DECLARE VARIABLE CIV        Char(1) ;
    DECLARE VARIABLE NAME        Char(40);
    DECLARE VARIABLE FIRSTNAME    Char(30);
    DECLARE VARIABLE DESCRIPT    Char(60);
    DECLARE VARIABLE CRLF        Char(2);
BEGIN
    CRLF = ASCII_CHAR(13)||ASCII_CHAR(10);/*Pour éviter de le faire plusieurs fois dans la boucle*/
 
    FOR
        SELECT CIVILID,NOM,PRENOM,DESCRIPT
        FROM CLIENT
        INTO :CIV, :NAME, :FIRSTNAME, :DESCRIPT
    DO
    BEGIN
        /*Insérer ici les Vérifications ou mises en forme */
        NAME = LOWER(NAME);/*Lower nécessite d'être déclarer (contrairement à Upper)*/
 
        INSERT INTO T_EXTERNAL (CIV,NAME,FIRSTNAME,DESCRIPT,RT)
        VALUES (:CIV,:NAME,:FIRSTNAME,:DESCRIPT,:CRLF);
    END
END;
				

L'utilisation de la fonction UDF LOWER peut 'bugger'. Dans ce cas, arrêtez et redémarrez le gardien IB/FB avant de relancer le script.

Mais que veut dire ASCII_CHAR(13)||ASCII_CHAR(10) ?


C'est tout simple.
Nous utilisons la fonction UDF ASCII_CHAR, elle remplace une valeur ASCII par le caractère correspondant.
Les deux pipes ( || ) permettent la concaténation de chaînes de caractères.
Quant à 13 et 10, ce sont les valeurs ascii du retour à la ligne sous Windows (utilisez seulement la valeur 10 sous Linux).

 Algorithme  Code correspondant
 
Sélectionnez
On exécute la procédure stockée
 
Sélectionnez
EXECUTE PROCEDURE PS_TRANSFERT;
 
Sélectionnez
Destruction de la table externe 
(mais pas du fichier correspondant),
de la procédure stockée et de la fonction UDF
si celles-ci ne sont plus utiles.
 
Sélectionnez
DROP PROCEDURE	PS_TRANSFERT;
DROP TABLE	T_EXTERNAL;
DROP EXTERNAL	FUNCTION ASCII_CHAR;

Maintenant, en allant sous l'explorateur de fichier, nous apercevons notre fichier EXPORT.TXT.

Vous pouvez l'ouvrir, vous verrez que le formatage est identique a celui de l'importation (cf. ).

Le script d'exportation est téléchargeable ici : Exportation.sql

III. Les pièges à éviter.

Vous trouverez ici un récapitulatif de tout ce qui vous permettra d'optimiser l'importation et l'exportation de vos données.

  • Désactivez le maximum de clés et d'index (Primary Key/Foreign Key/Index) dans la mesure du possible lors des importations.

  • N'oubliez pas le problème de la recréation des Foreign Key : nécessite parfois le redémarrage du Gardien IB/FB. Et si cela persiste c'est que vous avez une valeur dans la table qui ne trouve pas de correspondance dans la table liée. Pour vérifier la cohérence des données : SELECT C.IDCLIENT , C.CIVILID FROM CLIENT C LEFT JOIN CIVILITE CV ON CV.IDCIVILITE=C.CIVILID WHERE CV. IDCIVILITE IS NULL; Il ne vous reste plus qu'a modifier la civilité de ces clients ou ajouter les civilités manquantes à la table CIVILITE.
  • Évitez les 'SELECT *', préférez plutôt 'SELECT Colonne1, Colonne2 ...' . Et ainsi les colonnes ne sont pas recherchés avant l'exécution de la requête.

  • Lors d'exportation, pensez à indexer les colonnes présentent dans la clause WHERE de la procédure stockée.

  • Une valeur de type DATE s'écrit toujours sous la forme AAAA-MM-JJ ou AAAA/MM/JJ.

  • De même une valeur de type DECIMAL(15,2) comprend un point et non une virgule pour le séparateur décimal.

  • Une procédure stockée ne peut pas modifier la structure d'une base.

  • Utilisez une table externe plutôt que des insertions successives.

  • Il est nécessaire de modifier le fichier Firebird.conf pour utiliser des tables externes : Modifiez la ligne ExternalFileAccess en ExternalFileAccess = Restrict c:\MonRepAutorise\ ou ExternalFileAccess = Full (nécessite parfois le redémarrage du Gardien IB/FB). N'oubliez pas de rétablir les paramètres après car ceci entraine une certaine vulnérabilité

  • Pensez à la dernière colonne de la table externe, elle est nécessaire au stockage des deux caractères de retour chariot de chaque ligne sous Windows (un seul sous Linux) s'il y a. Sinon le fichier est plus difficile à lire avec un éditeur de texte.

  • Supprimez les procédures stockées, tables externes et fonctions UDF nécessaire au transfert après utilisation. (A moins que vous n'ayez besoin d'en réaliser régulièrement)

  • Le redémarrage du serveur SuperServeur sous linux se fait sous la forme service firebird restart

IV. Conclusion.

J'espère que ce tutoriel vous a aidé à y voir plus clair.

Quand il s'agit d'optimiser, il y a toujours moyen de faire mieux, cependant je pense que l'exemple proposé permet déjà de gros traitements de manière efficace.

Vous voilà parez pour de nouvelles aventures avec InterBase/Firebird.

Si vous avez une remarque à émettre vis-à-vis de ce tutoriel, n'hésitez pas à m'en faire part par mp.

V. Remerciements.

Je tiens à remercier toute l'équipe de developpez.com pour le beau travail réalisé chaque jour sur le site et le forum.

Je souhaite remercier tout particulièrement Barbibulle, sans qui, je n'aurais jamais pu faire ce tutoriel.

Je remercie aussi Giovanny Temgoua et Laurent Dardenne pour leurs aides.

Liste de mes articles :
Importation et exportation massives de données sous IB 6.0 / FB 1.5.x
L'avenir de Firebird : La RoadMap 2005-2006



Rubrique InterBase/Firebird (Accueil, FAQ, Tutoriels et Outils)
Rubrique SGBD (Accueil, FAQ, Tutoriels et Outils)
  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2005 Benjamin GAGNEUX. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.