Una de las tendencias en las plataformas de datos modernas es la virtualización de datos. De forma muy resumida la virtualización de datos nos permite acceder a los datos sin realizar un movimiento/copia de datos de forma transparente. De esta forma el acceso a dicha información se realiza de la misma forma que a cualquier otra tabla dentro de nuestro PostgreSQL aunque no resida en él.

El acceso a información externa al motor PostgreSQL se ha realizado tradicionalmente mediante DBLINK. Esta funcionalidad es similar al concepto de servidor enlazado (linked server) + OPENROWSET en SQL Server.

Debido a ello se creó el concepto de Foreign Data Wrapper (FDW en adelante) que implementa los métodos necesarios para acceder a datos externos como si fuesen locales El uso de DBLINK está limitado a servidores PostgreSQL y ha quedado poco obsoleta, recomendándose DBLINK_FDW incluso para acceso a otros PostgreSQL.

Vamos a instalar un FDW para conectar con SQL Server llamado tds_fdw. El primer paso para instalar una extensión es revisar si la tenemos ya disponible (por ejemplo por estar en el paquete common de PostgreSQL):

query editor foreign data wrappers

 

Como no la tenemos disponible tenemos que descargarla. En algunos casos podemos encontrar versiones en paquetes pero en nuestro caso hemos decidido descargar la última disponible y compilarla para mostrar el proceso completo que puede ser necesario para otras extensiones que no estén disponibles ya “paquetizadas”.

Comenzaremos clonando el repositorio de GIT correspondiente o donde se ubique el código de la extensión. Si no tenemos instalado el cliente de GIT podemos instalarlo también:

apt-get install git
git clone https://github.com/tds-fdw/tds_fdw.git

¿Necesitas sacar más partido a tus datos para impulsar tu proyecto?

Acelera tus procesos de Business Analytics. Toma mejores decisiones, optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.

Voy a echar un vistazo

Una vez tenemos el repositorio clonado tenemos que compilar la extensión. Para ello necesitamos bastantes requisitos (puede que alguno no lo tengamos instalado previamente) entre los que se incluye el paquete dev de PostgreSQL de la versión para la que vamos a compilar:

apt-get install libsybdb5 freetds-dev freetds-common gnupg gcc make
postgresql-server-dev-13
 

Una vez tenemos los requerimientos entraremos en el repositorio y compilaremos:

cd tds_fdw
make USE_PGXS=1
 

Revisaremos si hemos tenido algún error en el proceso y si todo ha funcionado correctamente realizaremos la instalación y verificamos que se copian los binarios a la carpeta de extensiones correspondiente:

make USE_PGXS=1 install
 
foreign data wrappers installation

Una vez tenemos la extensión compilada e instalada, forzaremos el refresco de la configuración con pg_reload_conf() y comprobaremos que ya tenemos el FDW tds_fdw disponible:

foreign data wrappers select from

Para poder usar la extensión comenzamos creándola en nuestra base de datos (recordemos que las extensiones se configuran a nivel de base de datos en PostgreSQL):

CREATE EXTENSION tds_fdw;

Una vez registrada tenemos que proceder a crear el resto de artefactos que nos permitirán acceder a los datos a través de un FDW. Concretamente nos referimos a un “servidor”, a un “mapeo de usuarios” y a una o varias “tablas externas” para acceder a nuestro SQL Server.

Es conveniente que antes que nada comprobemos la conectividad entre nuestro PostgreSQL y nuestra instancia de SQL Server es posible. Por ejemplo podemos testear la conectividad usando el comando telnet o nc (netcat, una verdadera navaja suiza). En mi caso la IP es la 192.168.1.26 y el puerto configurado en la instancia el 22222:

Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

Una vez verificado que podemos conectar, procederemos a crear el servidor:

CREATE SERVER miserver
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername ‘192.168.1.26’, port ‘22222’);
 

A continuación crearemos el mapeo del usuario. En mi caso en mi instancia de SQL Server he creado previamente un login llamado mipostgresql al que mapearé el usuario (rol) postgres de mi cluster PostgreSQL:

CREATE USER MAPPING FOR postgres
SERVER miserver
OPTIONS (username ‘mipostgresql’, password ‘dificildeimaginar’);
 

El siguiente paso será preparar los esquemas que van a contener las tablas externas que crearemos a continuación. Es habitual en este caso mimetizar los esquemas existentes en la base de datos remota. En este caso vamos a crear los esquemas Sales y Person:

CREATE SCHEMA Sales;
CREATE SCHEMA Person;
 

A continuación importaremos todas las tablas de los esquemas:

IMPORT FOREIGN SCHEMA Person
FROM SERVER miserver
INTO Person;
 
IMPORT FOREIGN SCHEMA Sales
FROM SERVER miserver
INTO Sales;
 

Una vez hecho esto deberíamos poder tener acceso como tablas externas a todas las tablas de ambos esquemas. Comprobamos primero en SQL Server cuantas tablas tenemos en cada esquema:

foreign data wrappers schema name

Cuando comprobamos este mismo conteo en PostrgreSQL nos encontramos que en el esquema Person tenemos 16 tablas externas y 26 en el esquema Sales. La razón es que las vistas también se han mapeado por lo que sumamos 3 y 7 tablas externas respectivamente a cada esquema:

foreign data wrappers tables
foreign data wrappers sales
Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

El siguiente paso es probar que podemos acceder a la información desde PostgreSQL y que los mapeos se han realizado correctamente:

foreign data wrappers select from sales

Vamos a lanzar una consulta donde crucemos datos de tablas externas para analizar cuál es el comportamiento del FDW:

foreign data wrappers select sum

Puede ser una buena idea verificar el resultado de la consulta lanzándola sobre el sistema original. Si lanzamos esta misma consulta sobre el SQL Server original este es el resultado:

Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

Como podemos ver existe un descuadre de unos pocos céntimos. Vamos a analizar el plan de ejecución en PostgreSQL para entender qué está ocurriendo:

foreign scan

Es decir, estamos escaneando ambas tablas y calculando el resultado localmente y se nos produce un descuadre. Lo primero que tenemos que ver es si los datos están llegando literalmente idénticos desde SQL Server. Para ello obtendremos por ejemplo los valores ordenados por SaleOrderID en SQL Server y PostgreSQL y compararemos los resultados:

SELECT “TotalDue” FROM “sales”.“SalesOrderHeader” order by “SalesOrderID”;
 

Podemos ver que tenemos un redondeo en el caso de PostgreSQL:

sql server postgresql

Si analizamos el mapeo que se ha realizado automáticamente vemos que se ha mapeado contra el tipo de datos money:

create foreign table

Por defecto en PostgreSQL el tipo de datos money tiene una precisión de únicamente 2 decimales:

Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

El formato de moneda está condicionado al locale configurado, concretamente al parámetro LC_MONETARY que en nuestro ejemplo está configurado para USD y el redondeo habitual de 2 decimales. Lo mismo nos pasaría con el euro, ya que se trabaja por defecto con 2 decimales. Podemos pensar que en este caso sería suficiente con cambiar el mapeo de la tabla externa de los money al tipo de datos DECIMAL:

subtotal column name

Una vez realizado este cambio si volvemos a realizar la consulta vemos que seguimos teniendo el mismo problema ya que nos está llegando redondeado el dato ya desde SQL Server por lo que únicamente estamos añadiendo dos ceros al valor ya truncado:

select sum totaldue

Si examinamos algunas de las opciones que nos ofrece el FDW vemos que podemos definir una consulta específica para representar la tabla por lo que podemos intentar cambiar el tipo de dato en esa consulta con un CONVERT para asegurarnos que no perdemos decimales:

Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

Si volvemos a verificar los datos ya vemos que nos aparecen los 4 decimales desde PostgreSQL y el resultado nos cuadrará con el que obtenemos en SQL Server:

select server options query
select inner join sum

Sin embargo al reemplazar con una consulta tenemos un problema añadido y es que el “pushdown” de predicados no funcionará. Esto puede darnos problemas tan extraños como que si lanzamos esta consulta nos devuelva todos los resultados:

select from tables sales

Sin embargo con la configuración original el resultado era correcto:

select from tables sales order

Por tanto una opción mucho mejor sería crear una vista nueva en SQL Server con la conversión de datos hecha y usarla como si fuese la tabla original con los tipos de datos modificados. De hecho normalmente la recomendación es trabajar con foreign tables que apunten a vistas en destino. Idealmente deberíamos usar sinónimos apuntando a vistas pero la importación automática solo funciona con vistas y tablas por lo que tendríamos que generar a mano los mapeos de todas las columnas si optamos por esta opción.

Para que el funcionamiento de estos FDW sea óptimo deben proporcionar métodos de acceso válidos en el sistema destino así como sus costes asociados de forma que el optimizador pueda obtener planes “globales” poco costosos. Gran parte del éxito o del fracaso en lo que al rendimiento de un FDW se refiere depende de este aspecto.

Por ejemplo en este caso si lanzamos una consulta filtrando por un único registro y uno que devuelva todos los resultados desde PostgreSQL vemos que la consulta que se genera en SQL Server realiza el “pushdown” del filtro al origen, lo cual es lo ideal:

SELECT * FROM “sales”.“SalesOrderHeader” WHERE “SalesOrderID”=43660;
SELECT * FROM “sales”.“SalesOrderHeader”;
Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

Además si analizamos la información estadística que devuelve vemos que es correcta, se estima una única fila en el primer caso y 31465 en la segunda. Los costes también se actualizan adecuadamente:

EXPLAIN SELECT * FROM “sales”.“SalesOrderHeader” WHERE “SalesOrderID”=43660;
 
“Foreign Scan on ““SalesOrderHeader”” (cost=200.00..200.01 rows=1 width=100)”
 
EXPLAIN SELECT * FROM “sales”.“SalesOrderHeader”;
 
“Foreign Scan on ““SalesOrderHeader”” (cost=200.00..3146914.65 rows=31465 width=100)”
 

Incluso en algunos casos donde podríamos tener un comportamiento erróneo, vemos que el optimizador de PostgreSQL es capaz de detectar la correlación en un IN y generar una consulta apropiada (aunque repita dos veces el mismo filtro) para evitar traer datos innecesarios:

SELECT * FROM “sales”.“SalesOrderHeader”
WHERE “SalesOrderID” IN (
SELECT “SalesOrderID” FROM “sales”.“SalesOrderHeader”
WHERE “SalesOrderID”=43660
);
foreign sales order ID

Pero en otros casos relativamente sencillos como un IN con dos valores ya no decide utilizar el pushdown y se trae toda la tabla en vez de los dos registros:

select from sales order
Virtualización de datos en PostgreSQL con Foreign Data Wrappers (I)

Por último comentar que la creación de un FDW para una nueva fuenta de datos no es una tarea banal pues se realiza en un lenguaje compilado, habitualmente C, y debe implementar un grupo de funciones y callbacks para que se pueda integrar con PostgreSQL En la documentación oficial tenemos los detalles de todo aquello que necesitaríamos para su implementación en caso que quisiéramos diseñar el nuestro propio: https://www.postgresql.org/docs/13/fdwhandler.html

Conclusión

En este artículo hemos visto como compilar y configurar un Foreign Data Wrapper para acceder a SQL Server desde PostgreSQL. También hemos visto algunos problemas de mapeos de datos que pueden surgir y cómo abordarlos.

En definitiva este tipo de funcionalidades nos permiten utilizar PostgreSQL como un data hub desde el que accedamos a una multitud de fuentes de datos (https://wiki.postgresql.org/wiki/Foreign_data_wrappers) de forma transparente usando el lenguaje SQL.

¡Has llegado al final! Parece que te ha gustado nuestro post sobre SQL

Recuerda, acelerar tus procesos de Business Analytics es imprescindible para la toma de mejores decisiones. Optimiza tu Datawarehouse y el proceso de generación de informes en tus proyectos de analítica con nuestra consultoría en Business Intelligence.

Voy a Echar un vistazo
0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
como hacer un custom visual power bi
Leer más

Cómo hacer un Custom Visual en Power BI

Cómo crear tus propios custom visual o widgets para PowerBI. Con un control personalizado podremos mostrar la información como a nosotros nos gusté más, pudiendo ser más corporativo o dándole a la visualización de los datos un toque más personal.
Reporte financiero con cuentas aplanadas en Power BI
Leer más

Reporte financiero con cuentas aplanadas en Power BI

Hoy, nuestro experto Dani Gil, nos ayuda a crear un report financiero con cuentas aplanadas con Power BI y nos explica la solución a algunos problemas que pueden surgir basándose en su experiencia con el proyecto real de un cliente.