Cada vez es más habitual trabajar con ficheros de texto en CSV desde un data lake, un blob, una URL, etc. para virtualización de datos. El uso de este tipo de estructuras de datos puede ocasionar que los optimizadores de consultas generen planes ineficientes, entre otras razones, por la falta de estadísticas.

Consume ficheros CSV. en Synapse

Vamos a plantear un ejemplo sencillo donde podamos ver cómo podemos ayudar al optimizador al consumir ficheros de tipo CSV.

Para ello comenzaremos conectándonos desde Synapse Studio y creando una base de datos “test” sobre el serverless pool:

Synapse studio

Una vez conectados vamos a lanzar una consulta sobre un CSV público que contiene datos sobre el COVID-19:

 select top 10 *

from openrowset(

    bulk ‘https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv’,

    format = ‘csv’,

    parser_version = ‘2.0’,

    firstrow = 2 ) as rows

Estadísticas sobre ficheros CSV en Synapse

A continuación obtendremos información sobre los tipos de columnas que tenemos en
el CSV:

exec sp_describe_first_result_set N’select top 10 *

from openrowset(

    bulk ”https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv”,

    format = ”csv”,

    parser_version = ”2.0”,

 

    firstrow = 2 ) as rows’

Estadísticas sobre ficheros CSV en Synapse

Desgraciadamente la “inteligencia” en la detección de las columnas está limitada y básicamente nos dice que la primera columna es una fecha, luego tenemos 5 enteros y luego campos de texto.

En este caso tenemos disponible la información también en formato parquet, por lo que podemos aprovecharnos de la metadata más precisa que sí tenemos en este formato:

Estadísticas sobre ficheros CSV en Synapse
Estadísticas sobre ficheros CSV en Synapse

Con esta información ya más completa procederemos a crear los formatos que necesitamos y exponer los datos a través de una tabla externa. Primero creamos nuestro external file format CSV:

 CREATE EXTERNAL FILE FORMAT csv

WITH (  

    FORMAT_TYPE = DELIMITEDTEXT,

    FORMAT_OPTIONS (

        FIELD_TERMINATOR = ‘,’,

        STRING_DELIMITER = ‘”‘,

        FIRST_ROW = 2

    )

);

 A continuación nuestro external data source pandemic_latest:

 CREATE EXTERNAL DATA SOURCE pandemic_latest

WITH ( LOCATION = ‘https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/’)

GO

 Y por último crearemos nuestra tabla externa:

 create external table ecdccases (

    date_rep    date,

    day         smallint,

    month       smallint,

    year        smallint,

    cases       smallint,

    deaths      smallint,

    countries_and_territories       varchar(8000),

    geo_id                          varchar(8000),

    country_territory_code          varchar(8000),

    pop_data_2018                   int,

    continent_exp                   varchar(8000),

    load_date                       datetime2(7),

    iso_country                     varchar(8000)

with (

    data_source= pandemic_latest,

    location = ‘ecdc_cases.csv’,

    file_format = csv

);

 

Comprobaremos que podemos consultar la información ya con sus nombres de columnas y tipos de datos correctos:

Estadísticas sobre ficheros CSV en Synapse

Si lanzamos una consulta sobre esta tabla externa, podemos ver que el rendimiento es el mismo tanto al solicitar información existente como información no existente (por ejemplo datos de un país inexistente):

 select count(*) from ecdccases e1 where countries_and_territories=‘Afghanistan’

— 2 segundos

select count(*) from ecdccases e1 where countries_and_territories=‘ZZZZZZZ’

— 2 segundos

 

En este caso, lo ideal sería que el resultado inexistente se devolviera de forma instantánea, ya que no vamos a tener registros que cumplan esta condición. Para que ello fuera posible, se necesitaría algún tipo de ayuda extra que nos indicara que no tenemos datos para dicho territorio. ¿Será una estadística sobre esta columna útil?

 Crearemos la estadística sobre la columna, teniendo en cuenta que actualmente estamos limitados a usar FULLSCAN:

 

CREATE STATISTICS countries ON ecdccases (countries_and_territories) WITH FULLSCAN,

 NORECOMPUTE

 

Una vez creada, si volvemos a lanzar las anteriores consultas vemos que los tiempos son exactamente iguales por lo que no es capaz de aprovechar la información estadística para acelerar la operación cuando tenemos filtros que devuelven 0 registros:

 select count(*) from ecdccases e1 where countries_and_territories=‘Afghanistan’

— 2 segundos

select count(*) from ecdccases e1 where countries_and_territories=‘ZZZZZZZ’

— 2 segundos

 

No olvidemos que el formato CSV no es el preferible en muchos casos para trabajar como formato final, sino que más habitualmente trabajaremos con otros formatos como Parquet. En el caso de Parquet podemos hacer una prueba y veremos como para estos mismos datos el tiempo de respuesta es prácticamente instantáneo:

 select *

from openrowset(

    bulk ‘https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet’,

    format = ‘parquet’ ) as rows

where countries_and_territories = ‘ZZZZZZZZ’

— < 1 segundo

 

 Vamos a borrar la estadística antes de proceder a probar una consulta un poco más compleja:

 DROP STATISTICS ecdccases.countries

 

La consulta sería la siguiente, la cual por nuestro conocimiento de los datos sabemos que no debería devolver ningún registro. Sin embargo el tiempo de procesado es de 5 segundos:

 select * from ecdccases e1 

where e1.countries_and_territories in (

    select countries_and_territories from ecdccases e2 where e2.countries_and_territories = ‘ZZZZZZZZ’

    )

 — 5 segundos   

 

A continuación vamos a volver a crear la estadística y lanzaremos de nuevo la misma operación:

 CREATE STATISTICS countries ON ecdccases (countries_and_territories) WITH FULLSCAN,

 NORECOMPUTE

 select * from ecdccases e1 

where e1.countries_and_territories in (

    select countries_and_territories from ecdccases e2 

where e2.countries_and_territories = ‘ZZZZZZZZ’

    )

Estadísticas sobre ficheros CSV en Synapse

En este caso hablamos de reducir a menos de la mitad el tiempo de la consulta. Aunque en este caso hablemos de 3 segundos, pensemos que en operaciones mucho más pesadas la diferencia puede ser muy significativa. Por tanto, a medida que la complejidad de las consultas aumenta, más impacto tendrá en rendimiento el que vayamos “a ciegas” sin estadísticas.

Al igual que otras estadísticas, si los datos cambian, dejará de estar actualizada. Actualmente la única forma que tenemos de actualizar las estadísticas es borrándolas y volviéndolas a generar:

DROP STATISTICS ecdccases.countries

 

CREATE STATISTICS countries ON ecdccases (countries_and_territories) WITH FULLSCAN,

 NORECOMPUTE

Me gustaría también hacer hincapié en un problema que tenemos con la opción serverless SQL pool. Concretamente me refiero a no tenemos la posibilidad de ver planes de ejecución lo cual nos deja también  bastante “a ciegas” cuando algo no funciona como esperamos:

Estadísticas sobre ficheros CSV en Synapse

Si creamos un pool dedicado veremos como sí tenemos dicha opción habilitada cuando lancemos consultas desde él:

Estadísticas sobre ficheros CSV en Synapse

En todo caso el soporte para los planes desde Synapse Studio está un poco verde para el tuning. Por ejemplo, si intentamos obtener el plan estimado nos aparece una ventana indicándonos que nos lo descarguemos localmente y lo abramos con Management Studio:

Estadísticas sobre ficheros CSV en Synapse

Pero ni siquiera la última versión de Management Studio disponible, la 18.9, es capaz de abrir los planes:

Estadísticas sobre ficheros CSV en Synapse

En los casos de consultas complejas cuyo rendimiento no sea idóneo, es de gran ayuda poder consultar dichos planes para entender qué está ocurriendo por debajo. Para que podamos experimentar en nuestras carnes la lectura en formato XML, en la documentación oficial tenemos un ejemplo del plan de una consulta de complejidad media: https://docs.microsoft.com/en-us/sql/t-sql/queries/explain-transact-sql?view=azure-sqldw-latest#examples

 

También tenemos un gran conjunto de DMVs que exponen los distintos pasos de cada request que vienen heredadas del Parallel DataWarehouse del que Synapse deriva. En muchos casos nos servirán para diagnosticar aquellos pasos más lentos de nuestras peticiones para poder realizar ajustes en las estrategias de distribución de datos o en nuestras propias consultas: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sql-and-parallel-data-warehouse-dynamic-management-views?view=aps-pdw-2016-au7

 

Por último recomendamos armarse de paciencia al trabajar con Synapse ya que muchos errores requieren minutos para aflorar, aunque se traten de errores de conectividad o de formato bastante triviales que normalmente esperaríamos que fuesen instantáneos:

Estadísticas sobre ficheros CSV en Synapse

Conclusión

Aunque Synapse nos da acceso sencillo a un sistema de procesamiento masivamente paralelo (MPP) ello no significa que podamos despreocuparnos totalmente del funcionamiento de sus “tripas”.  El optimizador de consultas que incluye, el cual es derivado del motor de optimización de SQL Server, no nos podrá ofrecer los mejores rendimientos si no le proporcionamos información estadística actualizada.

 

Esta información estadística estará disponible de forma automática, autogenerada, cuando usemos ficheros Parquet o tablas en nuestro pool dedicado. En otras ocasiones, como con los ficheros CSV, tendremos que ayudar al motor creando dichas estadísticas manualmente. En general es conveniente que en caso de problemas de rendimiento revisemos las estadísticas disponibles así como que las actualicemos si están desactualizadas.

0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Leer más

Autenticación Integrada en Azure Database con SSIS

En muchos escenarios se nos presenta la necesidad de usar autenticación integrada para acceder a los orígenes de datos necesarios para alimentar nuestro sistema analítico. Con el uso cada vez más extendido de Azure, como al menos parte de nuestra infraestructura, algunos de estos orígenes van a estar alojados en bases de datos en Azure. En este caso vamos hablar de un error real que hemos tenido en la configuración y uso de la autenticación integrada contra bases de datos Azure con SSIS.