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

Rubén Garrigós