Importer des données en SQL avec LOAD DATA INFILE

I. Présentation

Dans ce tutoriel, nous allons apprendre à importer des données avec la commande "LOAD DATA INFILE" qui permet d'aller chercher des données dans un fichier txt, csv ou autre pour les importer dans une structure de table SQL déjà construite. Dans un précédent tutoriel, nous avions déjà vu comment importer des données dans une base de données MySQL à l'aide d'un fichier au format ".sql" qui doit contenir un ensemble de commande du langage SQL. L'avantage de la commande LOAD DATA INFILE est que le fichier lu ne doit pas avoir un contenu de commande SQL pour que les informations soit importées.

Pour exemple, un fichier SQL qui peut être importé doit contenir des requêtes INSERT de ce type

INSERT INTO Table (champ1, champ2) VALUES ( "valeur1", "valeur2");

Ce qui peut être compliqué si les données à importer ne sont pas présentée et structurée en SQL. La commande LOAD DATA INFILE permet elle d'importer des informations présentées comme suivant

valeur1,valeur2

valeur3,valeur4

ou

valeur1; valeur2

ou

valeur1&valeur2

La commande LOAD DATA INFILE est donc plus flexible quand au format des données qu'il y a a importer. En revanche, la commande LOAD DATA INFILE ne peut contenir la structure d'une base de données ou d'un table comme pourrait le faire un fichier ".sql". En plus de cette plus grande flexibilité, la commande LOAD DATA INFILE permet une importation des données 20 fois plus rapide qu'avec une commande de type mysqlimport depuis un fichier .sql qui contiendrais des commande INSERT INTO (pour plus de détail technique à ce sujet, je vous dirige vers cette URL de la documentation MySQL : Vitesse des requêtes INSERT

Ce tutoriel est effectué sur une machine Linux Debian Wheezy avec un serveur MySQL 5.5.31

II. Construction de notre environnement de test

Pour tester l'utilisation de cette commande, nous allons créer une table dans laquelle nous allons importer nos données

mysql -u root -p
CREATE DATABASE Test1;
USE Test1;
CREATE TABLE T1 (ID INT Not Null, Name CHAR(20) NotNull) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_bin;

Dans un second temps, nous allons construire notre fichier de données, il peut être construit de beaucoup de façon différente grâce à la flexibilité de la commande LOAD DATA INFILE, nous le modifierons plus tard dans le tutoriel pour montrer les différentes options de la commande :

vim /tmp/import.txt

On met ce contenu à l'intérieur :

1,NameA
2,NameB
3,NameC

Nous voulons ici que ces valeurs soient importées dans notre table "T1" qui contient une colonne "ID" pour identifiant et "Name" pour le nom. Nous pouvons donc commencer.

III. Autorisation dans le serveur MySQL et droits utilisateurs

Pour pouvoir importer des données depuis un fichier, il faut que le serveur MySQL permette cette importation et que l'utilisateur ai suffisamment de droit. Nous nous connectons donc sur le serveur en ligne de commande pour vérifier cela.

mysql -u root -p

Note : j'utilise ici l'utilisateur "root", en production, il vaut mieux changer le nom de cet utilisateur (voir ce tutoriel) et utiliser dans la mesure du possible des utilisateurs à droits restreints

L'autorisation du serveur pour l'importation d'une données par la commande "LOAD DATA INFILE" dépends de la variable "load_infile" qui permet de charger un fichier situé sur le serveur. Pour que ce type de fichier soit lisible par le serveur MySQL, il faut qu'il soit situé dans le dossier contenant les bases de données ou alors qu'il soit lisible par tous. (droit de lecture pour les "autres" utilisateurs représenté par une valeur de "4" ou plus sur le chiffre des unités ("xx4"). On regarde donc la valeur de cette variable

show variable like "%local%";

On aura normalement un résultat comme celui la :

On voit donc ici que le serveur autorise l'importation et la lecture d'un fichier qui se situe sur le serveur (la valeur de la variable est à "ON"). La seconde chose à vérifier est que l'utilisateur doit avoir les droits "FILE" sur la table visée. Cela se vérifie avec la commande suivante :

select user,File_priv from mysql.user;

Le terminal MySQL nous retourne alors un résultat comme suivant :

On voit donc bien ici que les utilisateurs par défaut nous pas le droit "FILE", root en revanche oui. Pour donner le droit FILE à un utilisateur, il faut saisir la commande suivante :

GRANT FILE ON *.* to "utilisateur"@"hôte";

Note : le "*.*" représente fait que ce droit sera applicable sur toute les bases de données, il s'agit en effet d'un droit qui est affecté à l'utilisateur indépendamment de la base de données sur lequel il travail.

IV. Utilisation de la commande LOAD DATA INFILE

Nous pouvons maintenant utiliser la commande "LOAD DATA INFILE" pour importer les données que nous avons mis dans notre fichier "/tmp/import.txt". On se connecte donc à MySQL :

mysql -u root -p

Puis ont se connecte à la base de données cible :

USE Test1;

Et enfin on charge notre fichier :

LOAD DATA LOCAL INFILE '/tmp/import.txt' INTO TABLE T1 FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

 On se retrouve, si tout vas bien avec cet affichage dans le terminal :

On voit donc que 3 lignes on été affectées par notre importation de données, 3 on été enregistrées, 0 supprimées, 0 passées et 0 avertissement. On regarde maintenant le contenu de notre table pour voir si les données on bien été enregistrées  :

select * from T1 ;

On a donc ce résultat :

Nos données ont donc bien été importées. Nous allons maintenant détailler un peu plus la commande saisie :

• "LOAD DATA LOCAL INFO "tmp/import.txt" " : Ici on passe donc la commande où l'on va spécifier le fichier à lire

• "INTO TABLE T1 " : On indique ici qu'elle table est concernée par notre importation

• "FIELD TERMINATED BY "," " : Ici ont indique que ce sont les virgules "," qui séparent les différentes informations et qui correspondront donc aux différents champs du fichier.

• "LINE TERMINATED BY "\n" " : On indique ici que les lignes (qui détermine chacune un enregistrement) sont séparées par des sauts de lignes ("\n")

On peut également exclure des lignes du fichiers en question en les identifiants par leur numéro  en ajoutant cette option au reste de la commande :

"IGNORE 1 LINES ;"

Nous excluons ici la première ligne du fichier.

Mickael Dorigny Co-founder

Co-fondateur d'IT-Connect.fr. Auditeur/Pentester chez Orange Cyberdéfense.

See Full Bio