Interroger des fichiers avec un pool SQL serverless

10 minutes

Vous pouvez utiliser un pool SQL serverless pour interroger des fichiers de données de différents formats courants, notamment :

Les fichiers texte délimités, comme les fichier CSV.
Les fichiers JSON.
Les fichiers Parquet.

La syntaxe de base pour l’interrogation est la même pour tous ces types de fichiers. Elle est basée sur la fonction SQL OPENROWSET, qui génère un ensemble de lignes tabulaire à partir des données d’un ou plusieurs fichiers. Par exemple, la requête suivante peut être utilisée pour extraire des données à partir de fichiers CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

La fonction OPENROWSET inclut plus des paramètres qui déterminent des facteurs comme :

Le schéma de l’ensemble de lignes résultant
Des options de mise en forme supplémentaires pour les fichiers texte délimités.

Conseil

Vous trouverez la syntaxe complète de la fonction OPENROWSET dans la documentation Azure Synapse Analytics.

La sortie d’OPENROWSET est un ensemble de lignes auquel un alias doit être affecté. Dans l’exemple précédent, les lignes d’alias sont utilisées pour nommer l’ensemble de lignes obtenu.

Le paramètre BULK inclut l’URL complète de l’emplacement du lac de données qui contient les fichiers de données. Il peut s’agir d’un fichier ou d’un dossier avec une expression générique qui permet de filtrer les types de fichiers qui doivent être inclus. Le paramètre FORMAT spécifie le type des données interrogées. L’exemple ci-dessus lit le texte délimité de tous les fichiers .csv situés dans le dossier files.

Notes

Cet exemple suppose que l’utilisateur a accès aux fichiers du magasin sous-jacent. Si les fichiers sont protégés par une clé SAS ou une identité personnalisée, vous devez créer des informations d’identification au niveau du serveur.

Comme indiqué dans l’exemple précédent, vous pouvez utiliser des caractères génériques dans le paramètre BULK pour inclure ou exclure des fichiers de la requête. La liste suivante montre quelques exemples d’utilisation :

https://mydatalake.blob.core.windows.net/data/files/file1.csv : incluez uniquement file1.csv dans le dossier files.
https://mydatalake.blob.core.windows.net/data/files/file*.csv : tous les fichiers .csv du dossier files portant un nom commençant par « file ».
https://mydatalake.blob.core.windows.net/data/files/* : tous les fichiers du dossier files.
https://mydatalake.blob.core.windows.net/data/files/** : tous les fichiers du dossier files et récursivement ses sous-dossiers.

Vous pouvez également spécifier plusieurs chemins de fichier dans le paramètre BULK, en séparant chaque chemin par une virgule.

Interrogation de fichiers texte délimités

Les fichiers texte délimités sont courants dans les entreprises. Le format utilisé dans les fichiers délimités peut varier :

Avec ou sans ligne d’en-tête.
Valeurs délimitées par des virgules ou des tabulations.
Fins de ligne de style Windows et Unix.
Valeurs entre guillemets et sans guillemets, et caractères d’échappement.

Quel que soit le type de fichier délimité que vous utilisez, vous pouvez lire les données qu’il contient à l’aide de la fonction OPENROWSET avec le paramètre FORMAT csv et d’autres paramètres, selon les besoins, pour gérer les détails de mise en forme spécifiques de vos données. Par exemple :

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION est utilisé pour déterminer comment la requête interprète l’encodage de texte utilisé dans les fichiers. La version 1.0 est la valeur par défaut et prend en charge un large éventail d’encodages de fichiers. La version 2.0 prend en charge moins d’encodages, mais offre de meilleures performances. Le paramètre FIRSTROW permet d’ignorer les lignes du fichier texte, d’éliminer tout texte de préambule non structuré et d’ignorer une ligne contenant des en-têtes de colonne.

Les paramètres supplémentaires dont vous pouvez avoir besoin lors de l’utilisation de fichiers texte délimités sont les suivants :

FIELDTERMINATOR : caractère utilisé pour séparer les valeurs de champ sur chaque ligne. Par exemple, un fichier délimité par des tabulations sépare les champs avec un caractère de tabulation (\t). La marque de fin de champ par défaut est une virgule (,).
ROWTERMINATOR : caractère utilisé pour signifier la fin d’une ligne de données. Par exemple, un fichier texte Windows standard utilise la combinaison d’un retour chariot et d’un saut de ligne, indiquée par le code \n, tandis que les fichiers texte de style UNIX utilisent un caractère de saut de ligne unique, qui peut être indiqué à l’aide du code 0x0a.
FIELDQUOTE : caractère utilisé pour placer les valeurs de chaîne entre guillemets. Par exemple, pour garantir que la virgule située dans la valeur du champ d’adresse 126 Main St, apt 2 ne sera pas interprétée comme un délimiteur de champ, vous pouvez placer l’intégralité de la valeur du champ entre guillemets comme ici : "126 Main St, apt 2". Le guillemet double (") est le caractère de guillemet qui est utilisé par défaut pour les champs.

Conseil

Pour en savoir plus sur les autres paramètres disponibles quand vous utilisez des fichiers texte délimités, reportez-vous à la documentation Azure Synapse Analytics.

Spécification du schéma de l’ensemble de lignes

Les fichiers texte délimités incluent souvent des noms de colonne sur la première ligne. La fonction OPENROWSET peut utiliser ceci afin de définir le schéma pour l’ensemble de lignes résultant et déduire automatiquement les types de données des colonnes en fonction des valeurs qu’elles contiennent. Observons par exemple le texte délimité suivant :

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Les données comprennent les trois colonnes suivantes :

product_id (nombre entier)
product_name (chaîne)
list_price (nombre décimal)

Vous pouvez utiliser la requête suivante pour extraire les données avec les noms de colonne corrects et les types de données SQL Server déduits de manière appropriée (dans ce cas, INT, NVARCHAR et DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Le paramètre HEADER_ROW (qui est disponible uniquement lors de l’utilisation de l’analyseur version 2.0) indique au moteur de requête d’utiliser la première ligne de données de chaque fichier en tant que noms de colonne, comme ceci :

product_id	product_name	list_price
123	Widget	12,9900
124	Gadget	3,9900

À présent, tenez compte des données suivantes :

123,Widget,12.99
124,Gadget,3.99

Cette fois, le fichier ne contient pas de noms de colonne sur une ligne d’en-tête. Par conséquent, même si les types de données peuvent toujours être déduits, les noms de colonne sont définis sur C1, C2, C3, et ainsi de suite.

C1	C2	C3
123	Widget	12,9900
124	Gadget	3,9900

Pour spécifier des noms de colonne explicites et des types de données, vous pouvez remplacer les noms de colonne par défaut et les types de données déduits en fournissant une définition de schéma dans une clause WITH, comme ceci :

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Cette requête produit les résultats attendus :

product_id	product_name	list_price
123	Widget	12,99
124	Gadget	3.99

Conseil

Lorsque vous utilisez des fichiers texte, vous pouvez rencontrer une incompatibilité avec les données UTF-8 et le classement utilisé dans la base de données master pour le pool SQL serverless. Pour surmonter ce problème, vous pouvez spécifier un classement compatible pour certaines colonnes VARCHAR du schéma. Pour plus d’informations, consultez l’aide à la résolution des problèmes.

Interrogation des fichiers JSON

JSON est un format populaire pour les applications web qui échangent des données via des interfaces REST ou utilisent des magasins de données NoSQL comme Azure Cosmos DB. Il n’est donc pas rare de conserver des données au format JSON dans des fichiers d’un lac de données à des fins d’analyse.

Par exemple, un fichier JSON qui définit un produit peut ressembler à ceci :

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Pour retourner des données de produit à partir d’un dossier contenant plusieurs fichiers JSON dans ce format, vous pouvez utiliser la requête SQL suivante :

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET n’a pas de format spécifique pour les fichiers JSON. Vous devez donc utiliser le format csv avec FIELDTERMINATOR, FIELDQUOTE et ROWTERMINATOR défini sur 0x0b, et un schéma qui inclut une seule colonne NVARCHAR(MAX). Le résultat de cette requête est un ensemble de lignes contenant une seule colonne de documents JSON :

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Pour extraire des valeurs à partir du JSON, vous pouvez utiliser la fonction JSON_VALUE dans l’instruction SELECT, comme illustré ici :

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Cette requête retournera un ensemble de lignes similaire à celui-ci :

product	price
Widget	12,99
Gadget	3.99

Interrogation des fichiers Parquet

Parquet est un format couramment utilisé pour le traitement du Big Data sur un stockage de fichiers distribués. Il s’agit d’un format de données efficace optimisé pour la compression et l’interrogation analytique.

Dans la plupart des cas, le schéma des données est incorporé dans le fichier Parquet. Vous devez donc uniquement spécifier le paramètre BULK avec le chemin des fichiers que vous souhaitez lire, et le paramètre FORMAT avec la valeur parquet :

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Interroger des données partitionnées

Dans un lac de données, il est courant de partitionner des données en les répartissant dans plusieurs fichiers situés dans des sous-dossiers qui reflètent les critères de partitionnement. Cela permet aux systèmes de traitement distribué de fonctionner en parallèle sur plusieurs partitions des données, ou d’éliminer facilement les lectures de données à partir de dossiers spécifiques en fonction des critères de filtrage. Par exemple, supposons que vous deviez traiter efficacement les données des commandes client et que vous soyez souvent amené à filtrer les commandes passées par année et par mois. Vous pouvez partitionner les données à l’aide de dossiers, comme ceci :

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Pour créer une requête qui filtre les résultats de manière à inclure uniquement les commandes de janvier et février 2020, vous pouvez utiliser le code suivant :

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Les paramètres de chemin de fichier numérotés dans la clause WHERE référencent les caractères génériques des noms de dossiers dans le chemin BULK : le paramètre 1 est représenté par le * dans le nom du dossier year=*, et le paramètre 2 est représenté par le * dans le nom du dossier month=*.

Continuer

Interrogation de fichiers texte délimités

Spécification du schéma de l’ensemble de lignes

Interrogation des fichiers JSON

Interrogation des fichiers Parquet

Interroger des données partitionnées

Commentaires