Interroger des fichiers Parquet à l’aide d’un pool SQL serverless dans Azure Synapse Analytics

Article
03/18/2023

Cet article explique comment écrire une requête à l’aide d’un pool SQL serverless pour lire des fichiers Parquet.

Exemple de démarrage rapide

La fonction OPENROWSET vous permet de lire le contenu d’un fichier Parquet en fournissant l’URL de votre fichier.

Lire un fichier Parquet

Le moyen le plus simple d’afficher le contenu de votre fichier PARQUET consiste à fournir l’URL du fichier à la fonction OPENROWSET et à spécifier le FORMAT Parquet. Si le fichier est disponible publiquement ou si votre identité Microsoft Entra peut y accéder, vous devez voir le contenu du fichier en utilisant une requête comme celle affichée dans l’exemple suivant :

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

Assurez-vous que vous pouvez accéder à ce fichier. Si votre fichier est protégé par une clé SAP ou une identité Azure personnalisée, vous devez configurer les informations d’identification au niveau du serveur pour la connexion SQL.

Important

Assurez-vous que vous utilisez un classement de base de données UTF-8 (par exemple Latin1_General_100_BIN2_UTF8), car les valeurs de chaîne dans les fichiers PARQUET sont encodées à l’aide d’un encodage UTF-8. Une incompatibilité entre l’encodage de texte dans le fichier PARQUET et le classement peut entraîner des erreurs de conversion inattendues. Vous pouvez facilement modifier le classement par défaut de la base de données actuelle à l’aide de l’instruction T-SQL suivante : ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;Pour plus d’informations sur les classements, consultez Types de classements pris en charge pour Synapse SQL.

Si vous utilisez le classement Latin1_General_100_BIN2_UTF8, vous obtiendrez un renforcement des performances supplémentaire par rapport aux autres classements. Le classement Latin1_General_100_BIN2_UTF8 est compatible avec les règles de tri de chaîne Parquet. Le pool SQL est en mesure d’éliminer certaines parties des fichiers Parquet qui ne contiennent pas les données nécessaires dans les requêtes (nettoyage de segment de fichier/colonne). Si vous utilisez d’autres classements, toutes les données des fichiers Parquet sont chargées dans Synapse SQL et le filtrage se produit dans le processus SQL. Le classement Latin1_General_100_BIN2_UTF8 offre une optimisation supplémentaire des performances qui fonctionne uniquement pour Parquet et Cosmos DB. L’inconvénient est que vous perdez les règles de comparaison affinées, par exemple le non-respect de la casse.

Utilisation d’une source de données

L’exemple précédent utilise le chemin complet du fichier. Vous pouvez également créer une source de données externe avec l’emplacement qui pointe vers le dossier racine du stockage et utiliser cette source de données et le chemin relatif du fichier dans la fonction 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 une source de données est protégée par une clé SAS ou une identité personnalisée, vous pouvez configurer la source de données avec des informations d’identification dans l’étendue de la base de données.

Spécifier explicitement le schéma

OPENROWSET vous permet de spécifier explicitement les colonnes que vous souhaitez lire à partir du fichier à l’aide de la clause 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

Important

Veillez à spécifier explicitement un classement UTF-8 (par exemple Latin1_General_100_BIN2_UTF8) pour toutes les colonnes de chaîne dans la clause WITH, ou définissez un classement UTF-8 au niveau de la base de données. Une incompatibilité entre l’encodage de texte dans le fichier et le classement de colonne de chaîne peut entraîner des erreurs de conversion inattendues. Vous pouvez facilement modifier le classement par défaut de la base de données actuelle à l’aide de l’instruction T-SQL suivante : ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Vous pouvez facilement définir le classement sur les types de colonne, par exemple : geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8Pour plus d’informations sur les classements, consultez Types de classements pris en charge pour Synapse SQL.

Dans les sections suivantes, vous pouvez voir comment interroger différents types de fichiers Parquet.

Prérequis

La première étape consiste à créer une base de données avec une source de données qui fait référence au compte de stockage NYC Yellow Taxi. Ensuite, initialisez les objets en exécutant le script d’installation sur cette base de données. Ce script d’installation crée les sources de données, les informations d’identification délimitées à la base de données et les formats de fichiers externes utilisés dans ces exemples.

Dataset

Le jeu de données NYC Yellow Taxi est utilisé dans cet exemple. Vous pouvez interroger les fichiers Parquet de la même façon que vous lisez des fichiers CSV. La seule différence réside dans le fait que le paramètre FILEFORMAT doit être défini sur PARQUET. Les exemples de cet article montrent les spécificités de lecture des fichiers Parquet.

Ensemble de requêtes de fichiers Parquet

Vous pouvez spécifier uniquement les colonnes intéressantes lorsque vous interrogez des fichiers 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;

Inférence de schéma automatique

Vous n’avez pas besoin d'utiliser la clause OPENROWSET WITH lors de la lecture de fichiers Parquet. Les noms de colonnes et les types de données sont automatiquement lus à partir des fichiers Parquet.

Gardez à l’esprit que si vous lisez plusieurs fichiers à la fois, le schéma, les noms des colonnes et les types de données seront inférés du premier fichier que le service reçoit du stockage. Cela peut signifier que certaines des colonnes attendues seront omises, tout cela parce que le fichier utilisé par le service pour définir le schéma ne contenait pas ces colonnes. Pour spécifier explicitement le schéma, utilisez la clause OPENROWSET WITH.

L’exemple suivant montre les fonctionnalités d’inférence automatique du schéma pour des fichiers Parquet. Il renvoie le nombre de lignes de septembre 2018 sans spécifier de schéma.

Notes

Vous n’avez pas besoin de spécifier de colonnes dans la clause OPENROWSET WITH lors de la lecture de fichiers Parquet. Dans ce cas, le service de requête du pool SQL serverless utilise les métadonnées dans le fichier Parquet et lie les colonnes par nom.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Interroger des données partitionnées

Le jeu de données fourni dans cet exemple est divisé (partitionné) en sous-dossiers distincts. Utilisez la fonction filepath pour cibler des partitions spécifiques. Cet exemple montre les montants par année, par mois et par type de paiement pour les trois premiers mois de 2017.

Notes

La requête du pool SQL serverless est compatible avec le schéma de partitionnement Hive/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;

Mappage des types

Pour le mappage de type Parquet en type SQL natif, sélectionnez Mappage de type pour Parquet.

Étapes suivantes

Passez à l’article suivant pour savoir comment interroger des types imbriqués Parquet.

Partage via