Honeypot: un SQL Server desprotegido en Azure

Honeypot: un SQL Server desprotegido en Azure

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

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

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

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

A continuación habilitaremos el guest-level monitoring:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

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

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

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

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

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

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

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

 

Creación del paquete de SSIS

 

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

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

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

 

 

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

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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

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

 

 

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

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

 

# Parameters

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

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

# Change permissions in PBRS

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

 

 

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

 

Ejecución del paquete sobre Power BI Report Server

 

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

 

 

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

 

 

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

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

 

 

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

 

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

Algo conocido, pero no utilizado, el comando DBCC CHECKDB

Algo conocido, pero no utilizado, el comando DBCC CHECKDB

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

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

Verificación de página (CHECKSUM)

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

SELECT name, page_verify_option_desc FROM sys.databases

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

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

DBCC CHECKDB

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

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

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

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

DBCC CHECKDB en AZURE

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

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

 

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

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

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

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

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

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

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

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

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

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

    DROP TABLE #Temp;
END;

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

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

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

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

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

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

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

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

    DROP TABLE #Temp;
END;

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

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

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

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

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

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

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

    DROP TABLE #Temp;
END;

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

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

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

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

DBCC FREEPROCCACHE 
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

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

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

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

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

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

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

    DROP TABLE #Temp;
END;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    WAITFOR DELAY '00:00:10'

    DROP TABLE #Temp;
END;

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

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

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

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

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

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

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

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

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

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

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

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

    DROP TABLE #Temp;
END;

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

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

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

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

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

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

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

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

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

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

    DROP TABLE #Temp;
END;

Al realizar las ejecuciones vemos lo siguiente:

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

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

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

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

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

EXEC sp_getDistinctProductsPerAccountPrefix '10-40'

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

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

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

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

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

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

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

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

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

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

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

Azure Data Lake y PolyBase

Azure Data Lake y PolyBase

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

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

data lake polybase

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

data lake

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

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

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

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

test polybase data lake

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

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

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

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

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

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

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

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

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

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

RECONFIGURE WITH OVERRIDE

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

CREATE DATABASE Polybase

USE PolyBase

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

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

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

GO

CREATE SCHEMA ext;

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

data lake

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

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

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

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

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

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

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

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

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

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

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

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

data lake

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

data lake hash match

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

data lake

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

data lake

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

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

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

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

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

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

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

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

¡Un Data Lake no es un DataWarehouse!

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

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

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

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

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

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

  1. Tener suficiente conocimiento y nivel de madurez:

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

 

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

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

 

  1. Disponer de perfiles técnicos capacitados:

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

 

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

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

 

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

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

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

 

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

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

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

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

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

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

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

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

Alfonso Carreira y Chema Pérez.

DPA’s at SolidQ

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

Recomendando productos con Inteligencia…Artificial: Filtro colaborativo

Recomendando productos con Inteligencia…Artificial: Filtro colaborativo

Introducción

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

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

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

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

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

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

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

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

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

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

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

Carga de datos

Una vez hecho esto, ya podemos cargar los datos:

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

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

Elección de un subconjunto de películas

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

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

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

Carga de ratings de las películas

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

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

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

Sistema de recomendación de filtro colaborativo

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

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

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

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

Una vez hecho esto, podemos ver los resultados:

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

Análisis filtro colaborativo

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

Pros:

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

Contras:

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

Conclusiones

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

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

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

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

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

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

Memory-Optimized TempDB Metadata

Memory-Optimized TempDB Metadata

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

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

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

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

USE AdventureWorks
GO
CREATE OR ALTER PROCEDURE sp_tempdbload
AS
BEGIN

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

END

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

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

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

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

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

tempdb metadata

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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

tempdb metadata

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

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

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

tempdb metadata

Y también desaparecen los PAGELATCH_EX:

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

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

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

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

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

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

 

 

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

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

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

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

 

2º PASO: Qué servicio de Azure escogemos

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

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

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

IaaS (Máquina virtual en Azure)

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

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

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

 

PaaS (SQL Azure o Instancia Manejada)

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

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

SQL AZURE

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

Tenemos dos sabores por DTU o por Vcores.

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

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

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

Algunas notas:

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

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

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

Instancia Manejada

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

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

Resumen

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

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

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

Virtualización de datos con PolyBase scale-out

Virtualización de datos con PolyBase scale-out

Una de las tendencias en las plataformas de datos modernas es la virtualización de datos. De forma muy resumida la virtualización de datos nos permite acceder a los datos sin realizar un movimiento/copia de datos de forma transparente, de forma que para nuestras consultas el acceso a dicha información se realiza de la misma forma que a cualquier otra tabla dentro de nuestro SQL Server.

En SQL Server la funcionalidad que nos permite la virtualización de datos se denomina PolyBase. PolyBase apareció en SQL Server 2016 y ha ido mejorando en las sucesivas versiones SQL Server 2017 y SQL Server 2019. Esta funcionalidad está disponible también en la plataforma analítica en cloud Azure Synapse Analytics (antes conocida como Analytics Platform System y previamente a ello conocida como Parallel Data Warehouse) así como en la solución SQL Server Big Data Cluster (sobre Kubernetes on-premise o cloud).

En este artículo vamos a centrarnos en mostrar cómo es el funcionamiento de la configuración escalable (scale out) de PolyBase sobre SQL Server 2019. Para ello comenzaremos preparando las instancias que van a participar en dicha configuración. Durante la instalación es necesario seleccionar el componente PolyBase ya que no se instalará por defecto. Podremos seleccionar opcionalmente también el conector para HDFS:

También se nos solicitará que indiquemos si vamos a querer una configuración standalone (1 único nodo) o una scale-out (n nodos):

El rol de head node en un entorno scale-out requiere de licenciamiento Enterprise mientras que el resto de nodos Compute pueden utilizar licencia standard. También debemos tener en cuenta que actualmente la característica no puede instalarse en más de una instancia por máquina:

Una vez hemos finalizado la instalación veremos que tenemos un par de servicios extra instalados llamados SQL Server PolyBase Engine y SQL Server PolyBase Data Movement:

Para una configuración scale-out sobre instancias SQL Server sobre Windows debemos tener en cuenta que necesitamos utilizar una cuenta de dominio y que todos los nodos pertenezcan al mismo. Si tenemos problemas al levantar alguno de los servicios debemos acudir a una carpeta de logs de PolyBase que se crea dentro de la carpeta de logs de SQL Server para obtener más información:

En mi caso por “pasarme de listo” y haber clonado los nodos me encontré con el problema que, pese a haber renombrado el nombre de la instancia (sp_dropserver y sp_addserver), los servicios de PolyBase en el nodo renombrado no eran capaces de arrancar al intentar conectar con el nombre antiguo:

Ni corto ni perezoso me puse a renombrar en el registro de Windows las entradas correspondientes y a trastear en ciertos ficheros configuración (.config y .xml localizados en C:\Program Files\Microsoft SQL Server\MSSQL15. SQL2019\MSSQL\Binn\PolyBase\) tras lo cual pude conseguir que volviera a funcionar… pero no es algo que pueda recomendar a nadie:

Este bug está documentado pero parece que no resuelto por ahora: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896768-following-a-rename-of-computer-cannot-start-sql-po El workaround recomendado es desinstalar y reinstalar la feature de PolyBase en los nodos afectados.

Una vez tengamos al menos dos nodos instalados y con los servicios funcionando podremos comenzar la configuración. El primer paso es activar la funcionalidad, que viene desactivada por defecto excepto en Big Data Clusters:

exec sp_configure 'PolyBase enabled',  1;

RECONFIGURE;

Si conectamos a las instancias que hemos configurado PolyBase podremos ver que disponen de un conjunto de bases de datos preconfiguradas, con muchas tablas con el prefijo “pdw” (parallel datawarehouse) y conceptos muy similares a los que tenemos en Azure Synapse. No debemos olvidar que el código base de SQL Server es compartido entre los distintos “sabores” de la plataforma de datos por lo que tiene sentido encontrarnos este tipo de referencias.

El siguiente paso es configurar el nodo WS2019-2 como “esclavo” de WS2019-1 para que actúe únicamente como nodo de computación. Para ello deberemos lanzar el siguiente comando en WS2019-2

-- head node machine name, head node dms control channel port, head node sql instance name 

EXEC sp_PolyBase_join_group 'WS2019-1', 16450, 'SQL2019';

Tras esta configuración se nos deshabilitará el engine de PolyBase, ya que no es necesario en los compute nodes (solo se necesita en el head), y se nos solicita reiniciar el servicio de Data Movement para que se reconfigure tras el cambio.

Conceptualmente la arquitectura quedará como en el siguiente diagrama, donde tendremos un nodo de control al que llegarán las consultas y tendrá el Engine de PolyBase y N nodos extra de compute (https://docs.microsoft.com/en-us/sql/relational-databases/PolyBase/PolyBase-scale-out-groups?view=sql-server-ver15):

Desde SSMS podemos comprobar también que la configuración es correcta:

A continuación, procederemos a crear una tabla externa que apunte en este caso a una instancia SQL Server 2017. Crearemos una base de datos “DataVirtualization”, crearemos la master key y crearemos un credencial para conectar a ella:

CREATE DATABASE DataVirtualization

GO

USE DataVirtualization

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'strong_password'

CREATE DATABASE SCOPED CREDENTIAL SqlServer2017Credential

WITH IDENTITY = 'sqlusername', SECRET = 'strong_password';

El siguiente paso es crear un external data source que apunte a la instancia SQL Server 2017 utilizando el credencial que hemos creado previamente:

CREATE EXTERNAL DATA SOURCE SQLServer2017

    WITH ( LOCATION = 'sqlserver://HYPERV2:1433',

    PUSHDOWN = ON,

    CREDENTIAL = SQLServer2017Credential);

Por defecto SQL Server intentará realizar un PUSHDOWN de predicados salvo que configuramos a OFF dicha opción. Esto es debido a que normalmente filtrar y reducir en origen antes de mover los datos es la opción más eficiente.

Una vez tenemos el externa data source, crearemos la external table teniendo especial cuidado de que los tipos de datos coincidan (por ejemplo los collate de cada columna):

CREATE EXTERNAL TABLE dbo.Posts(    

            [Id] [int] NOT NULL,

            [AcceptedAnswerId] [int] NULL,

            [AnswerCount] [int] NULL,

            [Body] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            [ClosedDate] [datetime] NULL,

            [CommentCount] [int] NULL,

            [CommunityOwnedDate] [datetime] NULL,

            [CreationDate] [datetime] NOT NULL,

            [FavoriteCount] [int] NULL,

            [LastActivityDate] [datetime] NOT NULL,

            [LastEditDate] [datetime] NULL,

            [LastEditorDisplayName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS,

            [LastEditorUserId] [int] NULL,

            [OwnerUserId] [int] NULL,

            [ParentId] [int] NULL,

            [PostTypeId] [int] NOT NULL,

            [Score] [int] NOT NULL,

            [Tags] [nvarchar](150)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [Title] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [ViewCount] [int] NOT NULL)

 WITH (     

            LOCATION='StackOverflow2010.dbo.posts',

            DATA_SOURCE=SqlServer2017

);

El siguiente paso sería probar una consulta sencilla sobre la tabla externa:

Select count(*) from dbo.posts

Una vez comprobamos que funciona, vamos a analizar un poco más en qué se convierte exactamente cuando la lanzamos. Por una parte vemos que se implementa la llamada externa con un operador remote query:

Si trazamos desde el destino qué peticiones le llegan desde los nodos de PolyBase, vemos que tenemos unas primeras consultas orientadas a obtener los metadatos (incluyendo información de particionado) y a continuación una consulta similar a la original (interpretada):

Si en algún caso nos interesa, podemos forzar el pushdown o evitar su uso mediante el uso de una configuración OPTION en la consulta:

SELECT COUNT(*) FROM dbo.Posts OPTION (FORCE EXTERNALPUSHDOWN);

SELECT COUNT(*) FROM dbo.Posts OPTION (DISABLE EXTERNALPUSHDOWN);

Si probamos esa opción de deshabilitar el pushdown en nuestra consulta vemos que lo que obtenemos es una consulta que devuelve tantas filas como tenemos en la tabla, pero sin devolver todas sus columnas, y el conteo se realizará ya en el nodo de PolyBase:

En el caso de un conteo normalmente es siempre más beneficioso realizarlo en origen, pero puede haber casos donde el origen está “escaso” de CPU y si enviamos vía pushdown otras operaciones más pesadas, como agregados, puede resultar menos eficiente.

Por forzar un poco la situación, vamos a lanzar una consulta un tanto “absurda” que realice un producto cartesiano de la tabla posts consigo misma y además con las primeras 10 filas de nuevo:

select top 10 p1.favoritecount, count_big(*)

from dbo.posts p1, dbo.posts p2,(select top 10 * from dbo.posts) p3 group by p1.favoritecount order by count_big(*) desc

Si lanzamos la consulta directamente en el origen, vemos que necesitamos 21 segundos de CPU y nos lleva 26 segundos devolver la respuesta:

Si lo lanzamos a través de PolyBase vemos que solo necesitamos la mitad de tiempo, 13 segundos:

En la traza del origen de datos, vemos que PolyBase ha decidido leer los datos en dos peticiones, sin realizar los cálculos en origen, y luego procesar el resultado por sí mismo, obteniendo una mejora de rendimiento respecto a lo que sería ejecutar directamente en el origen:

Este es un ejemplo de la potencia que tiene este motor para descomponer la consulta en partes más sencillas y ejecutarla de una forma más óptima. Interpretar estos planes resulta más complicado que los planes tradicionales de SQL Server ya que no disponemos de un interfaz gráfico que nos ayude (como sí disponemos en Synapse). Podemos extraer del plan de ejecución que nos muestra el head-node la parte XML correspondiente y hacer un análisis parcial, como por ejemplo el movimiento y distribución usados:

Si esta funcionalidad se habilita en SSMS deberíamos poder usar EXPLAIN como en Synapse (https://docs.microsoft.com/en-us/sql/t-sql/queries/explain-transact-sql?view=azure-sqldw-latest) y obtendríamos una visualización gráfica (https://azure.microsoft.com/en-us/blog/unlock-query-performance-with-sql-data-warehouse-using-graphical-execution-plans/):

Otra optimización importante que podemos obtener en estos planes es la extracción de datos que estén particionados en paralelo desde varios nodos. Cada compute node generará una parte proporcional de readers para atacar las particiones que le correspondan en paralelo:

 

Vamos a realizar una prueba creando particionado para nuestra tabla Posts en origen. Utilizaremos un particionado sencillo, en base a rangos de IDs:

USE [StackOverflow2010]

GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [test](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000')

CREATE PARTITION SCHEME [test] AS PARTITION [test] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

ALTER TABLE [dbo].[Posts] DROP CONSTRAINT [PK_Posts__Id] WITH ( ONLINE = OFF )

ALTER TABLE [dbo].[Posts] ADD  CONSTRAINT [PK_Posts__Id] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [test]([Id])

COMMIT TRANSACTION

Una vez tenemos la tabla particionada, vamos a lanzar de nuevo nuestro conteo y vamos a ver en qué se convierte en la traza en el SQL Server 2017:

Podemos ver que el tiempo baja a aproximadamente la mitad de los valores que obteníamos antes (casi 6 segundos). En la traza lo que veremos son 6 peticiones, una por partición, que se lanzan repartidas 50-50 entre los dos nodos disponibles, siendo el tiempo total prácticamente el tiempo de la partición más lenta (la 3):

Hay bastantes escenarios donde dividir una petición en N peticiones por partición puede ser beneficioso. Uno de ellos es cuando tenemos latencia entre la fuente de datos y nuestro servidor desde el que leemos la información. Cada vez vemos más problemas en entornos híbridos cuando se intenta hacer una carga desde un servidor a otro (uno on-premise y el otro en cloud, o ambos en cloud pero en distintos proveedores/datacenters) pero utilizando únicamente 1 conexión y un único thread.

En esos escenarios la latencia penaliza mucho y limita la velocidad máxima que podemos obtener. Cuando tratamos estos temas con clientes suelo apoyarme en esta tabla como referencia: https://docs.microsoft.com/en-us/azure/expressroute/expressroute-troubleshooting-network-performance#latencybandwidth-results

Cuando nuestros procesos de carga se han diseñado y probado en escenarios con latencia LAN local, inferior a 1 ms, no han tenido que adaptarse para esta limitación de ancho de banda por sesión. En estos casos la técnica más recomendable es precisamente aplicar esa división de la consulta original en N bloques y lanzarlos en paralelo desde un paquete de SSIS.

Por último, vamos a ver la potencia de ambos factores, el uso del paralelizado y la mejora que aporta el motor. Modificaremos la consulta anterior y vamos añadirle un cálculo que añada más carga de CPU, una desviación típica, así como ajustar los valores de los TOP para que tenga una duración razonable:

set statistics time on

select top 100 p1.favoritecount, count_big(*) , STDEV(p1.favoritecount)

from dbo.posts p1, (select top 200000 * from dbo.posts) p2 group by p1.favoritecount order by count_big(*) desc

Cuando la lanzamos, vemos que llegamos a usos de CPU elevados en ambos nodos durante varios segundos de forma casi simultánea, demorándose la consulta 46 segundos:

Vamos a añadir otros dos nodos compute al scale-out PolyBase siguiendo los mismos pasos que dimos anteriormente y volveremos a lanzar esta misma consulta. Con esto pasaríamos de 8 cores disponibles para cómputo a 16 cores en total. Al volver a lanzar la consulta lo que vemos es que no llegamos a saturar del todo cada uno de los nodos (aunque sí se reparte la carga equilibradamente) y el tiempo de ejecución baja de 46.7s a 27.3s, un 41% de mejora duplicando la cantidad de CPU y de nodos disponibles. Debemos tener en cuenta que ni el número de particiones disponibles en este ejemplo es elevado ni tampoco están perfectamente balanceadas, por lo que el reparto tampoco ha sido perfecto entre el número de nodos de cómputo:

El siguiente gráfico resume los datos obtenidos en las distintas consultas y configuraciones:

Cuando el coste de la operación es pequeño, las diferencias entre realizar pushdown o no o incluso el usar 1 o más nodos son mínimas. Esto es el caso del count sencillo, conde contar las filas en origen y enviar el resultado o enviar N filas y devolver N en destino puede ser algo bastante indiferente desde el punto de vista de rendimiento.

Cuando la operación ya requiere mayor cómputo, como un producto cartesiano con agregados y conteos, la diferencia de PolyBase ya es sustancial. En un escenario de alto uso de CPU es donde encontramos una reducción del tiempo de respuesta del 50% con dos nodos y otro 40% adicional acumulativo en la configuración con 4 nodos lo que equivale a un 70% de reducción sobre el tiempo total original.

Pese a todo lo comentado en este artículo solo hemos raspado un poco la superficie de PolyBase y mostrado su funcionamiento básico. Si queremos profundizar más y comprender mejor qué está ocurriendo por debajo cuando lanzamos este tipo de operaciones distribuidas existen varias DMVs muy similares a las que tenemos en Synapse/APS/PDW que nos informan de los distintos pasos, tipos de operaciones ejecutadas, sus duraciones, etc.

En conclusión, PolyBase añade a SQL Server un nuevo engine optimizado para operaciones distribuidas con datos remotos. PolyBase nos aporta mejores planes de ejecución y escalado entre varios nodos así como el aprovechamiento de forma transparente del particionado nativo de las fuentes de datos. Si en tu entorno necesitas virtualización de datos y/o distribuir el coste de consultas pesadas entre varios nodos de computación PolyBase Scale-out puede llegar a ser una excelente opción para ti.