I Foreign Data Wrapper (FDW)

Prima dell’introduzione dei Foreign Data Wrapper l’unico modo per connettere un database Postgres con una fonte dati esterna era il modulo dblink.
Nel 2003 viene definito all’interno del linguaggio SQL l’insieme di regole per la gestione in maniera standard di fonti di dati esterne: SQL/MED (management of external Data).

All’interno di PostgreSQL 9.1, una prima implementazione dello standard SQL/MED viene introdotta con i Foreign Data Wrapper, fornendo a Postgres l’accesso diretto a fonti di dati come file o altri database (Oracle, Mysql…), permettendone l’utilizzo come tabelle.
Il vantaggio di questo approccio è evidente: la possibilità di interrogare una fonte di dati esterna per poterne estrarre dati in maniera nativa eseguendo una semplice query. Il non dover ricorrere a moduli esterni per ottenere questo risultato è una notevole semplificazione del lavoro per i DBA.

Per saperne di più, potete dare una occhiata all’articolo pubblicato nel 2011, prima dell’uscita di PostgreSQL 9.1,  sul nostro blog: “PostgreSQL 9.1: Tabelle esterne con SQL/MED“.

Piccolo esempio di uso di un FDW

PostgreSQL 9.3 introduce il supporto ai foreign data wrapper in scrittura ed aggiunge anche il supporto al foreign data wrapper per PostgreSQL. Vediamo adesso un semplice esempio di utilizzo di un FDW connettendo fra loro due database Postgres.
Creiamo due database:

CREATE DATABASE source;
CREATE DATABASE destination;

All’interno di source creiamo una tabella di test con dei dati di test:

\c source
CREATE TABLE test1 AS SELECT id, md5(random()::text) FROM generate_series(1,5) id;

Connettiamoci adesso al db di destinazione e connettiamo i due database:

\c destination
CREATE EXTENSION postgres_fdw ;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'source' );
CREATE USER MAPPING FOR postgres SERVER src_srv OPTIONS ( user 'postgres' );

Qualcuno di voi, giustamente, si sarà alzato in piedi, lamentandosi per la pessima scelta in fatto di sicurezza! Molto bene!

Per semplicità, ho infatti deciso di connetterci con l’utente amministratore “postgres” – anche per non deviare troppo dall’argomento principale dell’articolo. Sappiate che in un ambiente di produzione, per motivi di sicurezza, dovrete prendere altre scelte – ad esempio utilizzando un utente specifico per la vostra applicazione.

Ad ogni modo, una volta stabilita la connessione, possiamo creare sul database di destinazione una tabella esterna che punti a test1 sul database source:

CREATE FOREIGN TABLE test1_ft (id integer, md5 text) server src_srv options(table_name 'test1');

Possiamo adesso confrontare il contenuto fra le due tabelle di test:

select * from test1_ft ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

\c source

select * from test1 ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

È evidente, osservando questo esempio, che uno dei più grandi limiti all’utilizzo dei Foreign Data Wrapper è la necessità di definire separatamente, conoscendone la struttura, ogni tabella.
L’accesso a dati esterni quindi risulta laborioso qualora si voglia importare tabelle più complesse o, addirittura, interi schemi.

Fino ad adesso, operazioni del genere venivano fatte per mezzo di script in grado di connettersi al database sorgente e generare la struttura delle tabelle esterne in maniera automatica.
Fortunatamente la funzionalità IMPORT FOREIGN SCHEMA, presente nella prossima release di PostgreSQL, ci viene in aiuto.

IMPORT FOREIGN SCHEMA: sinossi

L’istruzione IMPORT FOREIGN SCHEMA, permette di importare uno schema intero da una fonte dati esterna, senza dover specificare la struttura di ogni singola tabella:

IMPORT FOREIGN SCHEMA remote_schema_name
FROM SERVER server_name INTO destination_schema;

Qualora non sia necessario importare uno schema intero, è possibile usare la clausola LIMIT TO e circoscrivere l’importazione unicamente alle tabelle a cui siamo interessati:

IMPORT FOREIGN SCHEMA remote_schema_name LIMIT TO (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Altrimenti, se siamo interessati a escludere solo alcune tabelle dallo schema, è possibile filtrarle con la clausola EXCLUDE:

IMPORT FOREIGN SCHEMA remote_schema_name EXCLUDE (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Esempio

Vediamo nel dettaglio come utilizzare questo comando, andando a estendere l’esempio usato in precedenza.
Connettiamoci al database sorgente e aggiungiamo due tabelle a quella che già è presente:

\c source
create table test2 as select id, md5(random()::text) from generate_series(1,20) as id;
create table test3 as select id, md5(random()::text) from generate_series(1,50) as id;

Creiamo adesso nel database di destinazione uno schema che useremo come target dell’istruzione IMPORT FOREIGN SCHEMA:

\c destination
create schema imported;

Adesso possiamo importare lo schema che abbiamo appena ampliato, contando sulla connessione aperta nell’esempio precedente:

IMPORT FOREIGN SCHEMA public FROM SERVER src_srv INTO imported;

Facciamo una rapida ispezione di tutte le tabelle sul database di destinazione per osservare il risultato dell’importazione dello schema:

\dE *.*

               List of relations
  Schema  |   Name   |     Type      |  Owner
----------+----------+---------------+----------
 imported | test1    | foreign table | postgres
 imported | test2    | foreign table | postgres
 imported | test3    | foreign table | postgres
 public   | test1_ft | foreign table | postgres

All’interno dello schema public notiamo la tabella che abbiamo creato in precedenza, mentre il risultato dell’importazione “in massa” è visibile nello schema imported.

Con questo esempio è possibile constatare quanto è più veloce e immediato l’utilizzo delle tabelle esterne con IMPORT FOREIGN SCHEMA.

Conclusioni

Con PostgreSQL 9.5, grazie a questa nuova funzionalità, le migrazioni dei dati diventeranno sempre più semplici e veloci.
Attualmente l’istruzione IMPORT FOREIGN SCHEMA è supportata solo da postgres_fdw e richiede che gli sviluppatori dei singoli driver la implementino nel modo più consono alla fonte di dati.
Aumentando il numero di driver in grado di supportare questa funzionalità si aprono scenari sempre più interessanti per PostgreSQL e per l’integrazione dei dati.

Dopo aver scaricato Talend Open Studio (reperibile dal sito di Talend) e dopo averlo installato, possiamo procedere creando un nuovo progetto, e all’interno di questo un nuovo job.

Creiamo quindi le connessioni alle risorse che ci serviranno durante l’importazione, ovvero la connessione al database remoto e i due file csv.

Nella sezione Metadata dell’interfaccia di Talend, aggiungiamo la connessione a Greenplum, dopo aver inserito il nome e i parametri di connessione, il programma creerà un nuovo oggetto di tipo DbConnection.

Cliccando su questo con il tasto destro e selezionando l’opzione “recupera schema”, sarà possibile selezionare le tabelle su cui vogliamo lavorare, nel nostro caso la tabella states e la tabella users.

Aggiungiamo ora i file contenenti i dati. Subito sotto l’icona delle connessioni ai database (DB connections), troviamo il gruppo dei file CSV, come precedentemente fatto per i database, clicchiamo con il tasto destro sul gruppo e poi su “crea file delimitato”.

Nella prima schermata del wizard che si aprirà, ci verrà richiesto di inserire un nome e una descrizione per la risorsa che stiamo creando. Si passa quindi allo step due, nel quale dobbiamo identificare il file di testo a cui vogliamo riferirci, e la sua codifica.

Una volta fatto sarà possibile possibile passare al terzo step, nel quale dobbiamo istruire Talend su come gestire le colonne del CSV. In questa schermata è possibile : selezionare l’encoding del file (1), i separatori di campo e i caratteri di fine linea (2), e qualora fosse necessario, segnalare quante e quali linee devono essere ignorate sia all’inizio che alla fine del file (3).

È inoltre possibile segnalare a Talend di utilizzare la prima riga come “schema” del CSV, in maniera che possa prendere i nomi delle colonne direttamente dal file, risparmiando all’utilizzatore l’inserimento manuale del nome delle colonne ( 4 ).

Nel quarto step resta solo da: modificare lo schema del file che stiamo associando, controllare e modificare i tipi di dato riconosciuti in automatico per ogni colonna e, qualora non fossero stati riconosciuti in automatico dalla testata del CSV, modificare i nomi delle colonne e la lunghezza dei campi.

Completato il 4° passo del wizard, il file è correttamente associato all’applicazione e pronto per essere usato.

Una volta aggiunte tutte le risorse che intendiamo utilizzare, è possibile procedere con la creazione del job. Trasciniamo quindi i due file sull’editor visuale del job, e selezioniamo tFileInputDelimited come tipo di file.

Dalla connessione a Greenplum precedentemente creata invece, trasciniamo sull’editor le due tabelle di destinazione (nel nostro caso states e users), e selezioniamo come tipo tGreenplumOutput.

Inseriamo ora un oggetto di tipo tMap – che si trova nella palette degli strumenti alla destra dell’editor visuale – nella cartella “elaborazione” e posizioniamolo fra il file CSV contenente gli stati da importare e la tabella di Greenplum di destinazione.

Colleghiamo il file al componente tMap (clic con il destro sul file -> riga -> main) e il componente tMap alla tabella di destinazione (clic destro sulla tabella -> riga -> nuovo output). Una volta collegati fra di loro i componenti possiamo fare doppio click sull’oggetto tMap che permette di associare le righe del file da importare a quelle della tabella di destinazione.

Trascinando i vari campi dalla tabella di sinistra a quella di destra , è possibile associare ogni colonna del file alla colonna relativa sulla tabella di destinazione. Cliccando su ok salveremo l’associazione appena effetuata e i dati sono pronti per essere importati dal file di origine alla tabella di destinazione.

Aggiungiamo ora l’importazione di una lista di utenti dal secondo csv, durante l’importazione è necessario fare lookup sulla tabella degli stati appena riempita, in maniera da verificare l’associazione “utente -> stato”, riga per riga. Con il gergo lookup si intende la ricerca di un valore all’interno di un dizionario, utilizzando una chiave al fine di recuperare un ID (solitamente la chiave primaria di quell’oggetto nel database, per garantire integrità referenziale).

Aggiungiamo quindi un oggetto tMap fra il file CSV con gli utenti e la tabella di destinazione, inoltre dalla lista delle tabelle di Greenplum trasciniamo nuovamente la tabella contenente gli stati sulla finestra dell’editor visuale, selezionando come tipo tGreenplumInput.

Colleghiamo quindi i tre elementi al componente tMap, come e’ stato fatto precedentemente.

Adesso eseguiamo il mapping fra i tre elementi: questa volta il campo idState del CSV deve essere mappato sul campo idState della tabella states (row3) e quest’ultimo deve essere collegato alla tabella utenti di destinazione. In questa maniera per ogni linea del CSV viene eseguito il lookup sulla tabella stati, assicurando così l’integrità della relazione “Utente – Stato”.

Per concludere, facciamo in modo che prima venga eseguita l’importazione dei dati relativi agli stati. Successivamente, quelli degli utenti, in modo da poter eseguire correttamente il lookup. Tracciamo quindi una riga di tipo OnComponentOK (click destro -> attivare -> onComponentOk) dal componente di output della tabella stati a quello di input degli utenti (il file CSV).

In questo modo prima verrano importati gli stati e solo in caso di successo verranno impotati gli utenti.

Adesso basterà cercare il tab di esecuzione nella parte bassa dell’area di lavoro e premere il tasto run per fare in modo che il nostro job venga eseguito.

Questo articolo mostra la procedura da seguire per creare una tabella esterna contenente dati ricavati da un file CSV locale e sfrutta un’altra caratteristica introdotta in PostgreSQL 9.1: le estensioni.

Prima di tutto, è necessario installare una estensione chiamata file_fdw distribuita nei moduli contrib di PostgreSQL 9.1.

Per installare i moduli contrib di PostgreSQL 9.1 dai sorgenti è sufficiente compilarli con gmake world e installarli con gmake install-world, oppure entrare nella directory contrib degli stessi sorgenti e digitare gmake install.

Una volta installati i moduli contrib, le estensioni si troveranno in ${postgresql_prefix}/share/extension. Controllate che esistano i file file_fdw.control e file_fdw--1.0.sql, che definiscono l’estensione (si rimanda all’articolo sulle estensioni per maggiori informazioni).

Creare l’estensione con:

CREATE EXTENSION file_fdw;

Il comando psql per mostrare le estensioni è dx, che possiede anche la variante dx+, che mostra maggiori dettagli:

test_db=# dx+ file_fdw
Objects in extension "file_fdw"
Object Description
-----------------------------------------
foreign-data wrapper file_fdw
function file_fdw_handler()
function file_fdw_validator(text[],oid)
(3 rows)

Si noti che questa estensione crea automaticamente un oggetto di tipo FOREIGN DATA WRAPPER di nome file_fdw, che tornerà utile in seguito.

Dato che SQL/MED può gestire la copia dei dati anche attraverso database remoti, è necessario creare un server usando l’oggetto file_fdw (questo passo è necessario anche nel caso si stiano caricando dati da un file locale, come in questo esempio):

CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ;

A questo punto, è possibile creare la tabella esterna caricando i dati, con:

CREATE FOREIGN TABLE statistical_data (field1 numeric, field2 numeric)
SERVER file
OPTIONS (filename '/tmp/statistical_data.csv', format 'csv', delimiter ';') ;

Attualmente, è possibile eseguire solo query di sola lettura tramite SELECT sulle tabelle foreign. Le tabelle foreign inoltre, funzionano anche attraverso dblink, il tool che permette la comunicazione tra database remoti.

Concludendo, questa caratteristica va a completare una piccola lacuna che Postgres aveva rispetto ad altri database. Va pertanto accolta con grande interesse data la sua utilità, specialmente nel settore del data warehousing, in particolare dell’estrazione e del caricamento dati (ETL), e in più in generale per l’integrazione del database con altri sistemi e altre fonti di dati.

Per maggiori informazioni si rimanda alla documentazione di PostgreSQL sul comando CREATE FOREIGN TABLE.