Foreign data wrappers (fdw til PostgreSQL)
En fdw er en extension til PostgreSQL
Med foreign data wrappers (fdw) er det enkelt at tilføje data fra andre datakilder til PostgreSQL. Datakilder kan være hvad som helst som f.eks. tekstfiler, csv-filer, webservices, andre databaser eller andre PostgreSQL databaser. Der findes mange 3 parts fdw'er og enkelte indbyggede i PostgresSQL kernen.
Fdw's kan således også anvende stil dataimport fra andre systemer, hvor man traditionelt ville have anvendt dump/restore eller ETL værktøjer, herunder ogr2ogr.
En foreign data wrapper egner sig fint til data import og i nogen grad til analyser direkte på de fremmede data. Det afhænger af, om eventuelle filtre eller funktioner afvikles lokalt eller er "skubbet" til den fremmede datakilde. Med en FDW er "fremmede" data direkte indlejret i databasen. Der er dog visse begrænsninger som vi vil se mere på senere.
Det giver f.eks mulighed for gradvist at migrere fra Oracle til PstgreSQL
se mere på:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
http://www.postgresql-sessions.org/en/4/start
Indbyggede i PostgreSQL er :
Kan bruges til at forbinde til andre PostgreSQL databaser
Kan bruges til oprette forbindelse til CSV-fil som om det var en tabel i databasen.
Af trejdeparts findes bl.a.
Der arbejdes på højtryk på Spatial support med denne fdw: https://github.com/laurenz/oracle_fdw/issues/5
Man kan tilbyde at hjælpe med finansieringen
Er et eksempel på hvordan ODBC kan bruges mod SQL server:
Multicorn er den datawrapper til mange forskellige RDMBS'er samt filer,rss feeds m.m. Er skrevet i Python og egne fdw'er skulle være "rimelig enkle at skrive".
Eksempel: Oracle Foreign Data Wrapper
create extension oracle_fdw;
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//10.211.55.5/xe');
GRANT USAGE ON FOREIGN SERVER oradb TO mbj
CREATE USER MAPPING FOR mbj SERVER oradb
OPTIONS (user 'septimadb', password 'septima4you');
-- Der er andre authentification metoder
DROP FOREIGN TABLE IF EXISTS oracle.demo_orders;
CREATE FOREIGN TABLE oracle.demo_orders (
ORDER_ID integer OPTIONS (key 'true') NOT NULL,
CUSTOMER_ID numeric,
ORDER_TOTAL numeric,
ORDER_TIMESTAMP date,
USER_ID integer
) SERVER oradb OPTIONS (table 'DEMO_ORDERS');
-- Vi kan læse fra tabellen
SELECT * FROM oracle.demo_orders;
--- Vi kan opdatere til tabellen
UPDATE oracle.demo_orders SET order_total = 666 WHERE order_id = 1
--- VI kan oprette i tabellen
INSERT INTO oracle.demo_orders (order_id, customer_id, order_total,order_timestamp,user_id) VALUES(777, 2, 999,'2014-09-28', 2)
Eksempel: PostgreSQL Foreign Data Wrapper
CREATE EXTENSION postgres_fdw;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-- FDW: Define a new foreing server for fdw-use
DROP SERVER IF EXISTS stamdata CASCADE;
CREATE SERVER stamdata
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'mydatabasehost', dbname 'stamdata', port '5432');
-- FDW: Create usermapping
DROP USER MAPPING IF EXISTS FOR postgres SERVER stamdata;
CREATE USER MAPPING FOR mbj
SERVER stamdata
OPTIONS (user 'martin', password 'XXXXX');
CREATE SCHEMA bbr;
IMPORT FOREIGN SCHEMA bbr
FROM SERVER stamdata INTO bbr;
Eksempel: OGR Foreign Data Wrapper
Ogr_fdw udnytter det meget udbredte GIS transformations bibliotek GDAL. I GDAL hedder vektordelen OGR. Med ogr_fdw kan man således trække på alle de datakilder GDAL/OGR stiller til rådighed og anvende dem som tabeller i PostGreSQL. Det er dog visse begrænsninger om man skal kende virkemåden for at vurdere om det en fornuftig løsning på et problem
Brug zippede shapefiler fra Kortfosyningens ftp-server
DROP SERVER IF EXISTS matrikel_jordstykke CASCADE;
CREATE SERVER matrikel_jordstykke
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/vsizip//vsicurl/ftp://testuser_ogr_fdw:testuser_ogr_fdw@ftp.kortforsyningen.dk/matrikeldata/matrikelkort/SHAPE/1084_SHAPE_UTM32-EUREF89.zip/1084_SHAPE_UTM32-EUREF89/MINIMAKS/BASIS/JORDSTYKKE.shp',
format 'ESRI Shapefile');
DROP FOREIGN TABLE IF EXISTS kursus.jordstykke_fdw;
CREATE FOREIGN TABLE kursus.jordstykke_fdw (
fid integer,
geom geometry(POLYGON, 25832),
uuid character varying,
feat_id real,
feat_kode real ,
feat_type character varying,
elavsnavn character varying,
elavskode double precision,
matrnr character varying,
kms_sagsid real,
kms_journr character varying,
skelsagsid real,
supmsagsid real,
komnavn character varying,
komkode integer,
sognnavn character varying,
sognkode integer,
regionnavn character varying,
regionkode integer,
retskrnavn character varying,
retskrkode integer,
moderjord real,
regareal real,
arealbereg character varying,
vejareal real,
vejarealbe character varying,
vandarealb character varying,
faelleslod character varying,
esr_ejdnr real,
sfe_ejdnr real,
sfe_sagsid real,
sfe_dato date,
sfe_journr character varying,
sfe_note character varying,
land_note character varying,
arealtype character varying,
dq_index real,
registdato date,
geomdato date,
publidato date)
SERVER matrikel_jordstykke
OPTIONS ( layer 'JORDSTYKKE' );
SELECT elavsnavn,
elavskode,
matrnr
FROM kursus.jordstykke_fdw
WHERE elavskode = 2000154
LIMIT 1
-- Det går langsomt og egner sig ikke til realtidsopslag, men mere til import. Opret istedet materialized view
CREATE MATERIALIZED VIEW kursus.elav_2000154 AS
SELECT elavsnavn,
elavskode,
matrnr
FROM kursus.jordstykke_fdw
WHERE elavskode = 2000154;
--Opdatér når nødvendigt
REFRESH MATERIALIZED VIEW kursus.elav_2000154;
Hent fra WFS hos Kulturarvstyrelsen
CREATE SERVER kulturarv
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'WFS:http://www.kulturarv.dk/geoserver/wfs',
format 'WFS' );
IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER kulturarv INTO kursus;
SELECT * FROM kursus.fbb_kommuneatlas
Hent fra lokale csv-filer
CREATE SERVER privat
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'CSV:/lommepenge.csv',
format 'CSV' );
IMPORT FOREIGN SCHEMA ogr_all
FROM SERVER privat INTO kursus;
SELECT * FROM kursus.lommepenge
--Opdatér filen
INSERT INTO kursus.lommepenge VALUES(5, 'test', 0, 0)