P2P vs bidirectional transactional replication

P2P vs bidirectional transactional replication

La replicación P2P (peer to peer) apareció en el ya ahora lejano SQL Server 2005 y fue refinada con funcionalidades importantes, como la detección de conflictos, en SQL Server 2008. Desde entonces hasta la actualidad prácticamente no ha tenido mejoras más allá de corrección de bugs y pequeños ajustes para mejorar su compatibilidad con otras características. La replicación en SQL Server hace muchos años que es el “patito feo” desde el punto de vista de marketing pero también por la existencia de herramientas de replicación de terceros más amigables y capaces.

La principal ventaja que aporta P2P respecto a otras soluciones es que cada nodo permite tanto escrituras como lecturas (multi master), aunque debemos tener claro que estas operaciones de escritura se ejecutan en el nodo local y luego, asíncronamente, se replican al resto de nodos. En sistemas de bases de datos distribuidas más modernas como CosmosDB este comportamiento puede ajustarse para cada transacción gestionando el nivel de consistencia que solicitemos al motor, desde Strong hasta Eventual, lo cual permite mucha más flexibilidad.

La adopción de replicación P2P ha sido muy escasa a lo largo de estos años por muchos motivos, pero uno de ellos es que se trata de una característica disponible únicamente en la versión Enterprise. Este coste también causa que software de replicación de terceros (que implica licenciamiento extra) también quede descartado en muchos escenarios. Si nos centramos solo en SQL Server ciertamente en muchas ocasiones la necesidad de replicación es entre dos nodos únicamente, no entre N, por lo que de forma manual, sin requerir Enterprise, podríamos configurar una replicación transaccional bidireccional cruzada entre los nodos. En el caso que necesitáramos más de 2 nodos deberíamos pasar necesariamente por una arquitectura de réplica tipo hub and spoke, de forma que todas las replicaciones bidireccionales sean cruzadas entre dos pares de nodos, con un nodo central común a todos.

En este tipo de replicaciones uno de los principales dolores de cabeza es la gestión de los conflictos, por lo que lo ideal es que contemos con un modelo de base de datos que disminuya al máximo la posibilidad de su ocurrencia. Por ejemplo, si tenemos algún tipo de clave basada en IDENTITY o secuencias, sería muy conveniente que tengamos rangos distintos entre los nodos para evitar colisiones simplemente al insertar nuevos registros al mismo tiempo. Si es previsible que por el funcionamiento de nuestra aplicación van a generarse una cantidad muy elevada de conflictos directamente no recomendaría el uso de este tipo de replicaciones ya que el coste de soporte podría ser absurdamente alto.

La replicación P2P nativamente nos detectará los siguientes conflictos:

  • Insert-insert

Ocurre cuando una fila con una misma clave se inserta en más de un nodo a la vez. Cuando nos referimos “a la vez” a lo que nos referimos es que los insert ocurren durante el periodo de tiempo que existe entre que se sincronizan los nodos entre sí, el cual suele ser de varios segundos. Otro caso menos habitual es que registros con distinta clave primaria sufran un conflicto insert-insert debido a, por ejemplo, alguna constraint UNIQUE extra de otras columnas, como un identificador de tipo documento nacional de identidad, pasaporte, etc.

  • Update-update

Ocurre cuando una misma fila se modifica a la vez en más de un nodo. Si este caso va a ser habitual recordemos que en la replicación de mezcla tendríamos mecanismos para poder mezclar los updates con facilidad y obtener una fila con los cambios aplicados de forma conjunta.

  • Delete-delete

Ocurre cuando una fila se borró en más de un nodo a la vez. Este caso suele ser sencillo de gestionar y puede ser ignorado habitualmente ya que el resultado deseado suele ser que las filas aparezcan borradas en ambos nodos.

  • Insert-update

Ocurre cuando una fila se modifica en un nodo pero la misma fila fue borrada y reinsertada en otro nodo.

  • Insert-delete

Ocurre cuando una fila se borra en un nodo, pero la misma fila fue borrada y reinsertada en otro nodo.

  • Update-delete

Ocurre cuando una fila se actualiza en un nodo y fue borrada en otro nodo.

En el caso de una replicación bidireccional cuando nos encontremos con alguno de estos errores el comportamiento que tendremos será una parada de la replicación con un fallo del agente de distribución al intentar replicar el comando correspondiente. Normalmente cada uno de estos conflictos estará correlacionado con la generación por parte del agente de distribución de un único código de error (algunos casos pueden ser varios errores distintos). Esto nos permite de alguna forma poder gestionar estos conflictos en base a los códigos de error dando lugar a la posibilidad de generar un sistema de detección de conflictos. Este sistema sería menos potente y eficiente que el que tenemos en la replicación P2P (la cual se apoya en la columna oculta $sys_p2p_cd_id) pero a cambio nos permitiría usar una versión SQL Server Standard. Adicionalmente, si lo que deseamos es que nuestra replicación continúe en caso de errores, podremos utilizar el parámetro SkipErrors en el agente de distribución para que, ya sin respetar el comportamiento habitual de una réplica transaccional, podamos continuar replicando el resto de cambios posteriores al que causó el error.

Para clarificar un poco todo esto vamos a mostrar el funcionamiento con un ejemplo práctico. Vamos a configurar 2 bases de datos idénticas con distinto nombre sobre las cuales replicaremos los datos vía replicación transaccional bidireccional. Las bases de datos las vamos a crear sobre una misma instancia, pero el comportamiento sería exactamente el mismo si utilizáramos varias instancias. Sobre dichas bases de datos replicadas vamos a simular conflictos y vamos a analizar cómo podríamos gestionarlos/resolverlos de forma manual.

El primer paso será asegurarnos que en la instancia que vamos a utilizar tenemos el soporte de replicación instalado:

Si no hemos configurado ninguna publicación previamente en esta instancia tendremos que configurarle su distribuidor. Podremos utilizar un distribuidor existente o bien crear uno nuevo. Para este ejemplo crearemos un nuevo distribuidor local y asociaremos la instancia como un publicador de dicho distribuidor:

A continuación, crearemos un par de bases de datos que llamaremos replicaA y replicaB donde crearemos una tabla llamada replica en cada una de ellas:

CREATE TABLE replica (ID int primary key, data varchar(100), ID2 int unique)

El siguiente paso será configurar dos publicaciones y dos subscripciones “cruzadas”, una para replicar de replicaA a replicaB y otra para replicar de replicaB a replicaA. La configuración de las réplicas sería igual que con cualquier otra réplica transaccional con un par de salvedades a la hora de crear las subscripciones.

La primera es que debemos tener la opción @loopback_detection habilitada para evitar que hagamos “ping pong” con los cambios, replicando el mismo cambio de A a B, de B a A, de A a B de nuevo, etc.

La segunda es que normalmente, al igual que ocurre en la P2P, tiene más sentido que no inicialicemos mediante snapshots las subscripciones, sino que utilicemos un método alternativo para esta sincronización inicial. En nuestro caso como vamos a partir de tablas vacías, podemos simplemente indicarle que el subscriptor ya se encuentra inicializado correctamente utilizando el sync_type “replication support only”:

Teniendo en cuenta este par de particularidades hemos creado las publicaciones y subscripciones en nuestra instancia. Comprobaremos desde el monitor de replicación el estado inicial:

Conviene que nos aseguremos que el funcionamiento es correcto y que los cambios “fluyen” desde el publicador al distribuidor y del distribuidor al subscriptor sin latencias ni errores. Para ello podemos simplemente insertar algunos trace tokens desde el propio monitor de replicación:

legados a este punto ya podemos comenzar a probar nuestros escenarios de conflicto. Comenzaremos por el insert-insert para el que insertaremos una fila con la misma clave primaria a la vez en ambas bases de datos:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (1,'data',null)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (1,'data',null)
COMMIT TRAN

Casi de forma inmediata obtenderemos un error que indica que algo ha ido mal en el batch de comandos que se han intentado ejecutar en el subscriptor:

Como un batch puede tener muchos comandos, la replicación intentará ejecutarlos individualmente antes de darse por vencida. Pasados unos segundos veremos ya el fallo por PK duplicada:

Llegados a este punto nuestras replicas quedarán detenidas y necesitarán de intervención manual para poder continuar. Si realmente la situación es que ambas filas del conflicto insert-insert son iguales, podríamos pensar que una posible solución “rápida” en este escenario sería borrar las filas en ambas bases de datos para que se inserten las filas pendientes en ambas subcripciones.

SET XACT_ABORT ON

BEGIN TRAN
DELETE FROM ReplicaA.dbo.replica WHERE ID=1
DELETE FROM ReplicaB.dbo.replica WHERE ID=1
COMMIT TRAN

Al hacer esto, veremos que la réplica puede continuar solucionándose el error:

Sin embargo, ocurre algo que inicialmente podríamos no tener en cuenta. Como hemos insertado un comando de delete en la réplica al realizar el borrado, lo que estamos acabando ejecutando en cada réplica es:

  • Replica A: Delete local + Insert replicado de B a A (el que falló) + Delete replicado de B a A
  • Replica B: Delete local + Insert replicado de A a B (el que falló) + Delete replicado de A a B

Por tanto, el resultado final tras la operación será que nos quedamos sin ninguna fila en ninguna de las réplicas:

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Para corregir la situación tendríamos que además de realizar los dos deletes, volver a reinsertar la fila “correcta” en únicamente uno de los nodos:

-- Insert only in one database

INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (1,'data',null)
WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Hemos visto cómo es importante que cuando vayamos a realizar cualquier tipo de “resolución de conflictos manual” tengamos presente que las nuevas operaciones correctivas también van a replicarse. Si quisiéramos intentar automatizar esta resolución de errores el primer paso sería modificar el parámetro SkipErrors en la configuración de nuestro agente de distribución. Para ello podríamos crearnos un perfil de agente personalizado donde indiquemos que queremos ignorar los comandos que generen un error 2627:

Sin embargo, existe ya un profile por defecto que nos ignora los casos más habituales, concretamente el 2627 (conflicto de PK duplicada), 2601 (conflicto por restricción UNIQUE) y 20598 (fila no encontrada):

De estos tres el último error puede ocurrir tanto si hemos borrado una fila e intentamos realizar un UPDATE sobre ella, como si intentamos realizar un DELETE sobre una fila que ha sido previamente eliminada. Vamos a utilizar este perfil y vamos a simular de nuevo el error anterior que nos genera un conflicto insert-insert. Es importante tener en cuenta que, dependiendo de la versión de SQL Server y del parámetro modificado, estos cambios de perfil de agente pueden entrar en efecto de forma automática o requerir que se pare y vuelva a arrancar el agente. En estos casos es siempre mejor ir sobre seguro y realizar el reinicio del agente para evitar que no coincida al 100% el perfil actual en ejecución con el configurado:

Lanzaremos de nuevo un insert-insert simultáneamente esta vez con el ID 2 en conflicto:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (2,'data',123)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (2,'data',123)
COMMIT TRAN

Esta vez la réplica no quedará detenida, por lo que seguiríamos replicando otros cambios que puedan llegar, pero se nos registrará un error en el histórico avisándonos que se ha ignorado un error:

Salvo que tengamos muy controlada la situación en la que se pueda llegar a generar el conflicto, no deberíamos confiarnos con este tipo de “parches” ya que imaginemos por ejemplo que realizamos un insert con conflicto pero donde no todas las columnas de la fila sean idénticas. Por ejemplo:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (3,'dataReplicaA',12345)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (3,'dataReplicaB',123456)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables
SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

En este caso lo que vamos a tener es un escenario en el cual las dos filas con ID 3 no tienen los mismos contenidos, es decir, que estarán desincronizadas. Esto se suele llamar un escenario de divergencia entre publicador y subscriptor. Para poder detectar este tipo de situaciones podemos utilizar un sistema de validación que viene incluido en la replicación. Básicamente debemos situarnos sobre cada una de las publicaciones e indicar que queremos validar sus subscriptores:

Este proceso de validación podrá ser más o menos pesado en función de las opciones de validación que usemos. Por ejemplo, para calcular el número de filas tenemos dos métodos, uno que ejecuta un count real y otro que se apoya en las tablas de metadatos para obtener el número de filas (que en condiciones normal debería ser correcto). Además, podemos indicar si queremos calcular checksums para verificar los datos de la fila:

Cuando ejecutamos la validación, normalmente esperaríamos que apareciera algún resultado, alguna ventana con las diferencias encontradas, etc. pero no es así. Tendremos que ir al historial del agente de distribución donde podremos encontrar (cuando termine, que en este caso es instantáneo al tratarse de una tabla muy pequeña) el resultado:

Es importante que validemos que realmente el fallo es real o no lo es. De hecho, el propio mensaje ya nos indica con el uso del “might be” que existen posibilidades de falsos positivos. La razón es que cuando comparamos los checksums y el número de filas entre las tablas si se trata de un sistema “vivo” con datos replicándose con frecuencia es bastante normal que los conteos y/o checksums puedan no coincidir temporalmente sin que sea un problema. Es recomendable lanzar estas validaciones en los periodos de menor actividad del sistema, de madrugada habitualmente, para minimizar la probabilidad de falsos positivos.

Desgraciadamente tampoco se provee de ningún método sencillo para poder solucionar estas diferencias por lo que debemos solucionarlas de forma manual, por ejemplo, realizando un update en uno de los nodos al valor que consideremos correcto. En nuestro caso vamos a preservar los valores de replicaA:

-- insert-insert manual resolution of divergence, update row on ReplicaB with values from ReplicaA

SET XACT_ABORT ON

BEGIN TRAN
UPDATE ReplicaB.dbo.replica SET data='dataReplicaA',ID2=12345 WHERE ID=3
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Una posible alternativa a este trabajo manual sería utilizar una herramienta de terceros para comparar y generar los comandos de sincronización necesarios. También podemos usar una herramienta poco conocida y que viene de serie en SQL Server desde 2005 (si no me falla la memoria) llamada tablediff. Esta herramienta nos permite especificar una tabla origen y una tabla destino y obtener el fichero con las diferencias y los comandos necesarios para sincronizar las tablas. Vamos a volver a crear una situación de divergencia y lanzaremos la herramienta para ver qué resultado obtenemos:

-- insert-insert manual resolution of divergence, update one row

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (4,'dataReplicaA',1234567)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (4,'dataReplicaB',12345678)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Una vez desincronizados, lanzamos la herramienta indicando el servidor, base de datos y tabla tanto origen como destino:

El resultado es el siguiente script que deberemos lanzar contra replicaB:

-- Host: .\SQL2019
-- Database: [ReplicaB]
-- Table: [dbo].[replica]

UPDATE [dbo].[replica] SET [data]=N'dataReplicaA',[ID2]=1234567 WHERE [ID] = 4

Aunque configuremos que la réplica “ignore” ciertos errores y continúe replicando el resto, esto no significa que este error no quede registrado. Vamos a provocar un insert-insert distinto a los anteriores, por un conflicto con la columna ID2 que tiene una restricción UNIQUE:

-- insert-insert

SET XACT_ABORT ON

BEGIN TRAN
INSERT INTO ReplicaA.dbo.replica (ID,data,ID2) values (6,'dataReplicaA',11)
INSERT INTO ReplicaB.dbo.replica (ID,data,ID2) values (7,'dataReplicaB',11)
COMMIT TRAN

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

En este caso acabamos con dos filas con distinta PK pero que únicamente una de ellas está presente en cada nodo de la réplica.  En la tabla MSrepl_errors de la base de datos de distribución podemos encontrar los errores registrados:

Violation of UNIQUE KEY constraint ‘UQ__replica__C49703DDC15EC0A2’. Cannot insert duplicate key in object ‘dbo.replica’. The duplicate key value is (11).

Violation of PRIMARY KEY constraint ‘PK__replica__3214EC27F4D1F7F1’. Cannot insert duplicate key in object ‘dbo.replica’. The duplicate key value is (5).

Si por el contrario lanzamos un UPDATE en una de las filas que no están aun replicadas, forzaremos el código de error 20598, que en este caso estaría producido como “efecto secundario” de un conflicto previo, lo cual es algo que tendremos que tener en cuenta cuando analicemos el estado de la sincronización de nuestros datos.

-- Conflict due to missing row update

UPDATE ReplicaA.dbo.replica SET ID2=111 where ID=6

WAITFOR DELAY '00:00:10'

-- Check tables

SELECT * FROM ReplicaA.dbo.replica
SELECT * FROM ReplicaB.dbo.replica

Este proceso en el que se explica cómo se ha llegado al conflicto es lo que suelo denominar buscar la “historia” del conflicto ya que muchas veces no es tan obvia como pensábamos en un principio. Podemos tener una situación que sea fruto de una desincronización previa anterior no detectada. O bien causada por un error asimétrico, como por ejemplo una dependencia con otra tabla no replicada, o con otra base de datos, o con un linked server, o con un trigger que modifica otra tabla indirectamente, etc. Si hiciéramos el ejercicio de analizar uno a uno el resto de conflictos de distinta naturaleza al insert-insert veríamos que se pueden afrontar de forma similar. Por ejemplo, en el caso del update-update deberíamos tener en cuenta si queremos preservar los cambios introducidos en ambos nodos (hacer un merge de los registros) o bien mantener la fila completa de uno de los nodos lanzando un nuevo update con los valores a preservar.

Teniendo en cuenta todo lo anterior, aunque no tengamos detección automática de conflictos como en la P2P, es posible utilizar replicación transaccional bidireccional para un entorno multi master si añadimos un proceso que detecte que la réplica está “saltando errores” y disparar medidas automatizadas de detección/resolución de conflictos en caso necesario. No olvidemos que en el caso de la replicación P2P tenemos detección de conflictos automática pero la resolución es extremadamente básica, ya que solo nos permite “machacar” las filas en conflicto asumiendo que el cambio procedente del nodo con mayor prioridad (ID mayor) es el que queremos preservar. Si esto no es así, tendremos que intervenir de forma manual igualmente. Lo mismo ocurre en cualquier situación en la que queramos realizar algún tipo de “merge” de cambios entre los dos nodos, que tampoco nos ofrecerá la posibilidad por lo que en ambos casos tendremos trabajo extra a realizar por nuestra parte si nuestra base de datos no tiene un diseño 100% a prueba de conflictos (algo no habitual).

En general el proceso a implementar sería dependiente de cada entorno, no hay algo genérico que vaya a funcionar en todos, pero en líneas generales podría ser algo como esto:

  • Verificar periódicamente la tabla MSrepl_errors para detectar presencia de nuevos errores.
  • Detectar los códigos de error existentes y extraer (parsear) del mensaje de error la tabla y el ID afectado.
  • Obtener los datos de ambos nodos y analizar si tenemos un conflicto de tipo insert-insertupdate-update, etc.
  • Si es un caso habitual/conocido de nuestro entorno y donde la resolución de conflictos esperamos que sea automática y sea siempre la misma acción a tomar (como machacar siempre con los datos del nodo A) podríamos:
    • Auditar/respaldar los valores existentes en las tablas con dichos IDs.
    • Generar script que con los IDs realice las acciones necesarias para sincronizar teniendo en cuenta los efectos de la replicación (updatesinsertsdeletes, etc.).
    • Guardar en un log de “scripts de resolución de conflictos” el script a lanzar para tenerlo como referencia en caso de fallos.
    • Ejecutar el script.
    • Verificar pasados unos segundos que los datos están sincronizados para esos IDs.
  • En un caso no conocido/habitual podríamos:
    • Notificar mediante el envío de un mensaje de correo o similar al DBA.
    • Tomar acciones correctivas manuales.
    • Si nos ocurre múltiples veces, considerar que nos encontramos ante un nuevo caso habitual y plantearnos cómo abordaríamos su resolución automatizada.

Adicionalmente a este automatismo, sería conveniente que periódicamente validáramos que los subscriptores permanecen sincronizados lanzando una validación de la subscripción AtoB y de la BtoA. Esto nos permitiría detectar situaciones que no podríamos detectar de otras formas. Tenemos que tener claro que en cuanto entre un proceso de replicación en nuestro entorno, y más si es bidireccional, con posibilidad de conflictos, etc. estamos añadiendo una complejidad a nuestro entorno normalmente mayor a la que esperamos inicialmente. Dicho de otra forma, se suele infraestimar el impacto que puede llegar a tener desde el punto de vista de soporte así como los problemas causados indirectamente por la información desincronizada.

Además, el tamaño del problema causado por un conflicto o tener los datos desincronizados empeora con el tiempo. Un ejemplo que es muy claro es si nos planteamos el alta de un nuevo cliente en nuestro ERP. Si por algún problema tenemos un mismo cliente dado de alta con un ID distinto en cada nodo, si es un cliente que se dio de alta hoy, seguramente solucionarlo hoy es poco costoso. Si ese conflicto/desincronización se mantiene durante días o semanas ese cliente tendrá comerciales asociados, tendrá pedidos con sus líneas, sus facturas asociadas, etc. todos asociados a distintos IDclientes en cada nodo. Esto complicara mucho más el realizar la sincronización ya que no podremos simplemente eliminar un IDcliente ya que desearemos reasociar sus pedidos (eliminando duplicidades quizás) ajustar la contabilidad de los dos clientes en únicamente uno, etc. Es posible que incluso para ciertas operaciones críticas de datos maestros como el alta de clientes, la modificación de sus datos, etc. podamos decidir utilizar únicamente uno de los dos nodos, no funcionando en modo multi-master, para así no correr con el riesgo de sufrir estos problemas.

En conclusión, las arquitecturas que incluyen replicación de datos con múltiples nodos de multi-master pueden ser una solución a ciertos problemas, como aportar escalabilidad horizontal de la carga de escritura, pero a cambio añaden complejidades respecto a la consistencia de los datos. En otros modelos no relacionales muchas veces esta consistencia se desprecia o se deja de lado de forma que podemos escalar, pero a cambio podemos acabar con datos duplicados, registros hijos huérfanos apuntando a padres que ya no existen, etc. En algunos casos tras analizar las necesidades de replicación multi-master es posible que una replicación transaccional P2P sea una alternativa viable técnicamente pero no viable por coste (SQL Server Enterprise Edition). En estos casos hemos es posible utilizar replicación transaccional bidireccional a cambio de una mayor complejidad a la hora de detectar y resolver los futuros conflictos por lo que es crítico que éstos sean estadísticamente poco probables y sea su resolución mediante operaciones complementarias una opción viable.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

 

Honeypot: un SQL Server desprotegido en Azure

Honeypot: un SQL Server desprotegido en Azure

Un Honeypot es un sistema el cual se expone de forma intencionalmente a un riesgo para observar cual sería el comportamiento “on the wild” en el caso de un sistema real desprotegido.

Creamos un Honeypot de un SQL Server al que se expone su puerto 1433 a Internet y además se utiliza una cuenta SA/sysadmin con un password sencillo.

Comenzaremos creando una máquina virtual con SQL Server 2019 a la que configuraremos conectividad pública, en el puerto 1433 y con autenticación de SQL Server:

Una vez tenemos la máquina creada, vamos a configurar que la información de nuestros logs se almacene en una cuenta de almacenamiento. Para ello comenzaremos creando un Log Analytics workspace (si no tenemos uno ya creado) y asociaremos nuestra VM:

A continuación habilitaremos el guest-level monitoring:

Durante todo este proceso, y en general con Azure, hay que tener algo de paciencia, ya que pueden pasar algunos minutos desde que realizamos la creación de un recurso y podemos utilizarlo. Seleccionaremos todos los logs de momento, para tener el máximo de información accesible desde fuera de la máquina, por si perdemos el control de ésta tras el ataque:

Es importante también asegurarnos que las extensiones finalizan su instalación correctamente. Especialmente si estamos usando una VM pequeña el tiempo puede ser elevado:

A continuación, conectaremos a la máquina y para “ablandar” algo más la seguridad y haremos algo que desgraciadamente muchas veces observamos en clientes: ejecutar el servicio con una cuenta con más permisos de los necesarios. En este caso usaremos una cuenta con permisos de administración local de la máquina:

También crearemos una auditoría a nivel de instancia que guardaremos en el application log para aumentar la información que dejemos registrada:

En poco tiempo, a los pocos minutos de tener la máquina levantada y con el puerto 1433 expuesto en Internet, podremos ver que en el log de SQL Server ya tenemos intentos de conexión a la cuenta de SA:

La gran mayoría de estos ataques vienen de China o Rusia:

Una vez hecho todo esto, comprobaremos que tenemos acceso a los logs externamente a la máquina desde el portal, con el Storage Explorer sobre nuestra cuenta.

También podemos integrar los logs con Azure Monitor tras asignarle una identidad de sistema en nuestro Azure AD:

También crearemos una base de datos con una tabla de nombre sugerente en la instancia, por si hay algún intento interactivo de conexión:

Y ya por último solo nos faltará “abrir la veda” activando la cuenta SA y poniendo un password sencillo, utilizando alguno de los valores habituales y que no deberíamos utilizar como sa, admin, P@ssw0rd, etc. A los pocos minutos de aplicar el cambio, recibimos nuestro ataque, cuya parte inicial podemos ver en una traza de profiler, hasta que ésta se cierra (por efecto del propio atacante que cierra todas las trazas que estén ejecutando):

Podemos ver cómo se eliminan varios procedimientos almacenados de sistema, se registran contra otras DLLs y se lanza este script para configurar y habilitar ole automation, xp_cmdshell así como cerrar todas las trazas que estuvieran abiertas:

EXEC sp_configure ‘show advanced options’,1;RECONFIGURE WITH OVERRIDE;EXEC sp_configure ‘xp_cmdshell’,1;RECONFIGURE WITH OVERRIDE;eXEC SP_CONFIGURE ‘SHOW ADVANCED OPTIONS’,1;RECONFIGURE WITH OVERRIDE;EXEC SP_CONFIGURE ‘SHOW ADVANCED OPTIONS’,1;RECONFIGURE WITH OVERRIDE;EXEC SP_CONFIGURE ‘DEFAULT TRACE ENABLED’,0;RECONFIGURE WITH OVERRIDE;EXEC sp_configure ‘Ole Automation Procedures’,1;RECONFIGURE WITH OVERRIDE;exec sp_configure ‘Agent XPs’, 1;RECONFIGURE WITH OVERRIDE;DECLARE @i INT,@Size INT;SET @i=1;SELECT @Size = MAX(traceid) FROM ::fn_trace_getinfo(default);WHILE @i <= @Size BEGIN  EXEC SP_TRACE_SETSTATUS @i,0;  SET @i=@i+1; END;

También, se nos modifica el password de SA y dejamos de tener acceso a la instancia:

El ataque también crea una cuenta llamada Mssqla para mantener acceso sysadmin aunque volvamos a modificar la cuenta sa y reseteemos su password:

También vemos en las trazas que se están intentando lanzar comandos de sistema operativo desde jobs de SQL Agent:

La mayoría de los jobs han funcionado, ejecutando sus malignos contenidos:

Algunos de los jobs son de tipo “ofuscado” como este que os mostramos, donde se intenta ocultar un varchar dentro de un string en hexadecimal, una técnica bastante habitual:

Si convertimos byte a byte nos aparece el siguiente código que es el que realmente se ejecuta:

No es el objetivo entrar en detalle en este script pero podríamos decir que el resumen de ese script es que “no hace nada bueno”, se conecta a servidores externos, descarga scripts, registra librerías sospechosas, etc.

Como la cuenta de SQL tiene permisos de administrador, el ataque ha instalado varios troyanos en la máquina sin dificultad:

En este tipo de situaciones realizar una «limpieza» del servidor suele ser la peor de las alternativas a largo plazo. Nunca tendremos la certeza al 100% que todo queda limpio y no queda afectada alguna librería, servicio, etc. que pueda volvernos a comprometer en un futuro.

Por tanto llegados a este punto el siguiente paso lógico sería salvar la información que podamos necesitar del servidor comprometido, preferiblemente de una forma offline y no conectando con el servidor para copiar ficheros vía SMB, etc. La razón es que si hacemos esto estamos dando más posibilidades al malware para intentar extenderse a otros equipos mediante esa vía. También en este caso alguno de los malware incluidos añaden un keylogger, por lo que cualquier usuario o password que escribamos una vez conetados a la máquina infectada acaba en manos del atacante y podríamos estar dándole acceso a otros sistemas. Por tanto, lo mejor es extraer de forma offline la información (ojo con los ficheros de naturaleza «infectable», como ejecutables, ficheros Office, etc.) y destruir el servidor totalmente al finalizar:

La conclusión de esta prueba es que realmente «ahi fuera» hay muchos scanners y herramientas automatizadas lanzando ataques de forma contínua. El que nuestra máquina esté en un proveedor cloud no nos protege de este tipo de ataques, podría protegernos de algún ataque de tipo DoS, pero un intento de conexión puntual no va a estar «cortado» por defecto si nos hemos expuesto innecesariamente con una IP pública. Incluso aunque no usemos acceso externo, hay ciertos riesgos si permitimos el acceso a los servicios de Azure. La propia Microsoft recomienda que desactivemos esta opción para reducir la superficie de ataque:

https://docs.microsoft.com/en-us/azure/azure-sql/database/security-best-practice#minimize-attack-surface

Sin embargo, vemos que hay muchos clientes que para poder utilizar servicios como Azure Data Sync u otros como PowerBI (de forma sencilla, sin crear un on-premise gateway pero desplegado en Azure en una VNET para desde ahi asegurar el acceso) acaban teniendo esta opción habilitada. Esto abre la posibilidad a muchos atacantes que tengan una subscripción de Azure accesible a intentar atacar a nuestros servidores a través de servicios de Azure comprometidos. Idealmente deberiamos poder limitar que los servicios solamente pudieran acceder si forman parte de nuestra subscripción y tener incluso una lista de ellos para tener más granularidad de a cual/cuales dejamos acceso, pero es ciertamente complejo debido a la naturaleza SaaS (y con recursos compartida entre clientes) de muchos de estos servicios cloud. Algo parecido nos puede pasar si tenemos que dar acceso externo a terceras partes, a un proveedor, a un cliente, etc. donde una falla de seguridad en sus sistemas puede acabar afectándonos indirectamente a los nuestros.

Muchos habréis visto en noticias recientes ataques de Ransomware que han afectado a empresas grandes como Garmin, Canon, ADIF, Mapfre etc. Debemos concienciarnos que el grado de profesionalización y complejidad de los ataques se va incrementando con el tiempo y con ello el riesgo de que acabemos siendo un objetivo de los hackers. En nuestro mundo donde los datos son la fuente de muchas decisiones del día a día de negocio que dejen de estar accesibles para nuestra organización puede causar un gran perjucio. Además puede generar un gran problema de imagen corporativa o implicar sanciones por el no cumplimiento de la legislación de protección de datos si acaban siendo estos datos expuestos públicamente. Por tanto, es vital que mantengamos un control férreo sobre nuestros datos, tanto desde el punto de vista de la seguridad en el acceso, de su encriptación como de sus respaldos offsite para los casos más graves que requieran poner en marcha nuestro plan de recuperación ante desastres.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

 

 

Manejo automático de permisos de usuario en Power BI Report Server a través de SSIS

Manejo automático de permisos de usuario en Power BI Report Server a través de SSIS

Power BI Report Server (PBRS) es un servidor de informes local con un portal web en el que se muestran y administran informes y KPI. Junto con él, se incluyen herramientas para crear informes de Power BI, informes paginados, informes móviles y KPI. Los usuarios pueden acceder a los informes de maneras diferentes: verlos en un explorador web, en un dispositivo móvil o como un correo electrónico en su bandeja de entrada.

El portal web de PBRS es una aplicación que se ejecuta en un explorador. Al iniciar el portal web, las páginas, los vínculos y las opciones que aparecen varían en función de los permisos que tenga cada usuario en el servidor de informes. Si está asignado a un rol con permisos completos, tiene acceso al conjunto completo de menús y páginas de la aplicación para administrar un servidor de informes; en cambio, si está asignado a un rol con permisos para ver y ejecutar informes, el usuario solo verá los menús y páginas que necesite para esas actividades. Se pueden tener distintas asignaciones de roles para distintos servidores de informes, o incluso para los distintos informes y carpetas en un único servidor.

La asignación de roles puede ser tarea sencilla si se trata de una pequeña cantidad de usuarios, pero para organizaciones con un gran número de usuarios puede ser una tarea muy laboriosa; más aún cuando, en el funcionamiento normal de una organización, se añadirán nuevos roles a nuevos usuarios, se quitarán permisos a otros o llegarán a modificarse estos permisos con el paso del tiempo.

Para modificar estas asignaciones de roles, entre otras cosas, PBRS admite API de transferencia de estado representacional (REST). Las API REST son puntos de conexión de servicio que admiten un conjunto de operaciones HTTP, logrando así crear, recuperar, actualizar o eliminar el acceso a los recursos de un servidor de informes.

Para automatizar este proceso, en este artículo vamos a crear un paquete de Microsoft SQL Server Integration Services (SSIS), el cual consumirá los datos de usuario y permisos asignados de una tabla que tendremos en SQL Server y, a través de un script de PowerShell, actualizará o eliminará los permisos asignados a cada usuario de forma automática.

 

Creación del paquete de SSIS

 

Microsoft SQL Server Integration Services es una plataforma que nos permite generar soluciones de integración de datos de alto rendimiento, entre las que se incluyen paquetes de extracción, transformación y carga de datos para el almacenamiento de datos. SSIS incluye herramientas gráficas y asistentes para generar y depurar paquetes o tareas para realizar funciones de flujo de datos, entre otras.

En nuestro caso, necesitaremos principalmente dos módulos de SSIS, uno para extraer los datos de la tabla que tenemos creada en SQL y otra para pasar esos datos a un script de PowerShell, el cual conectará con PBRS e irá mandando las solicitudes de cambio de permisos.

En este artículo partimos de una pequeña tabla, formada únicamente por cuatro usuarios a modo de demostración, aunque podríamos tener cualquier número de usuarios. La tabla consta de cuatro columnas: nombre de usuario, nombre de la carpeta, nombre del archivo y permiso concedido. En caso de tener el valor Carpeta en el campo ObjectName, se asume que el permiso se concede a la carpeta completa; en cambio, cuando tengamos el archivo concreto especificado, el permiso se concederá únicamente al archivo especificado.

 

 

Primero, vamos a conceder permisos a todos los usuarios, pero después modificaremos esta tabla para quitar algunos de estos permisos a los usuarios y ver cuál es el comportamiento de nuestro paquete de SSIS.

En primer lugar, deberemos establecer la conexión entre nuestro servidor de SQL Server y el paquete de SSIS, para ello crearemos un nuevo agente de conexión, el cuál apuntará directamente a nuestra base de datos (en este caso el nombre de la base de datos es Prueba).

 

 

El siguiente paso será utilizar el módulo Tarea Ejecutar SQL para, mediante una consulta simple, extraer los datos de la tabla y devolvernos un objeto conteniéndola.

 

 

Una vez añadido el módulo, dentro de la configuración general deberemos establecer la conexión, introducir la consulta que vamos a hacer a la base de datos y, por último, debemos especificar que el módulo debe devolver el conjunto de resultados completo.

 

 

Por último, en la pestaña Result Set, deberemos especificar el nombre que va a tener la variable que contenga el objeto con la tabla:

 

 

Con esto ya tendríamos nuestra tabla como variable dentro del paquete de SSIS, pero no podemos pasar la tabla completa a un script, por lo que necesitamos primero introducir un Foreach Loop Container para iterar por cada una de las filas e ir ejecutando el script sobre ella.

 

 

En cuanto a la configuración del módulo, en primer lugar, en la pestaña Collection, deberemos especificar el enumerador que vamos a utilizar, en este caso será un Enumerador de ADO para Foreach, y deberemos apuntar a la variable de salida del módulo anterior. Por último, en la configuración del enumerador deberemos elegir la opción Rows in the first table.

 

 

Por otro lado, en la pestaña Variable Mappings debemos mapear cada una de las columnas que contiene nuestra tabla.

 

 

Una vez configurado el bucle, solo nos quedaría el script de PowerShell para modificar los permisos de cada usuario. A este script tendremos que ir pasándole, en cada iteración del bucle, cada una de las columnas de la tabla en forma de parámetros; para esto tendremos que, por un lado, crearnos el script de PowerShell, para dentro del módulo lanzar el script y pasarle como argumentos cada una de las variables. Para esto utilizaremos el módulo Ejecutar Proceso de SSIS.

Dentro de la configuración del módulo, podemos definir la ruta del ejecutable y los argumentos directamente en la pestaña Process, pero otra opción es pasar el comando completo de ejecución a través de una expresión, dentro de la pestaña Expressions. Dentro de la pestaña crearemos una nueva expresión como la siguiente:

 

 

Los valores con @ se corresponden con las variables de SSIS y los valores con guion con las variables que entrarán en PowerShell. También hay que tener cuidado con los espacios ya que la expresión completa resultante será una cadena de texto.

Con esto ya tendríamos configurado todo lo necesario dentro de SSIS, pero nos falta el script de PowerShell que modificará los permisos, para ello utilizaremos el siguiente código:

 

# Parameters

param(
    [string]$UserName,
    [string]$FolderName,
    [string]$ObjectName,
    [string]$Permission
)

$server = 'localhost'
$ReportPortalUri = 'http://localhost/Reports';
$session = New-RsRestSession -ReportPortalUri $ReportPortalUri;
$name = $env:COMPUTERNAME

# Change permissions in PBRS

if($Permission -eq "Sin permisos")
{
    try
    {
        if($ObjectName = "Carpeta")
        {
Revoke-RsCatalogItemAccess -Identity ($name + "\" + $UserName) -     Path ("/" + $FolderName)
        }
        else
        {
Revoke-RsCatalogItemAccess -Identity ($name + "\" + $UserName) -Path ("/" + $FolderName + "/" + $ObjectName)
        }
    }catch{}
}
else
{
    try
    {
        if($ObjectName = "Carpeta")
        {
Grant-RsCatalogItemRole -Identity ($name + "\" + $UserName) -RoleName $Permission -Path ("/" + $FolderName) -Strict
        }
        else
        {
Grant-RsCatalogItemRole -Identity ($name + "\" + $UserName) -RoleName $Permission -Path ("/" + $FolderName + "/" + $ObjectName) -Strict
        }
    }catch{}
}

 

 

En el script, en primer lugar, definimos los parámetros de entrada que van a entrar como argumento con formato string, después establecemos la conexión con nuestro Servidor de Informes de Power BI y por último añadimos la lógica a partir de la cual se cambiarán los permisos, utilizando la librería ReportingServicesTools para facilitar el proceso.

 

Ejecución del paquete sobre Power BI Report Server

 

Con esto ya tendríamos nuestro paquete de SSIS completo y listo para ejecutar, así que vamos a probarlo. Para ello simplemente pulsaremos en ejecutar, en la interfaz de SSIS. Una vez haya finalizado el aspecto será el siguiente si todo ha ido bien:

 

 

Una vez ejecutado, podemos ir a PBRS y comprobar los resultados:

 

 

Por ejemplo, para el Informe4, dentro de la Carpeta3, se han añadido justamente los permisos que hemos especificado en la tabla de SQL Server.

Por último, una vez añadidos todos los permisos, vamos a probar a quitar algún permiso, para ello, solo tenemos que cambiar el valor de Permission de la tabla de SQL Server por Sin permisos para el caso en el que los queramos quitar.

 

 

Al volver a ejecutar el paquete de SSIS, veremos que dentro de la carpeta Carpeta3, en el archivo Informe4, el usuario Prueba4 ya no tendrá permisos asignados.

 

Si quieres dar forma a tu proyecto con Power BI, en SolidQ podemos guiarte en el proceso (mentoring), ayudarte a su desarrollo mediante nuestro framework, así como formarte en aquellas áreas que necesites. Consulta todos nuestros cursos de 0 a experto con Power BI, desde nuestro curso de Power BI para usuarios de negocio hasta formación más avanzada como DAX, Data Governance o Power Query.

Algo conocido, pero no utilizado, el comando DBCC CHECKDB

Algo conocido, pero no utilizado, el comando DBCC CHECKDB

Desde SolidQ solemos realizar chequeos de las instancias de bases de datos de muchos clientes (grandes, medianos y pequeños) y seguimos encontrándonos servidores de SQL donde no se realiza la tarea de chequeo de base de datos. Por otro lado, se piensa que cuando subimos nuestras bases de datos a la nube esta tarea ya no es necesaria. Por lo que he pensado en crear esta entrada de blog para recordar la importancia de esta tarea.

Microsoft SQL server tiene varios métodos de verificación de las páginas de datos para comprobar que no hay ningún tipo de corrupción, vamos a recordarlos:

Verificación de página (CHECKSUM)

CHECKSUM es una opción para la verificación de la página que está disponible individualmente para cada base de datos.  Podemos ver qué nivel de verificación utiliza actualmente cada una de nuestras bases de datos mediante la siguiente consulta:

SELECT name, page_verify_option_desc FROM sys.databases

En la mayoría de los casos, notaremos que la opción verificación de página está configurada en CHECKSUM y eso es porque hoy en día esta es la opción predeterminada para todas las bases de datos. Para las versiones anteriores a SQL 2005, este no era el caso, por lo que ocasionalmente, al revisar instancias de bases de datos que se han migrado de versiones anteriores, podríamos ver las otras opciones de Verificación de páginas: TORN PAGE y  NONE.

IMPORTANTE
En motores SQL superiores a 2005 todas las bases de datos deben tener la opción de verificación de página en CHECKSUM.

DBCC CHECKDB

Una idea común con respecto a la opción CHECKSUM que se tiene, es que reemplaza la necesidad de comprobación de coherencia de las bases de datos (CHECKDB). Esto es incorrecto; CHECKSUM solo informa en la operación de lectura de una página incoherente, si la página no se lee, no se informa del error. Sin embargo, DBCC CHECKDB realiza una verificación mucho más exhaustiva a nivel de la base de datos e incluye comprobaciones que un CHECKSUM simplemente no cubre, por lo tanto, la mejor práctica es combinar la verificación de la página CHECKSUM y las comprobaciones de consistencia regulares.
DBCC CHECKDB comprueba la integridad física y lógica de todos los objetos de la base de datos especificada mediante la realización de las siguientes operaciones:

  • Ejecuta DBCC CHECKALLOC en la base de datos.
  • Ejecuta DBCC CHECKTABLE en todas las tablas y vistas de la base de datos.
  • Ejecuta DBCC CHECKCATALOG en la base de datos.
  • Valida el contenido de cada vista indizada de la base de datos.
  • Valida la coherencia de nivel de vínculo entre los metadatos de la tabla y los directorios y archivos del sistema de archivos al almacenar datos varbinary(max) en el sistema de archivos mediante FILESTREAM.
  • Valida los datos de Service Broker en la base de datos.

Esto significa que los comandos DBCC CHECKALLOC, DBCC CHECKTABLE o DBCC CHECKCATALOG no tienen que ejecutarse por separado de DBCC CHECKDB.

IMPORTANTE
Muchas veces también nos encontramos entornos donde no se lanza el chequeo de base de datos porque las bases de datos son muy grandes y la ventana de mantenimiento no es suficiente, en este caso, se recomienda utilizar la opción PHYSICAL_ONLY. El uso de PHYSICAL_ONLY puede reducir mucho el tiempo de ejecución de DBCC CHECKDB en bases de datos grandes.
He visto bases de datos corruptas funcionando durante años sin problemas, porque los datos que están dañados son tan antiguos que nadie está accediendo a ellos y solo son visible cuando ejecutas el DBCC CHECKDB. Es por eso que DBCC CHECKDB es tan crítico ya que interroga a toda la base de datos.

DBCC CHECKDB en AZURE

En Azure tenemos dos tipos de servicios: PaaS e IaaS.

Dentro de IaaS la opción que tenemos de SQL es instalar SQL en una máquina virtual, en este caso Microsoft no va a gestionar lo que hay instalado dentro de la máquina virtual por lo que queda en nuestro debe realizar los chequeos de las bases de datos.
Para la parte de PaaS Microsoft nos ofrece dos sabores:
Azure SQL , aquí Microsoft se encarga de monitorizar y chequear la base de datos, en cuanto a las operaciones de chequeo que realiza hay una entrada en un blog que nos lo explica https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/. Aun así, yo sigo recomendando que hay que lanzar de forma periódica un DBCC CHECKDB sobre estas bases de datos.
Instancia Manejada, aquí Microsoft te gestiona y monitoriza el motor relacional pero lo que no hace es chequear la integridad de las bases de datos, por lo menos a día de hoy, entonces, también es necesario ejecutar periódicamente el comando DBCC CHECKDB.

 

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

¿Otro caso más asociado al parameter sniffing? ¿Es el recompile realmente una bala de plata?

¿Otro caso más asociado al parameter sniffing? ¿Es el recompile realmente una bala de plata?

En muchas ocasiones tendemos a simplificar nuestros esquemas mentales con la finalidad de aplicar estrategias generalistas para tratar de abordar un número potencialmente infinito de problemas. Esta simplificación es necesaria para ser más productivos y a la vez que nuestra mente sea capaz de tratar problemas desconocidos, pero a su vez puede traernos errores en casos concretos. Es lo que en general muchas veces ocurre cuando se aplican “buenas prácticas” o “reglas de oro”, etc. y no obtenemos el resultado esperado ya no son en realidad una “bala de plata” que mágicamente pueda solucionar todo lo que se nos ponga por delante.

En este post vamos a ver un caso concreto donde probablemente nuestro primer impulso para abordarlo no sea el correcto.

Imaginemos que tenemos el siguiente procedimiento en la base de datos AdventureWorks2017:

CREATE PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    DROP TABLE #Temp;
END;

Básicamente lo que hace este procedimiento es extraer un conjunto de cabeceras de venta que están asociadas cuentas con un prefijo concreto. Una vez tenemos dichas cabeceras en una tabla temporal cruzamos con el detalle y contamos cuantos productos distintos tenemos en dichas ventas.

Si ejecutamos este procedimiento con un par de valores distintos observamos algo que llama la atención en los planes de ejecución:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Podemos ver que la tabla temporal, que acabamos de llenar con datos, muestra una estimación incorrecta en el segundo de los casos. En este caso el impacto en el plan no es grande, pero podría serlo en otros casos, necesitar pasar de un nested loop a un hash join, etc.

Normalmente cuando tenemos este tipo de problemas pensamos de forma rápida… parameter sniffing de nuevo… el coste de compilar será despreciable…  vamos a añadir un recompile a la consulta y “asunto solucionado”:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber
  OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Y efectivamente podemos comprobar que la estimación respecto al número de filas que tenemos en la tabla temporal, se soluciona con este cambio y en la segunda ejecución tenemos una estimación exacta de 108 filas que son las que realmente tenemos:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Pero no cantemos victoria tan rápidamente.  Imaginemos que lo que hacemos es algo ligeramente más complicado y aplicamos un filtrado sobre esas filas de la tabla temporal. En este caso vamos a realizar un filtrado un poco “tonto” , pero que muestra claramente el problema, consistente en añadir el mismo filtro que tenemos previamente (AccountNumber like @AccountPrefix + ‘%’).

Es decir, vamos a añadir el filtro que ya usamos para cargar la tabla en la consulta posterior, lo cual no debe afectar al número de registros ni al resultado ya que es a efectos prácticos redundante, debería devolver las mismas filas:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
WHERE
    T.AccountNumber like @AccountPrefix + '%'
    GROUP BY
        T.AccountNumber
  OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Una vez hecho esto, volvemos a ejecutar los dos procedimientos y comparamos lo que obtenemos en la estimación de ese scan de la tabla temporal:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Como podemos ver ahora, la estimación en la segunda ejecución es de 1 fila, y no de las 108 que teníamos antes cuando en realidad el número de filas que tenemos y el número de filas que se han insertado en la tabla temporal es el mismo. Hemos mantenido también a “nuestro amigo” el RECOMPILE en su lugar, por lo que aparentemente algo extraño nos está pasando.

No sería raro que en este momento pensáramos… algo no está bien con el recompile, voy a borrar la caché de procedimientos y ejecutar el segundo caso, “asegurándome que compila” si o si a la fuerza para ver cómo se comporta para ese valor de parámetro:

DBCC FREEPROCCACHE 
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Efectivamente el problema “desaparece”, tenemos nuestra estimación precisa de 108 como queríamos. Si a continuación ejecutamos el procedimiento con el primer parámetro nos encontramos con el problema pero en “sentido contrario”:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

Convencidos que la recompilación no está “haciendo su trabajo como debe” decidimos subir el nivel de la recompilación a nivel de módulo, de procedimiento almacenado completo, modificando el procedimiento de la siguiente forma:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
  WHERE
    AccountNumber like @AccountPrefix + '%'
    GROUP BY
        T.AccountNumber
  --OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Volvemos a ejecutar el procedimiento y nos encontramos que ya todo funciona según lo esperado: obtenemos una estimación correcta tanto si ejecutamos en un orden o en otro. En base a esto concluimos que lo que teníamos era un problema de parameter sniffing y de una recompilación algo tozuda/perezosa  a nivel de statement que hemos tenido que “subir” a nivel de procedimiento almacenado para que fuese efectiva:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Ahora bien, ¿qué es lo que está ocurriendo en realidad? ¿Estamos en lo cierto? Pues en realidad no, lo que ocurre es algo bastante distinto y que puede considerarse un efecto colateral de una optimización de SQL Server encargada de cachear tablas temporales.

Comenzaremos volviendo el procedimiento a su versión original, sin recompiles ni nada, y simplemente mostraremos el object_id asociado a la tabla temporal que estamos usando:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    DROP TABLE #Temp;
END;
EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Podemos ver que el object_id es el mismo lo cual implica que el objeto no fue desasignado y recreado desde cero entre ejecuciones, se está reutilizando exactamente el mismo objeto entre dos ejecuciones distintas de un mismo procedimiento. En este momento podríamos pensar que esto no es posible, qué ocurriría en caso de concurrencia, etc. Vamos a añadir un waitfor de 10 segundos antes del drop de la tabla temporal y ejecutaremos concurrentemente en dos sesiones las llamadas al procedimiento:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    WAITFOR DELAY '00:00:10'

    DROP TABLE #Temp;
END;

Podemos ver que, al ejecutar, obtenemos dos object_id distintos en cada sesión:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

Podemos pensar equivocadamente que es un efecto secundario de lanzar en la misma sesión el mismo procedimiento almacenado. Si volvemos a lanzar esta segunda ejecución sin concurrencia con la primera (desde la misma sesión donde obtuvimos el -1277356580) podemos ver como obtenemos ahora el ID -1261356523 (que era el que obtuvo la otra sesión previamente) por lo que el ámbito de reutilización no es la sesión, es global:

En realidad, lo que tenemos es una caché de objetos temporales y, dado un mismo “template” de una tabla, podemos tener N copias distintas disponibles que se reutilizarán por los procedimientos de forma indistinta según se soliciten. El objetivo de esta caché no es otro que reducir el coste de creación y destrucción de objetos temporales y aumentar el rendimiento con ello.

Ahora que sabemos que estamos compartiendo a veces un mismo objeto temporal entre ejecuciones, creado por otra ejecución previa, tenemos que considerar otras opciones. Es decir, ¿cuál es la razón real por la que teníamos la estimación incorrecta cuando indicábamos RECOMPILE a nivel de statement solo si añadíamos el filtro WHERE redundante a la consulta?

La razón de fondo la encontramos en las estadísticas asociadas a dicho objeto, que no son “limpiadas” entre ejecuciones de los procedimientos, únicamente se eliminan los contenidos de la tabla. Personalmente creo que podría haber sido adecuado que el proceso de limpieza del motor eliminara también las estadísticas para evitar que podamos encontrarnos con esta situación “extraña”.

Vamos a modificar nuestro procedimiento para que muestre las estadísticas de nuestra tabla temporal:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

  DBCC SHOW_STATISTICS (N'tempdb.dbo.#Temp', AccountNumber) WITH HISTOGRAM;

    DROP TABLE #Temp;
END;

Si ejecutamos nuestras dos llamadas vemos que el histograma de las estadísticas es exactamente el mismo pese a que los datos insertados son distintos debido al filtrado basado en el prefijo:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Cuando añadimos el RECOMPILE lo que conseguimos fue que, cuando la consulta no tenía filtro, pudiéramos obtener una estimación real del número de filas. Sin embargo, cuando añadíamos el filtro no era suficiente con esta actualización del número total de filas, ya que al acceder el optimizador a la estadística para determinar cuántos valores tienen el prefijo “10-4030-0291” la respuesta eran 0, ya que cuando se creó dicha estadística únicamente teníamos valores del rango 10-4020 en la tabla temporal.

Cuando añadimos el WITH RECOMPILE al procedimiento almacenado lo que conseguimos fue no solamente recompilar sino deshabilitar la caché de tablas temporales para ese módulo, ya que la cláusula WITH RECOMPILE tiene ese efecto indirecto más allá de forzar la compilación. Otras razones típicas que inhabilitan la caché de tablas temporales son por ejemplo el realizar una operación DDL sobre dicha tabla tras su creación (añadir un índice, añadir una columna, etc.) o utilizar constraints con nombre explícito.

Una vez que hemos conseguido llegar al fondo del problema, a la causa raíz, podemos plantearnos como alternativa forzar una actualización de estadísticas para asegurarnos que no utilizamos una estadística “fantasma” heredada de una ejecución pasada y eliminar el RECOMPILE (mantendremos el object_id  y el show statistics). Para ello modificaremos la versión original a la que le añadimos el filtro where y añadiremos un UPDATE STATISTICS tras la carga de la tabla temporal:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
  UPDATE STATISTICS #Temp 
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

  DBCC SHOW_STATISTICS (N'tempdb.dbo.#Temp', AccountNumber)
    WITH HISTOGRAM;

    DROP TABLE #Temp;
END;

Al realizar las ejecuciones vemos lo siguiente:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Es decir, se está reutilizando el mismo objeto temporal cacheado (mismo object_id) pero gracias el UPDATE STATISTICS tenemos la estadística actualizada en cada ejecución con los datos correctos. Si revisamos las estimaciones de los planes de ejecución vemos lo siguiente:

Es decir, aunque hemos actualizado las estadísticas y los datos son correctos en ellas, el plan no se ha recompilado y seguimos utilizando la estimación de filas de la ejecución anterior (40). Es decir, cuando pensábamos que ya lo teníamos claro, resulta que aún falta profundizar algo más.

La razón por la que seguimos teniendo esa mala estimación es que la actualización de estadísticas no es una condición suficiente para invalidar el plan de la caché, además debe ocurrir que se exceda un threshold que en este caso al tratarse de una tabla temporal cacheada depende de la cantidad de filas que tenga la tabla cambie lo suficiente. Por tanto, en este caso, no está ocurriendo la recompilación al no cambiar lo suficiente la cantidad de filas que introducimos entre ejecuciones.

Si por ejemplo realizamos una llamada con un prefijo más amplio, obtendremos una recompilación y con ella cambiará la estimación y, en este caso, el plan en sí, pasando a ser paralelo, etc.:

EXEC sp_getDistinctProductsPerAccountPrefix '10-40'

Si después de ejecutar con ese parámetro, volvemos al anterior, el que manejaba 108 filas, nos encontramos que sí se recompila y se genera un nuevo plan con la estimación correcta, ya que el cambio en el número de filas (a la baja, pero un cambio al fin y al cabo) es suficientemente significativo como para forzar la recompilación sin tenerla que forzar con un OPTION RECOMPILE:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Si tras esta recopilación, ejecutamos el procedimiento con el parámetro que devolvía 40 filas, no es suficiente (viniendo del de 108) como para disparar esta recompilación de nuevo por lo que mantendríamos la estimación de 108:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

El problema de este tipo de comportamientos es lo complicado que puede ser, en un sistema productivo en vivo, donde tenemos multitud de ejecuciones concurrentes del mismo procedimiento, con distintos parámetros, generándose recompilaciones aleatoriamente, etc.  Encontrar la razón por la que algunas ejecuciones funcionan mejor o peor y solo a veces. Al final necesitamos capturar qué plan se está usando en cada caso, lo cual es costoso, además de saber si hemos recompilado en esa ejecución o no y si las estadísticas que usamos están actualizadas o son “heredadas” de otra ejecución.

Por tanto, en este tipo de situaciones debemos plantear qué posible solución sería menos costosa para nosotros. Una opción sería sopesar el coste de actualizar la estadística cada vez y recompilar cada vez la consulta que utiliza dicha estadística ya que si es pequeño puede ser la opción más segura.

Otra opción sería invalidar la caché de tablas temporales para esa tabla por ejemplo añadiéndole una constraint (aunque no limite nada en realidad), o quizás plantear el uso de variables de tipo tabla en vez de tablas temporales. También podríamos intentar utilizar un procedimiento almacenado “proxy” que llame a N procedimientos distintos en función de los parámetros, de forma que aquellos que necesiten “planes distintos” basados en estimaciones distintas, etc. se ejecuten por procedimientos distintos, cada uno con su plan de ejecución diferenciado, etc.

También se le podría dar una vuelta de tuerca más y seguir un enfoque similar a los procedimientos de tipo “búsqueda dinámica” donde acabemos componiendo un “template dinámico” en forma de consulta parametrizada. Esta consulta tendría que tener algún “token” diferenciador en función no solo de los parámetros introducidos, sino también del valor de dichos parámetros para que se generara un plan específico para cada caso.

Por último, solo nos queda soñar con que algún día SQL Server implemente alguna funcionalidad que permita no solo protegernos de regresiones en planes de ejecución “peores” de forma global sino tener planes múltiples “por parámetro”. Es decir, abrir la posibilidad de tener N planes de ejecución distintos que se adapten a los distintos valores de los parámetros que podamos suministrar a nuestros procedimientos almacenados de forma que se utilice aquél que sea más eficiente para cada caso.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

Azure Data Lake y PolyBase

Azure Data Lake y PolyBase

Con cierta frecuencia nos vamos encontrando con escenarios donde tenemos la necesidad de acceder a datos no estructurados desde nuestras instancias SQL Server. En Azure podemos tener almacenada información no estructurada en varios sabores, pero los más habituales para el almacenamiento masivo son blog storage, Azure Data Lake Gen1 y Azure Data Lake Gen2.

En este post vamos a centrarnos en un escenario particular donde necesitamos acceder a datos que están en un Azure Data Lake desde nuestra instancia de SQL Server. Desde SQL Server 2016 podemos acceder con Polybase a Azure Data Lake pero el verdadero potencial por rendimiento se encuentra en el uso de Azure Data Lake Gen2. Por diseño Gen 2 aporta un mayor throughput que con la Gen v1 por lo que es ideal para estos escenarios con datos masivos:

data lake polybase

Azure Data Lake Gen2 incorpora distintas APIs para acceder a los datos, una buscando la compatibilidad con sistemas legacy, que utilizan un endpoint de tipo blob y por otra parte aplicaciones que necesitan un endpoint de tipo DFS:

data lake

En nuestro caso, lo que queremos analizar es el rendimiento que nos puede proporcionar este tipo de accesos versus el que podríamos obtener si almacenáramos estos ficheros en otras alternativas. Compararemos el rendimiento de las mismas operaciones sobre el datalake con el que tendríamos tras un proceso de carga tradicional cuyo destino sean tablas en SQL Server.

Para la configuración de la máquina virtual de test elegiremos una que soporte accelerated networking, concretamente una máquina basada en el procesador EPYC 7452 una máquina de tipo E4s:

Para la cuenta de storage crearemos una nueva cuenta y nos aseguraremos de usar namespaces jerárquicos:

Una vez tengamos la cuenta creada, crearemos un nuevo contenedor:

test polybase data lake

Con el contenedor ya creado, cargaremos los datos que queremos utilizar para la prueba. En mi caso he elegido los datasets públicos del servicio de taxis de Nueva York disponibles aquí: https://archive.org/details/nycTaxiTripData2013

Una vez descargados y descomprimidos los subiremos, por ejemplo, desde Azure Explorer al contenedor en un par de carpetas, trip_fare (~18GB) y trip_data (~27GB) con unos 173M de filas en ambos casos. Estos tamaños no son “nada del otro mundo” para el mundo Big Data pero sí son suficientemente grandes como para que podamos realizar algunas operaciones más o menos realistas y tomar tiempos:

Para poder realizar los tests utilizaremos SQL Server 2019 y debemos asegurarnos que durante la instalación seleccionamos la funcionalidad PolyBase ya que no viene seleccionada por defecto:

En este post no vamos a analizar la escalabilidad de Polybase, por lo que instalaremos una configuración standalone con una única instancia:

Si estáis interesados en la escalabilidad de PolyBase en este otro post podéis ver el funcionamiento scale-out de Polybase: Virtualización de datos con PolyBase scale-out

Una vez tenemos la instancia instalada debemos habilitar la funcionalidad de PolyBase:

-- Enable polybase
EXEC sp_configure 'polybase enabled', 1;
-- Restart services

Una vez habilitada debemos configurar la conectividad para Hadoop, que en nuestro caso corresponde con el valor 7:

-- Hadoop mode 7
EXEC sp_configure @configname = 'hadoop connectivity', @configvalue = 7;

El siguiente paso es lanzar un RECONFIGURE y reiniciar los servicios de SQL Server asociados (motor relacional y PolyBase):

RECONFIGURE WITH OVERRIDE

Una vez tenemos el servicio en marcha y configurado el siguiente paso es crear el credencial que vamos a utilizar para el acceso. En este caso vamos a utilizar directamente la key del storage (aunque ello no sea lo más recomendable en un entorno productivo ya que nos da acceso full control a todo):

CREATE DATABASE Polybase

USE PolyBase

-- If we have no master key, create one
CREATE MASTER KEY ENCRYPTION  BY PASSWORD='Pa$$w0rd'
GO
CREATE DATABASE SCOPED CREDENTIAL StorageKey
WITH IDENTITY = 'SolidQ', Secret = 'sIK5C4SLS6X2NJ9myxOIzSWfJJdbd1tbGUhwHnA9m2WysLpDUHwgYe9P3F/bSXauyveASVJ67v5gWbyTGNZ+nA==';
GO

Crearemos también un formato de tipo CSV así como un esquema para incluir en él las external tables que vamos a crear:

CREATE EXTERNAL FILE FORMAT CSV 
WITH ( 
    FORMAT_TYPE = DELIMITEDTEXT, 
    FORMAT_OPTIONS ( 
        FIELD_TERMINATOR = ','
    ) 
);

GO

CREATE SCHEMA ext;

A continuación debemos crear un external data source que apunte a nuestro data lake. Si intentamos utilizar el driver abfss nos encontraremos con un error indicándonos que no está soportado en esta versión de SQL Server (Si lo está en Azure Synapse Analytics) pero debería estar disponible en breve (si es cierto lo que dicen los rumores):

CREATE EXTERNAL DATA SOURCE NYTaxi_error
WITH (TYPE = HADOOP,LOCATION = 'abfss://polybase@testpolybase2020.dfs.core.windows.net/', CREDENTIAL = StorageKey );


CREATE EXTERNAL TABLE [ext].[Trip]
(
    
    medallion VARCHAR(255),
    hack_license VARCHAR(255),
    vendor_id VARCHAR(255),
    rate_code VARCHAR(255),
    store_and_fwd_flag VARCHAR(255),
    pickup_datetime VARCHAR(255),
    dropoff_datetime VARCHAR(255),
    passenger_count  VARCHAR(255),
    trip_time_in_secs VARCHAR(255),
    trip_distance VARCHAR(255),
    pickup_longitude VARCHAR(255),
    pickup_latitude VARCHAR(255),
    dropoff_longitude VARCHAR(255),
    dropoff_latitude VARCHAR(255)
)
WITH
(
    LOCATION = '/trip_data',
    DATA_SOURCE = NYTaxi_error,
    FILE_FORMAT = csv,
    REJECT_TYPE = value,
    REJECT_VALUE = 0
);

Utilizaremos por tanto la API tradicional para acceder a nuestros datos:

CREATE EXTERNAL DATA SOURCE NYTaxi 
with (  
      TYPE = HADOOP,
      LOCATION ='wasbs://polybase@testpolybase2020.blob.core.windows.net',  
      CREDENTIAL = StorageKey  
);  
GO

El siguiente paso es crear las external tables que nos permitan acceder a nuestros ficheros de texto desde T-SQL. En estos casos lo recomendable es ajustar cada tipo de datos correctamente columna a columna pero en este ejemplo vamos a optar por simplemente recuperarlos todos como texto de 255 caracteres, sin analizar las conversiones que serían necesarias para cada columna (enteros, flotantes, fechas y su formato, longitudes máximas por campo, etc.):

CREATE EXTERNAL TABLE [ext].[Trip]
(
    
    medallion VARCHAR(255),
    hack_license VARCHAR(255),
    vendor_id VARCHAR(255),
    rate_code VARCHAR(255),
    store_and_fwd_flag VARCHAR(255),
    pickup_datetime VARCHAR(255),
    dropoff_datetime VARCHAR(255),
    passenger_count  VARCHAR(255),
    trip_time_in_secs VARCHAR(255),
    trip_distance VARCHAR(255),
    pickup_longitude VARCHAR(255),
    pickup_latitude VARCHAR(255),
    dropoff_longitude VARCHAR(255),
    dropoff_latitude VARCHAR(255)
)
WITH
(
    LOCATION = '/trip_data',
    DATA_SOURCE = NYTaxi,
    FILE_FORMAT = csv,
    REJECT_TYPE = value,
    REJECT_VALUE = 0
);

CREATE EXTERNAL TABLE [ext].[Fare] 
( 
  medallion VARCHAR(255),
  hack_license VARCHAR(255), 
  vendor_id VARCHAR(255),
  pickup_datetime VARCHAR(255),
  payment_type VARCHAR(255),
  fare_amount VARCHAR(255),
  surcharge VARCHAR(255),
  mta_tax VARCHAR(255),
  tip_amount VARCHAR(255),
  tolls_amount VARCHAR(255),
  total_amount VARCHAR(255)
)
WITH
(
    LOCATION = '/trip_fare/',
    DATA_SOURCE = NYTaxi,
    FILE_FORMAT = CSV,
    REJECT_TYPE = value,
    REJECT_VALUE = 0
);

Una vez tenemos las tablas externas creadas comprobamos que podemos acceder al dato:

select top 10 * from [ext].[Trip]
select top 10 * from [ext].[Fare]

Las pruebas que vamos a realizar van a ser relativamente sencillas, conteos de registros, algunos agregados por tipos de tarifas, número de pasajeros, distancias de recorridos medias, etc.

Comenzaremos por el caso más sencillo, los conteos, que aún así generan un altísimo consumo de CPU:

set statistics io on
set statistics time on
select count(*) from [ext].[Trip]
-- CPU time = 6968 ms,  elapsed time = 189279 ms.
-- Real CPU time = ~189279*4= 757116 ms

select count(*) from [ext].[Fare]
-- CPU time = 4078 ms,  elapsed time = 136842 ms.
-- Real CPU time = ~136842*4= 547368 ms

Podemos ver cómo durante los conteos se satura la red, usando todo el ancho de banda disponible para el tamaño de máquina elegido:

Si pasamos a las consultas con los cálculos de medias, los agregados y las ordenaciones vemos que también saturaremos los 4 cores del servidor:

select avg(convert(int,passenger_count)*1.0) avg_passenger_count,avg(convert(float,trip_distance)) avg_trip_distance  
from[ext].[Trip]
-- CPU time = 4078 ms,  elapsed time = 728542 ms.
-- Real CPU time = ~728542*4= 2914168 ms

select count(*) total, payment_type
from [ext].[Fare]
group by payment_type
-- CPU time = 37031 ms,  elapsed time = 381609 ms.
-- Real CPU time = ~381609*4= 1526436 ms

select passenger_count,avg(convert(float,trip_distance)) avg_trip_distance 
from [ext].[Trip]
group by passenger_count
order by avg_trip_distance desc
--CPU time = 222344 ms,  elapsed time = 1354534 ms.
-- Real CPU time = ~1354534*4= 5418136 ms

La diferencia es que, al ejecutar estas consultas, aunque nos “comemos” los cuatro cores al 100% y mantenemos un tráfico de red más o menos constante durante toda la duración de la consulta de unos ~450 Mbps, no llegamos a saturar el ancho de banda de red por la mayor complejidad de las consultas:

En estas consultas podemos ver como en algunos casos todo el «peso» del proceso recae en PolyBase (aparece todo dentro de una remote query en el plan de ejecución):

Mientras que en otros cierta parte se realiza a posteriori, como la ordenación en este caso:

Esto hace que el consumo de CPU reportado por SQL Server sea muy inferior al real, ya que el proceso de PolyBase no reporta dicho consumo a través del plan de ejecución, por lo que tenemos que medirlo de forma externa, mediante una traza de Performance Monitor (por ejemplo).

A continuación, vamos a comparar estos tiempos con los tiempos que obtendríamos si volcáramos previamente estos datos en una tabla “normal” y con esa misma tabla pero con un índice columnar clustered. Mantendremos los tipos de datos tal cual, como texto, que no es lo ideal por rendimiento, pero para intentar no dar ventajas adiciones respecto a la extracción en texto. Tampoco utilizaremos discos premium, ya que los discos premium aumentarían el coste y una de las premisas de los data lake es poder almacenar información a un bajo coste.

select * into dbo.trip_heap from [ext].[Trip]
select * into dbo.fare_heap from [ext].[Fare]

select top 0 * into dbo.trip_cci from dbo.trip_heap
select top 0 * into dbo.fare_cci from dbo.fare_heap

create clustered columnstore index cci on dbo.trip_cci
create clustered columnstore index cci on dbo.fare_cci

insert into dbo.trip_cci select * from dbo.trip_heap
insert into dbo.fare_cci select * from dbo.fare_heap

Comenzaremos comparando los tamaños de las tablas, respecto al tamaño de los ficheros planos:

Podemos ver como el almacenamiento en un heap viene a ocupar lo mismo que el fichero de texto aproximadamente mientras que el columnar ocupa un 91% menos en el caso de la tabla fare y un 83% menos en el caso de la tabla trip, lo cual son diferencias muy significativas y que podrán tener un impacto importante en el rendimiento.

Una vez tenemos las tablas cargadas comenzamos testeando los heaps. Realizaremos las mismas operaciones, los conteos, los agregados, etc. que en el caso anterior. Comenzamos por los conteos:

-- Normal table, no compression
set statistics io on
set statistics time on
select count(*) from trip_heap
-- CPU time = 33703 ms,  elapsed time = 477926 ms.

select count(*) from fare_heap
-- CPU time = 30265 ms,  elapsed time = 240078 ms.

En este caso vemos que el consumo de CPU no es tan elevado, un conteo no es muy intensivo en CPU en realidad, y el coste de procesar la entrada/salida no es ni de lejos tan elevado como parsear un CSV:

data lake

Con las operaciones de agregado el consumo de CPU aumenta, pero en ningún caso llegamos a saturar la CPU:

select avg(convert(int,passenger_count)*1.0) avg_passenger_count,avg(convert(float,trip_distance)) avg_trip_distance  
from trip_heap
-- CPU time = 105375 ms,  elapsed time = 486219 ms.

select count(*) total, payment_type
from fare_heap
group by payment_type
-- CPU time = 123921 ms,  elapsed time = 224195 ms.

select passenger_count,avg(convert(float,trip_distance)) avg_trip_distance 
from trip_heap
group by passenger_count
order by avg_trip_distance desc
-- CPU time = 62499 ms,  elapsed time = 482598 ms.

Pasaremos ahora a realizar las mismas operaciones sobre las tablas en formato columnar. En el caso de los conteos los resultados son prácticamente instantáneos por la capacidad que tiene el columnar de mantener dicha información ya precalculada para cada uno de los segmentos:

-- CCI
set statistics io on
set statistics time on
select count(*) from trip_cci
-- CPU time = 281 ms,  elapsed time = 239 ms.

select count(*) from fare_cci
-- CPU time = 375 ms,  elapsed time = 325 ms.

En el resto de consultas si tenemos ya un consumo de CPU más elevado especialmente cuando tenemos los cálculos de las medias. Esto se podría haber optimizado si hubiésemos elegido el tipo de datos correcto y no texto para almacenar el dato en la tabla:

select avg(convert(int,passenger_count)*1.0) avg_passenger_count,avg(convert(float,trip_distance)) avg_trip_distance  
from trip_cci
--  CPU time = 87219 ms,  elapsed time = 44172 ms.

select count(*) total, payment_type
from fare_cci
group by payment_type
--  CPU time = 453 ms,  elapsed time = 246 ms.

select passenger_count,avg(convert(float,trip_distance)) avg_trip_distance 
from trip_cci
group by passenger_count
order by avg_trip_distance desc
-- CPU time = 60843 ms,  elapsed time = 31503 ms.

En las operaciones sobre el índice columnar vemos que el uso de CPU aproximadamente llega hasta el 50% y pese a ello los tiempos son mucho mejores que las alternativas anteriores:

data lake

Con el uso del columnar, además de la compresión de los datos, obtenemos otra ventaja adicional que es el procesamiento de filas en modo batch:

data lake hash match

El siguiente gráfico muestra las duraciones en cada uno de los casos. Podemos ver como las duraciones claramente son mucho mejor con el índice columnar:

data lake

Respecto al consumo de CPU vemos que en el momento que cambiamos a formato tabular la mejora es muy evidente para todos los casos, siendo mayor el ahorro en el modo columnar:

data lake

La conclusión que obtenemos es que si queremos utilizar Polybase debemos tener en cuenta la cantidad de CPU para la “fuerza bruta” que necesitaremos para las operaciones que queremos abordar. En muchos escenarios puede ser interesante considerar un entorno híbrido, con los datos más consultados en formato tabular o en un columnar, etc. y los menos consultados en formato texto. Se podría dar una visión unificada usando una vista para “particionar por fecha” con un UNION ALL sobre la tabla externa y una tabla local, de forma que podamos acceder a unos datos u otros de forma transparente.

Conceptualmente el Datalake puede tener “todo” el dato, pero acceder a todo el dato desde PolyBase puede no ser una forma efectiva desde el punto de vista de consumo de recursos y coste total (no solo considerando el storage), también la CPU necesaria para tener un tiempo de respuesta razonable. Las estrategias de fuerza bruta para el proceso de datos requieren, obviamente, esa fuerza bruta, ese «abuso» de recursos, que tendrá también un impacto, un coste, asociado al uso de dichos recursos.

Algo parecido puede ocurrirnos en un entorno Big Data Cluster donde pese a que tengamos una mayor escalabilidad del Storage Pool y del Compute Pool, la “fuerza bruta” no siempre es suficiente para superar el tener una mejor localidad del dato, una mayor compresión y en general la eficiencia en el procesado de los datos que tenemos con el columnar y el modo batch. Por tanto, debemos siempre tener presente las alternativas a la aproximación que estemos tomando inicialmente y no tener miedo al cambio, no cerrarnos a realizar pruebas de concepto para validar alternativas, ya que la mejor solución para nuestro negocio puede ser distinta a la inicialmente planteada.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temariodel máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

El monstruo del Data Lake: Debo migrar mi DW a un DL

El monstruo del Data Lake: Debo migrar mi DW a un DL

Data Lake, otra de esas palabras que últimamente están de moda. Y lo peor de todo es que mucha gente asocia esta palabra a un concepto equivocado:

¡Un Data Lake no es un DataWarehouse!

Un Data Lake puede ser un complemento perfecto para un DataWarehouse, pero no debería ser un sustituto de este, principalmente porque cada uno de ellos está optimizado para propósitos distintos, y sus usuarios finales, por norma, también van a ser perfiles distintos. De hecho, ambos pueden ser términos totalmente opuestos: mientras que un DataWarehouse está muy bien estructurado y con información limpia y procesada, un Data Lake puede ser “un caos” tanto a nivel estructural como de contenido.

Llegados a este punto, nos preguntamos entonces qué papel juega el Data Lake en esto del BI, y en donde lo podemos posicionar dentro de la arquitectura de este.

Un posible ejemplo de arquitectura que haga uso de un Data Lake podría ser el siguiente:

Como se puede observar en la imagen, un Data Lake lo podemos situar a dos niveles dentro de la arquitectura. Por un lado, puede ser, al igual que un área de Staging, la antesala de un DataWarehouse, pero por otro puede contener también información que proviene del DataWarehouse.

Una vez que tenemos claro el qué y el dónde, lo que nos debemos preguntar realmente es ¿lo necesito?, ¿me puede aportar algo que no aporten mi DataWarehouse o mi área de Staging?

A la hora de abordar la implementación de una arquitectura “Modern Data Warehouse” debemos tener en cuenta las siguientes consideraciones:

  1. Tener suficiente conocimiento y nivel de madurez:

Debemos tener la capacidad de autoanálisis para saber si realmente necesitamos un datalake, si dispones de multitud de fuentes de datos desestructurados que pueden ser valiosos a la hora de analizar datos, o si la capacidad de almacenamiento (o procesado) de tu actual Data Warehouse se está viendo comprometida o supone grandes costes, es muy probable que sea beneficioso implementar un Data Lake. Este sería un buen principio o puerta de entrada a la hora de decidirse a usar un Data Lake aunque hay muchos más casos de uso en los que es beneficioso.

 

  1. Mantenimiento de varios componentes y tecnologías diferentes:

Este tipo de ecosistemas suele estar compuesto por una variedad amplia de diferentes tecnologías, por lo que implantar una solución de este tipo conllevará un gasto en contratación de servicios en la nube (procesamiento y almacenamiento).

 

  1. Disponer de perfiles técnicos capacitados:

Es necesario disponer de personal capacitado, o formar al personal en estas nuevas tecnologías, es muy fácil cometer errores a la hora de diseñar la estrategia de flujo de información o de diseño de la arquitectura. Necesitaremos el apoyo de ingenieros de datos, arquitectos y probablemente científicos de datos.

 

  1. Saber lidiar con el mantenimiento de diferentes tecnologías e interfaces ETL/ELT

Definir bien como se mueve la información es clave y en este aspecto los procesos ETL y ELT son primordiales, debemos tener la información necesaria en el lugar necesario (y evidentemente en el momento “necesario”), ¿debo mover datos estructurados al datalake?, ¿puedo usar el Data Lake como área de staging para mi DW? Debemos responder preguntas como esta y muchas otras cuya implementación supondrá un esfuerzo en trabajo y servicios.

 

  1. Gobierno del dato, especialmente en el Data Lake y herramientas como los notebooks:

La definición de políticas de gobierno del dato suele ser muy beneficioso para tener claro el origen y la calidad de los datos. qué es cada cosa, de donde sale y quien tiene acceso.

En ocasiones definir políticas de restricción de acceso a datos en un Data Lake no es algo trivial y debemos tenerlo en cuenta.

 

Algunas de las cuestiones que trataremos en la charla de este SolidQ Summit 2020 serán las siguientes:

En qué Casos debo migrar datos estructurados a un DL antes de ser volcados a un DW, algunos de los escenarios en los que puede ser beneficioso este movimiento son:

  • Querer descargar el procesado de los datos al Data Lake (normalmente basado en tecnología Hadoop), para que el procesamiento y el espacio en el EDW se reduzca, y se evite chocar con la gente que hace consultas en el EDW.
  • Si nos es beneficioso usar alguna de las tecnologías/herramientas de Hadoop para refinar datos, ya que hacen esta tarea más rápido y mejor que su EDW. Por ejemplo, para reprocesar grandes volúmenes de datos de stock se podrían crear tareas en paralelo en el Data Lake en función de agrupaciones de tiendas u otro atributo y de este modo se podrían reajustar los datos de forma más eficiente en menos tiempo.
  • El Data Lake puede ingerir grandes archivos rápidamente y proporcionar redundancia de datos. ¿Lo necesita?… adelante!
  • Los trabajos del ELT en el DW están tardando demasiado debido al aumento de los volúmenes de datos y el aumento de la tasa de ingestión, por lo que descargar algunos de ellos al datalake puede ser beneficioso. Es posible que necesite una arquitectura Lambda.

  • El Data Lake es un buen lugar para los datos que «podría» usar en el futuro. Puedes almacenarlos en el Data Lake y hacer que los usuarios usen SQL a través de PolyBase para mirar los datos y determinar si tienen valor.  Tenga en cuenta que PolyBase permite a los usuarios finales consultar los datos en un Data Lake utilizando SQL normal, por lo que no es necesario que aprendan ninguna tecnología relacionada con el Hadoop.  PolyBase incluso permite al usuario final usar cualquier herramienta de reporte que use SQL, para unir datos en una base de datos relacional con datos en un cluster Hadoop.
  • Tener una copia de seguridad de los datos en bruto en caso de que necesite cargarlos de nuevo debido a un error de ETL (y no tener que volver a la fuente). Puedes mantener un largo historial de datos sin procesar en el Data Lake.
  • Si tiene usuarios avanzados/científicos de datos pueden hacer uso de los datos estructurados en el Data Lake (generalmente combinándolos con datos no relacionales).
  • Como una forma más rápida de cargar datos en el Azure SQL Data Warehouse a través de PolyBase desde el Data Lake (que suele ser mucho más rápido que usar SSIS para copiar desde la fuente al Azure SQL Data Warehouse “Synapse”).

Tenga en cuenta también que hay que valorar estos beneficios en contra posición con el trabajo extra que requiere el exportar los datos de una fuente relacional a un formato como CSV, luego copiarlos al Data Lake (donde pueden limpiarse con herramientas como databricks y luego moverse al DW).

También hay que tener en cuenta que los datos relacionales que se mueven al lago de datos perderán los metadatos como tipos de datos, restricciones, claves externas…

Otra opción, si es necesario tener los datos relacionales en el datalake, es la de hacer cargas incrementales en el DataWarehouse y una vez allí, mover estos datos ya actualizados al Data Lake mediante algún proceso ETL o mediante databricks con spark (en el caso de que dispongamos de Azure Synapse como EDW)

Esperamos que este artículo os haya sido de utilidad.

Alfonso Carreira y Chema Pérez.

DPA’s at SolidQ

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temariodel máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada. ¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

Recomendando productos con Inteligencia…Artificial: Filtro colaborativo

Recomendando productos con Inteligencia…Artificial: Filtro colaborativo

Introducción

En la entrada de blog anterior vimos qué eran los sistemas de recomendación y lo presentes que se encuentran en nuestra vida diaria. Hoy vamos a ver el primer tipo de sistema de recomendación; desde qué es, pasando por cómo se implementa y terminando por las ventajas e inconvenientes que presenta.

Imaginemos el siguiente escenario donde tenemos una aplicación para ver películas online y quisiéramos recomendarle películas nuevas a un usuario:

En este escenario existe un usuario (en color naranja), el cúal es similar a n usuarios (tres en este caso), pues han visto las mismas películas. Sin embargo, estos tres usuarios han visto más peliculas que el usuario naranja. Es por ello por lo que se podría tratar de predecir cuál sería la valoración que ese usuario le asignaría a esa película. Después, se puede aplicar algún tipo de regla de negocio. Por ejemplo, si la valoración o rating supera el 2,5 en una escala del 0 al 5, podría ser una película de su interés y por lo tanto se le recomendaría, desechándose en caso contrario.

Si esto lo quisiéramos ver en datos tabulares, como los de Excel, tendrían la siguiente pinta:

En este caso podemos ver que Bob es similar a Alex pues las valoraciones de las películas Avengers, Star Wars y Thor son bastante parejas. Si tratáramos, a ojo de buen cubero, de predecir cuál sería la valoración que Bob le asignaría a Spider-man seguramente sería 0, 1 o 2, basándonos en las valoraciones anteriores. Los sitemas de filtro colaborativo buscan similitudes en n usuarios similares, tratando de encontrar aquellos productos que todavía no ha consumido y recomendándoselos tras aplicarle una regla de negocio.

El primer problema es que esta tabla puede hacerse inmensamente grande. Pensemos en un cliente como Amazon con millones de productos y clientes. Es por ello por lo que se necesita un protocolo para poder calcular estas valoraciones. Permitidme que me ponga un poco friki:

La matriz (conjunto de números ordenados en filas y columnas) R es la matriz que teníamos antes de usuarios en filas y películas en columnas. Nuestro objetivo será factorizar esta matriz en dos matrices cuyo producto de como resultado una aproximación de la original. Es como tratar de conseguir factorizar el número 33; podría ser tan sencillo como encontrar el 3 y el 11. Pero no os austéis con tanto tecnicismo. A la hora de la práctica el open-source hace milagros, pero era inevitable alguna alusión a lo que realmente está sucediendo en la trastienda.

Ahora ya sí, podemos empezar a construir nuestro sistema de recomendación con el lenguaje de programación Python.

%load_ext autoreload
%autoreload 2
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import json
from sklearn.feature_extraction.text import TfidfVectorizer, CountVectorizer
from sklearn.metrics.pairwise import linear_kernel, cosine_similarity
from ast import literal_eval
from nltk.stem.snowball import SnowballStemmer
import matplotlib.pyplot as plt
import solidml as ml
import torch
from sklearn.model_selection import train_test_split
from scipy.sparse import csr_matrix
from sklearn.neighbors import NearestNeighbors
from surprise import Reader, Dataset, SVD, evaluate, KNNBasic
from surprise import NMF
from gensim.models import KeyedVectors

Lo primero que debemos hacer es añadir todas las librerías que vamos a utilizar para realizar el recomendador. Cabe destacar que también existen librerías que utilizaremos en la próxima entrada de blog donde explicaremos el sistema de recomendación basado en contenido.

Posteriormente, vamos a cargar los datos necesarios. Para este y siguientes ejemplos vamos a hacer uso de la base de datos de películas tmdb que podrás descargar aquí.

Carga de datos

Una vez hecho esto, ya podemos cargar los datos:

md = pd.read_csv('movies_metadata.csv')

Los metadatos tienen esta pinta, donde tendremos el título de la película, su presupuesto, los actores, directores, géneros y un largo etcétera.

Elección de un subconjunto de películas

Para no usar todas las películas que aparecen (pues son muchas), vamos a filtrarlas usando un csv dónde aparecen sólo un subconjunto de las mismas:

links_small = pd.read_csv('links_small.csv')
links_small = links_small[links_small['tmdbId'].notnull()]['tmdbId'].astype('int')
md['id'] = md['id'].astype('int')
smd = md[md['id'].isin(links_small)]
smd = smd.groupby('id').head(1)
links_small = links_small[links_small.isin(smd['id'])]

Este código es como lanzar si hubiéramos lanzado una SELECT de T-SQL con una subquery dentro de un IN (SELECT … FROM … WHERE … IN (SELECT…)).

Carga de ratings de las películas

Por último, necesitamos cargar las valoraciones de películas de los usuarios:

ratings = pd.read_csv('ratings.csv')
ratings = ratings[ratings['movieId'].isin(linked_small)]

Una vez tenemos filtrados los datos preparados, ya podemos empezar nuestro sistema de recomendación.

Sistema de recomendación de filtro colaborativo

Tal y como os he comentado anteriormente, para crear nuestra matriz de usuarios-películas y ser capaces de extraer los n usuarios similares y luego ser capaces de predecir el rating de la película parece una auténtica locura a nivel técnico. Sin embargo, afortunadamente el open-source una vez más hace el trabajo por nosotros. En nuestro caso vamos a usar Singular Value Decomposition (SVD):

svd = SVD() # creamos el modelo
data = Dataset.load_from_df(ratings[['userId', 'movieId', 'rating']] # creamos el dataset
cross_validate(svd, data, reader), measures=['RMSE', 'MAE'], cv=5, verbose=True) # entrenamos con cross-validation

En este momento ya tendremos nuestro modelo entrenado. Hemos usado la técnica de cross-validation o validación cruzada para ello, la cual consiste en usar todo el dato disponible, dividirlo en porciones y realizar el entrenamiento y la predicción de los mismos realizando iteraciones para que cada vez el conjunto de test sea uno distinto:

Esta técnica nos permite no sólo usar todos los datos disponibles (lo cuál es crucial en proyectos con pocos datos), sino que también evita que se produzca overfitting, es decir, que el modelo se ajuste demasiado a los datos de entrenamiento, no siendo capaz de generalizar para casos que todavía no ha visto.

Una vez hecho esto, podemos ver los resultados:

Si nos fijamos en la media de la métrica que nos interesa (MAE), podemos ver que tenemos 0,65 de error (es decir, de media fallamos en 0,65 de rating). Básicamente la métrica MAE o Mean Absolute Error muestra la suma de la diferencia entre las predicciones y los valores reales. Justificar que esa cifra es baja o alta debería venir desde negocio, donde deberían habernos provisto de métricas extra para evaluarlo. No obstante, podemos tratar de reducir la métrica MAE aplicando técnicas tales como el tuneo de hiperparámetros.

Análisis filtro colaborativo

Los sistemas de recomendación de filtro colaborativo tienen (como todo en la vida) ciertos pros y ciertos contras.

Pros:

  • Tienen en cuenta las recomendaciones de otros usuarios
  • Provee recomendaciones personalizadas para cada usuario

Contras:

  • No se tiene en consideración los metadatos de las películas (es decir, género, actores, directores, etc.)
  • Cold start. De un usuario que no conocemos nada, no podemos saber a qué otros usuarios es similar y, por lo tanto, no podemos recomendarle películas

Conclusiones

Tal y como podemos ver, los sistemas de recomendación de filtro colaborativo tienen ciertos pros y ciertos contras que debemos valorar y ser conscientes. Por otro lado, su implementación es muy sencilla gracias a las librerías open-source.

En la siguiente entrada de blog veremos los sistemas de recomendación basado en contenido, en el que sólo se tendrá en cuenta los metadatos.

Si no has visto la primera parte, puedes verla aquí.

En caso de tener algún problema para visualizar el código, puedes consultarlo en mi repositorio de GitHub.

¿Quieres formarte en Machine Learning? En SolidQ venimos trabajando en proyectos de Machine Learning desde hace algunos años y esta experiencia nos ha servido para adaptar nuestro conocimiento y crear los contenidos formativos objeto de este Módulo de Machine Learning con el que adquirir nuevos conceptos y conocimientos relacionados con analítica avanzada, dar el salto para no quedarte en el BI Tradicional” y seguir creciendo como profesional. Aprovecha la oportunidad y no dejes escapar el descuento que tenemos para ti… ¡antes de que se agote!

¿Quieres poner en marcha tu proyecto de Machine Learning? Conseguir el éxito en proyectos de analítica avanzada y su monetización requiere de datos, un buen modelo… y su interpretación asociada al caso de negocio. Esta combinación es imprescindible para que el proyecto tenga sentido y sea rentable. Por esta razón, durante nuestro Ideation Workshop nos reunimos en una sesión con todas las partes implicadas de tu equipo, para poner en contexto el proyecto y asegurar tanto su viabilidad técnica como los hitos de negocio en términos de rentabilidad. ¡Infórmate! Mira: Machine Learning Ideation Workshop: ¿Cómo empiezo mi proyecto?

Memory-Optimized TempDB Metadata

Memory-Optimized TempDB Metadata

Con la nueva versión SQL Server 2019 aparecen un conjunto interesante de funcionalidades orientadas a mejorar el rendimiento de forma transparente. Una de ellas es Memory-Optimized TempDB Metadata que consigue aprovechar la tecnología in-memory OLTP para la metadata de tempdb permitiendo de esa forma que muchos de los problemas de congestión habituales desaparezcan.

¿Si al finalizar el artículo quieres profundizar más en este tema con los mejores expertos, nuestro experto Rubén Garrigós dará una sesión práctica sobre SQL Server 2019 in practice: Memory-Optimized Temp-DB en el SolidQ Summit 2020, el evento nacional dedicado a la plataforma de datos de Microsoft, que tendrá lugar del 18 al 22 de mayo en formato online. ¿Te gustaría acompañar a Rubén en esta sesión y llevarte conocimientos para poder aplicar a tu propia empresa?

Reserva ya tu plaza aquí ▶️ http://ow.ly/yHGe50zvNCB 

Vamos a mostrar de forma sencilla cómo puede esta mejora afectar a una carga que tenga una congestión clara por metadatos en tempdb. Comenzaremos creando un procedimiento almacenado en AdventureWorks que únicamente crea un par de tablas temporales:

USE AdventureWorks
GO
CREATE OR ALTER PROCEDURE sp_tempdbload
AS
BEGIN

  CREATE TABLE #test(id int NOT NULL PRIMARY KEY);
  CREATE TABLE #test2(id int NOT NULL PRIMARY KEY);

END

A continuación, haremos uso de una herramienta bastante práctica para generar carga concurrente llamada ostress. Esta herramienta se incluye en las RML tools y puede descargarse aquí: https://support.microsoft.com/en-us/help/944837/description-of-the-replay-markup-language-rml-utilities-for-sql-server 

Concretamente vamos a utilizar ostress configurado con 64 threads y 1000 iteraciones por thread ejecutando el procedimiento sp_tempdbload para saturar a la instancia con suficiente peticiones (64000 peticiones en total):

ostress.exe -dadventureworks -S.\sql2019_2 -E -mstress -n64 -r1000 -Q"exec dbo.sp_tempdbload" -quiet

Podemos ver que el tiempo de ejecución de esta carga es de aproximadamente 22 segundos:

Este tiempo nos da un throughput de 2910 ejecuciones por segundo (~364 por segundo y core).  Podemos ver como no podemos usar toda la CPU disponible, existe contención que lo evita:

tempdb metadata

Concretamente la contención viene causadas por una gran cantidad de esperas de tipo PAGELATCH_EX:

Una vez tenemos esta «linea base», habilitaremos esta nueva funcionalidad, tras lo cual será necesario reiniciar.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Tras reiniciar verificaremos que efectivamente está habilitada consultando la propiedad IsTempdbMetadaMemoryOptimized:

tempdb metadata

Si volvemos a lanzar la misma carga que lanzamos anteriormente, esta vez el tiempo total es de menos de 6 segundos (versus los 22 segundos que necesitábamos sin esta optimización):

ostress.exe -dadventureworks -S.\sql2019_2 -E -mstress -n64 -r1000 -Q"exec dbo.sp_tempdbload" -quiet

Este resultado nos da un throughput de 11206 ejecuciones por segundo (~1401 por segundo y core).  Podemos ver como ahora sí podemos explotar al máximo la CPU disponible:

tempdb metadata

Y también desaparecen los PAGELATCH_EX:

En conclusión, esta nueva funcionalidad puede permitir incrementar notablemente el rendimiento de aquellas cargas que tengan una alta concurrencia y hagan uso intensivo de la metadata de tempdb.

Cómo puedo saber si puedo migrar mi SQL a Azure

Cómo puedo saber si puedo migrar mi SQL a Azure

Durante los últimos meses muchos clientes nos están preguntando como deberían migrar sus actuales bases de datos SQL a Azure, con este artículo queremos resumir cuales son las opciones actuales y que datos importantes hay que tener en cuenta.

1ºPaso : Qué es lo que tenemos actualmente.

Lo primero que hay que revisar es que versión de SQL tenemos y que funcionalidades estamos usando actualmente en nuestras bases de datos, para ello nos vamos a ayudar de una herramienta gratuita que Microsoft nos proporciona llamada «Data Migration Assistant»  https://www.microsoft.com/en-us/download/details.aspx?id=53595, mediante esta herramienta vamos a poder descubrir si estamos utilizando alguna funcionalidad no compatible tanto a nivel de motor/instancia de SQL como dentro de la definición de los objetos dentro de nuestras bases de datos. Esta herramienta te ofrece varias variantes para comprobar si tu base de datos es compatible con los diferentes destinos que podemos elegir actualmente en Azure.

 

 

Lo más importante de esta herramienta es que nos avisa de características bloqueantes que estemos usando y que nos limitan a la hora de poder ir a un destino u otro, como, por ejemplo:

  • Si tenemos linked server definidos en nuestra instancia actual, no podremos ir a SQL Azure, pero si podemos ir a una Instancia Manejada en Azure.
  • Si en nuestro código tenemos consultas cruzadas entre varias bases de datos nos avisará que esto no está soportado en SQL Azure.
  • Si utilizamos jobs de SQL, nos avisará que no podemos migrar a SQL Azure.

Por tanto, esta herramienta nos va a ahorrar una parte de trabajo, para no tener que mirar de forma manual que características tenemos que no nos permiten migrar a PaaS (SQL Azure o Instancia manejada) o IaaS (MV con SQL). Pero como todo, hay parte que nos va a tocar a nosotros mirar:

  • Antes comentábamos la funcionalidad de los linked servers, la herramienta nos avisa de su existencia, pero aquí nosotros tenemos que realizar un chequeo manual, porque no vale con cualquier linked server, si éste está conectado a una fuente diferente de una instancia Microsoft SQL Server, no vamos a poder crearlo en la Instancia Manejada, por tanto solo nos queda la opción de irnos a un entorno IaaS con una máquina virtual en Azure con el Sistema Operativo y el SQL instalado.
  • Si aparte del motor relacional tenemos otros motores instalados como son SSIS, SSAS y SSRS. Aquí nos va a tocar tener que revisar su contenido, teniendo en cuenta lo siguiente:
    • Los paquetes de SSIS tienen como posible destino en Azure ser migrados a Azure Data Factory, pero aquí hay que ver con que fuentes de datos estamos trabajando, si estos están en Azure o en local, para ver las diferentes alternativas de diseñar la arquitectura de Azure Data Factory.
    • Si tenemos SSAS y está configurado en modo multidimensional, si no queremos migrarlo a Tabular solo nos queda la opción de ir a SaaS con una MV con SSAS instalado. SI tenemos un SSAS configurado como Tabular tenemos la opción de migrarlo a Azure Análisis Services.
    • Si tenemos SSRS, hay que revisar si podemos ir a Power Bi Services o si no es posible, tenemos la opción de ir a IaaS con una MV con SSRS instalado.
  • Hay que revisar que aplicaciones y que servicios trabajan con las bases de datos que queremos migrar a Azure, por varias razones:
    • Si es una aplicación de terceros que te obliga a cumplir con una serie de requisitos: versión de SQL, tipo de conexión, etc. Lo más seguro es que tengas que acabar yéndote a IaaS con una MV con su versión especifica de SQL.
    • Hay que revisar el trasiego de datos entre lo que se queda en local y lo que se va a Azure, para conocer si el ancho de banda que tenemos entre local y Azure es suficiente para esa carga.
    • Tamaños de las bases de datos, esto es importante porque en SQL Azure tenemos según el nivel de servicio un límite de 1 a 4 TB.
    • Tipo de seguridad, comprobar si utilizamos seguridad Windows o nativa de SQL. SI utilizamos seguridad integrada deberemos de migrarla con el directorio activo de Azure.

 

2º PASO: Qué servicio de Azure escogemos

Una vez que tenemos todos los datos del paso anterior, ya habremos hecho una primera criba y tendremos claro si podemos ir hacia un entorno IaaS o PaaS.

Por nuestra experiencia sabemos que en las migraciones complejas una de las primeras opciones es migrar a IaaS porque la dificultad de modificar la funcionalidades no compatibles con PaaS es a tener en cuenta. Además, como hemos comentado antes, si hay aplicaciones de terceros que nos fuerzan a versiones concretas del sistema operativo o del motor de base de datos para poder migrar a PaaS hay que coordinarse, si es posible, con el desarrollador de la aplicación, lo que lo complica aún más. Cuando nos encontramos con este tipo de escenarios una de las estrategias que poder seguir es migrar a Azure en IaaS como primer paso y luego, con todo en Azure, ir planteando evolutivos que paso a paso nos permitan aprovecharnos de las ventajas de una migración a PaaS.

Pasamos a destacar los principales puntos a tener en cuenta de cada uno de los posibles destinos en Azure:

IaaS (Máquina virtual en Azure)

Si nos vamos a IaaS, tenemos que tener en cuenta que tendremos que pagar por la máquina y por las licencias del sistema operativo y del motor de SQL, para ello habrá que hacer cuentas y ver si nos podemos aprovechar de algún beneficio que Microsoft aporte por migrar a Azure.

Además, tendremos que seguir realizando las tareas propias de administración del Sistema Operativo y los servicios de SQL.

Una herramienta que nos puede ayudar a decidir que máquina necesitamos en el entorno IaaS, es Microsoft Assesment and Planning (MAP) https://www.microsoft.com/en-us/download/details.aspx?id=7826, esta herramienta permite recoger datos de consumo de recursos de las servidores actuales de SQL a migrar y darnos una idea aproximada de que tipo de máquina en Azure necesitaríamos para poder aguantar la carga actual.

 

PaaS (SQL Azure o Instancia Manejada)

En esta plataforma ya no tenemos que realizar las tareas administrativas del Sistema Operativo y el servicio de SQL, y siempre estaremos a la última versión.

Para las bases de datos que podemos migrar a PaaS directamente, tendremos que decidir a qué nivel de servicio por precio y funcionalidad nos interesa:

SQL AZURE

 Esta opción la solemos elegir cuando estamos migrando una sola base de datos que hemos revisado que no tiene ningún bloqueante y que el tamaño de la base de datos no es mayor de 4TB.

Tenemos dos sabores por DTU o por Vcores.

Un poco de historia aquí: el modelo DTU (Unidad de transacción de base de datos) fue el primero en introducirse con Azure SQL DB. DTU es una medida; una mezcla de memoria de CPU e IO. La idea era crear una medida que nos diera una idea relativa de la cantidad de energía o recursos detrás de la base de datos: cuanto mayor sea el número de DTU, más potente será la base de datos que tengamos.

El rango de DTU va de 5 en el extremo inferior hasta 4,000 en el extremo superior. El problema para muchos era no saber exactamente qué era una DTU. Después de un tiempo, Microsoft ha querido dar respuesta a esa pregunta introduciendo el precio por vCore. vCore es la abreviatura de núcleo virtual y es un modelo que está diseñado para simplificar la traducción de sus especificaciones de recursos de hardware premier en especificaciones similares en la plataforma de base de datos SQL Azure.

Es decir, con vCore, tienes cierta visibilidad de la cantidad real de RAM que está disponible, así como también una idea del tipo de procesador y la velocidad del procesador que se está utilizando en el hardware. Con el modelo DTU, todo eso es solo parte del servicio, por lo que no se conocen esos detalles.

Algunas notas:

      • Es importante tener en cuenta que en ambos casos el servicio tiene un precio por base de datos.
      • Con el modelo DTU, pagas un precio fijo por su cómputo (o E / S), así como por su almacenamiento de datos y retención de copias de seguridad.
      • Con el modelo vCore, tienes por un lado cargos por computación (qué tipo de nodo o potencia de cómputo estás usando) y por otro cargos por almacenamiento. Con vCore, tienes más flexibilidad para administrar tus gastos que con DTU.

El elegir un modelo u otro no te ata a ese modelo, puedes cambiar entre ellos. Por tanto a la pregunta ¿cuál debo usar? La respuesta es simple, depende. El modelo DTU es más simple en cuanto a la cantidad de opciones que tiene ya que con un precio fijo lo incluye todo. El modelo vCore brinda más flexibilidad y transparencia en lo que se está pagando.

En resumen, por simplicidad, el modelo DTU tiene ventaja. Además, si estas comenzando con Azure SQL Database, el modelo DTU ofrece más opciones en el extremo inferior del rendimiento, por lo que puedes comenzar a un precio más bajo que con vCore. Si tienes garantía de software con Microsoft y estas familiarizado con cómo funciona, existen algunas ventajas al usar vCore. Si no estás familiarizado con la garantía de software, puedes comenzar con el modelo DTU.

Instancia Manejada

Esta opción la elegimos cuando estamos migrando una instancia completa con todas sus bases de datos y en el paso primero no hemos detectado ningún bloqueante que no nos permita migrar a esta opción.

Este tipo de servicio PaaS, es el más parecido a una instancia de SQL en local con la ventaja de que no nos tenemos que preocupar de las típicas tareas de administración, como son: aplicación de revisiones y actualizaciones de versión automáticas, copia de seguridad automáticaalta disponibilidad.

Resumen

Lo primero que tendremos que revisar es si nuestra instancia o base de datos tiene alguna funcionalidad bloqueante para migrar a alguno de los posibles destinos en Azure, ya sea a PaaS como a IaaS. Una vez revisado y según los resultados, podremos encontrar instancias o base de datos que solo se puedan migrar directamente (sin cambios) a IaaS y otras que sí podremos PaaS sin grandes cambios. Por último, en las que se pueden migrar a PaaS nos deberemos decidir si es mejor a SQL Azure o a Instancia Manejada, teniendo en cuenta las limitaciones y el precio de cada opción.

Una vez decidido el destino, deberemos de planificar el proceso de migración, que intentaremos explicaros en próximas entradas del blog.

Por nuestra parte, ponemos a vuestra disposición toda nuestra experiencia en la realización de migraciones. Puedes ver más información en: https://www.solidq.com/es/consultoria/data-platform-modernization/