Récupérer des données d'une base MSSQL dans une base postgreSQL

Nous avons vu, dans les posts précédents, comment charger des fichiers plats dans une base PosgreSQL, comment accéder a une base Oracle a partir d’une base PostgreSQL.
Nous allons voir maintenant comment connecter notre cluster PostgreSQL sous Linux à notre base MSSQL favorite.

•    Pré Requis

Pour installer ce data wrapper, on a besoin d’un produit tiers, comme pour Oracle, qui va fournir les librairies nécessaires à la connexion avec la base MSSQL.
Dans notre cas, on va utiliser Freetds.
Cet outil joue le rôle de l’InstantClient pour Oracle. Il permet de se connecter à la base MSSQL à partir du serveur Linux.
Pour récupérer Freetds, rendez-vous à l’adresse http://www.freetds.org/software.html et downloader la dernière version stable du produit.
Déposez-la sur votre serveur puis lancez l’installation.
Sous root, mettre en place les fichiers avec la commande tar et positionnez-vous dans le répertoire ainsi créé.

cd /home/postgres/freetds/
tar xvf  freetds-patched.tar
cd freetds-1.00.48

Lancez les commandes suivantes :

./configure
make
make install

Une fois fait, on va installer le data wrapper tds.

•    Installation du data wrapper

Positionnez l’environnement postgreSQL désiré dans la variable PATH

#PATH=$PGHOME:$PATH

Rendez-vous à l’adresse https://github.com/tds-fdw/tds_fdw et downloadez le fichier sur le serveur voulu.
Positionnez-vous dans le répertoire où a été déposé le fichier tds_fdw-master.zip et décompressez le fichier.
Positionnez-vous dans le répertoire ainsi créé :

#cd /home/postgres/tds_fdw
#unzip tds_fdw-master.zip
#cd tds_fdw-master

Lancez la création et l’installation du package :

#make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/tds_fdw.o src/tds_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/options.o src/options.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I./include/ -fvisibility=hidden -I. -I./ -I/usr/pgsql-9.6/include/server -I/usr/pgsql-9.6/include/internal -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/deparse.o src/deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/usr/pgsql-9.6/lib -Wl,--as-needed  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags  -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--2.0.0-alpha.1.sql
cp README.md README.tds_fdw.md

puis

#make USE_PGXS=1 install
/usr/bin/mkdir -p '/usr/pgsql-9.6/lib'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-9.6/doc/extension'
/usr/bin/install -c -m 755  tds_fdw.so '/usr/pgsql-9.6/lib/tds_fdw.so'
/usr/bin/install -c -m 644 .//tds_fdw.control '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//sql/tds_fdw--2.0.0-alpha.1.sql  '/usr/pgsql-9.6/share/extension/'
/usr/bin/install -c -m 644 .//README.tds_fdw.md '/usr/pgsql-9.6/doc/extension/'

•    Mise en place du FWD dans le cluster postgreSQL

Sous le user postgres, créez l’extension dans la base

psql -d mabdd
mabdd=#CREATE EXTENSION tds_fdw;
CREATE EXTENSION

On passe maintenant à la création du serveur de données.
Dans notre exemple, on cherche à se connecter la base ACHATS du serveur MSSQL ‘172.25.152.119’.

psql –d dbref
create server mssql_svr foreign data wrapper tds_fdw
options ( servername '172.25.152.119', port '1433',  database 'achats', tds_version '7.3', msg_handler 'notice' );
GRANT USAGE ON FOREIGN SERVER mssql_svr  TO monpguser;

On crée un mapping entre le user postgreSQL monpguser et le user MSSQL et son mot de passe.

CREATE USER MAPPING FOR monpguser SERVER mssql_svr  OPTIONS (username 'supervision', password 'I_lov_SQL');

Sous le user postgreSQL monpguser, on crée les foreign tables voulues

psql –d dbref –U monpguser
create foreign table monpguser.f_rayons (
 code_rayon            varchar(4)
,code_secteur          varchar(4)
,lib_rayon             varchar(20))
SERVER mssql_svr
    OPTIONS (table 'dbo.rayon', row_estimate_method 'execute');
create foreign table monpguser.f_refsol (
 art_code            varchar(9)
,date_stock          timestamp)
SERVER mssql_svr
    OPTIONS (table 'dbo.refsol', row_estimate_method 'execute');

On contrôle le bon fonctionnement des objets créés.

select * from f_rayons;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'achats'., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'achats'., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: PRDWABP006, Process: , Line: 1, Level: 0
 code_rayon | code_secteur | lib_rayon
------------+--------------+-----------
(0 rows)
select * from f_refsol;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'achats'., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'achats'., Server: PRDWABP006, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: PRDWABP006, Process: , Line: 1, Level: 0
 art_code  |     date_stock
-----------+---------------------
 100305038 | 2007-08-21 00:00:00
 100305040 | 2007-08-21 00:00:00
 100305042 | 2007-08-21 00:00:00
 100305044 | 2007-08-21 00:00:00

Pour supprimer les messages d’information générés par MSSQL, il faut modifier le serveur de données de la manière suivante :

psql  –d dbref
alter server mssql_svr  options (drop msg_handler);

Contrôle

psql –d dbref –U monpguser
select * from f_refsol;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 art_code  |     date_stock
-----------+---------------------
 100305038 | 2007-08-21 00:00:00
 100305040 | 2007-08-21 00:00:00
 100305042 | 2007-08-21 00:00:00

L’option msg_handler peut être utile lors du débogage.
Si on fait l’analogie avec Oracle, la commande IMPORT FOREIGN SCHEMA n’existe pas dans le cas du FDW pour MSSQL.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *