Bonjour, je dois remplacer sur un dump sql ( ou un csv ) 100.000 clés par 100.000 valeurs associées ( afin de transformer une base de 2gigas en relationnelle )

Jusque aujourd'hui la méthode la plus rapide que j'ai trouvée étant de générer un batch opérant chacun des remplacements par :
  sed -i "s/'clé1'/'valeur1'/gi" dump.sql;# ( sur /dev/shm/ bien entendu )

Cela tient le rythme de 500 remplacements par heure .. ce qui n'est pas assez .. et pourtant bien plus rapide qu'en faisant des updates via sql ..

Mes questions
- Est-il possible d'accélerer ce traitement ?
- Est-il possible de renseigner à sed ou autre utilitaire le tableau associatif ? Cela serait - il plus rapide ?
- Existerait-il un mode "sed" qui ne fonctionne pas par expressions régulières ( teste la présence de la chaine, remplace si rencontrée, else next ) ?

Je tente également en splittant le fichier de base en une centaine de morceaux, mais ne suis pas convaincu ..

Merci pour vos lumières  
Merci bcp, bcp, bcp

Il y a des suites significatives de blancs dans ton dump ou pas?
Bref, si j'ai une ligne de ton dump (si tant est qu'il y ait des lignes), est il possible de l'exploser en une liste de mots de manière relativement simple (split par exemple) sans perte de données?
 
Si oui, j'envisagerais en effet de passer par du perl et un tableau associatif.
L'idée de base serait de faire un tableau associatif, de tester si un mot est un mot clé en faisant un grep sur la liste des mots clés, et de remplacer le mot par sa valeur si c'est le cas. Mais pour faire cela, il faut arriver a lire le fichier mot a mot (et même ligne a ligne, pour être relativement efficace)
 
L'idée serait donc de  
1) faire un hash %h associant les clés aux valeurs
2) stocker dans un coin une liste @c des clés (obtenue en faisant keys %h ou déjà construite pour faire l'étape 1)
3) Découper le dump en ligne (si c'est possible), et le lire avec une tie (Tie::File), la structure la plus adapte aux très gros fichiers en Perl.
4) Lecture ligne a ligne donc, on, explose la ligne en une liste de mots
Pour chaque mot m on teste s'il est dans la liste des mots clés avec un grep (grep /^m$/, @c) si oui, on le remplace dans la liste par sa valeur $h{m}
Et écriture en sortie (fichier) de la liste de mots modifiée et refusionnée en une ligne  
 
Note: A l'étape 4, on peut aussi essayer de faire un remplacement de chaque mot m par $h{m} si $h{m} n'est pas vide, mais je crains que ce soit plus lent.
 
A+,

Bien j'ai également essayé en splittant le fichier pour chaque délimiteur .. en n parties .. en une nuit il n'est pas parvenu à finir de remplacer le premier fichier ( 20Mo ) .. Du coup j'ai pris une machine dispo avec un joli proc ( un xeon ) un boot disk usb linux .. 4go sur /dev/shm et c'est parti .. ( là il parvient à me faire plusieurs clés par minutes, selon les occurences, sur le fichier global, c'est la meilleure solution trouvée à ce jour, et me permettra de dire à mon supérieur : bad, bad, bad idea )

Bref .. 17396 sur 159448 .. plus de 10% en 2 jours, c'est déjà ça .. si seulement je pouvais utiliser les 16 cores du serveur de production ..

Bien bon du coup j'essaye en perl .. désolé pour ma syntaxe php like .. le truc est en cours .. ne sait pas lorsqu'il aura fini .. 99% proc .. 35% ram

Du coup, comment je ferais un str_replace($k,$v,$data); en perl ?

J'ai les idées plus claires ce matin.
Ton fichier est un dump de base de données, donc tu as une structure de lignes avec
- éventuellement une marque de début de ligne
- des champs séparés par des séparateurs de champ
- une marque de fin de ligne
Je suppose que tu as un nombre fixe de champs. Toutes les clés que tu veux remplacer sont dans le même champ (je suppose que non mais sinon, ça simplifierait)
 
Donc en perl
1) Tu crées un hash dont les clés sont les clés à remplacer et les valeurs les valeurs de remplacement (je suppose que tu as déjà ça quelque part en un ou deux fichiers, il y a juste a lire ça dans la structure appropriée)
Je note ce hash %h et je note @k la liste des clés (keys %h)
2) tu ouvres ton fichier avec Tie::Handle::CSV qui a l'avantage d'utiliser Tie::File, le standard pour manipuler les gros fichiers et Text::CSV_XS, qui est ce qu'il y a de plus rapide pour parser un CSV
my $csv_fh = Tie::Handle::CSV->new( file => 'mydump', header => 0 );
Il y a des options de cache qui permettront d'optimiser la vitesse de lecture d'une ligne, et d'autres options pour préciser les séparateurs
3) tu ouvres un fichier en sortie, de handle $fh_out
Et après c'est la boucle bien connue:
while (my $csv_line = <$csv_fh> ) {
....
}
 
dans laquelle on fait une boucle sur les champs on teste si un champ est un mot clé, et on fait la substitution si oui.
 
Bref schématiquement, ça ressemble à ceci:

use strict;
use warnings;
 
use Tie::Handle::CSV;
 
my %h = ('cle-1' => 'valeur1', ..., 'cle-n' => 'valeur-n');
my @c = (keys %h);
 
my $csv_parser = Text::CSV_XS->new( { quote_char => undef } );
my $csv_fh = Tie::Handle::CSV->new( file => 'dump.csv', header => 0 , csv_parser => $csv_parser);
while (my $csv_line = <$csv_fh> ) {
  foreach my $i (0..(@{$csv_line}-1)) {
    # optimisable si @{$csv_line} ie le nombre de champs, est fixe
    if (grep(/^$csv_line->[$i]$/, @c) > 0) {
      $csv_line->[$i] = $h{$csv_line->[$i]};
    }
  }
  # IRL ce sera 'print $fh_out $csv_line, "\n";' et le \n est a remplacer par ce qu'il faut en fin de ligne le cas échant
  print $csv_line, "\n";
}

 
A+,
 
 

Nos posts se sont croisés.
 
> plante avec la clé : AC(+),Z19212(+),Z20919(-),Z22040(+) interprêtée comme une regex
faut faire un quotemeta dessus avant.
ou bien faire (c'est plus rapide en execution a priori)
#$data=~s/'\Q$k\E'/'$v--'/g;
 
Bon, si tu as des clés comme ceci, ce que je t'ai filé ne marchera pas directement. c'est quoi ton séparateur de champs? faudra le passer en option a Text::CSV_XS->new
et il faudra tenir compte de ce que je viens de taper:
my $key = quotemeta($csv_line->[$i]);
if (grep(/^$key$/, @c) > 0)
mais faire un quotemeta systématiquement ça risque de rajouter du temps
Dans ce cas la, plutôt que tester avec un grep, il sera sans doute mieux de faire
my @c = map {quotemeta} (keys %h);
my $regexp = '^('.join('|', @c).')$';
et de tester avec if ($csv_line->[$i] =~ /$regexp/o)
 
Bref schématiquement, ça ressemble à ceci:

use strict;
use warnings;
 
use Tie::Handle::CSV;
 
my %h = ('cle-1' => 'valeur1', ..., 'cle-n' => 'valeur-n');
my @c = map {quotemeta} (keys %h);
my $regexp = '('. join('|', @c) .')';
 
my $csv_parser = Text::CSV_XS->new( { quote_char => "'"} );
my $csv_fh = Tie::Handle::CSV->new( file => 'dump.csv', header => 0 , csv_parser => $csv_parser);
while (my $csv_line = <$csv_fh> ) {
  foreach my $i (0..(@{$csv_line}-1)) {
    # optimisable si @{$csv_line} ie le nombre de champs, est fixe
    if ($csv_line->[$i] =~ /^$regexp$/o) {
      $csv_line->[$i] = $h{$csv_line->[$i]};
    }
  }
  # IRL ce sera 'print $fh_out $csv_line, "\n";' et le \n est a remplacer par ce qu'il faut en fin de ligne le cas échant
  print $csv_line, "\n";
}

 
 
A+,

Merci bcp, désolé si j'ai répondu un peu à côté de la plaque, sur le dump sql les valeurs texte sont évidemment séparées par des quotes simples (')

Au final j'ai opté pour un file_get_contents php en mode cli sans limite de temps et de ram, cela passe à peu près 100 clés par minutes ..
A moins d'avoir un plantage ou panne de courant j'aurais le fichier final d'ici un jour ..

Je suis quand même très surpris que faire une update sur une BD avec 100.000 enregistrements prenne énormément de temps (et soit plus lent que 500 remplacements à l'heure)
Pour moi, ça sent la BD mal indexée et le fichier de conf de MySql non tuné. Perso, je serais parti soit sur une procédure stockée, soit sur un script php + Mysql tuné. Tu utilises innoDB ou MyISAM ? En effet, le temps du traitement, il pourrais être intéressant, pour gagner en perfs, de changer le moteur de stockage ou de charger la BD dans une autre BD avec un moteur différent. Si ton serveur le permet, le moteur MEMORY pourrait peut-être grandement améliorer les perfs.
 
J'ai pas toutes les infos en mains pour bien cerner le traitement que tu veux faire. As-tu envisager de déléguer le traitement à un outil de type NoSQL (beaucoup à base de clé/valeur justement). Si le passage des infos pertinentes pour le traitement de Mysql à une BD NoSQL n'est pas trop compliqué, tu gagnerais probablement beaucoup en temps d'exécution.
 
"Au final j'ai opté pour un file_get_contents php en mode cli sans limite de temps et de ram, cela passe à peu près 100 clés par minutes .."
-> Perso, je chargerais pas via PHP un fichier de 2 Go en ram. Je ferais plutôt une lecture séquentielle du fichier, petits bouts par petits bouts. L'alloc mémoire de PHP est très coûteuse en temps et augmente de manière exponentielle en fonction du volume à stocker en ram.
 
Pour info, il m'arrive de faire du calcul matriciel via MySql sur des tables ayant 5 millions d'enregistrements. Ca me prend 30 min environ pour faire un produit de 2 matrices d'un peu plus de 2000x2000 C'est pour ça que je pense que le traitement reste sans doute jouable via Mysql.

Effectivement, moi aussi cela m'a surpris ..

De toutes façons, au lieu d'y passer 3 jours de plus à étudier d'autres procédés, mon responsable a préféré valider cette version qui traite le fichier sql complet .. voilà qui est fait

Autrement, remplacer les valeurs une par une par des instructions sql .. n'était vraiment pas fructueux en terme de performances, même avec le repertoire data de mysql placé sur /dev/shm avec une belle allocation de 8go de ram .. je n'ai pas essayé le moteur de stockage "memory", car je n'ai vu nulle part par le passé de réels gains par l'usage de ce dernier ..

Peut être ai-je loupé un truc, quasiment toutes les colonnes sont indexées .. le script php parvenait à remplacer environ 40 clés par minute .. sql :  une à deux ..

Indexer toutes les colonnes, c'est pas top, à mon avis Je te parie que pour faire ton traitement, aucun index n'est utilisé (ou alors, un mauvais). T'as fait un EXPLAIN de ta requête pour voir quel(s) index était utilisé ? Au passage, j'avais constaté avec Mysql que dans certains cas, c'était plus performant de faire un index portant sur 2 champs (ou plus suivant le traitement) plutôt que de faire un index pour chacun des 2 champs.
 
"le script php parvenait à remplacer environ 40 clés par minute .. sql :  une à deux .." -> C'est clair, Mysql n'utilise pas d'index et scanne toute ta table. Trouve les bons index à mettre en place et tu verras, ça va le faire et aller bien plus vite que par du Perl ou php pur.
 
Edit : et tune le fichier de conf de Mysql. Bien fait, ça peut t'apporter de gros gains. POur une requête bien précise, modifier qq variables de conf de Mysql m'avait fait diviser par 2 à 3 le temps de traitement.