SQL/MED com jdbc_fdw / Integrando o PostgreSQL 9.2 com dados externos

Pessoal, seguindo o post anterior de integração do PostgreSQL com dados externos utilizando o odbc_fdw, esse tutorial trata da integração do SGBD PostgreSQL (9.2) com diversas fontes de dados externas através de padrões bem definidos.

A conexão de dados é estabelecida através do padrão ISO/IEC 9075-9:2003 ou popularmente conhecida como SQL/MED, especificado no PostgreSQL via api do SQL/MED. A implementação que gostaria de trazer até vocês é a JDBC_FDW do meu amigo indiano Atri, que foi fruto do projeto GsoC 2012 que ele participou.

Eu (e também a Ampliato) preferi utilizar o JDBC_FDW ao invés do anteriormente mencionado aqui ODBC_FDW. O conector JDBC_FDW dá mais possibilidades (justamente por trabalhar com conectores jdbc e quase todo SGBD possuir uma implementação da especificação) e faz uma tratativa melhor para coerção de dados, além de também melhorar o tratamento de excessões e erros, podendo também ser utilizada em parceria com outros softwares / componentes do mundo Java. Tive experiências muito ruins com o ODBC_FDW por SIG_SEGFAULT, o que geralmente levava o banco inteiro a baixo. Também não tive problemas de desempenho para consultas básicas / moderadas.

Além disso, há um impasse técnico que aflige todo aventureiro que tenta algum FDW. Vivemos tempos de indefinições nesse mundo de implementação dos conectores, pois o PostgreSQL vem mudando a especificação da API FDW. Tais modificões tornou tanto o ODBC_FDW quanto o JDBC_FDW obsoletos na visão da versão mais recente (no momento 9.3).

Ainda não testei o JDBC_FDW na recém-lançada versão 9.3 do PostgreSQL, mas receio que não vá funcionar. Indico a versão 9.2, onde já usei tanto em ambiente Windows (PostgreSQL + JDBC_FDW compilados em Visual Studio. Não funciona em MingW, nem tente), quanto em Linux (compilados com GCC), e tive boas experiências em ambos os ambientes, conectados. O ODBC_FDW ficou pra trás já na versão 9.1.

Uma lista de todos os conectores FDW disponíveis para o PostgreSQL pode ser encontrada em sua wiki.  Com muito esforço na lista de emails pgsql-hackers o módulo postgresql_fdw foi incluso como contrib oficial (isso é, módulo adicional suportado, oficialmente incluso no source code. O restante dos FDWs são independentes, mantidos – ou não – por uma comunidade de aventureiros ou empresas). O postgresql_fdw veio para por um ponto final no dblink, anteriormente utilizado para realizar conexões e cross-database joins entre bancos distintos no mesmo cluster do SGBD.

Enfim, o JDBC_FDW nada mais é que um wrapper para a API FDW do PostgreSQL, basicamente respondendo da seguinte forma:

a) Usuário executa statement “Select * From TabelaEstrangeira”

b) PostgreSQL identifica TabelaEstrangeira como uma FDW, aciona o wrapper específico dessa (CREATE SERVER), que vai até a .so (ou .dll), e chama uma função pré-definida, dependendo da fase do statement. A .so (ou .dll) é o JDBC_FDW compilado, escrito em C.

c) Na chamada da função, em algum momento é instanciada um JVM (daí a importância da libjvm.so que vou mencionar abaixo). A JVM injeta no classpath da aplicação a implementação JDBC do banco destino.

d) A JVM manipula as chamadas do banco distinto através do JDBC.

e) JDBC_FDW faz coerção de dados e manipulação de volta dos dados para a API FDW do PostgreSQL.

f) PostgreSQL devolve os dados manipulados ao usuário.

De forma bem geral e abstrata é isso. Pra quem quiser se aventurar e escrever seu próprio FDW, aqui vai um link de como fazê-lo.

Vamos aos passos de instalação:

1. Baixe o jdbc_fdw através do github. Descompacte na pasta dos fontes (subpasta contrib) e prepare-se, pois você vai compilar código. Para realizar a compilação é necessário que você possua o código-fonte original que utilizou para compilar sua instalação do PostgreSQL. Caso não possua seu fonte original (instalou via yum / apt-get / zypper), recomendo remover a instalação antiga e instale na unha. Aqui tem a explicação de como realizar. Não esqueça que após a compilação é necessário iniciar o cluster através do initdb. É um procedimento simples de toda santa compilação: configure / make / make install.

2. Uma vez em  <SOURCE_POSTGRESQL/contrib/JDBC_FDW>, siga os passos do Atri aqui. Não se esqueça de realizar o link simbólico da libjvm.so. Ela será importante para que o JDBC_FDW funcione corretamente.

3. configure / make / make install.

4. Inicie o PostgreSQL (pg_ctl start -D PG_DATA).

5. Pronto, seu código está compilado e dentro da árvore do PostgreSQL. Registre a extensão instalada através do comando:

psql -c “CREATE EXTENSION jdbc_fdw” <BANCO>

Estamos quase lá. A partir de agora, todos os códigos serão executados dentro de uma conexão com algum banco no PostgreSQL (de preferência, o banco que você criou a extensão).

6. Crie um servidor para dados externos dentro do banco desejado no PostgreSQL (via psql ou outra ferramenta que preferir)

CREATE SERVER jdbc_serv FOREIGN DATA WRAPPER jdbc_fdw OPTIONS(
drivername 'org.sqlite.JDBC',
url 'jdbc:sqlite:/home/bruno/sqlitedatabase.db',
querytimeout '15',
jarfile '/home/bruno/sqlite-jdbc-3.7.2.jar',
maxheapsize '600'
);

Caso você tenha feito merda, remova o servidor através de:

DROP SERVER jdbc_serv;

7. Como todo SGBD de respeito exige um usuário e senha, mapeie seu usuário atual do banco PostgreSQL com o usuário do banco destino através de:

CREATE USER MAPPING FOR <PG_USER> SERVER jdbc_serv OPTIONS (username ‘<REMOTE_USER>’, password ‘s3cr3t’);

Caso você tenha feito merda, remova o mapeamento através de:

DROP USER MAPPING FOR <PG_USER> SERVER jdbc_serv;

8. Finalmente, mapeie a tabela remota com uma tabela local, através de um FOREIGN TABLE:

CREATE FOREIGN TABLE jdbc_table (
db_idfuncionario varchar,
db_re varchar,
db_nome varchar)
SERVER jdbc_serv
OPTIONS (query ‘select db_idfuncionario, db_re, db_nome from funcionarios;’);

Consulte a tabela:

select * from odbc_table limit 10; e seja feliz

Enfim, temos a conexão funcionando. Tive bons resultados com MS SQLServer 2000, MS SQLServer 2005, MS SQLServer 2008, SQLite, MySQL, e PostgreSQL 9.2.

Em tempo 1: Infelizmente a API FDW disponível na versão 9.2 é somente leitura. Além disso, a API da 9.2 não permite push-down join. Isso significa que se você realizar um join entre uma tabela local e uma estrangeira, a API irá primeiro recuperar todos os registros da tabela estrangeira e realizar um join local no PostgreSQL, e não remotamente como deveria acontecer. Escrita e push-down join em FDW somente na 9.3+.

Em tempo 2: O projeto JDBC_FDW está a procura de interessados em realizar a migração pra versão 9.3 do PostgreSQL (e implementar push-down / write). Quem tiver interesse não pense duas vezes em forkar o projeto do github!

Boa sorte!