[DBI] Rendre DBI verbeux avec un LOAD DATA LOCAL INFILE

Version imprimable

08/09/2017, 14h59
Arioch

[DBI] Rendre DBI verbeux avec un LOAD DATA LOCAL INFILE
Hello ! :D

J'ai des données assez volumineuses à charger dans une table d'une base de données MySql.

Environ 400.000 enregistrements et ceci, quotidiennement.

Pour gagner du temps, j'ai choisi de charger ces données en les stockant dans un fichier csv, puis en demandant à MySql de les charger via la méthode LOAD DATA LOCAL INFILE.

En ligne de commande depuis la console de MySql, j'obtiens ceci :
Code:

1 2 mysql>LOAD DATA LOCAL INFILE 'essai.csv' INTO TABLE essai FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n"; mysql>test.essai: Records: 404416 Deleted: 0 Skipped: 0 Warnings: 1
Ce qui m'intéresse ici, c'est de récupérer les infos de la ligne retournée par MySql avec les infos deleted, skipped et warnings.

Dans mon code Perl, je ne sais pas comment stocker cette info lorsque j'exécute la commande LOAD DATA LOCAL INFILE.

Je ne vois pas l'info apparaitre dans la console Linux, ni dans le log amoureusement géré par Log4Perl.

J'ai regardé du côté de DBI_TRACE mais même en passant la variable d'environnement à la valeur MAX de 15, je ne vois rien.

Et du coup, c'est pas pratique du tout !

Quelqu'un aurait une idée ?
08/09/2017, 19h53
Lolo78

Peux-tu montrer la syntaxe de la commande Perl que tu utilises pour appeler le loader MySql?

Salut Arioch.

Code:

mysql>LOAD DATA LOCAL INFILE 'essai.csv' INTO TABLE essai FIELDS TERMINATED BY "\t" LINES TERMINATED BY "\n";

Dans quel environnement êtes-vous ? Windows, linux ou Apple ?

Voici le contenu du fichier ".csv" à charger :

Code:

1
2
3
\N;"10/5/2014 15:33:17";"web"
125;"15/5/2014 23:53:59";"alpha\; beta\; gamma"
-247;\N;"zéro"

Et voici un exemple basique, sous windows, d'un script mysql pour charger un fichier Excel de type ".csv".

Code:

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS trav
--------------
 
--------------
CREATE TABLE trav (
  col_num   integer      null default null,
  col_date  datetime     null default null,
  col_char  varchar(255) null default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3)
     set col_num  =             trim(@F1),
         col_date = str_to_date(trim(@F2), '%d/%c/%Y %H:%i:%s'),
         col_char =             trim(@F3)
--------------
 
--------------
select * from trav
--------------
 
+---------+---------------------+--------------------+
| col_num | col_date            | col_char           |
+---------+---------------------+--------------------+
|    NULL | 2014-05-10 15:33:17 | web                |
|     125 | 2014-05-15 23:53:59 | alpha; beta; gamma |
|    -247 | NULL                | zéro               |
+---------+---------------------+--------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

Comme vous le voyez, la table "trav" est vide.
Si par la suite, vous avez besoin de faire un dispatching du contenu de votre table "trav" dans les tables définitives, le mieux est de procéder ainsi.

Code:

mysql>test.essai: Records: 404416 Deleted: 0 Skipped: 0 Warnings: 1

Cette information n’apparaît pas sous MySql et ne vous sert à rien si vous chargez votre fichier dans une table de travail.

Si vous avez besoin de connaitre le nombre de lignes chargé dans votre table, il suffit de faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
--------------
select count(*) from `trav`
--------------
 
+----------+
| count(*) |
+----------+
|        3 |
+----------+
 
Appuyez sur une touche pour continuer...

11/09/2017, 15h07
Arioch

Bonjour et merci pour vos réponses, c'est sympa ! :D

Alors, pour répondre à vos questions :

1) la syntaxe exacte que j'utilise pour charger mes fichiers csv est la suivante :

Code:

LOAD DATA LOCAL INFILE '$table.csv' REPLACE INTO TABLE $table FIELDS TERMINATED BY \"\t\" LINES TERMINATED BY \"\n\"

La table de destination est une table de production et les données que je lui envoie via le csv sont susceptibles d'être déjà présentes dans la table.

2) ce que je recherche pour le récupérer et le coller dans le log, c'est justement ce que renvoie MySql lorsqu'on se connecte dessus via la console et qu'on charge les données via LOAD DATA INFILE à la main, c'est à dire :

Code:

Records: 404416 Deleted: 0 Skipped: 0 Warnings: 1

Non seulement, cela permet d'avoir une trace mais aussi ça permet d'alerter qu'il y a un warning ou autre durant la mise à jour de la table.

J'en ai 8, des tables à mettre à jour quotidiennement et quand je fais le cumul du nombre d'enregistrements à envoyer à la base de données, c'est plus d'un million. Du coup, LOAD DATA est bien pratique pour gagner un temps fou mais j'aimerais juste pouvoir capturer ces infos là.
11/09/2017, 18h53
Lolo78

Bonjour,
ce que je demandais, c'est quelle syntaxe Perl tu utilises pour lancer ta commande MySQL.
12/09/2017, 00h36
Artemus24

Salut Arioch.

Citation:

Envoyé par Arioch

Alors, pour répondre à vos questions :

Vous ne répondez pas à ma question qui est la suivante :

Citation:

Envoyé par Artemus24

Dans quel environnement êtes-vous ? Windows, linux ou Apple ?

Et vous ne faites que répéter ce que je sais déjà.

Citation:

Envoyé par Arioch

La table de destination est une table de production et les données que je lui envoie via le csv sont susceptibles d'être déjà présentes dans la table.

Oui et alors ???

1) on ne travaille jamais directement sur une table mysql qui est en production. Je le répète jamais !
On passe par une copie de celle-ci et quand vous êtes sûr et certain qu'elle correspond à votre attente alors là, oui, vous pouvez basculer la copie en production.

Et que faites-vous si, suite à une mauvaise manipulation, vous devez revenir en arrière ?
Je suppose que vous allez faire une restauration de votre dernier backup !
Que de temps perdu, juste parce que vous ne travaillez pas correctement.

2) Je vous conseille de ne pas faire un "replace" mais de charger votre fichier ".csv" directement dans une table de travail qui sera destiné au chargement.
Cette table de travail servira comme base de départ à la mise à jour de vos autres table définitives.

3) comme je vous l'ai dit précédemment, cette information que vous recherchez ne vous sert à rien, car c'est au niveau de mysql que vous devez faire votre mise à jour, mais pas dans un "load".

4) vous devez créer un script mysql, qui pour chaque table que vous devez mettre à jour, devra tenir compte des nouvelles créations, des modifications ou des suppressions de lignes.
Évitez de faire des répétitions inutiles dans vos tables MySql.
En utilisant un SGBDR, vous devez appliquer les règles de la théorie des ensembles et non reprendre à l'identique vos fichiers ".csv".

5) si vous ne savez pas comment faire, je peux vous aider à construire votre script MySql.

Citation:

Envoyé par Arioch

Non seulement, cela permet d'avoir une trace mais aussi ça permet d'alerter qu'il y a un warning ou autre durant la mise à jour de la table.

Je suis d'accord pour conserver une trace dans un fichier log, mais faut encore que cette information serve à quelque choses !

Là, vous chargez vos tables et ensuite, vous dites attention j'ai un problème.
Il est déjà trop tard pour intervenir et corriger vos problèmes.

Citation:

Envoyé par Arioch

J'en ai 8, des tables à mettre à jour quotidiennement et quand je fais le cumul du nombre d'enregistrements à envoyer à la base de données, c'est plus d'un million.

1 million de lignes, c'est pas grand chose comme volumétrie.
Raison de plus pour travailler correctement, ne croyez-vous pas ?

Citation:

Envoyé par Arioch

Du coup, LOAD DATA est bien pratique pour gagner un temps fou mais j'aimerais juste pouvoir capturer ces infos là.

Non, juste votre approche est dangereuse car ce que vous croyez gagner comme temps, vous risquez d'en perdre bien plus en cas de problème.

@+
13/09/2017, 13h04
Arioch

Bonjour Artemus24,

Citation:

Envoyé par Artemus24

Vous ne répondez pas à ma question

Toutes mes excuses !

Mon environnement est Linux (CentOS).

Je comprends tout à fait votre vision des choses mais moi, je suis obligé de me plier à la manière de fonctionner de mon environnement professionnel.

Un backup de nos bases est fait toutes les nuits et en cas de soucis (comprendre par là, si une mise à jour foire), on recharge et voilà, problème réglé.

Je suis également soumis à un impératif de temps. Grosso modo, 5/10 minutes MAX pour le traitement complet, lot initial excepté.

C'est pourquoi je travaille directement dans la base de production (pas en ce moment bien évidemment puisqu'en phase de dév) et c'est pour une question de réduire le temps de traitement que j'emploie du LOAD DATA plutôt que des requêtes SQL de type REPLACE INTO. C'est encore une fois pour gagner du temps que j'utilise du DELETE par groupe de 1000 enregistrements MAX plutôt qu'unitairement.

Votre vision en matière de gestion d'une base de données en production est une vision idéale que je ne peux malheureusement pas mettre en place.

Je vous ai trouvé quelque peu "acide" dans vos réponses, pour ne pas dire limite "agressif". Ce n'était pas utile.

Salut Arioch.

Citation:

Envoyé par Arioch

Je vous ai trouvé quelque peu "acide" dans vos réponses, pour ne pas dire limite "agressif". Ce n'était pas utile.

J'étais administrateur DB2 sur gros système IBM, et comme je suis maintenant à la retraite, je m'occupe des SGBDR MySql, FireBird.
Je sais de quoi je parle, et pour vous répondre, OUI, c'est utile car quand on est correcte, le message ne passe pas du tout !
J'essaye de vous faire comprendre que vous devez appliquer les bonnes pratiques.

Pour répondre à votre première question, à savoir comment récupérer un message en provenance d'un programme ou d'une commande.
Je suis sous Windows, et je lance un batch où je désire récupérer les messages disons d'anomalies. Voici le batch qui va produire les messages :
Code:

1 2 3 4 5 6 7 @echo off echo Le premier paramètre : %1 echo Le second paramètre : %2 echo Message d'erreur >&2 exit 5
Il est basique mais reflète le cas général.
Le message d'erreur sort en "stderr" alors que les autres messages sortent en "stdout".
Et en plus, j'ai un code retour dont la valeur est "5". Voici le script perl :
Code:

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 #!C:\perl64\bin\perl.exe -w use strict; use warnings; use diagnostics; use 5.24.1; use mylib::finish; # -------------------------------- # Passage de paramètres à un Batch # -------------------------------- my @cmd = ('Batch.bat', 'hello', 'sir', '2>&1'); my $res = `@cmd`; my $codret = $? >> 8; foreach (split /\n/, $res) { say ">>>".$_."<<<"; } say ">>> code retour = ".$codret." <<<"; # --- # Fin # --- &finish::sortie();
Faites attention, ce sont des back quote (touche 7 du clavier) dans la ligne "my $res".
C'est dans le foreach que l'on va récupérer toutes les messages et les afficher.

Et voici l'exécution de ce script perl sous windows :
Code:

1 2 3 4 5 6 >>>Le premier paramètre : hello<<< >>>Le second paramètre : sir<<< >>>Message d'erreur <<< >>> code retour = 5 <<< Appuyez sur une touche pour continuer...
A vous de l'adapter à la commande "mysql" que vous remplacerez par mon "Batch.bat".

Lolo78 ayant plus d'expérience en Perl que moi, répondra aux autres syntaxes du déclenchement d'une commande.

@+