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

Benjamin GAGNEUX - yobenzen

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

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

- Auteur : Benjamin GAGNEUX - yobenzen

- Parution : 16 avril 2005

- Mise à jour : 16 mars 2006

- Licence : 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.

Sommaire

- I. Introduction

- II. Étude de cas

- II-1. Conditions initiales

- II-2. Importer des données

- II-3. Exporter des données

- III. Les pièges à éviter

- IV. Conclusion

- V. Remerciements

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 à éviter.

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

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

II - Étude de cas

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

II-2 - Importer des données

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.

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 :

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

Algorithme

Code correspondant

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 :

Algorithme

Code correspondant

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 :

Algorithme

Code correspondant

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

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

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

II-3 - 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 recours à 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 soi-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

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.

Algorithme

Code correspondant

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;

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

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

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.

SELECT C.IDCLIENT , C.CIVILID
FROM CLIENT C
LEFT JOIN CIVILITE CV ON CV.IDCIVILITE=C.CIVILID
WHERE CV. IDCIVILITE IS NULL;

- 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 :
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ées avant l'exécution de la requête.

- Lors d'exportation, pensez à indexer les colonnes présentes 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écessaires au transfert après utilisation. (À 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 leur aide.