Consulta de archivos de Parquet mediante un grupo de SQL sin servidor en Azure Synapse Analytics
En este artículo, aprenderá a escribir una consulta mediante un grupo de SQL sin servidor que leerá archivos de Parquet.
Ejemplo de inicio rápido
La función OPENROWSET
permite leer el contenido del archivo Parquet al proporcionar la dirección URL al archivo.
Lectura del archivo de Parquet
La forma más fácil de ver el contenido del archivo PARQUET
es proporcionar la dirección URL del archivo a la función OPENROWSET
y especificar el FORMAT
de Parquet. Si el archivo está disponible públicamente o si la identidad de Microsoft Entra puede tener acceso a este archivo, debería poder ver el contenido del archivo mediante la consulta como la que se muestra en el ejemplo siguiente:
select top 10 *
from openrowset(
bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
format = 'parquet') as rows
Asegúrese de que puede tener acceso a este archivo. Si el archivo está protegido con una clave SAS o una identidad personalizada de Azure, necesitaría configurar una credencial de nivel de servidor para el inicio de sesión de SQL.
Importante
Asegúrese de usar alguna intercalación de base de datos UTF-8 (por ejemplo, Latin1_General_100_BIN2_UTF8
) porque los valores de cadena de los archivos de Parquet se codifican como UTF-8.
Una falta de coincidencia entre la codificación de texto del archivo de Parquet y la intercalación puede producir errores de conversión inesperados.
Puede cambiar fácilmente la intercalación predeterminada de la base de datos actual mediante la siguiente instrucción T-SQL: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;
Para obtener más información sobre las intercalaciones, consulte Tipos de intercalación admitidos para Synapse SQL.
Si usa la intercalación Latin1_General_100_BIN2_UTF8
, se obtiene un aumento del rendimiento adicional en comparación con las otras intercalaciones. La intercalación Latin1_General_100_BIN2_UTF8
es compatible con las reglas de ordenación de cadenas parquet. El grupo de SQL es capaz de eliminar algunas partes de los archivos parquet que no contendrán los datos necesarios en las consultas (eliminación de segmentos de archivos o columnas). Si usa otras intercalaciones, todos los datos de los archivos parquet se cargarán en Synapse SQL y el filtrado se está produciendo dentro del proceso SQL. La intercalación Latin1_General_100_BIN2_UTF8
tiene optimización de rendimiento adicional que solo funciona para Parquet y Cosmos DB. La desventaja es que se pierden reglas de comparación específicas, como la no distinción entre mayúsculas y minúsculas.
Uso del origen de datos
En el ejemplo anterior se usa la ruta de acceso completa al archivo. Como alternativa, puede crear un origen de datos externo con la ubicación que apunta a la carpeta raíz del almacenamiento y usar ese origen de datos y la ruta de acceso relativa al archivo en la función OPENROWSET
:
create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
bulk 'latest/ecdc_cases.parquet',
data_source = 'covid',
format = 'parquet'
) as rows
Si un origen de datos está protegido con una clave SAS o una identidad personalizada, puede configurar el origen de datos con una credencial de ámbito de base de datos.
Especificación explícita del esquema
OPENROWSET
permite especificar explícitamente qué columnas desea leer del archivo con la cláusula WITH
:
select top 10 *
from openrowset(
bulk 'latest/ecdc_cases.parquet',
data_source = 'covid',
format = 'parquet'
) with ( date_rep date, cases int, geo_id varchar(6) ) as rows
Importante
Asegúrese de especificar explícitamente alguna intercalación UTF-8 (por ejemplo, Latin1_General_100_BIN2_UTF8
) para todas las columnas de cadena de la cláusula WITH
o establezca alguna intercalación UTF-8 en el nivel de base de datos.
La falta de coincidencia entre la codificación de texto del archivo y la intercalación de las columnas de cadena podría producir errores de conversión inesperados.
Puede cambiar fácilmente la intercalación predeterminada de la base de datos actual mediante la siguiente instrucción T-SQL: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;
Puede establecer fácilmente la intercalación en los tipos de columnas, por ejemplo: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8
Para obtener más información sobre las intercalaciones, consulte Tipos de intercalación admitidos para Synapse SQL.
En las secciones siguientes, puede ver cómo consultar varios tipos de archivos PARQUET.
Prerrequisitos
El primer paso consiste en crear una base de datos con un origen de datos que haga referencia a la cuenta de almacenamiento de NYC Yellow Taxi. Luego, se inicializan los objetos, para lo que hay que ejecutar un script de instalación en esa base de datos. Este script de instalación creará los orígenes de datos, las credenciales con ámbito de base de datos y los formatos de archivo externos que se usan en estos ejemplos.
Dataset
En este ejemplo se usa el conjunto de datos NYC Yellow Taxi. Puede consultar los archivos Parquet de la misma manera en que lee archivos CSV. La única diferencia es que el parámetro FILEFORMAT
debe establecerse en PARQUET
. En los ejemplos de este artículo se muestran los detalles de la lectura de archivos de Parquet.
Consulta de conjunto de archivos de Parquet
Solo puede especificar las columnas de interés al consultar los archivos de Parquet.
SELECT
YEAR(tpepPickupDateTime),
passengerCount,
COUNT(*) AS cnt
FROM
OPENROWSET(
BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
DATA_SOURCE = 'YellowTaxi',
FORMAT='PARQUET'
) WITH (
tpepPickupDateTime DATETIME2,
passengerCount INT
) AS nyc
GROUP BY
passengerCount,
YEAR(tpepPickupDateTime)
ORDER BY
YEAR(tpepPickupDateTime),
passengerCount;
Inferencia automática del esquema
No es necesario usar la cláusula OPENROWSET WITH al leer los archivos de Parquet. Los nombres de columna y los tipos de datos se leen automáticamente desde los archivos de Parquet.
Tenga en cuenta que, si está leyendo varios archivos a la vez, el esquema, los nombres de columna y los tipos de datos se deducirán del primer servicio de archivos que se obtenga del almacenamiento. Esto puede significar que algunas de las columnas esperadas estén omitidas, y todo porque el archivo utilizado por el servicio para definir el esquema no contenía estas columnas. Para especificar explícitamente el esquema, use la cláusula OPENROWSET WITH.
En el ejemplo siguiente se muestran las funcionalidades de inferencia automática del esquema para los archivos Parquet. Devuelve el número de filas de septiembre de 2018 sin especificar un esquema.
Nota
No es necesario especificar columnas en la cláusula OPENROWSET WITH al leer los archivos de Parquet. En ese caso, el servicio de consulta del grupo de SQL sin servidor utilizará los metadatos del archivo de Parquet y enlazará las columnas por el nombre.
SELECT TOP 10 *
FROM
OPENROWSET(
BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
DATA_SOURCE = 'YellowTaxi',
FORMAT='PARQUET'
) AS nyc
Consulta de datos con particiones
El conjunto de datos que se proporciona en este ejemplo se divide (particiona) en subcarpetas independientes. Puede dirigirse a particiones específicas mediante la función filepath. Este ejemplo muestra los importes de las tarifas por year, month y payment_type durante los tres primeros meses de 2017.
Nota
La consulta del grupo de SQL sin servidor es compatible con el esquema de partición de Hive y Hadoop.
SELECT
YEAR(tpepPickupDateTime),
passengerCount,
COUNT(*) AS cnt
FROM
OPENROWSET(
BULK 'puYear=*/puMonth=*/*.snappy.parquet',
DATA_SOURCE = 'YellowTaxi',
FORMAT='PARQUET'
) nyc
WHERE
nyc.filepath(1) = 2017
AND nyc.filepath(2) IN (1, 2, 3)
AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
passengerCount,
YEAR(tpepPickupDateTime)
ORDER BY
YEAR(tpepPickupDateTime),
passengerCount;
Asignación de tipos
Para la asignación de tipos de Parquet al tipo nativo de SQL, consulte la asignación de tipos para Parquet.
Pasos siguientes
Pase al siguiente artículo para obtener más información sobre cómo consultar los tipos anidados de Parquet.