Charger des données spatiales : alimenter PostgreSQL PostGIS

Nous avons récupéré les données spatiales qui vont servir à développer l’application cartographique puis installé la base de données et son extension spatiale qui vont servir à enregistrer les données.

Quand se pose la question de l’alimentation de la base, une multitude de solutions se profilent. On peut par exemple utiliser un outil logiciel en ligne de commande, une extension à un logiciel existant, QGIS ou Pg admin proposent de telles extensions, ou encore utiliser un ETL (Extract Transform Load) comme Geokettle afin d’automatiser le processus.

Nos jeux de données possèdent quelques particularités comme nous l’avons vu et je vais couvrir deux cas de figure pour répondre au besoin.

Gérer les tables au travers de QGIS Desktop

QGIS est une solution complète qui permet d’effectuer beaucoup d’opérations simplement à la souris et avec beaucoup de contrôle par rapport à un outil en ligne de commande. Je vais l’utiliser en priorité avec l’extension DB Manager.

Sur les anciennes version de QGIS l’extension SPIT permettait déjà l’import de fichiers au format Shapefile vers une base de données. Cette extension sera remplacée à terme par un système unifié, DB Manager que nous utiliserons pour nos besoins. (J’ai rencontré par ailleurs des problèmes d’import avec SPIT sur les dernières versions)

Import des données et coordonnées géographiques des établissements scolaires français

Ouvrez QGIS et procédez à l’ouverture du fichier de données (référez vous au document de préparation des données).

Le logiciel écarte automatiquement 120 enregistrements pour absence de géométrie, nous n’avons pas à faire l’opération manuellement.

Vous devriez alors vous retrouver avec une représentation des établissements par des points pour les établissements de métropole et d’outre mer.

Comme nous l’avons vu précédemment, le fichier possède plusieurs système de coordonnées pour les valeurs X et Y, et seules les valeurs pour la France métropolitaine et la Corse sont exprimées dans le système de coordonnées RGF93 / Lambert-93, les points pour les établissements en dehors de la métropole sont donc mal positionnés sur la carte.

Nous avons également vu qu’au niveau des attributs seul le code postal est un facteur discriminant, nous ne possédons pas d’information sur le département ou la commune.

Voyons comment éliminer simplement les établissements d’outre mer pour lesquels nous ne possédons pas de données parcellaires sur les cultures agricoles.

La liste des codes département à l’outre mer est :

971 Guadeloupe
972 Martinique
973 Guyane
974 Réunion
975 St Pierre et Miquelon
976 Mayotte
986 Wallis et Futuna
987 Polynésie Française
988 Nouvelle Calédonie

Le code postal pour la France d’outre mer commence par les 3 chiffres du code départements, on peut s’arrêter aux 2 premiers chiffres pour éliminer l’outre mer, la recherche d’un numéro 97 ou 98 en première position du code postal permet de sélectionner un enregistrement à éliminer.

Ouvrez la table d’attributs (clic droit sur la couche dans l’explorateur de couches).

Par défaut le tableau fait afficher tous les enregistrements. QGIS permet de faire une sélection d’enregistrements par filtre et d’enregistrer la sélection comme une nouvelle couche. C’est ainsi que nous allons procéder, en enregistrant la sélection de tous les enregistrements dont le code postal ne reflète pas une appartenance à la France outre mer.

Ouvrez la table d’attributs (clic droit sur la couche dans l’explorateur de couches). En bas à gauche de l’explorateur sélectionnez l’entrée déroulante Filtre avancé (Expression)

QGIS ouvre une fenêtre qui permet de construire une expression qui va permettre de filtrer les enregistrements.

Dans la liste des fonctions, sélectionnez le champ code_postal_uai dans Champs et valeurs, puis l’opérateur LIKE dans Opérateurs, puis tapez le format de valeur ‘97%’ qui signifie que l’on souhaite une valeur qui débute par les caractères ‘9’ et ‘7’ suivis de n’importes quels caractères. Vous devriez être familier du format d’expression si vous connaissez SQL, QGIS utilise un format semblable avec un jeu d’instructions SQL limité pour les expressions. Il suffit de chercher les enregistrements dont le code postal commence par 97 ou 98 et inverser la condition de recherche.

Le format complet de l’expression est :

NOT (
 "code_postal_uai"  LIKE '97%'
 OR
 "code_postal_uai"  LIKE '98%'
)
Une fois la table filtrée, sélectionnez tous les enregistrements par un clic dans l’angle gauche de la table

 

Il reste à enregistrer ces informations comme une nouvelle couche. Vous pouvez au passage vérifier sur la carte : les points sélectionnés apparaissent en jaune.

Faites un clic droit sur la couche d’établissements dans l’explorateur de couches tout en conservant la table d’attributs ouverte, sélectionnez l’entrée de menu Sauvegarder sous

QGIS vous propose de sauvegarder la couche, nous pourrions profiter de l’occasion pour transformer la couche dans une autre projection et basculer l’encodage des attributs à UTF-8 mais ne le faites pas pour le moment.

Sélectionnez le format ESRI Shapefile, le SCR de la couche RGF93 / Lambert-93, le codage ISO-8859-1 et cochez la case qui permet de n’enregistrer que les entités sélectionnées et celle qui permet d’ajouter le fichier sauvegardé à la carte. Validez puis supprimez la couche d’établissements obsolète.
Notez que QGIS limite la taille des noms d’attributs à 10 caractères lors de la sauvegarde : c’est une limitation du format Shapefile où les noms d’attributs sont limités à 10 caractères, les noms d’attribut du fichier des établissements sont donc tronqués.

 

Si vous parcourez les attributs de la couche vous pouvez constater que l’import du fichier texte ne s’est pas déroulé comme l’on pouvait s’attendre, le programme a considéré le champ code postal comme une valeur numérique et a supprimé le zéro qui préfixe les codes postaux sur quatre chiffres ! Nous allons rétablir le code postal en tant qu’attribut de type texte, complété à gauche par des caractères zéro ‘0’ sur 5 caractères.

Ouvrez les propriétés de la couche sauvegardée d’un double clic sur son nom, et sélectionnez l’onglet Champs.Cliquez sur l’icône de crayon pour basculer en mode édition : vous pouvez alors accéder à l’icône de boulier qui permet de calculer un champ.Ouvrez le calculateur de champ.

Le calculateur de champ permet de créer de nouveaux champs à partir de fonctions ou de champs existants, ou de redéfinir des champs existants. Ce qui nous importe est de redéfinir le champ de code postal afin qu’il devienne de type chaîne de caractère et soit complété à droite.

Cochez la case de création d’un nouveau champ nommé code_post de type texte et de longueur 254.
Dans la liste des fonctions vous pouvez trouver les fonctions de chaîne de caractères et plus bas les champs et valeurs, ces fonctions permettent à nouveau de construire une expression.Saisissez l’expression :
lpad("code_posta" , 5, '0')
L’expression signifie que l’on créé un nouveau champ en appliquant la fonction de chaîne de caractères complémentée à gauche sur le champ code_posta (code_postal_uai du fichier d’origine mais tronqué à 10 caractères) paramétrée pour complémenter avec des caractères ‘0’ sur une longueur de 5 caractères au total.Sélectionnez la colonne code_posta obsolète et cliquez sur l’icône de suppression à gauche du bouton de passage en édition puis désactivez le mode édition et enregistrez lorsque vous y êtes invité.

Vérifiez la modification dans la table d’attributs.

Nous allons maintenant procéder à une jointure spatiale entre deux couches de données afin de palier au manque d’informations de nos données d’établissement.

Effectuer une jointure entre 2 couches sous QGIS

Pour faire une jointure nous allons nous baser sur la position géographique des établissements et les données des fichiers IGN pour les contours administratifs des départements et communes.

Ajoutez la couche vecteur des départements récupérée lors de la préparation des données. (Faites glisser le fichier DEPARTEMENT.SHP sur la fenêtre d’application QGIS)
Rendez vous dans le menu Vecteur > Outils de gestion de données > Joindre les attributs par localisation
Paramétrez la fenêtre :

  • Indiquez une couche vecteur : indiquez la couche des établissements sur laquelle va se faire la jointure
  • Joindre la couche vecteur : indiquez la couche des données à joindre, les départements
  • Résumé de l’attribut : indiquez de prendre les attributs de la première entité au cas où plusieurs entités sont concernées par la jointure
  • Saisissez un fichier Shapefile de résultat
  • Table en sortie : lorsque les entités de la couche de départ n’ont pas de correspondance dans la couche à joindre on conserve tout de même les enregistrements

 

Après vérification dans la table attributaire, l’opération a aboutit à l’ajout des attributs de la couche département aux attributs de la couche d’établissements sauf pour une vingtaine d’établissements en bord de littoral. Cette fois recommençons l’opération en supprimant les entités de la table en sortie afin d’éliminer ces cas que nous négligerons donc parmi environ 65000 établissements.

QGIS utilise un opérateur intersection pour la jointure spatiale, je n’ai pas testé le cas où plusieurs entités seraient concernées par la jointure

Procéder à l’import

Le moment est venu d’importer les données d’établissements et départements en base. L’extension DB Manager (installée par défaut) va nous permettre de transférer ces couches vers PostGIS.

La première chose à faire avant de lancer le gestionnaire de bases de données est d’établir une connexion à la base PostGIS.

Ouvrez la fenêtre de gestion des tables PostGIS, depuis le menu
Couche > Ajouter une couche PostGIS

La fenêtre qui s’ouvre permet d’ajouter une table spatiale récupérée depuis PostGIS, c’est également à partir de cet endroit que l’on peut créer une nouvelle connexion, l’extension DB Manager ne permet pas de le faire contre toute attente.

Paramétrez une nouvelle connexion comme sur l’exemple suivant.Donnez un nom parlant à la connexion : je donne l’adresse du serveur, le type PostGIS et le nom de la base de données pour les distinguer plus tard depuis le nom.

QGIS viens d’enregistrer une nouvelle connexion à la base. Vous pouvez ensuite quitter la fenêtre d’ajout de tables PostGIS, nous reviendrons plus tard après création des tables.

Sous QGIS, ouvrez le gestionnaire de base de données :
Base de donnée > Gestionnaire de base de données > Gestionnaire de base de données

Une fois que vous avez la connexion vous pouvez charger les données d’une couche ou d’un fichier à partir du gestionnaire de base de données.

Sélectionnez la connexion dans l’explorateur, sous l’entrée PostGIS, puis cliquez sur l’icône en forme de flèche vers la gauche, elle permet de paramétrer un import.

La couche des départements est dans le système de coordonnées de référence EPSG:2154 – RGF93 / Lambert-93, l’encodage est System. (Ces informations sont visibles dans les propriétés générales de la couche). Pour importer les données nous préciserons de convertir l’encodage en UTF-8 et de passer le SCR des données en WGS 84 d’identifiant EPSG:4326.

Ce dernier point n’est pas obligatoire mais je préfère unifier les système pour faciliter les développements ultérieurs et éviter les conversions en aval entre tables de données sous PostGIS.

Paramétrez l’import de la couche des départements :

  • Saisie : vous pouvez sélectionner une couche parmi les couches du projet ouvert ou bien utiliser le bouton de navigation à droite afin de préciser le chemin vers un fichier Shapefile.
    Sélectionnez la couche DEPARTEMENT
  • Saisissez le nom de la table Departement pour la table en sortie
  • Précisez le schéma : public
  • Dans l’encart Action vous pouvez si besoin écraser la table existante
  • Précisez la clef primaire : id_geofla. Ici on réutilise l’attribut qui sert d’identifiant dans la couche, par défaut le gestionnaire créé un identifiant associé à une séquence si ce champ n’est pas renseigné
  • Précisez si vous le souhaitez la colonne de géométrie the_geom. Certaines conventions utilisent the_geom, depuis les dernières versions de PostGIS geom est utilisé comme nom de colonne par défaut
  • Précisez le SCR source : 2154
  • Précisez le SCR cible : 4326
  • Précisez le codage cible : UTF-8
  • Cochez la case afin de créer un index spatial

Une fois l’import réussi il est possible de réactualiser la liste des objets de la connexion afin de faire apparaître la table qui vient d’être crée. Les onglets du gestionnaire sur la droite permettent d’accéder aux informations sur la table, de vérifier le contenu et le rendu.

Le gestionnaire donne également accès à certaines vues, si l’on consulte la vue geometry_columns on vérifie bien la présence d’une nouvelle ligne où l’on accède aux informations sur la colonne de type Geometry que l’on a créé dans la nouvelle table. Vous pouvez à nouveau vous référer aux documents de standards sur la vue geometry_columns. La vue référence bien la colonne geom de la table Departement, de type MULTIPOLYGON et de srid 4326.

Ultérieurement j’ai rencontré des problèmes avec des requêtes spatiales sur cette table du fait que la colonne est de type MULTIPOLYGON, mais que certaines de ses entrées sont de type GeometryCollection. Une solution serait peut être de modifier dans les fichiers SQL générés les instructions de création de table. J’ai choisi par manque de temps d’ignorer (grâce à la fonction ST_IsValid()) les lignes de type GeometryCollection qui représentent un pourcentage négligeable.

Si vous observez maintenant la table sous PgAdmin vous pouvez vérifier le contenu de la colonne de géométrie sur quelques enregistrements : le contenu est au format standard WKB (Well-Known Binary), un format de représentation hexadécimal. A présent vous pouvez parcourir les fonctions spatiales de PostGIS dans l’explorateur d’objet de PgAdmin, la fonction st_astext(geometry) permet de retourner la représentation de la colonne dans le format standard WKT (Well-Known Text) que l’on utilisera ultérieurement pour les échanges d’informations entre les différents composants de l’application cartographique que l’on va mettre en place.

Un exemple de requête retournera une représentation WKT où l’on va voir apparaître des objets de type MULTIPOLYGON, le type de la colonne, avec des coordonnées en 2 dimensions (X et Y) exprimées dans le SCR de référence SRID 4326 tel que l’on peut le consulter dans la définition de la colonne dans la vue geometry_columns :

SELECT st_astext(geom) FROM "Departement" LIMIT 1;
"MULTIPOLYGON(((5.83122641362104 45.9384595782932,5.82212102507634 45.9301359535238,5.82915456688181 45.9172371235251,5.82614026745121 45.903273829068,5.81515407306268 45.8772007832312,5.80752504239684 45.8606398467483,5.80110949497983 45.8457964776072,5.79 (...)"

Procédez à l’import de la couche des établissements via DB Manager, nommez la table Etablissement et utilisez l’attribut numero_uai comme clef primaire.(Mon installation sur VPS à ressources limitées ne supporte pas le chargement de telles quantités de données au travers de la connexion au serveur de base de données. Pour les développements je travaillerai en local pour le confort d’utilisation et j’utiliserai l’utilitaire de sauvegarde et restauration pour migrer la base afin de faire fonctionner le prototype)

Attention à l’encodage des caractères… malgré les options dans DB Manager j’ai dû au préalable changer le jeu de caractères en UTF-8 en sauvegardant la couche sous un autre nom (on peut préciser le jeu de caractères lors de l’enregistrement)

Pour gagner du temps je n’ai pas supprimé d’attributs inutiles ni renommé les noms tronqués des colonnes… mais rien ne vous empêche de le faire. Lors de la sauvegarde d’une couche on peut renommer les attributs, sinon après import il suffit de passer par PgAdmin et d’effectuer un clic droit sur la table dans l’explorateur d’objets pour faire afficher les propriétés. On accède alors à la définition des colonnes dans un onglet, où l’on peut ajouter, modifier ou supprimer une colonne.

Import des données de parcelles

Traiter les parcelles une à une serait assez consommateur de temps.

Nous disposons d’une centaine de ressources sous format d’archive zip, d’en moyenne 15M, ce qui totalise environ 1,5Go.

Je fais le choix de conserver le découpage par départements plutôt que d’utiliser une table unique où l’on ajouterai les données de chaque département. Cela permettra d’interroger individuellement les tables en fonction des codes département.

Deux options se présentent alors pour interroger la France métropolitaine dans son intégralité : dans un premier temps j’utiliserai une vue, puis je couvrirai les capacités de partitionnement de tables offertes par PostgreSQL avec héritage d’une table maître pour comparer les approches.

Procéder à l’import

L’import doit se dérouler de manière automatique, on doit pour cela utiliser un script combiné à un utilitaire d’import en ligne de commande.

Pour le langage de script j’utiliserai Python qui est notamment employé sous QGIS.

Vous pouvez trouver une version embarquée sous Windows sous le répertoire d’installation de QGIS :

cd C:\Program Files\QGIS Chugiak\bin
python --version
Python 2.7.4

Pour l’utilitaire en ligne de commande j’utiliserai shp2pgsql pour la génération de fichiers SQL couplé à psql pour le chargement en base, il sont disponibles dans le répertoire d’installation de PostgreSQL :
C:\Program Files\PostgreSQL\9.3\bin

Ajouter le chemin vers ces exécutables à la variable système PATH permet de s’affranchir des chemins lorsque l’on lance une commande.

Import à l’aide de shp2pgsql

shp2pgsql convertit un fichier Shapefile en instructions SQL qui peuvent être exploitées ensuite pour alimenter une base de donnée, par psql dans notre exemple.

L’utilitaire peut être accompagné d’une interface utilisateur qui permet également le chargement (ou l’export) de plusieurs fichiers. Elle offre cependant moins de contrôle et je ne couvrirai pas son utilisation mais elle peut s’avérer pratique lorsque l’on a un nombre restreint de fichiers à importer.

Sous Windows l’utilitaire est disponible sous le répertoire d’installation de PostgreSQL :

C:\Program Files\PostgreSQL\9.3\bin\postgisgui\shp2pgsql-gui.exe

La sortie de commande de shp2pgsql peut être capturée dans un fichier SQL (ou redirigée vers un autre utilitaire sous système de type Unix)

Les paramètres de commande sont :

shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]

L’ouverture d’un fichier *.shp sous QGIS nous renseigne sur le SCR et l’encodage. Un coup d’œil aux attributs permet d’ignorer les problématiques d’encodage puisque l’on travaille avec des valeurs numériques.

Au niveau des options dans notre cas de figure :

  • -s [:] Fixe le SRID en entrée et en sortie
  • -c Créé une nouvelle table et les instructions d’insertion
  • -I Créé un index spatial sur la colonne géométrie
  • -N skip Les enregistrements avec géométries vides ne seront pas importés
Si vous ne l’avez pas déjà fait, décompressez les archives de parcelles dans un répertoire !

L’usage est alors le suivant :

(Sous Windows 7 la combinaison touche SHIFT + Clic droit sur la fenêtre d’explorateur en cours permet d’ouvrir une fenêtre de commande à cet endroit, vous pouvez sinon ouvrir un terminal à l’aide de l’utilitaire cmd)

cd C:\SIG\data\parcelles\RPG_2012_004
shp2pgsql -s 2154:4326 -c -I -N skip RPG_2012_004.shp Cultures_004 > "C:\SIG\sql\Cultures_004.sql"

Le fichier SQL généré contient dans une transaction les instructions de création de table, d’indexe et de clef primaire, ainsi que les instructions d’insertion des données. La colonne géométrie est ajoutée après création de table pour être compatible avec les anciennes versions de PostGIS. Nous pourrons plus tard altérer ces instructions SQL pour tester l’implémentation avec héritage d’une table maître.

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "cultures_004" (gid serial,
"num_ilot" varchar(12),
"cult_maj" int4);
ALTER TABLE "cultures_004" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','cultures_004','geom','4326','MULTIPOLYGON',2);
INSERT INTO "cultures_004" ("num_ilot","cult_maj",geom) VALUES ('004-190399','4',ST_Transform('01060000206A0800000100000001030000000100000015000000787AA5EC64362C41C9E53F4C6D195841A8A44E8015362C41BC7493087E1958417DAEB622F9352C4106F016707D195841151DC92599352C410DE02D509E195841F241CFA696352C41F697DD03A01958416519E2189C352C41D2DEE0DBA119584183C0CAE1B1352C411A2FDDE4A4195841E10B9369BB352C41EA73B565A6195841151DC965C5352C41840D4FB7A9195841AACFD556CD352C41F1F44A19AC1958416D348037E2352C4189B0E139AE19584182E2C71812362C4147E17AE4B0195841C56D34405F362C41627FD9EDA7195841499D80E673362C415E29CB20A4195841B515FB4B7C362C41D634EF20A01958416132557078362C41779CA2338E19584179C729FA7C362C41D1915C7E85195841174850BC86362C41E92631F87E195841F163CCDDA7362C41F54A590E73195841728A8EA487362C41627FD99D70195841787AA5EC64362C41C9E53F4C6D195841'::geometry, 4326));
...
CREATE INDEX "cultures_004_geom_gist" ON "cultures_004" USING GIST ("geom");
COMMIT;

Il reste à exécuter le script sur la base de données. Nous pouvons utiliser l’utilitaire psql.

psql --host=localhost --port=5432 --username=postgres --no-password --dbname=Pesticides --file="C:\SIG\sql\Cultures_004.sql"

Les options sont :

  • –host=HOTE nom d’hôte du serveur de la base de données ou répertoire de la socket (par défaut : socket locale)
  • –port=PORT port du serveur de la base de données (par défaut : « 5432 »)
  • –username=NOM nom d’utilisateur de la base de données
  • –no-password ne demande jamais un mot de passe
  • –dbname=NOM_BASE indique le nom de la base de données à laquelle se connecter
  • –file=FICHIER exécute les commandes du fichier, puis quitte

Vous pouvez vérifier l’import sous PgAdmin et supprimer la table en cascade.

Nous savons comment réaliser un import, voyons comment réaliser un traitement par lot.

Import automatisé, traitement par lot à partir d’un script

Un script très simple va permettre d’automatiser l’appel à l’utilitaire shp2pgsql avec les paramètres que nous avons validés précédemment.

Ajoutez si ce n’est pas déjà fait le chemin vers l’exécutable python à la variable système PATH

Enregistrez le script suivant dans un fichier d’extension .py placé sous le répertoire où se trouvent vos données, le script utilise le répertoire courant pour rechercher les fichiers Shapefile.

J’ai pris soin d’écrire du code portable mais vous devrez peut être procéder à des adaptations sous votre système d’exploitation.

#! /usr/bin/python
import os
import fnmatch

instructions = r'shp2pgsql -s 2154:4326 -c -I -N skip "%s" %s > "%s"'

currentdir = os.path.realpath(os.curdir)
sqldirectory = currentdir + os.sep + 'sql'
if not os.path.exists(sqldirectory):
    os.makedirs(sqldirectory)

for root, dirnames, filenames in os.walk(currentdir):
  for shapefilename in fnmatch.filter(filenames, '*.shp'):
      print shapefilename, '...'
      shapefilepath = root + os.sep + shapefilename
      tablename = 'Cultures_' + shapefilename[-6:-4]
      sqlfilepath = sqldirectory + os.sep + tablename + '.sql'
      command = instructions % (shapefilepath, tablename, sqlfilepath)
      os.system(command)

raw_input("Appuyez sur une la touche Entree pour quitter...")

Que fait le programme ?

Premièrement il créé une variable où l’on conserve un gabarit d’appel générique à la commande shp2pgsql, le nom du fichier shapefile, le nom de table et le fichier de destination sont des paramètres.

Ensuite il récupère le répertoire courant et créé si besoin un répertoire sql sous le répertoire courant.

Le programme parcours alors le répertoire courant et les sous répertoires à la recherche de fichiers d’extension .shp, lorsqu’il trouve un fichier il extrait le code du département et construit les paramètres (chemin vers le fichier, nom de table, chemin vers le fichier de destination) et appelle la commande ainsi formatée en exécution système.

Nos fichiers sql sont alors disponibles sous le répertoire sql dans le répertoire courant, il reste à les modifier à nos besoins et à les importer.

Ce deuxième script est très semblable au premier. Dans ce cas on recherche les fichiers d’extension .sql sous les dossiers du répertoire courant et lorsqu’ils sont trouvés la commande système d’appel à psql est lancée afin d’exécuter les instructions SQL contenues dans le fichier.

#! /usr/bin/python
import os
import fnmatch

instructions = r'psql --host=localhost --port=5432 --username=postgres --no-password --dbname=Pesticides --file="%s"'

for root, dirnames, filenames in os.walk(os.path.realpath(os.curdir)):
  for sqlfilename in fnmatch.filter(filenames, '*.sql'):
      print sqlfilename, '...'
      sqlfilepath = root + os.sep + sqlfilename
      command = instructions % (sqlfilepath)
      os.system(command)

raw_input("Appuyez sur une la touche Entree pour quitter...")

L’opération prend un certain temps, même en local, mais le chargement en base c’est bien déroulé.

A présent créons une vue qui regroupera l’information de toutes les tables, ou plutôt une vue matérialisée.

Une vue matérialisée, à la différence d’une vue, stocke à la fois la requête qui a permis la génération de la vue mais également les données du résultat de requête. La vue matérialisée est équivalente sur ce point à une table et il est possible de l’indexer de la même façon. Ce comportement diffère de la vue qui exécute la requête à chaque appel, ce qui dans notre cas se traduirait par des performances moindres, dans le sens où les indexes de chaque table ne seraient probablement pas utilisés au mieux.

L’inconvénient principal des vues matérialisées c’est qu’il faut rafraîchir la vue lorsque les tables impactées par la requêtes sont modifiées, ce qui ne pose pas problème par rapport à nos données qui sont des données destinées à la consultation uniquement, pas à la mise à jour. Par contre les données sont dupliquées, nous aurons donc environ 1,5Go de données en double.

Lors de la création des instruction SQL, shp2pgsql créé une colonne gid qui sert de clef primaire. Cette colonne est construite sur la base d’une séquence à partir de 1, incrémentée de 1, ce qui est problématique pour lier plusieurs tables dans une vue : nous ne devons pas utiliser l’attribut gid dans la vue, ce qui créerait des doublons de clef, mais préciser lors du chargement d’une couche sous QGIS que l’attribut de référence pour la clef est le numéro d’îlot num_ilot

Nous devons aboutir à une instruction SQL comme celle ci :

CREATE MATERIALIZED VIEW mv_cultures AS
SELECT num_ilot, cult_maj, geom FROM "cultures_01" UNION ALL
SELECT num_ilot, cult_maj, geom FROM "cultures_02" UNION ALL
(...)
SELECT num_ilot, cult_maj, geom FROM "cultures_95"

Nous possédons tous les codes département dans la table Departement, pour générer la vue il va falloir créer la requête par une instruction SQL qui récupère tous les codes département sauf les Hauts de seine et Paris pour lesquels il n’existe pas de table, puis exécuter la requête :

SELECT
 'CREATE MATERIALIZED VIEW mv_cultures AS ' ||
 string_agg(format('SELECT num_ilot, cult_maj, geom FROM "cultures_%s"', lower("code_dept")), ' UNION ALL ')
FROM
 "Departement"
WHERE
 code_dept NOT IN ('75', '92');

Vous pouvez enregistrer la requête dans un fichier et passer par psql pour générer le résultat et vérifier avant exécution.

psql --host=localhost --port=5432 --username=postgres --no-password --dbname=Pesticides --file="C:\SIG\sql\createquery.sql" --quiet --output="C:\SIG\sql\query.sql" --log-file="C:\SIG\sql\createquery.log"
psql --host=localhost --port=5432 --username=postgres --no-password --dbname=Pesticides --file="C:\SIG\sql\query.sql"

Procédez de même avec une instruction CREATE VIEW et une instruction CREATE TABLE afin de comparer les plans d’exécutions de requêtes de type SELECT.N’oubliez pas de créer clefs primaires et indexes sur la vue matérialisée et la table…

CREATE INDEX t_cultures_geom_gist ON t_cultures USING gist (geom);
ALTER TABLE t_cultures ADD PRIMARY KEY (num_ilot);

CREATE INDEX mv_cultures_geom_gist ON mv_cultures USING gist (geom);
CREATE UNIQUE INDEX ON mv_cultures (num_ilot);
Partitionnement de la table

Une autre option qui s’offre à nous est de créer une table logique à partir de plusieurs tables physiques plus petites.

PostgreSQL supporte une forme simple de partitionnement de table. Je vais l’essayer sur ce cas de figure pour le découpage départemental des données et comparer le plan d’exécution avec les autres approches.

Le fichier de configuration de PostgreSQL permet de contrôler de quelle façon le planificateur de requêtes utilise les contraintes de table pour optimiser les requêtes.

Pour modifier le paramètre d’exclusions de contraintes, il suffit d’éditer le fichier postgresql.conf (sous C:\Program Files\PostgreSQL\9.3\data) :

constraint_exclusion = partition	# on, off, or partition

Le paramètre par défaut est partition, il pourra être intéressant de modifier le paramètre pour vérifier l’impact sur les plans d’exécution.

Procédons à la création de la table logique qui sera la table maître dont les autres tables vont hériter. La table ne possède pas d’indexe, et par rapport à la définition des tables cultures, elle possède désormais un code département.

CREATE TABLE master_cultures
(
  num_ilot character varying(12),
  cult_maj integer,
  code_dept character varying(2),
  geom geometry(MultiPolygon,4326)
)
;

Il reste ensuite à créer chaque table fille avec les instructions d’héritage appropriées. Les requêtes sont construites à la volée comme précédemment, le code n’est pas très élégant mais sera à usage unique !

SELECT
 string_agg(
	 format('DROP TABLE IF EXISTS child_cultures_%s;CREATE TABLE child_cultures_%s AS SELECT num_ilot, cult_maj, (SELECT CODE_DEPT from "Departement" WHERE code_dept = ''%s''), geom FROM "cultures_%s";ALTER TABLE child_cultures_%s ADD CONSTRAINT CK_%s CHECK (code_dept = ''%s'');ALTER TABLE child_cultures_%s ADD CONSTRAINT PK_%s PRIMARY KEY (num_ilot);CREATE INDEX child_cultures_%s_geom_gist ON child_cultures_%s USING gist (geom);ALTER TABLE child_cultures_%s INHERIT master_cultures;'
	 , lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept")
	 , lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept"), lower("code_dept")
 ), ' ; ')
FROM
 "Departement"
WHERE
  code_dept NOT IN ('75', '92');

Ces données ne sont pas destinées à être mises à jour, il n’est pas nécessaire de contrôler les opérations de mise à jour sur la table maître par des procédures stockées comme dans la documentation. (La table ne possède d’ailleurs pas de clef primaire ni d’OIDS, l’accès en édition sera interdit sous PgAdmin)

Tout ça pour ça… Et maintenant ? Contrôlons la validité de chaque vue et chaque table créées pour un usage en tant que couche vectorielle.

Contrôle du chargement des couches PostGIS sous QGIS

Afin de vérifier le chargement d’une couche PostGIS à partir de la vue, de la vue matérialisée ou de la table il faut au préalable définir une emprise pour le rendu qui soit suffisamment restreinte pour ne pas charger toutes les données depuis PostGIS sans quoi il va falloir être à nouveau très patients !

Depuis la couche GEO_FLA des communes j’ai effectué sous QGIS un zoom sur Montélimar.

Cela se fait simplement depuis la table d’attributs en sélectionnant une ligne puis en cliquant sur l’icône de zoom sur la ligne sélectionnée.

La barre de statuts en bas permet d’afficher l’emprise (icône souris à côté des coordonnées), elle s’exprime sous la forme xmin,ymin : xmax,ymax où les coordonnées sont exprimées dans le SCR du projet :

xmin,ymin : xmax,ymax EPSG:4326
4.689,44.509 : 4.803,44.601
Sous QGIS, zoomez sur une commune pour restreindre les limites d’affichage puis allez dans le menu :
Couche > Ajouter une couche PostGISSous la connexion à la base vous devez apercevoir les vues et table nouvellement crées.

Avant d’ajouter une vue, vous devez préciser à QGIS la clef primaire, le numéro d’îlot, sans quoi QGIS retournera une erreur d’invalidité de la couche.

L’ajout de la couche à partir de la table ou de la vue matérialisée est presque instantané, alors que pour la vue… eh bien, vous vous féliciterez peut être d’avoir investi dans un disque SSD dernier cri.

A l’œil nu, le rafraîchissement des polygones sur la carte prend sensiblement le même temps pour la table et la vue matérialisée lorsque l’on agrandit l’étendue représentée, il est à nouveau beaucoup plus lent en ce qui concerne la vue.

A partir de la table maître et des tables héritées l’ajout d’une couche n’est pas instantané, une douzaine de secondes sont nécessaires pour initialiser le rendu par contre le rafraîchissement est ensuite beaucoup plus réactif que pour la table et la vue matérialisée lorsque l’on représente une plus grande étendue.

Cherchons à savoir pourquoi les performances sont mauvaises avec une simple vue, quelles optimisations apporter aux autres cas et quel choix faire pour notre application.

Bilan à l’heure du choix. Explication du plan d’exécution

PostgreSQL permet de visualiser les plans d’exécution de chaque requête SQL, l’instruction EXPLAIN retourne des informations pertinentes sur le plan d’exécution qu’il va utiliser.

Pour tester les plans d’exécution dans chaque configuration je vais construire une requête spatiale afin de simplement retourner toutes les parcelles sur une étendue géographique donnée.

En effet, ce sera le mode de fonctionnement de l’application web, il importe de le tester : les librairies javascript en charge du rendu des cartes vont faire appel aux informations sur l’étendue de la carte en cours, avec possibilité d’agrandir ou rétrécir l’étendue représentée.

Lorsque l’étendue sera élevée la couche des parcelles ne sera simplement pas affichée, car elle n’apporte pas d’information particulière dans un contexte de rendu global, par contre elle sera visible dès que l’échelle de la carte sera suffisamment élevée.

Sous PostGIS, la fonction ST_MakeEnvelope créé un polygone rectangulaire à partir de coordonnées X, Y et d’un SRID.

Le format est le suivant :

geometry ST_MakeEnvelope(double precision xmin, double precision ymin, double precision xmax, double precision ymax, integer srid=unknown);

Pour la commune de CAHUZAC située dans le département de code 47, le polygone recouvrant l’étendue géographique sera donc obtenu par :

SELECT ST_AsText(ST_MakeEnvelope(0.533, 44.639, 0.589, 44.685, 4326));

L’option ANALYSE permettra de lancer l’exécution de la requête afin de disposer des temps d’exécution et nombre de lignes réels.

L’étendue géographique sera celle de l’Auvergne :

EXPLAIN ANALYSE SELECT ST_AsText(geom) FROM t_cultures WHERE geom && ST_MakeEnvelope(2, 45 , 5, 47, 4326);
EXPLAIN ANALYSE SELECT ST_AsText(geom) FROM mv_cultures WHERE geom && ST_MakeEnvelope(2, 45 , 5, 47, 4326);
EXPLAIN ANALYSE SELECT ST_AsText(geom) FROM v_cultures WHERE geom && ST_MakeEnvelope(2, 45 , 5, 47, 4326);
EXPLAIN ANALYSE SELECT ST_AsText(geom) FROM master_cultures WHERE geom && ST_MakeEnvelope(2, 45 , 5, 47, 4326);

Les résultats sur cette requête me laissent perplexe car ils semblent contradictoires avec le ressenti sous QGIS.

La méthode est certainement sujette à un problème, si quelqu’un peut m’éclairer les remarques seront bienvenues pour construire des requêtes de test plus pertinentes !

Ce article est assez long, et je m’égare. La prochaine étape sera d’installer un serveur cartographique. L’héritage de table donne des résultats corrects même si la vue semble meilleure, par rapport au ressenti sous QGIS c’est la solution que je vais retenir par défaut, je conserve toutefois la possibilité de basculer sur les autres solutions.

Migration d’une base de données Oracle vers PostgreSQL

L’objectif de cet article est de documenter la procédure pour migrer une base de données simple d’Oracle vers PostgreSQL.

A propos des bases de données

La base de données de destination est une installation locale de PostgreSQL 9.3.1 sur poste Windows.

La version de la base Oracle sera Oracle Database 10g Express Edition Release 10 installée sur le même poste de travail. Nous utiliserons le schéma de test HR qui peut être installé à partir de l’environnement de développement Oracle Application Express (APEX) qui est fourni en standard sur les éditions Oracle et permet de gérer les bases de données depuis une interface web.

L’interface est accessible par défaut sur http://localhost:8080/apex, nous utiliserons le compte utilisateur sys pour se connecter avec des privilèges administrateur.

L’application APEX permet d’accéder rapidement aux informations sur le support des langues nationales
Page d’accueil > Administration > A propos de la base de données

La base de donnée exemple HR

Par défaut vous pouvez trouver un schéma d’exemple qui sert à certains tutoriels Oracle et aussi d’environnement assez complet pour tester des fonctionnalités. Un utilisateur nommé HR (acronyme pour Human Resources) est associé à la base, il faut cependant déverrouiller le schéma HR pour pouvoir l’exploiter.

Cela peut se faire depuis l’interface APEX ou au travers d’une commande SQL (n. Où password est le mot de passe utilisateur HR, mémorisez le) :

ALTER USER HR IDENTIFIED BY password ACCOUNT UNLOCK;

La base HR sur notre environnement est assez complète pour procéder à un test de migration simple dans le sens où elle comporte quelques tables reliées entre elles par des relations de clefs étrangères, des clefs primaires sur différents types de données, des séquences, un déclencheurs, deux procédures et une vue.

Si vous ne possédez pas le schéma HR vous pouvez l’installer manuellement en exécutant le fichier de démonstration sous votre répertoire ORACLE_HOME
C:\oraclexe\app\oracle\product\10.2.0\server\demo\schema\human_resources\hr_main.sql

Pour les opérations sur la base Oracle nous utiliserons le logiciel Oracle SQL Developer (version 3.0.02), plus souple que l’environnement APEX.

A ce stade nous avons une connexion au schéma HR paramétrée. Pour référence, l’arborescence des objets sous SQL Developer :

Migration de la base de données

Afin de rejouer le scénario de migration il peut être intéressant de travailler sur une copie de la base Oracle, sur laquelle on peut procéder en amont à certaines opérations par exemple afin de réduire la volumétrie.

Voyons comment exporter une base.

Oracle SQL Developer possède des fonctionnalités d’import et d’export de base de données (nommées par les termes charger et décharger sous certaines versions du logiciel).

J’ai de mauvaises expériences de ces outils qui ne fonctionnent que rarement du premier coup, voir pas du tout. Je suppose que le problème vient d’une mauvaise prise en charge des version d’Oracle plus anciennes que les versions de SQL Developer utilisées pour l’export, de ce fait je vais m’orienter vers les utilitaires d’import et d’export inclus à Oracle XE, cela doit permettre d’éviter les problèmes de compatibilité de versions.

Pour tester l’import de fichiers au format SQL pour les instructions de construction de schéma et au format SQL Loader pour les données (*.ctl et *.ldr) il peut être nécessaire de supprimer le schéma au préalable pour repartir sur une base vierge. Tous les objets de base de données appartiennent à un utilisateur, que vous pouvez supprimer.

Vous devez être connecté avec un compte utilisateur qui possède suffisamment de privilèges, et vous déconnecter avec l’utilisateur à supprimer. Vous pouvez procéder au travers de l’interface APEX ou bien en ligne de commande.

Avant import vous pouvez (re)créer un utilisateur avec les privilèges nécessaires à la création des objets.

DROP USER HRMIG CASCADE;
CREATE TABLESPACE THRMIG DATAFILE 'C:/oraclexe/oradata/XE/thrmig.dbf' SIZE 200M;
CREATE USER HRMIG IDENTIFIED BY HRMIG DEFAULT TABLESPACE THRMIG QUOTA UNLIMITED ON THRMIG;
GRANT CONNECT, RESOURCE, DBA TO HRMIG;
GRANT ALL PRIVILEGES TO HRMIG;

Les utilitaires en ligne de commande sont une solution fiable pour gérer les exports et les imports. Ils sont inclus avec la base. Par défaut sous Windows ils sont sous le répertoire
C:\oraclexe\app\oracle\product\10.2.0\server\BIN

La première étape est de définir un objet répertoire sous Oracle auquel les utilitaires en ligne de commande pourront accéder.
L’objet répertoire pointe vers un répertoire physique (C:/Temp/ dans l’exemple), il faut ensuite accorder des droits aux utilisateurs afin de pouvoir accéder en lecture et écriture sur ce répertoire.

CREATE OR REPLACE DIRECTORY hrdir AS 'C:/Temp/';
GRANT READ, WRITE ON DIRECTORY hrdir TO hr;

J’ai accordé des droits à l’utilisateur HR, car il va utiliser le répertoire en écriture et lecture. Seul cet utilisateur est nécessaire car l’outil d’import en ligne de commande permet de ré-allouer le schema à un autre utilisateur.

cd C:\oraclexe\app\oracle\product\10.2.0\server\BIN

Exporte le schéma HR vers un fichier de sauvegarde

expdp HR/HR directory=hrdir schemas=HR dumpfile=hr.dmp logfile=hr.log

Importe le fichier de sauvegarde avec ré-attribution du schéma de HR vers HRMIG et du tablespace SYSTEM vers THRMIG

impdp hrmig/hrmig directory=hrdir remap_schema=hr:hrmig remap_tablespace=system:thrmig dumpfile=hr.dmp logfile=hrmig.log

Cette solution est fiable comparée aux exports sous SQL Developer, si l’on respecte toutefois un principe : d’après la documentation, les exports seraient compatibles à partir du moment où l’on tente un import sur une base de version équivalente ou supérieure.

Pensez à transférer le fichier de log en même temps que le fichier d’export, il contient des informations utiles !

L’outil de migration Ora2Pg

Nous possédons une base de test pour évaluer les possibilités de migration d’Oracle vers PostgreSQL.

L’outil que l’on va utiliser est un utilitaire écrit en langage Perl, il s’agit de Ora2Pg.

C’est un outil gratuit qui se connecte à une base de données Oracle, parcours automatiquement la base et extrait structure et données. Il génère alors des scripts SQL que vous pouvez charger dans PostgreSQL.

Installation

Téléchargez et décompressez l’archive

Vous devez disposer d’une version de Perl supérieure à 5.6

perl --version
This is perl 5, version 12, subversion 3 (v5.12.3) built for (...)

Sous Windows vous pouvez installer l’édition communautaire d’ActivePerl, distribuée par ActiveState, de nombreux modules sont compilés dont les modules dont nous avons besoin.

L’installation de la distribution ajoute le chemin vers l’exécutable Perl à la variable d’environnement PATH, et elle inclus le gestionnaire de paquets ppm qui permettra d’installer les librairies requises : l’installation requiert des modules Perl qui à leur tour demandent d’avoir les librairies clients Oracle installées.

J’ai une installation d’Oracle XE je n’ai pas à me préoccuper des librairies clientes cependant l’occasion est bonne pour ajouter des variables d’environnement au système.

Vous aurez besoin de la variable d’environnement ORACLE_HOME.

J’ajoute le répertoire des exécutables Oracle XE à la variable PATH

C:\oraclexe\app\oracle\product\10.2.0\server\BIN

Je créé la variable ORACLE_HOME et la variable LD_LIBRARY_PATH

C:\oraclexe\app\oracle\product\10.2.0\server
C:\oraclexe\app\oracle\product\10.2.0\server\LIB
Ouvrez le gestionnaire de paquets Perl ppm listez les modules installés et si besoin installez les modules suivants :
ppm list

ppm install DBI
ppm install DBD-Pg
ppm install DBD-Oracle
ppm install Time-HiRes
ppm install Compress-Raw-Zlib
Procédez alors à l’installation du module Ora2pg
ppm install dmake

cd "C:\Program Files\ora2pg"
perl Makefile.PL
dmake && dmake install

Le programme d’installation ous informe que le fichier de configuration est placé sous C:\ora2pg\ora2pg_dist.conf

Configuration et exécution

L’utilitaire peut s’utiliser avec des paramètres en ligne de commande, par exemple pour préciser un fichier de configuration ou avoir plus de granularité sur les les éléments à exporter. Dans la suite du document on utilisera uniquement le fonctionnement par fichier de configuration…
Ouvrez le fichier de configuration C:\ora2pg\ora2pg_dist.conf et enregistrez le sous ora2pg.conf

Nous pouvons demander à l’utilitaire d’exporter directement le schéma Oracle vers un schéma PostgreSQL, dans ce cas il faut paramétrer les directives PG_DSN, PG_USER, PG_PWD et EXPORT_SCHEMA

Les options sont nombreuses, consultez toutes les directives à partir de la documentation.

Au plus simple il suffit de paramétrer les directives pour l’accès à la base Oracle, les directives pour la sortie sous forme de fichier et la directive SCHEMA qui permet de restreindre l’export à ce schéma (Attention sur ce dernier point !).

La directive type permet d’indiquer quels types d’objets exporter, elle est également indispensable.

Modifiez les directives de connexion à Oracle
ORACLE_DSN	dbi:Oracle:host=localhost;sid=xe
ORACLE_USER	HRMIG
ORACLE_PWD	HRMIG

ATTENTION. L’utilisateur HRMIG possède ici un profil avec des droits étendus qui lui permettent d’accéder aux objets système Oracle.

Modifiez les directives qui permettent de préciser les fichiers en sortie, afin d’examiner au mieux les fichiers SQL produits nous demanderons au programme d’enregistrer un fichier par table.
OUTPUT		migration.sql
OUTPUT_DIR	C:/Temp
FILE_PER_TABLE	1
La directive SCHEMA permet de préciser que l’on souhaite uniquement le schéma HR, on souhaite exporter ce schéma vers un schéma PostgreSQL.
La directive TYPE permet de renseigner le type d’export souhaité : ici on souhaite la structure des tables et les données, les séquences, déclencheurs, procédures stockées, et les vues.
EXPORT_SCHEMA	1
SCHEMA		HRMIG
TYPE		TABLE SEQUENCE VIEW PROCEDURE TRIGGER INSERT
ATTENTION ! Le script produira les instructions SQL dans l’ordre que vous spécifiez dans la directive TYPE.

Dans notre exemple j’ai mis les tables en premier car c’est à ce moment que sont produites les instructions de création de schéma.
Vous devrez éditer le fichier SQL généré pour :

  • Contrôler et adapter les codes PL/SQL des procédures et déclencheur transcrits partiellement en PL/pgSQL
  • Contrôler l’ordre d’appel aux instructions INSERT de chaque table pour respecter les références de clef étrangères (ou bien désactiver la création des clefs étrangères avant insertion qui est l’option que j’ai choisie et exécuter ce code plus tard)
  • Contrôler l’ordre de création des fonctions, procédures et déclencheur

Le fichier est configuré par défaut, vous pouvez modifier les autres directives si vous le souhaitez, plus particulièrement NLS_LANG et NLS_NCHAR qui permettent de forcer les paramètres d’encodage Oracle au lieu de considérer les paramètres détectés.

Il est possible de demander à l’utilitaire les informations sur l’encodage de la base source.

ora2pg -t SHOW_ENCODING
        Using character set: NLS_LANG=AMERICAN_AMERICA.AL32UTF8, NLS_NCHAR=AL32U
TF8.
        Using Perl output encoding :utf8.
        Using PostgreSQL client encoding UTF8.
Trying to connect to database: dbi:Oracle:host=localhost;sid=xe
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Showing Oracle encoding...
NLS_LANG AMERICAN_AMERICA.AL32UTF8
CLIENT_ENCODING UTF8

Par défaut l’encodage client sous PostgreSQL est fixé automatiquement à UTF8 pour éviter les problèmes d’encodage.

Il ne reste qu’à exécuter l’utilitaire et contrôler le processus, pour cela il peut être utile d’afficher les traces (Basculer la directive DEBUG à 1)

Lancer l’exécution de la migration, l’utilitaire est sous un répertoire d’exécutables de Perl, C:\Perl\site\bin\ora2pg.bat avec l’installation par défaut de la distribution ActivePerlSoyez patient… l’opération peut être longue.
C:\Perl\site\bin\ora2pg.bat --quiet --log "C:/Temp/migration.log"

L’exécution nous averti d’une erreur que l’analyse du fichier de log nous permet d’ignorer : le programme tente d’importer plusieurs fois des tables, et ignore la seconde tentative.

Par contre si vous regardez attentivement les fichiers générés, vous verrez l’absence de la table countries !

Que ce passe-t-il ?

Pour tenter de le découvrir nous pouvons vérifier sous Oracle les tables système pour vérifier les informations disponibles sur cette table.

La première tentative est de vérifier le propriétaire de chaque objet et la validité, en effet seuls les objets qui ont pour propriétaire HRMIG sont exportés et par défaut Ora2pg ne prend pas les objets invalides. Si vous avez des procédures stockées, des fonctions ou des déclencheurs vérifiez bien qu’ils soient compilés !

SELECT DISTINCT OBJECT_NAME, OWNER FROM ALL_OBJECTS WHERE OWNER = 'HRMIG' AND STATUS='VALID';
COUNTRIES	HRMIG	VALID

L’objet existe, HRMIG est bien le propriétaire et l’objet est valide… la seconde tentative est de vérifier les propriétés de la table.

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, IOT_TYPE FROM DBA_TABLES WHERE OWNER = 'HRMIG';
HRMIG	REGIONS	        THRMIG    (null)
HRMIG	LOCATIONS	THRMIG    (null)
HRMIG	DEPARTMENTS	THRMIG    (null)
HRMIG	JOBS	        THRMIG    (null)
HRMIG	EMPLOYEES	THRMIG    (null)
HRMIG	JOB_HISTORY	THRMIG    (null)
HRMIG	COUNTRIES	(null)    IOT

Le problème se précise. La table n’appartient pas au TABLESPACE THRMIG et c’est une table IOT (index organized tables)

En tant que table IOT elle n’est pas enregistrée directement dans la table système Oracle DBA_SEGMENT mais emploie un segment indexé nommé SYS_IOT_TOP_{TABLE_OBJECT_ID}

Qu’est ce que cela implique ? Pourquoi Ora2pg n’a pas connaissance de cette table ?

Pour obtenir une réponse à ces questions il faut entrer en détail dans le code de l’utilitaire. Comme il s’agit d’un script Perl c’est assez facile, il suffit d’éditer le fichier Ora2Pg.pm !

Faites une copie de sauvegarde du fichier
C:\Perl\site\lib\Ora2Pg.pm
Basculez le mode lecture seule puis éditez le fichier

Le script utilise le module Perl DBI pour les accès à la base de données, cela rend facile le débogage des requêtes SQL utilisées par le module Ora2Pg.pm. Il suffit de paramétrer l’objet de connexion pour rediriger les traces vers un fichier de log.

sub _oracle_connection
{
	my ($self, $quiet) = @_;

	$self->logit("Trying to connect to database: $self->{oracle_dsn}\n", 1) if (!$quiet);

	my $dbh = DBI->connect($self->{oracle_dsn}, $self->{oracle_user}, $self->{oracle_pwd}, {ora_envhp => 0, LongReadLen=>$self->{longreadlen}, LongTruncOk=>$self->{longtruncok} });

	# Fix a problem when exporting type LONG and LOB
	$dbh->{'LongReadLen'} = $self->{longreadlen};
	$dbh->{'LongTruncOk'} = $self->{longtruncok};

	# Check for connection failure
	if (!$dbh) {
		$self->logit("FATAL: $DBI::err ... $DBI::errstr\n", 0, 1);
	}

	# Use consistent reads for concurrent dumping...
	$dbh->begin_work || $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	if ($self->{debug} && !$quiet) {
		$self->logit("Isolation level: $self->{transaction}\n", 1);
	}
	my $sth = $dbh->prepare($self->{transaction}) or $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	$sth->execute or $self->logit("FATAL: " . $dbh->errstr . "\n", 0, 1);
	$sth->finish;
	undef $sth;

       $dbh->trace($dbh->parse_trace_flags('SQL|2|test'), 'C:\Temp\traces.txt');
(...)

La ligne suivante a été ajoutée après obtention de la connexion à Oracle :

       $dbh->trace($dbh->parse_trace_flags('SQL|2|test'), 'C:\Temp\traces.txt');

La méthode trace() se voit passer un paramètre SQL qui précise d’enregistrer les déclarations SQL dans le fichier de log. Le niveau 2 indique d’enregistrer les paramètres et on passe également le chemin vers le fichier de destination.

Alors que fait Ora2pg ?

SELECT A.TABLE_NAME,
  A.COMMENTS,
  A.TABLE_TYPE
FROM ALL_TAB_COMMENTS A
JOIN DBA_SEGMENTS S
  ON (S.SEGMENT_TYPE LIKE 'TABLE%'
  AND S.SEGMENT_NAME=A.TABLE_NAME)
WHERE A.OWNER     ='HR'
  AND ( NOT REGEXP_LIKE(TABLE_NAME,'^USLOG\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MLOG\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^RUPD\$_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDXT_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDRT_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^MDRS_.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^DR\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^CLI_SWP\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^LOGMNR\$.*$')
  AND NOT REGEXP_LIKE(TABLE_NAME,'^REPCAT\$.*$'))
  AND (A.OWNER, A.TABLE_NAME) NOT IN
    (SELECT OWNER, MVIEW_NAME FROM ALL_MVIEWS
     UNION ALL
     SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS
    )

L’origine du problème est là : il fait une jointure sur la table système DBA_SEGMENTS qui l’empêche d’avoir connaissance des tables IOT, et pas seulement, il va aussi ignorer les tables externes et toutes les tables qui n’apparaissent pas dans cette table système.

On peut se rendre compte que la table DBA_SEGMENTS ne référence pas toutes les tables à l’aide d’une simple requête SQL

SELECT OWNER, TABLE_NAME, CLUSTER_NAME, PARTITIONED, TEMPORARY, IOT_TYPE
  FROM DBA_TABLES
  WHERE (OWNER, TABLE_NAME) NOT IN
          (SELECT OWNER, SEGMENT_NAME FROM DBA_SEGMENTS
          WHERE SEGMENT_TYPE='TABLE')

Le problème est une limitation d’Ora2pg qu’il faut contourner.

Comment faire ? J’ai tenté sans succès une approche pour changer le mode d’indexation de la table COUNTRIES, et au final ma seule solution est de dupliquer la table, supprimer l’original et renommer la copie.

Commençons par dupliquer la table et recréer des contraintes de clef primaire et clef étrangère. Cette nouvelle table est par défaut sous organisation HEAP avec des lignes stockées sans ordre particulier.

CREATE TABLE HRMIG.COUNTRIES_COPY AS SELECT * FROM HRMIG.COUNTRIES;
ALTER TABLE HRMIG.COUNTRIES_COPY ADD CONSTRAINT "C_ID_PK" PRIMARY KEY ("COUNTRY_ID");
ALTER TABLE HRMIG.COUNTRIES_COPY ADD CONSTRAINT "C_REG_FK" FOREIGN KEY ("REGION_ID")
	  REFERENCES "HRMIG"."REGIONS" ("REGION_ID");

Nous pouvons alors supprimer l’original en précisant à Oracle de supprimer les informations indexées dans le segment (PURGE), et renommer la copie.

DROP TABLE "HRMIG"."COUNTRIES" CASCADE CONSTRAINTS PURGE;
ALTER TABLE "HRMIG"."COUNTRIES_COPY" RENAME TO COUNTRIES;

Finalement il reste à recompiler la vue qui exploite la table sans quoi elle est en état invalide et ne sera pas prise en compte par Ora2pg !

ALTER VIEW "HRMIG"."EMP_DETAILS_VIEW" COMPILE;
Relancez la migration…

Vous pouvez si la migration semble complète importer les fichiers SQL créés sous PostregSQL via l’utilitaire psql

Vous devez éditer le fichier de migration principal et adapter ce fichier. J’ai procédé en plusieurs étapes, d’abord la création des tables et séquences, et l’insertion des données en base, puis la création des contraintes de clefs étrangères, puis la réécriture du code PL/pgSQL et la création des procédures et déclencheurs.

Import des résultats sous PostgreSQL

Attention à la casse de caractères ! (PostgreSQL requiert d’entourer les identifiants par des guillemets lorsque l’on veut accéder à des objets enregistrés avec des majuscules)

Avant tout, créons un utilisateur !

createuser --host=localhost --port=5432 --username=postgres --no-superuser --no-createrole --no-createdb hrmig
psql --host=localhost --port=5432 --username=postgres --no-password
ALTER ROLE hrmig SET search_path TO hrmig,public;
ALTER ROLE hrmig WITH PASSWORD 'hrmig';

Créons une base de données où exécuter les commandes

CREATE DATABASE hrmig
  WITH ENCODING='UTF8'
       OWNER=hrmig
       CONNECTION LIMIT=-1;

Créons un tablespace

CREATE TABLESPACE thrmig
  OWNER hrmig
  LOCATION 'C:\Program Files\PostgreSQL\9.3\data\base';
Tentez d’importez le fichier !
cd C:\Temp
psql --host=localhost --port=5432 --username=hrmig --dbname=hrmig --file="C:\Temp\migration.sql" --quiet --log-file="C:\Temp\import.log"

Résultats

En procédant par étape, le schéma est d’abord créé correctement avec tables et vue, et les données sont insérées. Les séquences sont crées avec le dernier numéro adéquat. Puis les contraintes de table que l’on a déplacées sous un script à exécuter post insertion sont correctement appliquées.

Il reste toutefois à migrer procédure et trigger.

Sous Oracle la procédure est définie ainsi

create or replace PROCEDURE         "ADD_JOB_HISTORY" 
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type 
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

Le script Ora2pg génère un squelette de procédure PL/pgSQL qu’il nous faut adapter

CREATE OR REPLACE FUNCTION HRMIG.add_job_history (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type 
   )
 RETURNS VOID AS $body$
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;
 
$body$
LANGUAGE PLPGSQL;

En fait il ne reste que très peu de travail sur une fonction aussi simple pour obtenir un résultat qui compile

CREATE OR REPLACE FUNCTION hrmig.add_job_history (  
  p_emp_id          job_history.employee_id%type, 
  p_start_date      job_history.start_date%type, 
  p_end_date        job_history.end_date%type, 
  p_job_id          job_history.job_id%type, 
  p_department_id   job_history.department_id%type 
)
 RETURNS VOID AS $body$
BEGIN
  INSERT INTO hrmig.job_history (employee_id, start_date, end_date, 
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END;
$body$
LANGUAGE PLPGSQL;

La fonction prend des paramètres qui servent à insérer une nouvelle ligne dans une table.

Par rapport au code généré par Ora2pg il a fallu :

  • Supprimer l’étiquette add_job_history de l’instruction END add_job_history;
  • Mettre en minuscule hrmig dans la déclaration de fonction CREATE OR REPLACE FUNCTION hrmig.add_job_history
  • Préciser la table dans laquelle on insère : INSERT INTO hrmig.job_history

Notez que les paramètres sont convertis selon leur type lors de la création.

C’est évidemment un exemple très très simple, parfait pour commencer. Vous pouvez trouver dans la documentation PostgreSQL des instructions de portage de PL/SQL vers PL/pgSQL.

Le portage de l’autre procédure est aussi simple (elle compile en tout cas, mon objectif n’est pas de vérifier les aspects fonctionnels)

CREATE OR REPLACE FUNCTION hrmig.secure_dml () 
RETURNS VOID AS $body$
BEGIN
  IF TO_CHAR (LOCALTIMESTAMP, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (LOCALTIMESTAMP, 'DY') IN ('SAT', 'SUN') THEN
	RAISE EXCEPTION 'You may only make changes during normal office hours';
  END IF;
END;
$body$
LANGUAGE PLPGSQL;

Le portage du déclencheur a nécessité l’emploi du mot clef PERFORM pour exécuter la fonction crée précédemment. Le fonctionnement n’est pas vérifié mais la création est valide.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION hrmig.trigger_fct_update_job_history () 
RETURNS trigger AS $BODY$
BEGIN
  PERFORM hrmig.add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP, 
                  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history
	AFTER UPDATE ON employees FOR EACH ROW
	EXECUTE PROCEDURE hrmig.trigger_fct_update_job_history();

Conclusion

Nous avons vu comment utiliser le script Ora2pg sur le schéma de démonstration HR pour tester les possibilités de migration d’Oracle vers PostgreSQL.

La migration qui semblait à priori simple nous a tout de même permis de nous rendre compte des limites du script et du travail à faire soit en aval soit en amont, d’abord par rapport aux tables organisées en indexe et aux tables externes d’Oracle, puis par rapport au code PL/pgSQL.

L’outil semble posséder des limitations mais il est déjà très complet et fiable. Il est exploitable sur des cas de migration simple (donc sur des projets où le coût d’une licence Oracle ne se justifie pas), je l’ai par ailleurs depuis utilisé avec succès sur deux projets en environnement professionnel.

 

Je partages volontiers savoir faire et expériences et au delà du temps passé, cela représente chaque année quelques frais d’hébergement auxquels vous pouvez contribuer si vous appréciez les services rendus. Merci !