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.

¿Qué es Power Platform?

¿Qué es Power Platform?

Power Platform

Power Platform es un conjunto de herramientas de Microsoft integrada dentro de Office 365, diseñada para analizar, crear soluciones y automatizar procesos de una empresa. Estas herramientas de Microsoft son: Power Apps, Power Automate, Power BI y Power Virtual Agent.

Todas estas herramientas están pensadas para usuarios de negocio, lo que significa que puedes desarrollar aplicaciones y automatizar procesos sin necesidad de escribir una línea de código.

Además pueden integrarse con otras herramientas de Microsoft dentro de Office 365, Dynamics 365, Azure, etc… y crear soluciones integrales para su organización.

Herramientas Microsoft Power Platform

A continuación una pequeña descripción sobre cada una de ellas:

Power Apps

Power Apps permite crear aplicaciones Web y Móvil que aportan transformación digital a procesos manuales y obsoletos de su empresa obteniendo datos de distintos orígenes, todo ello sin necesidad de programación, de una forma rápida, eficaz y rentable.

PowerApps

Power Automate

Power Automate, antes llamado Microsoft Flow, es una herramienta que nos permite crear flujos de trabajo automáticos para ahorrarnos procesos manuales y repetitivos que no aportan valor a nuestro negocio.

Power Automate

Power BI

Power BI es una herramienta de análisis de datos empresariales, que permite dar sentido a sus datos mediante paneles interactivos en tiempo real, liberando la información necesaria para mejorar la toma de decisiones e impulsar la evolución de su empresa.

Además permite la conexión a cientos de orígenes de datos y es accesible desde cualquier dispositivo.

PowerBI

Power Virtual Agent

Con Power Virtual Agent (PVA) automatizamos los niveles de soporte de su empresa a través de bots, desde la creación del diálogo para ponerlos en sistemas de chats usando IA, los usuarios no tienen que pasar demasiado tiempo definiendo estos diálogos, ya que esta herramienta puede comprender las intenciones y activar las acciones correctas.

Power Virtual Agent

En las siguientes referencias pueden encontrar más información sobre cada una de estas herramientas que componen Power Platform.

Referencias:

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.

Azure SQL Database Hyperscale: Una nueva bestia en la manada

Azure SQL Database Hyperscale: Una nueva bestia en la manada

Azure SQL Database HyperScale es una nueva modalidad alternativa al modelo tradicional de Azure SQL Database (single, elastic y managed instance) que aporta unas características especiales que la hacen única. Desde el punto de vista técnico supone una revolución respecto a lo que tenemos disponible on-premise y en otras alternativas cloud. Podríamos decir que actualmente tenemos SQL Server en distintos sabores.

(más…)

Data Masking de datos sensibles… piénsalo dos veces

Data Masking de datos sensibles… piénsalo dos veces

Dynamic data masking (enmascaramiento) es una técnica que busca limitar/ocultar información sensible sin requerir cambios en las aplicaciones. Los datos en la base de datos realmente no se modifican, se alteran “al vuelo” de forma que cuando las consultas devuelven resultados se aplican las máscaras apropiadas. Esto hace que esta funcionalidad sea sencilla de implementar ya que no requiere cambios sustanciales y sea bastante transparente para las aplicaciones que utilizan los datos enmascarados.

(más…)

Escenarios para controlar la gestión de Stocks

Escenarios para controlar la gestión de Stocks

Como bien sabemos, el modelado dimensional está compuesto por dos tipos de tablas. Por un lado, las tablas de hechos, cuya función es la de almacenar datos relacionados con el registro de eventos. Y por otro, las tablas de dimensión, qué relacionadas con las anteriores, permiten desglosar y agrupar las métricas que estás contienen por diversos atributos.

(más…)