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

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 :

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.

L'importation de données ne pose aucun problème lorsqu'il n'existe que très peu d'enregistrements (< 1000), 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 ?

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

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 vite comblés, obligeant Interbase/Firebird à désorganiser les données.

Cela fait pas mal de choses à 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 là… 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 :

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

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 :

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.

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

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 :

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 :

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

(Le champ 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) :

Suppression de la clé
  primaire PK_CLIENT

ALTER TABLE CLIENT
  DROP CONSTRAINT PK_CLIENT;

Suppression de la clé
  étrangère FK_CLIENT_CIV

ALTER TABLE CLIENT
  DROP CONSTRAINT FK_CLIENT_CIV;

Suppression des index

DROP INDEX IDX_NOM_ASC;
DROP INDEX IDX_NOM_DESC;
DROP INDEX IDX_IDCLT_DESC;

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

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 :

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

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 :

EXECUTE PROCEDURE PS_TRANSFERT;

À 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) :

DROP PROCEDURE    PS_TRANSFERT;
DROP TABLE    T_EXTERNAL;

Puis nous reprenons notre algorithme :

Création des index

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);

Création de la clé
  étrangère FK_CLIENT_CIV

ALTER TABLE CLIENT
  ADD CONSTRAINT FK_CLIENT_CIV
  FOREIGN KEY (CIVILID) REFERENCES CIVILITE (IDCIVILITE);

Création de la clé primaire PK_CLIENT

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

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 recours à la fonction UDF ASCII_CHAR.

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

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

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.

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.

Pour chaque enregistrement
  de la table CLIENT
{
  Ajout de la ligne dans la table externe.
}

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 :

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éclaré (contrairement à Upper)*/
 
        INSERT INTO T_EXTERNAL (CIV,NAME,FIRSTNAME,DESCRIPT,RT)
        VALUES (:CIV,:NAME,:FIRSTNAME,:DESCRIPT,:CRLF);
    END
END;

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

On exécute la procédure stockée

EXECUTE PROCEDURE PS_TRANSFERT;

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.

DROP PROCEDURE    PS_TRANSFERT;
DROP TABLE    T_EXTERNAL;
DROP EXTERNAL    FUNCTION ASCII_CHAR;

Maintenant, en allant sous l'explorateur de fichiers, 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