Requete sql à partir d'un csv le tout en bash

Ashgenesis · Février 20, 2016, 4:43

Bonjour,
Ne me demandez pas pourquoi le bash c’est imposé comme ça.
En fait, le problème que j’ai actuellement est de pouvoir rajouter des données dans les différentes tables à partir d’un seul csv contenant tous les champs nécessaires.
prenons l’exemple d’un csv comme celui çi

L’objectif en fait est de passer le fichier csv en paramètre d’un script bash et qu’il me rajoute le contenu du csv dans les bonnes tables. Le csv peux contenir des centaines de lignes donc rajouter le contenu à la main c’est pas trop le top.

La requète LOAD DATA INFILE m’aide bien pour une table temporaire mais après pour faire la correspondance avec les autres tables, je ne vois pas trop comment faire. Si vous avez des idées, je suis preneur

Ashgenesis · Février 20, 2016, 4:43

Bon c’est pas encore optimisé mais ça à le mérite de fonctionner.

[code]while read enreg; do
var1=echo $enreg | cut -d';' -f1
var2=echo $enreg | cut -d';' -f2
var3=echo $enreg | cut -d';' -f3
var4=echo $enreg | cut -d';' -f4

mysql -uuser -ppass <<END_OF_STMT

INSERT INTO tableA (champ1, champ2) VALUES ($var1, $var2);
INSERT INTO tableB (champ1, champ2) VALUES ($var3, $var4);
END_OF_STMT
done < lefichiercsv[/code]
C’est résolu mais en attente d’amélioration

ripat · Février 20, 2016, 4:43

D’expérience, le LOAD DATA INFILE sera beaucoup plus rapide pour des gros fichiers. Tu pourrais manipuler les fichiers plats pour en créer un par table (par manipulation awk ou sed) et ensuite faire autant de LOAD DATA INFILE que tu as de tables à insérer.

Fichier de base

Table A

champ1;champ2 champ1;champ2 champ1;champ2
Table B

champ1;champ2 champ1;champ2 champ1;champ2
Ensuite

qry="LOAD DATA INFILE 'fichier-csv-tableA' INTO TABLE tableA FIELDS TERMINATED BY ';'" mysql -s -u $MYSQL_LOGIN -p$MYSQL_PASSWORD -e "$qry"

dexmon · Février 20, 2016, 4:44

Personnelement j’utilise un fichier de connexion externe

Fichier “mysql_connect.db”:

sql="/usr/bin/mysql" hostdb="localhost" userdb="totouser" passdb="totopasse" db="totodb"

Et je l’appelle comme ceci:

[code]#!/bin/sh

PATH=/bin:/usr/bin:/usr/local/bin
. mysql_connect.db
[…][/code]

Si ça peu aider, ça évite de taper les infos de connexions à chaque fois…

Ashgenesis · Février 20, 2016, 4:44

[quote=“ripat”]D’expérience, le LOAD DATA INFILE sera beaucoup plus rapide pour des gros fichiers. Tu pourrais manipuler les fichiers plats pour en créer un par table (par manipulation awk ou sed) et ensuite faire autant de LOAD DATA INFILE que tu as de tables à insérer.[/quote]Je vois ce que tu veux dire mais par contre si tu avais un exemple d’utilisation de awk ou sed avec un csv, je suis preneur
En attendant je vais consulter les man

@dexmon : Merci de ta participation c’est un avantage certain d’utiliser un fichier externe Je le fait en php mais j’avoue qu’avec bash, j’ai pas forcément les bonnes pratiques.

fran.b · Février 20, 2016, 4:44

En fait il faudrait être plus précis sur ce que tu veux faire:

Tu as un CSV et tu veux le mettre en forme pour remplir une table? (genre ; -> TAB par exemple)
ou
Tu as un CSV et tu veux mettre à jour plusieurs tables?

Dans tous les cas, awk est bien pour ça: Si ton séparateur est ;, tu fais

et dans … $1=champ1, $2=champ2, etc. Tu fabriques tes commandes MySQL comme dans php puis tu fais mysql -u… -p < fic.sql

Ashgenesis · Février 20, 2016, 4:44

C’est mon gros défaut je sais ce que je dois faire mais j’oublie d’en dire la moitié

Donc, en fait j’ai un csv contenant des champs de plusieurs tables et il faut que je mette en place un script bash qui prend ce csv et qui rajoute le contenu dans les différentes tables.

Ashgenesis · Février 20, 2016, 4:45

Une alternative plus propre à ce code.[code]while read enreg; do
var1=echo $enreg | cut -d';' -f1
var2=echo $enreg | cut -d';' -f2
var3=echo $enreg | cut -d';' -f3
var4=echo $enreg | cut -d';' -f4

mysql -uuser -ppass <<END_OF_STMT

INSERT INTO tableA (champ1, champ2) VALUES ($var1, $var2);
INSERT INTO tableB (champ1, champ2) VALUES ($var3, $var4);
END_OF_STMT
done < lefichiercsv[/code]cat lefichiercsv | \ while IFS=";" read var1 var2 var3 var4 do mysql -uuser -ppass <<END_OF_STMT INSERT INTO tableA (champ1, champ2) VALUES ($var1, $var2); INSERT INTO tableB (champ1, champ2) VALUES ($var3, $var4); END_OF_STMT done

fran.b · Février 20, 2016, 4:45

Pas mal, je ne connaissais pas IFS…

ripat · Février 20, 2016, 4:45

Pour confirmer mon intuition sur le LOAD DATA INFILE j’ai fait le petit bench suivant:

Importation d’un fichier de 8000 lignes dans deux tables différentes.
[ol]
[li]Script : (boucle avec INSERT)

[code]MYSQL_LOGIN=‘’
MYSQL_PASSWORD='*****’

cat /tmp/f |
while IFS=";" read var1 var2 var3 var4
do
mysql -u $MYSQL_LOGIN -p$MYSQL_PASSWORD <<END_OF_STMT
INSERT INTO test.table1 (col1, col2) VALUES ("$var1", “$var2”);
INSERT INTO test.table2 (col1, col2) VALUES ("$var3", “$var4”);
END_OF_STMT
done
[/code]
[/li]
[li]Script 2: (split avec awk et import avec LOAD DATA INFILE)

[code]awk ‘BEGIN {FS=";"}
{print $1 “;” $2 > “/tmp/f1”}
{print $3 “;” $4 > “/tmp/f2”}’ /tmp/f

qry="
LOAD DATA INFILE ‘/tmp/f1’ INTO TABLE test.table1 FIELDS TERMINATED BY ‘;’;
LOAD DATA INFILE ‘/tmp/f2’ INTO TABLE test.table2 FIELDS TERMINATED BY ‘;’;
"
mysql -s -u $MYSQL_LOGIN -p$MYSQL_PASSWORD -e “$qry”[/code][/li][/ol]

Résultats impressionnants:
Script 1: 1m57.703s
Script 2: 0m0.095s

Le moteur MySQL déteste les INSERT en boucle!

fran.b · Février 20, 2016, 4:45

J’imagine que les insert ferme la table à chaque fois, mais le load data concatène à la suite d’une table existante? Je n’y avais jamais pensé à vrai dire…

Ashgenesis · Février 20, 2016, 4:45

Sympa ton benchmark, intéressant, je vais voir pour optimiser le script mais malheureusement, j’ai déjà rendu la première version, enfin je verrais ça vendredi

ed · Février 20, 2016, 4:47

avec un begin / commit à la fin, ça donne quoi ?

Moi je pense plutot que le pb viens de l’autocommit

ripat · Février 20, 2016, 4:47

Je pense surtout que la lenteur des SELECT en boucle provient du fait que le script plus haut ouvre une connexion à la bdd tous les deux INSERT. Il serait plus efficace de mettre l’ensemble des INSERT dans un seul fichier et ensuite l’injecter dans la bdd en une seule connexion.

Ceci dit, si les tables utilisent un moteur de stockage transactionnel comme InnoDB ou DBD, un BEGIN/… INSERT …/COMMIT devrait sans doute encore améliorer les choses.

Je teste ça demain.

ripat · Février 20, 2016, 4:47

Alors, voici:

Pour mémoire, 4 manières d’insérer:
[ol]
[li]Deux INSERT et une connexion MySQL par boucle.while ...; do; mysql ... (INSERT;INSERT) done[/li]
[li]Tous les INSERT dans un fichier puis une seule connexion MySQLwhile ...; do; INSERT...; INSERT...; >> requete.sql done mysql .... < requete.sql[/li]
[li]Tous les INSERT dans un fichier avec BEGIN/COMMIT puis une seule connexion MySQLecho "BEGIN;" > requete.sql while ...; do; INSERT...; INSERT...; >> requete.sql done echo "COMMIT;" >> requete.sql mysql .... < requete.sql[/li]
[li]Split avec awk et LOAD DATA IN FILE dans une seule connexion MySQLawk ... > f1 awk ... > f2 mysql .... -e 'LOAD DATA INFILE f1...; LOAD DATA INFILE f2...;'[/li][/ol]

Résultats du bench:

1- 2 INSERT par connexion 138.0 sec. 276x plus lent 2- tous les INSERT, 1 connexion 46.0 sec. 92x 3- idem avec BEGIN/COMMIT 3.6 sec. 7x 4- LAOD DATA INFILE 0.5 sec. 1x

Bien vu pour le BEGIN/COMMIT. Ça améliore sensiblement les perf. J’ai essayé ce bench sur des tables vides, des tables déjà peuplées (8000 lignes), avec ou sans index. Pas de différences significatives.