Discussion :

[titimomotete]

Bonjour à tous,

Je vous sollicite car je reconstitue actuellement une base de données par des imports successifs de fichiers CSV. Ceux-ci comportent plus de 500 000 lignes pour environ 180Mo chacun.
Je les importe par la commande SQL suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
LOAD DATA INFILE "D:/TaxRef/TAXREFv110 UTF8 (A).txt"
          INTO TABLE maj_taxref.taxref_11
		  FIELDS TERMINATED BY '\t'
		  OPTIONALLY ENCLOSED BY ''
		  LINES TERMINATED BY '\n'
		  (REGNE,PHYLUM,CLASSE,ORDRE,FAMILLE,SOUS_FAMILLE,TRIBU,GROUP1_INPN,GROUP2_INPN,CD_NOM,@CD_TAXSUP,@CD_SUP,CD_REF,RANG,LB_NOM,LB_AUTEUR,NOM_COMPLET,NOM_COMPLET_HTML,NOM_VALIDE,NOM_VERN,NOM_VERN_ENG,@HABITAT,FR,GF,MAR,GUA,SM,SB,SPM,MAY,EPA,REU,SA,TA,TAAF,PF,NC,WF,CLI,URL)
		  SET CD_TAXSUP = IF(@CD_TAXSUP = '', NULL, @CD_TAXSUP),
		  CD_SUP = IF(@CD_SUP = '', NULL, @CD_SUP),
                  HABITAT = IF(@HABITAT = '', NULL, @HABITAT);

Je n'ai pas eu trop de soucis jusque là mais sur certaines tables (les plus grosses), je rame pour réussir à les importer en entier : sur le dernier, j'ai tronçonné les fichiers en 2 parties de 200 000 lignes et une dernière partie de 150 000 lignes. Je lance la commande pour le premier fichier et j'obtiens 200 000 lignes dans la table, j'insère le second et je totalise 400 000 fiches mais sur le troisième fichier, j'abouti à 524 708 lignes, puis 532 456, 543 624, etc. et un nombre différent à chaque tentative mais jamais les 550 000 que je devrais normalement obtenir.
Si je commence par le dernier fichier, je parviens bien à insérer mes 150 000 lignes donc le problème n'est pas dans le fichier lui-même ou son contenu.
Sur la table précédente qui m'a posée le même soucis, j'ai envoyé chaque segment dans un ordre différent et je n'ai fini, je ne sais comment, à insérer toutes mes lignes sans exception. Sur cette table, malgré de multiples essais, pas moyen d'y parvenir.

Est-ce que quelqu'un aurait la moindre idée d'où peut venir le problème ? Problème de taille (actuellement toute la base de données pèse 1.3Go) ? limite de performances de l'ordinateur (les requêtes prennent entre 6 et 7 secondes à s'exécuter) ?

Je tourne sous Windows 10 avec un serveur UwAmp portable 3.1.0, PHP 7.0.3, MySQL 5.7.11 et PHPMyAdmin 4.5.4.1
La structure de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE TABLE `taxref_11` (
  `REGNE` varchar(255),
  `PHYLUM` varchar(255),
  `CLASSE` varchar(255),
  `ORDRE` varchar(255),
  `FAMILLE` varchar(255),
  `SOUS_FAMILLE` varchar(255),
  `TRIBU` varchar(255),
  `GROUP1_INPN` varchar(255),
  `GROUP2_INPN` varchar(255),
  `CD_NOM` int(7),
  `CD_TAXSUP` int(7) DEFAULT NULL,
  `CD_SUP` int(7) DEFAULT NULL,
  `CD_REF` int(7),
  `RANG` varchar(5),
  `LB_NOM` varchar(255),
  `LB_AUTEUR` varchar(255),
  `NOM_COMPLET` varchar(255),
  `NOM_COMPLET_HTML` varchar(255),
  `NOM_VALIDE` varchar(255),
  `NOM_VERN` varchar(700),
  `NOM_VERN_ENG` varchar(255),
  `HABITAT` tinyint(1) DEFAULT NULL,
  `FR` char(1),
  `GF` char(1),
  `MAR` char(1),
  `GUA` char(1),
  `SM` char(1),
  `SB` char(1),
  `SPM` char(1),
  `MAY` char(1),
  `EPA` char(1),
  `REU` char(1),
  `SA` char(1),
  `TA` char(1),
  `TAAF` char(1),
  `PF` char(1),
  `NC` char(1),
  `WF` char(1),
  `CLI` char(1),
  `URL` varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Merci de votre aide !

[manzeki]

Je ne comprend pas l'usage des variables utilisateurs @CD_TAXSUP, @CD_TAXSUP dans la liste des champs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
(REGNE,PHYLUM,CLASSE,ORDRE,FAMILLE,SOUS_FAMILLE,TRIBU,GROUP1_INPN,GROUP2_INPN,CD_NOM,@CD_TAXSUP,@CD_SUP,CD_REF,RANG,LB_NOM,LB_AUTEUR,NOM_COMPLET,NOM_COMPLET_HTML,NOM_VALIDE,NOM_VERN,NOM_VERN_ENG,HABITAT,FR,GF,MAR,GUA,SM,SB,SPM,MAY,EPA,REU,SA,TA,TAAF,PF,NC,WF,CLI,URL)

Pourquoi ne pas faire le SET en une commande séparée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
UPDATE maj_taxref.taxref_11 SET CD_TAXSUP = NULL WHERE CD_TAXSUP = ''

J'ai l'impression que vous lancez les inserts suivants alors que MySQL est encore occupé.

Un truncate avant les insertions peut aider :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TRUNCATE maj_taxref.taxref_11;

[escartefigue]

En marge de cette discussion, la table est extrèmement mal modélisée : absence de PK, pléthore de colonnes varchar qui devraient pour la plupart être des FK de type integer, non respect des formes normales. Bref, à revoir complètement !

Exemple : s'il s'agit, comme je le suppose, d'une table de classification d'êtres vivants, vu qu'une famille appartient à un et un seul ordre, qu'un ordre appartient à une et une seule classe et une classe à un seul règle, seule la famille devrait être présente, pas l'ordre, ni la classe, ni le règne. Et cette famille ne devrait évidemment pas être de type varchar(n) mais de type integer(n) en lien référentiel (FK) vers une table des familles.

Vous gagnerez énormément de place disque, de performances et de fiabilité

[titimomotete]

En marge de cette discussion, la table est extrèmement mal modélisée : absence de PK, pléthore de colonnes varchar qui devraient pour la plupart être des FK de type integer, non respect des formes normales. Bref, à revoir complètement !

Exemple : s'il s'agit, comme je le suppose, d'une table de classification d'êtres vivants, vu qu'une famille appartient à un et un seul ordre, qu'un ordre appartient à une et une seule classe et une classe à un seul règle, seule la famille devrait être présente, pas l'ordre, ni la classe, ni le règne. Et cette famille ne devrait évidemment pas être de type varchar(n) mais de type integer(n) en lien référentiel (FK) vers une table des familles.

Merci pour la remarque. Alors, comme ce n'est pas moi qui suis à l'origine des CSV, je fais avec ce qu'on me donne ! Et ça n'a rien d'une base relationnelle !
Pour pouvoir changer la structure de la table... et virer tout ce qui me sera parfaitement inutile, il faut bien que j'insère les données avant pour pouvoir les travailler : l'objectif de la manœuvre est juste de disposer des données pour mettre à jour notre propre base. Avec une autre table, qui elle contient des références à cette table ci, on doit venir piocher les informations dont on a besoin, une fois et une seule (en gros, NOM_COMPLET avec CD_NOM).

[titimomotete]

Pour info, je viens de tester en faisant l'import des 400 000 premières fiches dans une table puis des 150 000 restantes dans une autre avant de copier les données de la seconde table dans la première depuis PHPMyAdmin (donc sans plus passer par le CSV) et le problème est le même : une partie des lignes disparaissent dans l'opération.

[manzeki]

Ajoutez une clé primaire (autoincrement ) dans cette table juste pour faciliter le contrôle de tes données, et si possible mettre les champs integer en varchar.

Dès que toutes les données sont importées, reformatez et importez les dans des tables qui sont normalisées.

Installez Open Refine, c'est un outil ETL permettant de valider, nettoyer et exporter les données et capable de supporter de grands volumes de données.
Vous pouvez y importer du excel, csv, json, xml, ... puis exporter après traitement en sql, json, csv...

[titimomotete]

@Manzeki

Je ne comprend pas l'usage des variables utilisateurs @CD_TAXSUP, @CD_TAXSUP dans la liste des champs

le CSV a des valeurs numériques qui sont vides. Je suis obligé de les remplir avec un NULL car sinon, MySQL bloque en me disant qu'un integer ne peut pas être vide #1366 - Incorrect integer value: ''

Pourquoi ne pas faire le SET en une commande séparée:

Pour la raison évoquée juste au dessus, je suis obligé de le faire au moment ou j'insère les données pour que les données soient acceptées par MySQL

J'ai l'impression que vous lancez les inserts suivants alors que MySQL est encore occupé.

Je vérifie pourtant avant chaque nouvelle insertion de données que le nombre de ligne est correct en affichant le contenu de la table. J'ai essayé de laisser un petit temps (entre 30 secondes et une minute) entre chaque commande mais ça ne change rien (aucun problème pour les deux plus gros, même quelques secondes après avoir lancé la commande d'insertion, juste pour le dernier qui est systématiquement incomplet)

Un truncate avant les insertions peut aider

Je vide systématiquement la table manuellement avec un TRUNCATE avant chaque nouvel essai