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):
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:
¿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 vistazoUna 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:
Una vez tenemos los requerimientos entraremos en el repositorio y compilaremos:
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:
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:
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):
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:
Una vez verificado que podemos conectar, procederemos a crear el servidor:
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:
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:
A continuación importaremos todas las tablas de los esquemas:
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:
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:
El siguiente paso es probar que podemos acceder a la información desde PostgreSQL y que los mapeos se han realizado correctamente:
Vamos a lanzar una consulta donde crucemos datos de tablas externas para analizar cuál es el comportamiento del FDW:
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:
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:
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:
Podemos ver que tenemos un redondeo en el caso de PostgreSQL:
Si analizamos el mapeo que se ha realizado automáticamente vemos que se ha mapeado contra el tipo de datos money:
Por defecto en PostgreSQL el tipo de datos money tiene una precisión de únicamente 2 decimales:
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:
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:
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:
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:
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:
Sin embargo con la configuración original el resultado era correcto:
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:
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:
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:
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:
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