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:
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
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’
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:
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:
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’
)
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:
Si creamos un pool dedicado veremos como sí tenemos dicha opción habilitada cuando lancemos consultas desde él:
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:
Pero ni siquiera la última versión de Management Studio disponible, la 18.9, es capaz de abrir los planes:
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:
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.
