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í.

Seguridad a nivel de página con Power BI

Seguridad a nivel de página con Power BI

Una cultura basada en datos es crucial para que los negocios prosperen en el entorno actual. La base de esta cultura es la capacidad de brindar información oportuna a todas las personas de su organización a través de todos sus datos, a partir de los cuales los usuarios de negocio tomarán decisiones y llevarán a cabo acciones basadas en los conocimientos que encuentran en los mismos.

Cada día, más organizaciones están utilizando Power BI para llevar a cabo esta labor, por lo que se vuelve cada vez más importante para las organizaciones tener la capacidad de gobernar este escenario de BI de forma efectiva cumpliendo con las regulaciones y con sus propios requerimientos de seguridad.

Hasta ahora, Power BI nos permitía definir políticas de acceso condicional a su servicio que proporcionen controles contextuales a nivel de usuario o grupo, ubicación, dispositivo y aplicación; permitiendo así que cada usuario accediera a los datos necesarios para desempeñar sus labores.

Con la nueva actualización de mayo, Power BI nos permite dar un paso más a la hora de definir los contenidos a los que puede acceder cada usuario, la seguridad a nivel de página.

En este artículo veremos cómo, con una sencilla técnica dentro de Power BI Desktop, podemos definir las páginas de un informe que cada usuario puede visualizar.

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.

 

Seguridad a nivel de página

 

Hasta ahora, Power BI Desktop nos permitía establecer navegación a través de páginas mediante marcadores, pero con la nueva actualización se añade esa función dentro de las acciones que puede desempeñar un botón. Además, nos solo nos permite establecer la página destino a la que nos llevará, sino que nos permite, mediante una medida, hacer que ese cambio de página sea condicional.

Para llevar a cabo esta labor, en primer lugar, deberemos definir dos tablas previas para controlar tanto los usuarios y su acceso, como las páginas que tiene nuestro informe y las acciones correspondientes. En este ejemplo vamos a definir únicamente dos usuarios, pero podría ser cualquier número.

 

 

 

Como vemos, se han definido dos usuarios, uno con el rol de administrador y otro de explorador. La idea será permitir al usuario administrador ver todas las páginas del informe, pero el usuario explorador solo tendrá acceso a la página de inicio.

El siguiente paso será crear un slicer con las páginas a las que podemos acceder desde cada una dentro del informe.

 

 

En el slicer meteremos la columna Menu, definida en las tablas anteriores. En este caso se ha utilizado un filtro para mostrar solo las opciones posibles y no mostrar la página actual, pero eso es opcional.

Por último, quedará crear el botón que nos permitirá navegar a la página que seleccionemos dentro del slicer. Como se ha mencionado antes, en el botón se podría directamente definir la página objetivo o apuntar directamente a la columna Action, definida en las tablas anteriores y todo funcionaría correctamente, pero el objetivo es que solo los administradores puedan ver estas páginas.

Para poder configurar este botón, lo primero será crearnos una métrica a través de la cual comparar el usuario actual con los roles de la tabla de usuarios. La métrica será la siguiente:

 

Rol = LOOKUPVALUE(Users[Rol], Users[User], USERNAME())

 

Con esta métrica, accederemos a la tabla de usuarios y obtendremos el rol que tiene asignado el usuario que esté viendo el informe. Por lo cual, para que la navegación funcione, tendremos que tener todos los usuarios que vayan a acceder al informe definidos y con su rol asignado.

El siguiente paso será crear la métrica que defina el destino al que nos llevará la navegación, el código DAX es el siguiente:

 

NavigationAction = IF([Rol] == "Administrador", SELECTEDVALUE(Menu[Menu]), "Without Permission")

 

La lógica es simple, si el rol del usuario es Administrador, la métrica apuntará al valor seleccionado del slicer; en cambio, si tiene otro rol asignado, la opción escogida será Without Permission. Otro punto a tener en cuenta es que tendremos que crear esa página en nuestro informe para que la navegación pueda llevarnos a ella.

Con la métrica de navegación lista, ya solo nos quedará incluir el botón en nuestro informe y configurarlo. En este caso hemos utilizado un botón en blanco para mostrar el texto, pero podría ser cualquier tipo de botón y funcionaría de la misma manera.

 

 

Para definir la acción que llevará a cabo el botón, una vez seleccionado, en el menú Visualizaciones, dentro de Acción, definiremos su tipo como Navegación de Página y en la opción Destino seleccionaremos el icono Función. Dentro del menú que se abre, seleccionaremos la métrica previamente creada NavigationAction.

 

 

Ahora ya solo nos quedará ocultar las páginas cuyo acceso queremos limitar y tendremos nuestra seguridad a nivel de página definida.

Vamos a publicar el informe en el Servicio de Power BI y vamos a probar si todo funciona correctamente.

Primero vamos a acceder con un usuario con permisos de administrador, y veremos como todo funciona de forma normal.

 

 

 

Ahora probemos a acceder con un usuario que no tenga permisos de administrador.

 

 

 

Personalización de los botones

 

Con los pasos anteriores ya tendríamos definida seguridad a nivel de página para nuestro informe, pero aún podemos llevar esto más allá. Aparte de configurar de manera condicional la navegación de página mediante acciones de botones, también podemos personalizar dichos botones para hacerlos más intuitivos y atractivos de cara al usuario.

Power BI nos permite personalizar varios aspectos de los botones de manera condicional, en este caso vamos a ver cómo configurar el color que se muestra al pasar por encima del botón, tanto en el caso de tener permisos como de no tenerlos, y el tooltip que se nos mostrará; aunque Power BI nos permite configurar muchas cosas más.

En primer lugar, vamos a configurar el color de fondo del botón, para ello tendremos que crear una nueva métrica con el siguiente código:

 

Color = IF([Rol] == "Administrador", "#57B956", "#D82C20")

 

La lógica es simple, si el rol del usuario actual es Administrador, la métrica apuntará a un color, y en caso de no ser Administrador apuntará a otro.

Una vez creada la métrica, seleccionamos el botón, vamos al campo Rellenar, dentro del menú Visualizaciones, seleccionamientos la opción Al pasar el cursor y añadimos esta métrica como función en el campo Color.

 

 

El resultado es el siguiente:

 

 

El siguiente paso será modificar el tooltip que se nos mostrará al pasar por encima del botón para ambos casos, para ello utilizaremos de nuevo una métrica con una lógica muy similar a la anterior.

 

Tooltip = IF([Rol] == "Administrador", "You have the right permissions", "You don't have the right permissions")

 

Como vemos, la lógica es muy similar, si nuestro rol es Administrador veremos el primer texto y si tenemos otro rol veremos el segundo.

Para configurar el tooltip condicional deberemos seleccionar nuestro botón, ir a la opción Acción, dentro del menú Visualizaciones, y apuntar en Información sobre herramientas a nuestra métrica.

 

 

Con esto ya tendríamos nuestro tooltip condicional configurado.

 

 

Hemos visto que gracias a las nuevas funcionalidades añadidas a Power BI, cosas que antes resultaban muy costosas o que no podíamos implementar, como la seguridad a nivel de página, se vuelven una tarea sencilla. Pero lo visto aquí es solo la punta del iceberg, os animamos a que lo llevéis un paso más allá.

10 Librerías Python para Data Science y Machine Learning

10 Librerías Python para Data Science y Machine Learning

En este artículo haremos un repaso por 10 librerías Python de uso diario en proyectos de Data Science y Aprendizaje Automático. Entre las tareas que realizamos como Científico de Datos o como Ingenieros en Machine Learning, debemos hacer análisis exploratorio de datos, limpieza, preprocesado, modelado, ingeniería de características, selección del mejor modelo, entrenamiento, validación y test, implementación del servicio. Para todas estas tareas contamos con paquetes de software que nos facilitan conseguir nuestros objetivos.

Las 10 librerías Python seleccionadas son:

  1. Pandas
  2. Numpy
  3. Plotly
  4. Scikit-learn
  5. Category-encoders
  6. Imbalance Learning
  7. LightGBM / XGBoost
  8. Keras / Tensorflow
  9. Shap
  10. AzureML-sdk

Hay muchísimas otras librerías Python de interés, que hoy no comentaremos en este artículo, pero les dejo un listado por si quieren curiosear:

Otras librerías Python de interés

  1. Scipy
  2. joblib
  3. Matplotlib / Plotly / Seaborn
  4. Eli5
  5. Statsmodels
  6. Runipy / IPython
  7. Xlsxwriter
  8. Tqdm
  9. Sphinx
  10. Pytest
  11. Dask
  12. Pytorch
  13. Bokeh
  14. NLTK
  15. SpaCy
  16. Gensim
  17. Pattern
  18. Scrapy
  19. BeautifulSoap
  20. Selenium
  21. Django
  22. Flask
  23. Arrow (para fechas)
  24. Parsedatetime
  25. Scikit-image

 

And the winner is…

Centrémonos en nuestras 10 elegidas.

Veamos una breve descripción a cada una:

Librería Sirve para…
Pandas Manipulación de Datos, limpieza
Numpy Manejo de vectores, matrices y operaciones matemáticas a gran velocidad.
Plotly Generar Visualizaciones interactivas
Scikit-learn Preprocesado de datos, selección de modelos, modelos de Machine Learning, métricas
Category Encoders Ponderación y transformación de datos categóricos a continuos para utilizar en ML
Imbalance Learning Permite equilibrar muestras de datos con diversas estratégias, cuando las clases están muy desbalanceadas.
LightGBM y XGBoost Son dos de los modelos de árboles de ML más poderosos y rápidos.
Keras / Tensorflow Keras nos permite la creación de Redes Neuronales sobre Tensorflow a un alto nivel, facilitando la tarea y su mantenimiento.
Shap Interpretación de Modelos
AzureML SDK Nos permite utilizar la potencia de Azure para entrenar modelos, reutilizarlos y hacer su despliegue en servidores de producción.

 

Y ahora, comentaremos una a una.

1-Pandas, manejo de datos

https://pandas.pydata.org/

pandas librerias python

Pandas es hoy en día una de las librerías más usadas en Data Science pues nos facilita mucho el manejo de datos. Con ella podemos leer archivos ó bases de datos de múltiples fuentes (csv, sqlite, sqlserver, html) y hacer operaciones entre las columnas, ordenar, agrupar, dividir, pivotar, totalizar. Nos ayuda a detectar valores nulos, detectar ouliers, duplicados y también hacer merge ó joins entre distintos orígenes. También nos permite guardar fácilmente nuestro nuevo dataset.

 

2-Numpy, operar matrices

https://numpy.org/

numpy librerias python

Numpy es un estándar en Python y de hecho es utilizada como base por Pandas y por muchas otras librerías que se apoyan en ella para operar.

Numpy nos permite crear todo tipo de estructuras numéricas, múltiples dimensiones, permite transformarlas, operar aritméticamente, filtrar y es útil muchas veces para la inicialización de datos aleatorios.

 

3-Plotly, imagen y clic

https://plotly.com/python/

pltly librerias python

Al realizar gráficas y visualización de los datos, muchas veces al momento de realizar el Análisis exploratorio ó al estudiar los resultados obtenidos solemos utilizar el standard Matplotlib.pyplot que realmente es muy buena librería. Sin embargo, echamos de menos no poder “pasar el cursor” por encima de la gráfica e interactuar. También está Seaborn que embellece y expande mucho el alcance de Matplot. Pero nos quedamos con Plotly que con relativamente poco esfuerzo nos regala gráficas cliqueables, que nos aportan mayor información y nos ayudan en nuestra labor diaria.

 

4-Scikit Learn, Machine Learning en tu mano

https://scikit-learn.org/

scikit learn librerias python

Esta librería creció y creció y cada vez cubre más de nuestras necesidades al momento de preprocesar datos, hacer transformaciones y crear modelos de ML. De hecho, muchas de las nuevas librerías que aparecen siguen sus interfaces para implementar su código. ¿Te suenan los métodos fit(), transform(), predict()? Ó el muy usado train_test_split? Todos vienen de aquí!

Una de las funciones que más me gustan de sklearn, es la de crear Pipelines para las transformaciones y poder reutilizarlos. No hay que perder de vista que para proyectos empresariales los datos que nos llegan “en crudo” deberán ser transformados siempre de la misma manera para alimentar a los modelos de ML.

Por otra parte, sklearn cuenta con implementaciones de los algoritmos <<clásicos>> para clasificación, regresión y clusterización: Regresión Lineal / Logística, Support Vector Machines, K Nearest Neighbors, Procesos Gaussianos, Naive Bayes, Árbol de decisión, PCA, y modelos de Ensamble.

Habría mucho más que decir sobre sklearn, pero por el momento aquí lo dejamos.

 

5-Category Encoders, mejora del dato

https://contrib.scikit-learn.org/category_encoders/

Esta librería es muy útil para intentar dar significado a datos categóricos. Suele ocurrir que entre nuestras variables tenemos valores “A,B,C” ó nombre de zonas, ó diversas categorías “Alto, medio, bajo” que para ser utilizadas en modelos de ML deberemos convertir en valores numéricos. Pero… ¿asignaremos 1,2,3 para “ABC” sin que esto tenga ninguna lógica? ¿ó descartamos esas variables? ¿Valores aleatorios?

Mejor dejarle la tarea a Category Encoders. Nos ofrece diversas transformaciones para dar valor a esas variables categóricas y asignarles un “peso” que pueda aportar valor y significado al momento de entrenar el modelo.

Este paso puede ser tan valioso que podría marcar la diferencia para conseguir unas buenas predicciones.

 

6-Imbalance Learning, emparejar los datos

https://imbalanced-learn.org/stable/

librerias python

Otro caso que se da con mucha frecuencia al hacer tareas de clasificación es contar con una cantidad desbalanceada de muestras de cada clases. Casos típicos son la detección de alguna enfermedad en donde la mayoría de las muestras son negativas y pocas positivas o en set de datos para detección de fraudes.

Para que un algoritmo supervisado de ML pueda aprender, deberá poder generalizar el conocimiento y para ello, deberá de <<ver>> una cantidad suficiente de muestras de cada clase ó será incapaz de discernir.

Allí aparece esta librería al rescate con diversos algoritmos para el re-muestreo de nuestra información. Con ello podremos disminuir al conjunto mayoritario (sin afectar al resultado del entrenamiento), aumentar al conjunto minoritario (creando muestras artificiales “con sentido”) ó aplicar técnicas  combinadas de oversampling y subsampling a la vez.

 

7- LightGBM / XGBoost, árboles con potencia

https://lightgbm.readthedocs.io/en/latest/

Los modelos de sklearn de ML están bien, pero… qué pasa si necesitamos “algo más potente” que un árbol de decisión? pues allí aparecen modelos novedosos como LightGBM ó XGboost.

Utilizan técnicas de “Gradient Boosting” (es decir, optimizar una función objetivo para ponderar el valor de los árboles creados y mejorar así el resultado) pero varían en la manera en que generan los árboles (priorizando niveles ú hojas) y eso afecta a la velocidad de ejecución, aunque son razonablemente rápidas las dos librerías.

LightGBM es mantenida por Microsoft y cuenta con implementaciones para C, Python y R. Puede ejecutar en paralelo y cuenta con soporte a GPU logrando mayores velocidades sobre todo en datasets grandes.

 

8-Keras / Tensorflow, Deep Learning en pocas líneas

https://keras.io/

deep learning

¿Y cuando necesitamos más potencia aún? Allí aparece el Deep Learning…

Sus librerías más populares son Tensorflow y PyTorch, son muy buenas, potentes y optimizadas (¿dije Open-Source = gratuito?).

En este caso, me quedo con Keras que es una librería de Alto Nivel para crear y utilizar Redes Neuronales con Tensorflow como backend.

Es decir, que nos ayuda a poder crear nuestros modelos de Redes Neuronales de una manera simple sin tener que programar directamente con Tensorflow.

Con Keras podremos crear redes multicapa, redes convolucionales, Autoencoders, LSTM, RNN y muchas otras arquitecturas en pocas líneas.

 

9-Shap, Interpretación de modelos

https://github.com/slundberg/shap

9-shap

Al volverse más complejos los modelos de ML y con la aparición de las redes neuronales profundas, se hizo cada más más difícil, si no imposible poder explicar porqué un modelo de ML estaba pronosticando sus resultados de una manera u otra. Esto se volvió un inconveniente pues… ¿podemos confiar en esos resultados? ¿Cómo saberlo?

Para explicar las predicciones de los modelos de ML aparecieron diversas soluciones, siendo una de las más sorprendentes la que se apoya en la “Teoría de Juegos” e intenta contabilizar cuánto aporta cada variable a la predicción final (Shapley values). Nos permite tener una comprensión Global del modelo pero también local, es decir, de cada predicción.

 

10- AzureML Sdk, implementación ML en la Nube

https://docs.microsoft.com/en-us/python/api/overview/azure/ml/?view=azure-ml-py

librerias python

Finalmente presentamos AzureML SDK que nos provee de muchísimas herramientas para trabajar con Data Science y ML, además de permitir implemetar nuestro propio servicio en la nube.

Quería destacar que podemos subir a entrenar nuestros modelos de ML en la nube y aprovechar el paralelismo que nos da su Cluster de Computación -en la nube-. Podemos elegir entre decenas de configuraciones de máquinas con mayor CPU, RAM, GPU y disco.

De esta manera, estaremos ejecutando en muchos nodos a la vez, ahorrando tiempo y dinero (pay-per-use).

Además podemos programarlos y automatizar tareas de extracción de datos, preparación del dataset ó entrenamiento de modelos con la frecuencia que sea de nuestro interés.

Por ejemplo, para un servicio meteorológico podríamos hacer que dispare las predicciones a cada hora y que las escriba en una base de datos, que notifique por email ó que escriba en un archivo de logs.

Estas fueron las 10 mejores librerías Python seleccionadas a principios de 2020, Ya veremos cómo evolucionan y cuales ganarán el podio el año que viene!

¿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?

Creación de Valor Empresarial usando Inteligencia Artificial

Creación de Valor Empresarial usando Inteligencia Artificial

En este artículo repasaremos el estado actual y la madurez de las tecnologías de Inteligencia Artificial para su aplicación empresarial, repasaremos los casos de uso más comunes y tres maneras distintas de iniciar la implantación en la organización.

Si aún no eres experto en estas tecnologías y quieres iniciarte, este texto te ayudará a comprender en qué contexto estamos, la definición del Deep Learning y los pasos a seguir para desarrollar una solución en tu empresa.

A modo de spoiler: ¡Ya puedes empezar a usar Inteligencia Artificial en tu compañía y aprovechar la gran ventaja competitiva que supone!

Inteligencia Artificial y el mundo en 2020

En los último años, mucho se viene hablando sobre Inteligencia Artificial en los medios, sobre todo lo que será capaz de hacer, sobre “cuando” igualará a las capacidades del hombre, sobre qué deparará a la sociedad futura rodeada por robots. La inteligencia Artificial ya está aquí. Ya es parte de nuestro mundo, de nuestro día a día. Estamos rodeados de servicios con IA, coches con IA y por supuesto nuestro móvil, tablet ó smartwatch. Estamos interactuando con la IA, probablemente mucho más de lo que somos conscientes.

Una nueva era de cómputo

La IA ha estado rondando el panorama analítico desde hace muchos años. Sin embargo es ahora cuando a alcanzado un grado de madurez suficiente para ser implementado. La IA moderna se refiere a sistemas que no tienen que ser programados explícitamente, si no, que aprenden por si mismos, ganan una experiencia basada en datos y alcanzan grandes aptitudes como para poder tomar decisiones confiables: sean por prediccion, clasificación, detección de patrones, planificación, organización ó recomendación.

En las últimas implementaciones de alto impacto, la IA se está apoyando en las Redes Neuronales Profundas (Deep neural networks) como parte fundamental de la solución. Este nuevo algoritmo crea un paradigma que abre las puertas a la resolución de problemas complejos en tiempo y forma.

 

Cuando se alinearon Datos, Hardware y Algoritmos

La historia de la IA puede contarse en 3 Capítulos: la explosión de los datos, las mejoras en Hardware y la disrupción de nuevos algoritmos.

Crecimiento exponencial de Datos

Creo que todos vivimos el auge en la aparición de grandes volúmenes de datos y la era del Big Data de los últimos 10 años.

Antes de eso el acceso a datos era muy limitado, restringido: “cerrado”. Ahora el acceso a datos permite alimentar procesos inteligentes desde múltiples fuentes, flexibles y un ámbito mucho más abierto. El dato pasa a ser el combustible indispensable del Aprendizaje Automático.

Avances Computacionales para poder manejar Big Data

A raíz de la explosión de datos, la industria se vio obligada a dar evolución a nuevas arquitecturas y soluciones hardware para manejar grandes cantidades de información. Las GPU surgieron a principios del 2000 para el procesamiento de gráficos sobre todo en Video Juegos y hace pocos años resultaron ser una pieza fundamental para el “cálculo matemático” sobre matrices que son fundamentales para la construcción de las Redes Neuronales. Eso sumado al cómputo distribuido y en paralelo dieron otro paso hacia la IA.

Acceso a los Algoritmos

El acceso al framework más potente del momento para desarrollo de Redes Neuronales Profundas es gratuito: tanto Tensorflow como Pytorch pueden descargarse e instalarse sin ningún coste y son de código abierto.

Esto también posibilitó su difusión y que los mismos proveedores de servicios en la nube AWS, Google ó Azure puedan ofrecerlos como parte de sus herramientas a todos los usuarios.

Esto termina siendo un caldo de cultivo en el que <<cualquiera>> es capaz de tener las tecnologías de acceso a datos, potencia en hardware y tecnología al alcance de la mano. La democratización de la IA empieza a florecer en el ambiente académico y a llegar al empresarial.

 

¿Qué es IA? Aprendizaje de Máquina y Aprendizaje Profundo

Antes de seguir hablando sobre IA, vamos a intentar definir qué es.

Para el término IA tenemos muchas definiciones. La mayoría destacan el hecho de que sea un algoritmo que puede mejorar por sí solo, <<aprender>>, basado en los datos. Esta es ciertamente la mayor característica de la IA, puesto que es un cambio radical a cómo se desarrolla el software, en donde los humanos escribimos explícitamente las reglas de lo que un algoritmo debe hacer a que el algoritmo “se instruya sólo”.

¿Qué es Deep Learning?

El Deep Learning surge desde los avances logrados en Machine Learning y logra detectar patrones mediante el uso de Redes neuronales artificiales que contienen múltiples capas. Las capas intermedias son llamadas “capas ocultas” y son las que permiten la extracción automática de “features”, es decir, las características más importantes de los datos de entrada de la red.

Los avances más grandes en Deep Learning ha sido en el incremento de capas y la complejidad de calculo que una red puede procesar. Hace décadas existían redes neuronales artificiales de 5 ó 10 capas, pero las nuevas Redes por ejemplo para visión artificial pueden contener cientos de capas intermedias. Esto termina dando como benficio el poder “romper récords” en aciertos de predicciones ó poder localizar objetos en imágenes, lograr el reconocimiento de voz u otras tareas complejas.

inteligencia artificial

La importancia del Deep Learning

Debido a su arquitectura, el Deep Learning es perfecto para tratamiento de grandes y complejos volúmenes de datos. Puede aprender, comprender, predecir y adaptarse por sí solo e ir mejorando con el tiempo. Se ha vuelto tan protagónico que muchas veces se usa el término Deep Learning como sinónimo de Inteligencia Artificial.

Entre otros diferenciadores:

  • Los modelos de Deep Learning pueden procesar features directamente sin necesidad de preprocesar como en tareas de Visión por Ordenador ó Procesamiento de Lenguaje Natural.
  • Los modelos de Deep Learning tienden a generalizar mejor el conocimiento, volviéndose robustos aún ante la presencia de “ruido” en los datos.
  • Deep Learning logra mayor precisión y aciertos en sus predicciones que otras técnicas, sobre todo ante datasets grandes y complejos.

 

Inteligencia Artificial aplicada en la Industria

Si bien los medios de comunicación están resaltando a veces de sobremanera y buscando titulares polémicos, la realidad es que la IA ha llegado a las empresas y está demostrando su gran valor añadido. Las compañías más importantes del mundo en industrias como transporte, banca, manufactura, retail y telecomunicaciones están tomando ventaja del poder que proporciona.

 

Estrategia de IA

Es claro que la IA representa un profundo cambio en las organizaciones tanto a corto como a largo plazo y es una tecnología que exige un cambio de estrategia y acción.

Una estrategia de compañía en torno a la IA es en donde se trabaja en el desarrollo de producto y la toma de decisiones. Un ejemplo es la industria financiera en donde la IA permite reducir el riesgo al tiempo que maximiza la rentabilidad. En la industria automotriz se empieza a ver cómo los potenciales compradores empiezan a interesarse más por “qué software tiene” ó “con qué móvil es compatible” que con la potencia del motor ó sus neumáticos.

En todos los campos el uso de Deep Learning tiene el potencial de aumentar la producción, reducir costes, reducir residuos, mejorar eficiencia e impulsar la innovación. Google, Apple, Amazon y Microsoft lo saben bien.

 

¿Dónde usar Deep Learning?

Deep Learning es extremadamente poderoso cuando se cuenta con gran cantidad de datos, cuando nos enfrentamos a muchas dimensiones (features) y también para procesar datos no estructurados como imágenes, video y audio.

En el corto plazo esta tecnología es prometedora para resolución de problemas como la detección de fraude, mantenimiento predictivo, motores de recomendación, optimización de recursos y fidelización de audiencias (reducción de la taza de abandonos, por ejemplo). En esos casos puede lograr mejoras extraordinarias de dos maneras:

  • Mejorar la predicción a partir de los modelos estadísticos ó matemáticos actuales (ó con los datos actuales).
  • Deep Learning puede permitir analizar conjuntos de datos que antes eran imposibles y no se tenían en cuenta.

Repasemos algunos de los casos de uso frecuentes:

Finanzas

El fraude financiero cuesta a las instituciones, consumidores y al propio mercado miles de millones de euros cada año. Cuando los sistemas bancarios y el propio manejo de transacciones bursátiles se digitalizaron gracias a internet; apareció con ella el peligro del acceso ilegal y fraudulento que se convirtió en una amenaza sin precedentes para industria y gobierno.

Las organizaciones financieras están usando machine Learning desde hace años junto con estadística, y data-mining para mitigar riesgos. El panorama actual exige nuevas herramientas.

El Deep Learning es mejor para detectar patrones sospechosos desde múltiples fuentes (orígenes) y da la posibilidad de analizar nuevos tipos de datos. Gracias a esto, la IA puede brindar a bancos e instituciones el cambio requerido ante el fraude moderno.

Optimización en las fábricas

Las fabricas actuales sufren de diversas ineficiencias muchas veces por tener restringido el acceso a datos, comunicación insuficiente entre la cadena de producción y la adquisición de materias primas ó incluso con Ventas. Mejorar estos obstáculos podría ser una oportunidad competitiva a un nuevo nivel.

La IA podría permitir realizar iteraciones y ajustes al sistema en minutos en vez de meses.

El poder predictivo que ofrece la IA permite una comprensión proactiva de las necesidades y permite comunicarlo a los interesados con antelación. Según diversos estudios la IA podría incrementar la capacidad de producción en un 20% y reducir el consumo de materiales en un 4%.

También posibilita el análisis en tiempo real con propuestas de reacción temprana. Día a día aparecen nuevos casos de uso en campos de la industria que vamos descubriendo.

Sistemas de Recomendación

Los sistemas de recomendación se están volviendo herramientas esenciales para poder competir en el mercado online. Estos modelos permiten a las compañías comprender mejor a sus clientes a la vez que ofrecer de manera personalizada mejores productos ó servicios a cada usuario consolidando esa relación. Los recomendadores aumentan la satisfacción, el gasto y la valoración global del cliente con la empresa.

Si bien los motores de recomendación existen desde hace años su potencial se está incrementando y siguen evolucionando, mejorando sus predicciones y enfoques. Amazon reporta un impresionante 35% de ventas que provienen de sus sistemas de recomendación. Netflix provee hasta un 75% de contenidos a sus usuarios gracias a estos algoritmos.

Mantenimiento Predictivo

Este es uno de los casos de uso de IA más valorables con el potencial de generar 1 trillon de euros para 2025. Cuando en una industria ocurre un desperfecto técnico y ocasiona que se frene la producción; provoca pérdidas millonarias. En la industria del automóvil, cada minuto de downtime no planeado cuesta 22.000 U$D.

El mantenimiento predictivo con IA es una parte clave para la automatización industrial. Ayuda a reducir el gasto, extiende la vida útil del equipamento y mejora la seguridad.

Tradicionalmente se utilizaba conocimiento estadístico para prevenir malos funcionamientos y hacer el reemplazo de piezas de maquinaria también mediante la utilización de sensores y estos modelos funcionaban bastante bien.

Al incorporar Deep Learning, estamos expandiendo la capacidad de utilizar como fuentes de datos imágenes, video y audios mediante diversos tipos de Redes neuronales logrando un aumento en la capacidad predictora y en la fiabilidad.

Es posible que ni si quiera imaginemos todo el potencial que se llegará a alcanzar gracias al Deep Learning aplicado en la industria.

 

Opciones para el consumo de IA Empresarial

Vamos a repasar las tres principales alternativas para implantar IA dentro de tu empresa, de la más rápida y económica a la más lenta y costosa. Veremos la opción de aprovechar el Software como Servicio (SaaS), el uso de Infraestructura en la Nube ó de soluciones hechas “en casa” y a medida.

inteligencia artificial

Soluciones Saas

Tal vez la solución más rápida para implementar IA en una organización sea mediante las SaaS. Estas son soluciones empaquetadas para casos “típicos” en el ámbito de visión, asistencia u operativo. También lo podemos encontrar como extensiones o mejoras en productos como CRM que ofrecen características que implementan IA.

Estas plataformas tienen ciertas ventajas como la simplicidad del “plug and play” para iniciarse, suelen ser económicas pero también tienen algunas contracaras. Por lo general en las soluciones que nos ofrecen tendremos configuración limitada (esto podría ser fundamental en algunos casos) y también tendremos implicancias en seguridad al exponer nuestros datos a terceros.

IA en la Nube

Con APIs en la nube los desarrolladores no necesitan comprender por completo la tecnología IA para poder beneficiarse de sus capacidades. Las APIS ofrecen servicios de fácil acceso incluyendo visión por ordenador, traducción de audio a texto, Procesamiento de Lenguaje natural.

Todos los proveedores líderes de servicios Cloud como Microsoft, Amazon y Google ofrecen este tipo de APIs. Por lo general contamos con planes de pago flexible “por uso” que permiten aventurarse con relativo bajo riesgo.

Para muchos casos estas posibilitarán soluciones con poco esfuerzo de desarrollo para su uso. Sin embargo, ocurre que en otros casos la flexibilidad de las APIs pueda estar limitada y que se cubran casos globales ó comunes, pero que no son específicos de una compañía. Muchos algoritmos de Vision Artificial son capaces de detectar coches, personas y perros “out of the box” pero no de detectar piezas propias de la maquinaria de una fábrica.

IA hecha a medida

Como tercer opción para que las empresas puedan consumir IA está la de crear sus propios algoritmos a medida mediante frameworks lo que le dará a las empresas mayor flexibilidad de ajuste para utilizar IA. Existen frameworks libres y populares como Tensorflow, keras y Pytorch ú otros propietarios como Watson de IBM.

El desarrollo propio y personalizado de soluciones IA permite el uso completo del dato de la compañía y la integración con sus procesos actuales y dirigir las soluciones de sus “problemas únicos”.

Diseñar y entrenar modelos Deep Learning pueden transformarse en una verdadera ventaja competitiva. En contraste con las otras opciones requiere de conformar un equipo de científicos de datos e ingenieros expertos (ó tercerizar), de mayor tiempo de implementación y será más costoso económicamente.

 

Conclusiones

Estamos vislumbrando la punta del Iceberg de la IA y todo el cambio y mejoras que suponen para las organizaciones. Para lograrlo debemos estar atentos a la estrategia que mejor se adapte a tu Empresa.

 

Identificación de áreas con alto retorno

Comienza por seguir los siguientes pasos:

  • Identificar en qué áreas de la Organización puede tener impacto la IA.
  • Evitar apresurarse a seleccionar una tecnología. Primero asegurarse de entender cual será el objetivo (resultado) que deberá obtener el uso de la IA y su posible retorno.
  • Educar a los altos mandos para tener una visión realista de lo que puede aportar esta nueva tecnología sin generar expectativas fantásticas.
  • Demostrar la viabilidad de la tecnología en la resolución de problemas en las áreas que identificaste, con casos de éxito similares en la industria ó con pequeños experimentos.

Los desafíos de desarrollar e implementar una solución personalizad de IA no son triviales especialmente porque el campo aún está en plena maduración.

Finalmente decir que a pesar de las dificultades y obstáculos iniciales en el proceso de implantar IA en una Compañía, el cambio exponencial de crecimiento que puede brindar es gigante y vale la pena.

Y lo puedes empezar a hacer hoy!

¿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?

Análisis de sentimiento a través de Azure ML Studio para analizar la repercusión del COVID-19 en Twitter

Análisis de sentimiento a través de Azure ML Studio para analizar la repercusión del COVID-19 en Twitter

El Análisis de Sentimientos es “el proceso de identificar y categorizar computacionalmente las opiniones expresadas en texto, especialmente para determinar si la actitud del escritor hacia un tema, producto, etc… en particular es positiva, negativa o neutral”.

En el caso particular de las redes sociales, los usuarios tienen hoy en día todo tipo de facilidades para mostrar sus opiniones sobre cualquier tema que deseen. Tener constancia sobre las opiniones referentes a una marca, producto o situación y medir su impacto es actualmente de vital importancia para todas las empresas, pero el análisis de sentimientos no solo permite responder “qué opinan los internautas sobre algo”, sino que, facilita, mediante los medios adecuados, obtener ventajas competitivas en diferentes ámbitos.

Una vez visto lo que es el análisis de sentimiento y sus usos, la siguiente pregunta es: ¿Cómo podemos construir un analizador de sentimientos automatizado? Una respuesta es: utilizando Machine Learning.

Existen varias herramientas que nos permiten analizar sentimientos en textos de forma automática, pero si las que tenemos no nos cubren las necesidades, queremos personalizar el comportamiento o incluso por un tema de costes, podemos montarnos un analizador nosotros mismos de forma muy sencilla.

Con esta finalidad, uno de los mejores entornos donde empezar a crear modelos de Machine Learning es Microsoft Azure Machine Learning Studio. Esta herramienta nos permite crear sistemas automatizados de forma sencilla, sin necesidad de altos conocimientos en programación y, una vez entrenado el modelo, nos permite publicarlo en un servicio web para poder consumirlo.

En este artículo partiremos de una serie de tweets ya catalogados respecto al Coronavirus en España, aunque podría ser cualquier otro texto, como entradas a un foro. A partir de estos datos construiremos un modelo, lo publicaremos en un servicio web y lo consumiremos a través de Excel para realizar nuestras predicciones.

 

Construcción de un modelo predictivo en Azure ML Studio

 

Para desarrollar un modelo de análisis predictivo, normalmente se utilizan datos de una o varias fuentes, se transforman, analizan los datos a través de diversas funciones estadísticas y de manipulación de datos y se genera un conjunto de resultados. Microsoft Azure Machine Learning Studio nos permite hacer todo esto, pero en lugar de tener que hacerlo completamente a través de código, nos facilita una interfaz gráfica sencilla dónde conectaremos los conjuntos de datos y módulos para construir el modelo de análisis predictivo.

 

Carga de datos

 

Para poder entrenar nuestro analizador de sentimientos lo primero que necesitaremos, como para cualquier modelo de Machine Learning, son datos. En nuestro caso, nuestro objetivo es predecir el sentimiento en textos, por lo que para entrenar el modelo necesitaremos un conjunto de textos que ya tengan el sentimiento asignado. Lo primero que haremos será cargar estos datos.

 

 Azure ML Studio datasets

 

Ahora solo tendremos que arrastrar el módulo al espacio de trabajo y ya tendremos nuestro conjunto de datos listo para usar.

 

 

Nuestro conjunto de datos consta de dos columnas, una con los fragmentos de texto y otra con el sentimiento asociado; 4 para sentimientos positivos, 2 para sentimientos neutros y 0 para sentimientos negativos.

Ya hemos completado el primer paso para construir nuestro analizador de sentimientos.

 

Pre-procesado de los datos

 

Primero debemos asegurarnos de que nuestros datos se encuentran en el formato correcto, para ello incluiremos el módulo Edit Metadata, en el cual indicaremos el tipo de datos de cada columna. En nuestro caso tendremos una columna con valores de texto y otra con valores categóricos.

El siguiente paso será procesar estos datos. Los textos suelen estar desestructurados, con gran cantidad de caracteres especiales, enlaces a páginas web, hashtags y palabras muy comunes que aportan poco valor a la predicción (conocidas como stopwords). El rendimiento de nuestro modelo puede aumentar considerablemente al limpiar estos datos.

Este proceso de limpieza podría hacerse directamente con un script de Python o R, pero en este caso vamos a utilizar un módulo incluido en Azure ML Studio llamado Preprocess Text. Este módulo nos permite realizar varias transformaciones, además de las mencionadas anteriormente:

 Azure ML Studio sentiment analysis

El siguiente paso será tranformar las secuencias de texto obtenidas tras la transformación anterior en un conjunto de características representadas como enteros (conocido como Feature Hashing). La ventaja de utilizar Feature Hashing es que podemos representar textos de longitud variable como vectores de entidades numéricas de igual longitud y así lograr una reducción de la dimensionalidad; por el contrario, si tratamos de utilizar una columna de texto para entrenar sin convertir, se trataría como una columna categórica con muchos valores distintos, lo que empeoraría el rendimiento. Para realizar esta transformación utilizaremos el módulo Feature Hashing, incluido en Azure ML Studio.

Por último, utilizaremos el módulo Select Columns in Dataset para quitar la columna de texto original.

Con esto ya tendríamos nuestro conjunto de datos transformado, por lo que el siguiente paso será empezar a entrenar.

 Azure ML Studio analysis

 

Entrenamiento del modelo

 

Con nuestro conjunto de datos ya transformado, el siguiente paso será entrenar el modelo, para ello, lo primero será dividir los datos en un conjunto de entrenamiento y otro de testeo. El conjunto de entrenamiento lo utilizaremos para entrenar el modelo y el de testeo para evaluar el modelo.

Para dividir los datos utilizaremos el módulo Split Data. Debemos indicar la fracción por la que dividir, que en nuestro caso será 0.7 para entrenamiento y 0.3 para testeo, y definiremos el modo de división como estratificado.

 

 Azure ML Studio data

 

El siguiente paso será decidir el algoritmo de Machine Learning que vamos a utilizar. En nuestro caso nos encontramos frente a un problema de clasificación, ya que estamos buscando predecir una categoría, y esta categoría tiene tres posibles valores, por lo que deberemos escoger un algoritmo multiclase. Azure ML Studio nos facilita varios algoritmos, de los cuales, en este caso vamos a utilizar un Multiclass Decision Forest, pero se puede probar cualquier otro para comprobar cual se ajusta mejor a nuestros datos.

Una vez decidido el algoritmo, lo que nos queda es entrenar el modelo. Para ello, añadiremos el módulo Train Model y le conectaremos como entradas el algoritmo que vamos a utilizar y nuestro conjunto de entrenamiento. También deberemos escoger cuál es el valor que queremos predecir, en nuestro caso será el sentimiento.

 

 

Evaluar el modelo

 

Una vez entrenado el modelo, lo único que quedaría por hacer sería evaluarlo. Para ello utilizaremos los módulos Score Model y Evaluate Model. Con el módulo Evaluate Model obtenemos medidas del rendimiento del modelo que hemos creado, para con ellas decidir si nuestro modelo es lo suficientemente bueno o debemos realizar ajustes para mejorarlo.

 

 

Creación de un servicio web y consumo a través de Microsoft Excel

 

Además de crear y entrenar modelos, Azure ML Studio nos permite publicarlos como un servicio web para después consumirlo y realizar nuestras predicciones.

Para publicar un modelo de Azure ML Studio como servicio web lo primero que debemos hacer es, dentro de nuestro experimento, seleccionar la opción Set Up Web Service, en la barra inferior de la interfaz. Se nos abrirá una nueva pestaña en la parte superior del espacio de trabajo llamada Predictive Experiment y veremos como Azure ML Studio simplifica nuestro diagrama añadiendo una entrada Web Service Input y una salida Web Service Output. Estos nuevos módulos serán los datos de entrada y salida de nuestro servicio web.

 

 

Al entrenar el modelo, introducíamos un conjunto de datos con dos columnas, el texto y el sentimiento, pero en este caso no es eso lo que buscamos, sino que buscamos que la entrada sea únicamente un texto sin sentimiento asignado y que el modelo se encargue de asignarlo. Lo mismo sucede con la salida, ahora mismo nos está devolviendo todo el conjunto de datos con la predicción como una nueva columna añadida, pero nosotros solo queremos esa columna como salida.

Lo que haremos será añadir dos módulos Select Columns in Dataset, una al principio y otra al final, para definir así los datos que vamos a introducir y los datos que queremos que el modelo nos devuelva.

Ejecutamos de nuevo y si todo está correcto el modelo resultante debería quedar de la siguiente manera:

 

 

Una vez ha ejecutado todo correctamente, solo deberemos pulsar sobre Deploy Web Service y ya tendremos nuestro servicio web activo y listo para ser consumido.

 

 

Azure ML Studio nos permite probar directamente el modelo pulsando en Test, pero vamos a utilizarlo para predecir el sentimiento de un conjunto de tweets en un libro de Microsoft Excel. Para ello solo tenemos que descargar el archivo Excel que se muestra en el apartado Apps, en la fila de Request/Response.

Una vez estemos dentro del libro, veremos que se nos muestra en la parte derecha el add-in de Azure ML Studio. Ahora tendremos que introducir en el libro los datos cuyo sentimiento queremos predecir, seleccionar nuestro modelo en el menú de Azure, seleccionar los datos de entrada, la celda de salida y, con solo pulsar en Predict, tendremos una nueva columna con todas las predicciones. ¡Así de sencillo!

 

 

Visualización de los resultados

 

Con los pasos anteriores ya tendríamos nuestro conjunto de datos de resultados listo, pero resulta complicado sacar conclusiones teniendo únicamente el texto de los tweets y los sentimientos; por lo que vamos a visualizarlos para poder sacar nuestras conclusiones. Esto lo haremos a través de Python.

Lo primero que deberemos hacer es cargar las librerías que necesitamos para hacer funcionar nuestro código.

 

import pandas as pd
import numpy as np
import unicode
import matplotlib.pyplot as plt
from wordcloud import WordCloud, ImageColorGenerator, STOPWORDS
from PIL import Image
import random
from collections import Counter
from palettable.colorbrewer.sequential import Reds_9
from palettable.cmocean.diverging import Balance_20
from palettable.tableau import BlueRed_12
from scipy.misc import imread
from nltk.corpus import stopwords
def color_func(word, font_size, position, orientation, random_state=None, **kwargs):
    return tuple(Balance_20.colors[random.randint(2,18)])

 

A continuación, cargaremos nuestro conjunto de resultados y sustituiremos los valores de sentimiento por su correspondiente significado:

 

df = pd.read_excel('predicciones.xlsx', encoding = 'utf8')

df['Scored Labels'] = df['Scored Labels'].replace(0, 'Negativo').replace(4, 'Positivo').replace(2, 'Neutro)

 

Una vez tenemos los datos cargados, vamos a realizar una serie de visualizaciones.

 

Distribución sentimientos

 

Lo primero que podemos visualizar es la distribución de los tweets categorizados como positivos contra los tweets categorizados como negativos.

 

labels = 'Positivo', 'Negativo'
sizes = [df[df['Scored Labels'] == 'Positivo'].shape[0], df[df['Scored Labels'] == 'Negativo'].shape[0]]
colors = ['green', 'red', ]
explode = (0.1,0)

plt.pie(sizes, explode = explode, labels = labels, colors = colors, autopct = '%1.1f%%', shadow = True, startangle = 140)
plt.axis('equal')
plt.show()

 

 Azure ML Studio graph

Wordcloud

 

Un wordcloud es una visualización que nos permite ver las palabras más frecuentes, es decir, las palabras que más se repiten a lo largo del texto. Para ello, wordcloud visualiza con un tamaño más grande las palabras más frecuentes y en tamaño más pequeño aquellas menos frecuentes.

Para realizar la visualización correctamente, como sucedía al entrenar el modelo, deberemos eliminar las palabras que no aportan significado (stopwords).

 

otras = stopwords.words('spanish')
sw = set(STOPWORDS)
for word in otras:
   sw.add(word)
sw.add('RT')
sw.add('http')
sw.add('https')

 

Vamos a crear dos wordclouds, uno para los tweets positivos y otro para los tweets negativos.

 

# Wordcloud tweets positivos

positivos = df[df['Scored Labels'] == 'Positivo']
textos_positivo = ' '.join(positivos.fillna('')['tweet_text'].tolist())

wc = WordCloud(
    background_color = 'rgba(255, 255, 255, 0)', mode = 'RGBA',
    width = 1800,
    height = 1400,
    stopwords = sw
).generate(textos_positivo)
wc.recolor(color_func=color_func, random_state=3)

%matplotlib notebook
plt.imshow(wc)
plt.axis('off')
plt.savefig('./wordcloud_positivo.png', dpi=600)
plt.show()

 

 Azure ML Studio word analysis

 

# Wordcloud tweets negativos

negativos = df[df['Scored Labels'] == 'Negativo'] 
textos_negativo = ' '.join(negativos.fillna('')['tweet_text'].tolist()) 
wc = WordCloud( 
    background_color = 'rgba(255, 255, 255, 0)', mode = 'RGBA', 
    width = 1800, 
    height = 1400, 
    stopwords = sw 
).generate(textos_negativo) 
wc.recolor(color_func=color_func, random_state=3) 

%matplotlib notebook 
plt.imshow(wc) 
plt.axis('off') 
plt.savefig('./wordcloud_negativo.png', dpi=600) 
plt.show()

 

 Azure ML Studio wordmap

 

Top palabras más frecuentes

 

Por último, también podemos visualizar, mediante un gráfico de barras, el top de palabras más frecuentes, tanto en tweets positivos como en tweets negativos.

 

# Positivas

textos_positivo = unicode.unidecode(textos_positivo)
word_tokens = word_tokenize(textos_positivo)
filtered_text = []
for w in word_tokens:
    if w not in sw:
        filtered_text.append(w)

counter_pos = Counter(filtered_text)
most_occur_pos = counter_pos.most_common(10)

df_pos = pd.DataFrame(most_occur_pos, columns=['palabra', 'count']).sort_values(by='count', ascending=True)
df_pos.plot.barh(x='palabra', y='count', rot=0, title='Palabras más frecuentes tweets positivos', color='royalblue')
plt.tight_layout()

 

 

# Negativas

textos_negativo = unicode.unidecode(textos_negativo) 
word_tokens = word_tokenize(textos_negativo) 
filtered_text = [] 
for w in word_tokens: 
    if w not in sw: 
        filtered_text.append(w) 

counter_neg = Counter(filtered_text) 
most_occur_neg = counter_neg.most_common(10) 
df_neg = pd.DataFrame(most_occur_neg, columns=['palabra', 'count']).sort_values(by='count', ascending=True) 
df_neg.plot.barh(x='palabra', y='count', rot=0, title='Palabras más frecuentes tweets negativos', color='royalblue') 
plt.tight_layout()

 

¿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?

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í.