Lecture de fichiers CSV avec H2

1 - Téléchargement et installation de la base de données H2

La base de données H2 est téléchargeable sur le site du projet H2.

Téléchargez l’installateur windows si vous êtes sur une machine windows ou l’installateur multi-plateformes dans le cas contraire.

En double cliquant sur l’installateur, vous installez à la fois le moteur de base de données, et un client léger qui permet de s’y connecter via une interface web.

2 - Trois bases de données en une

H2 vous permet de créer 3 types principaux de bases de données.

Base de données serveur Pour une application professionnelle ou internet. Une base de données serveur utilise le protocole d’accès suivant :

jdbc:h2:tcp://<servername>[:<port>]/[<path>]<databaseName>

Base de données locale (ou embarquée) Pour une utilisation de type bureautique (ex. pour remplacer access), où la base de données n’est interrogée que par un utilisateur à la fois, on utilise le protocole suivant :

jdbc:h2:[file:][<path>]<databaseName>

Base de données en mémoire Pour manipuler de petits volumes de données rapidement, il existe un protocole où toutes les données sont gérées dans la mémoire physique de votre ordinateur :

jdbc:h2:mem:

3 - Créer une base de données locale (ou embarquée)

Si vous lancez H2, votre navigateur par défaut s’ouvre avec une boite de dialogue vous invitant à vous connecter à une base de données existante ou à créer une nouvelle base de données.

En choisissant Generic H2 (embeded) dans les configurations enregistrées vous paramétrez l’accés à une nouvelle base de données :

de type local (embeded)
située dans votre répertoire utilisateur (symbolisé par le tilda)
qui s’appelle test (à changer en "TP_H2/MaBase" pour les besoins de l’exercice)
à laquelle vous pourrez vous connecter en utilisant le nom d’utilisateur ’sa’ et sans mot de passe (remplacez ces valeurs par celles qui vous conviennent)

Connectez-vous.

Un fichier MaBase.h2.db est créé dans le répértoire TP_H2. Un deuxième fichier l’accompagne tant que la base est ouverte (MaBase.lock.db)

Dans l’interface utilisateur, vous voyez apparaitre sur le panneau de gauche :

le nom de la base : jdbc:h2 : /TP_H2/MaBase
un répertoire INFORMATION_SCHEMA qui contient toutes les tables système de la base
le numéro de version du moteur de base de données

Dans le panneau de droite, vous avez :
une zone de texte pour taper vos instructions SQL
quelques explications sur le fonctionnement de l’interface

4 - Récupérer des données à partir d’un fichier csv (ou tabulé)

Récupérer le fichier tabulé mis à disposition et enregistrez-le sur votre disque dans le répertoire C :/TP_H2.

Pour lire cette table dans h2, on utilise la commande CSVREAD.

Cette commande prend un seul paramètre obligatoire, le nom du fichier, et plusieurs paramètres facultatifs.
1 - Nom du fichier : ’chemin et nom du fichier’
2 - En-tête : NULL (fichier avec en-tête) ou nom des colonnes (séparés par le même séparateur que les données elles-mêmes)
3 - Encodage : NULL (encodage natif) ou encodage à utiliser (ex. ’UTF8’)
4 - Séparateur : NULL (si le fichier utilise la virgule comme séparateur) ou le caractère servant de séparateur sinon. Une tabulation étant difficile à insérer dans un formulaire, on peut utiliser la fonction CHAR(9) pour signifier que c’est une tabultaion qui sert de séparateur
5 - Délimiteur : NULL (pas de délimiteur) ou le délimiteur de texte à utiliser (le csv utilise des guillemets pour délimiter des champs incluant eux-mêmes le séparateur de champ)
6 - Caractère d’échappement : NULL ou le caractère d’échappement utilisé pour les caractères spéciaux
7 - Chaîne nulle : NULL ou la chaîne à utiliser pour désigner un NULL au sens SQL

Le fichier DEP_VAR_POP.txt peut ainsi être interrogé en tapant dans la zone de texte :

CALL CSVREAD('C:/TP_H2/DEP_VAR_POP.txt', NULL, NULL, CHAR(9));

Les deux premiers NULL signifient qu’on utilise la ligne d’en-tête du fichier texte pour nommer les colonnes, et que l’on interprète le fichier en utilisant la table de caractères native (Cp1252 pour windows).

Il peut être nécessaire de remplacer le deuxième NULL par ’Cp1252’ à partir d’une machine linux.

CHAR(9) définit la tabulation comme séparateur

5 - Plus loin avec CSVREAD et CSVWRITE

Accéder à une ressource distante

H2 peut accéder à une ressource distante de la même façon qu’à une ressource locale. Ainsi, on peut accéder directement à notre fichier en ligne :

CALL CSVREAD('http://geo.michaelm.free.fr/H2/resources/DEP_VAR_POP.txt',NULL,NULL,CHAR(9));

Interroger le fichier en utilisant le langage SQL

SELECT * FROM CSVREAD('C:/TP_H2/DEP_VAR_POP.txt',NULL,NULL,CHAR(9)) WHERE VARPOP1999_2008 >= 1;

Ici, on a rajouter une condition sur les données à afficher.

Formatter la première colonne en utilisant les fonctions disponibles

La première colonne contient à la fois le numéro et le nom du département.

Utiliser les fonctions SUBSTRING et LOCATE pour extraire chacune des parties qui nous intéresse et pour les placer dans une colonne distincte.
Utiliser REPLACE pour normaliser l’écriture des nombres en remplaçant les virgules par des points et les espaces par rien du tout.
utiliser la syntaxe attribut ::type pour "transtyper" les colonnes texte résultantes en nombres.

SELECT SUBSTRING("DÉPARTEMENT", 1, LOCATE(' ',"DÉPARTEMENT")) AS NUMDEP,

           SUBSTRING("DÉPARTEMENT", LOCATE(' ',"DÉPARTEMENT")+1) AS NOMDEP,

           REPLACE(REPLACE(POP2008,',','.'),' ','')::NUMERIC AS POP,

           REPLACE(REPLACE(VARPOP1999_2008,',','.'),' ','')::NUMERIC AS VAR

FROM CSVREAD('C:/TP_H2/DEP_VAR_POP.txt',NULL,NULL,CHAR(9));

SUBSTRING extrait les premiers caractères jusqu’au premier blanc rencontré, dont la position est identifié par LOCATE(’ ’, "DÉPARTEMENT")
Le double REPLACE change les virgules par des points puis enlève les espaces.
::NUMERIC transforme le champ de type texte en champ de type numerique (il faut bien sur que le contenu de la colonne ne contienne que des nombres bien formattés, d’où le traitement précédent).

Créer une table structurée et pérenne dans votre base de données

Ce code est le même que le précédent, sauf qu’on a inséré une instruction CREATE TABLE VARPOP AS devant.

CREATE TABLE VARPOP AS

    SELECT SUBSTRING("DÉPARTEMENT", 1, LOCATE(' ',"DÉPARTEMENT")) AS NUMDEP,

           SUBSTRING("DÉPARTEMENT", LOCATE(' ',"DÉPARTEMENT")+1) AS NOMDEP,

           REPLACE(REPLACE(POP2008,',','.'),' ','')::NUMERIC AS POP,

           REPLACE(REPLACE(VARPOP1999_2008,',','.'),' ','')::NUMERIC AS VAR

    FROM CSVREAD('C:/TP_H2/DEP_VAR_POP.txt',NULL,NULL,CHAR(9));

Exporter cette nouvelle table dans un fichier texte tabulé

L’instruction suivante écrit la table nouvellement créée et bien reformattée dans un nouveau fichier texte.

CALL CSVWRITE('C:/TP_H2/NEW_DEP_VAR_POP.txt', 'SELECT * FROM VARPOP', CHAR(9));

Se référer à la documentation de CSVWRITE pour les options.

Noter que l’on peut aussi exporter le résultat d’une requête plus complexe comme :

CALL CSVWRITE('C:/TP_H2/EVO_MOYENE.txt', 'SELECT ''EVO_MOYENNE'', SUM(POP*VAR)/SUM(POP) AS MOYENNE FROM VARPOP', NULL, CHAR(9));

Cette requête calcul l’évolution de population moyenne pour toute la France (somme des variations pondérées par la population divisée par la population totale) et écrit le résultat dans un fichier texte.