SafeChildren Banner

Havoc Oracle Solaris Experts

lunes, 15 de febrero de 2010

DBLinks en PostgreSQL

Introducción
Para aquellos que ya saben Qué es un DBLink pueden pasar al siguiente apartado. Para aquellos que no saben qué es, lo resumiremos como Un sistema que nos permite acceder de una base de datos a otra de forma transparente.

La idea es poder acceder desde la base de datos DB1 a los objetos de la base de datos DB2 de forma sencilla y transparente, pudiendo utilizar los comando SQL generales.

En Oracle haremos esto utilizando el símbolo "@" para hacer referencia a que la SQL pertenece a un base de datos remota.
 SQL> SELECT 1 FROM DUAL@OLTP2;

         1
----------
         1
En postgres es un poco diferente, principalmente porque el soporte para dblinks no está activado por defecto y debemos cargar el archivo dblink.sql que se encuentra en $POSTGRES_HOME/share/contrib/dblink.sql

Un ejemplo Completo
Vamos a crear dos base de datos -Test1 y Test2- y una tabla en cada una de ellas, con un registro de "Saludos desde Test1" -para Test1- y "Saludos desde Test2" -para Test2-. Posteriormente, haremos una consulta desde Test2 a la tabla de Test1 a traves de un dblink.
$ psql -U postgres
psql (8.4.0)
Digite ?help? para obtener ayuda.

postgres=# create database test;
CREATE DATABASE
postgres=# create database test2;
CREATE DATABASE
postgres=# \c test1
psql (8.4.0)
Ahora está conectado a la base de datos "test1".
test1=# create table test1(nombre character varying);
CREATE TABLE
test1=# insert into test1 values('saludos desde test1');
INSERT 0 1
test1=# \c test2
psql (8.4.0)
Ahora está conectado a la base de datos "test2".
test2=# create table test2(nombre character varying);
CREATE TABLE
test2=# insert into test2 values ('saludos desde test2');
INSERT 0 1
Una vez creada la estructura, vamos a cargar el soporte de dblink en la base de datos TEST2
$ psql -U postgres test2 -f $POSTGRES_HOME/share/contrib/dblink.sql
Una vez cargado, nos conectaremos a TEST2 y haremos la prueba de conexión -en nuestro caso no es necesario poner una contraseña, sin embargo, si es vuestro caso, simplemente deberemos incluir la cadena password=CONTRASEÑA en la conexión-
$ psql -U postgres test2
psql (8.4.0)
Digite ?help? para obtener ayuda.

test2=# select dblink_connect('dbname=test1');
 dblink_connect
----------------
 OK
(1 fila)

test2=# \q
Ya hemos dicho antes que en PostgreSQL es un poco diferente de Oracle, y uno de los cambios es que debemos informar a PostgreSQL de qué tipos son los resultados devueltos ya que sino -postgres- no será capáz de entender las cláusulas WHERE de la consulta SQL.

Por lo tanto, la recomendación -por parte de PostgreSQL- es crear una vista que sea la que realmente llame al DBLink y nosotros acceder a dicha vista de forma normal. El formato de la instrucción será el siguiente:
 SELECT *
        FROM dblink('dbname=NOMBREDB password=PASSWORD', 'SQL_A_EJECUTAR')
        AS t1(NOBRE_COLUMNA1 TIPO COLUMNA1, NOMBRE_COLUMNA2 TIPO_COLUMNA2, ...);
Así, siguiendo con nuestro ejemplo, vamos a crear una vista en la base de datos TEST2 que nos permitirá acceder a los datos de TEST1

test2=# select * from test2;
       nombre       
---------------------
 saludos desde test2
(1 fila)

test2=#  CREATE OR REPLACE VIEW test_view AS
test2-#       SELECT nombre
test2-#         FROM dblink('dbname=test1', 'select nombre from test1')
test2-#         as t1(nombre character varying);
CREATE VIEW
test2=# select * from test_view;
       nombre       
---------------------
 saludos desde test1
(1 fila)
Conclusión
El acceso a diferentes base de datos es un de las opciones más interesantes que podemos encontrar en el mundo SQL y PostgreSQL nos ofrece un sistema -aunque un poco extraño al principio- muy potente y flexible.

Referencias

18 comentarios:

  1. gracias por tenernos en cuenta quiero que en una tabla de postgres me muentre los resultados de la otra tabla por medio de un campo como lo hago?

    ResponderEliminar
  2. Hola,

    La verdad es que no entiendo muy bien tu pregunta, pero vamos a ver si es esto lo que me estás preguntando.

    Si tenemos dos tablas (tabla1 y tabla2) con la siguiente estructura

    Tabla1
    =====
    ID
    Nombre
    Apellido


    Tabla2
    ====
    ID
    Nombre_ID_FK
    Dirección
    CP


    Y lo que quieres es tener,

    ID, NOMBRE, DIRECCIÓN, CP

    Entonces, simplemente tienes que hacer

    SELECT a.id, a.nombre, b.direccion, b.cp FROM tabla1 as a, tabla2 as b
    WHERE a.id = b.nombre_id_fk


    Aquí te dejo más documentación sobre Cómo Unir Tablas en PostGres (bueno, en SQL): http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html

    Un saludo,
    y disculpa si no es esto lo que me estabas preguntando,
    Urko

    ResponderEliminar
  3. Como borrar una vista ya creada ????

    ResponderEliminar
  4. Hola Anónimo,

    Para borrar una vista creada, simplemente deberemos ejecutar el comando

    DROP VIEW _nombre_de_la_vista;

    Por ejemplo,

    search=> create or replace view test as select 1;
    CREATE VIEW
    search=> select * from test;
    ?column?
    ----------
    1
    (1 row)

    search=> drop view test;
    DROP VIEW

    Un Saludo,
    Urko

    ResponderEliminar
  5. Saludos, deseo realizar un join de dos tablas pero cada una se encuentra en diferentes bd como lo haria?
    Gracias.

    ResponderEliminar
  6. Hola Anónimo,

    Sobre como hacer un join entre dos tablas que se encuentran en dos bases de datos diferentes,

    Bueno, técnicamente podemos hacerlo de igual forma, simplemente utilizando una vista remota -aunque tiene algún problema-

    Por ejemplo, si tengo la tabla URL en la DB_A con la siguiente estructura:

    page_href CHARACTER VARYING;
    page_href_hash CHARACTER VARYING;

    Y en la DB_B quiero hacer un JOIN desde la tabla VISIT a la tabla URL (en DB_A)


    1.- Creo una vista en la DB_B que se enlace con la tabla URL de DB_A

    CREATE OR REPLACE VIEW my_url_rv AS
    SELECT *
    FROM dblink('dbname=DB_A user=USER password=PASSWORD', 'select page_href, page_href_hash from search.spider_url_ref')
    AS t1(page_href character varying, page_href_hash character varying)

    2.- Hago la consulta de forma "normal"
    SELECT
    url_name, url_hash
    FROM
    url_analyzed_queue as a, my_url_rv as b
    WHERE
    a.url_hash = b.page_href_hash;

    Y hago el "join" utilizando los campos índice de ambas tablas.

    3.- Problemas de esto:

    Bueno, principalmente el rendimiento, ya que tiene que "bajarse" un gran número de datos y puede que no sea útil.

    Espero haberte ayudado,

    Sin embargo, esto me ha hecho pensar en escribir un post dedicado a este tema ... es una buena consulta.

    Un Saludo,
    Urko

    ResponderEliminar
  7. Hola necesito acceder con jpa a una entidad pero que en realidad la tabla de la misma este apuntado a un vista que es un dblink a otra base de datos.

    ResponderEliminar
  8. Muy bien explicado gracias!!1

    ResponderEliminar
  9. Buenos días,
    Tengo un problema con el dblink, quiero pasarle la conexión parametrizada puesto que quiero meterle en un bucle para consultar varias bases de datos, pero me da un error al ejecutarlo. Sabes si permite esto??
    Si ejecuto lo siguiente me sale el error:
    ERROR: no hay parámetro $1
    LINE 1: ...EW miProcedRemoto AS SELECT t.report FROM dblink( $1 , 'SELE...
    ^
    conection := 'dbname=' || quote_literal(BDName) || 'port=5432 host= localhost user=postgres password=postgres ' ;

    CREATE VIEW miProcedRemoto AS
    SELECT t.report
    FROM dblink(conection , 'SELECT reports.report as nombreReport
    FROM scr_reports reports') as
    t(nombreReport text);

    En cambio, todo funciona perfectamente si ejecuto:
    CREATE VIEW miProcedRemoto AS
    SELECT t.report
    FROM dblink('dbname=db port=5432 host= localhost user=postgres password=postgres ' ;, 'SELECT reports.report as nombreReport
    FROM scr_reports reports') as
    t(nombreReport text);

    ResponderEliminar
  10. Hola anónimo,

    Sobre si es posible crear una Vista con un DBLink parametrizado, siento decirte que no se puede, pero ... no hay que desesperar, se puede solucionar.

    Podemos crear un procedimiento que nos devuelva un "record" y pasarle como parámetros la cadena de conexión, por ejemplo,


    CREATE OR REPLACE FUNCTION test_dblink_with_parameter(dbname character varying, dbhost character varying, dbuser character varying, dbuserpass character varying)
    RETURNS SETOF record AS
    $BODY$
    SELECT t.device_name
    FROM dblink('dbname=' || $1 || ' port=5432 host=' || $2 || ' user=' || $3 ||' password=' || $4 , 'SELECT device_name FROM devices.as_device') as
    t(device_name character varying);
    $BODY$
    LANGUAGE sql VOLATILE;


    Y luego llamamos a nuestro procedimiento:

    SELECT * FROM test_dblink_with_parameter('dbname','localhost','user','password') AS (device_name character varying);

    Espero que te sirva,

    Un Saludo,
    Urko

    ResponderEliminar
  11. tengo instalado postgres 9.04 ingreso ala base de datos pero ala hora de mandar la instruccion dblink no me la reconoce y ya he ejecutado la instruccion ( psql -U postgres prueba -f $POSTGRES_HOME/share/contrib/dblink.sql )

    ResponderEliminar
    Respuestas
    1. Hola anónimo,

      Qué es lo que te pone en la consola cuando cargar el archivo "dblink.sql"? Te dice que lo ha creado correctamente?

      Comprueba también que tienes la "so" para hacer uso de DBLinks "dblink.so"

      Un Saludo,
      Urko

      Eliminar
  12. Hola Urko, tengo un problema... como cargo el archivo dblink.sql ... estoy en la consola psql.exe ... pero nose que comando debo colocar en esta para cargar el archivo dblink.sql ... Gracias

    ResponderEliminar
  13. Hola como estan una pregunta se puede utilizar el dblink para realizar actualizaciones dblink(UPDATE....)

    ResponderEliminar
    Respuestas
    1. Hola Anónimo,

      Si, depende del usuario con el que estés conectado, si éste tiene o no los permisos suficientes, pero si es posible.

      Un Saludo,
      Urko

      Eliminar
  14. podrian por favor aprender lo vasico y dejar de preguntar cosas tontas como "como borrar una vista?".. me parece que lo que esta planteando el amigo es una cuestion ya un poco mas avanzada como para que se agan preguntas de cosas tan basicas.

    ResponderEliminar
    Respuestas
    1. Hola Anónimo,

      Es cierto que esto es un poco "más allá que una gestión básica", pero, yo también recuerdo cuando empecé (hace mucho) y la verdad es que cualquier cosa me parecía un mundo.

      Cualquier pregunta es válida, aunque nos pueda parecer "sencilla", es la mejor forma de que todo aprendamos,

      Un Saludo,
      Urko

      Eliminar
  15. Hola como están les escribo porque necesito ayuda con una vista que estoy creando en un server remoto con dblink. Necesito contar registros, a nivel estadisticos. Les dejo un ejemplo:

    select t1.campo1, t1.campo2, t1.campo3 from dblink('dbname=basededatos port=5432 host=xxx.xx.xx.xx user=usuario password=password',
    'SELECT EXTRACT(YEAR, c.fecha) AS anio, count(c.id) AS total, a.campo2 from tabla c
    JOIN tabla2 b ON b.id = c.id
    JOIN tabla3 a ON c.id = a.id'
    where b.estatus = ''POSITIVO'')
    AS t(anio integer, campo2 character varying(250), total integer)
    ORDER BY EXTRACT(YEAR, c.fecha), total, campo2

    al ejecutar me arroja elsiguiente error ERROR: invalid input syntax for integer: ""
    SQL state: 22P02

    Gracias de antemano por la ayuda.
    Saludos

    ResponderEliminar