Utiliser les métadonnées de fichier dans les requêtes de pool SQL sans serveur

• Démarrez un essai gratuit Fabric.
• Assistant Migration pour Fabric Data Warehouse.

Dans cet article, vous apprendrez à interroger des fichiers ou des dossiers spécifiques à l'aide des métadonnées. Le pool SQL serverless peut traiter plusieurs fichiers et dossiers. Pour plus d’informations, consultez Requête de répertoires et de plusieurs fichiers.

Vous devrez peut-être parfois savoir quelle source de fichier ou de dossier est corrélée à une ligne spécifique dans un jeu de résultats. Vous pouvez utiliser les fonctions filepath et filename pour retourner des noms de fichiers et/ou le chemin d’accès dans le jeu de résultats, ou vous pouvez les utiliser pour filtrer les données en fonction du nom de fichier ou du chemin d’accès au dossier. Ces fonctions sont décrites dans les fonctions filename et filepath.

Les sections suivantes fournissent de brèves descriptions et des exemples de code.

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 à un compte de stockage. Ensuite, initialisez les objets en exécutant un script d’installation sur cette base de données. Ce script crée les sources de données, les informations d'identification étendues à la base de données et les formats de fichiers externes utilisés dans ces exemples.

Fonctions

Nom du fichier

La fonction filename retourne le nom du fichier d’où provient la ligne.

L’exemple suivant lit les fichiers de données de NYC Yellow Taxi du mois de septembre 2017 et retourne le nombre de courses par fichier. La partie OPENROWSET de la requête spécifie les fichiers à lire.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

L’exemple suivant montre comment utiliser filename() dans la clause WHERE pour filtrer les fichiers à lire. Il accède au dossier entier dans la partie OPENROWSET de la requête et filtre les fichiers dans la clause WHERE.

Vos résultats seront les mêmes que dans l’exemple précédent.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Chemin de fichier

La fonction filepath retourne un chemin d’accès complet ou partiel :

• En cas d’appel sans paramètre, elle retourne le chemin complet du fichier d’où provient la ligne. Lorsque DATA_SOURCE est utilisé dans OPENROWSET, il retourne le chemin d’accès relatif à DATA_SOURCE.
• Lorsqu'on l'appelle avec un paramètre, elle retourne une partie du chemin qui correspond au caractère générique à la position spécifiée dans le paramètre. Par exemple, la valeur de paramètre 1 retourne une partie du chemin qui correspond au premier caractère générique.

L’exemple suivant lit les fichiers de données NYC Yellow Taxi pour les trois derniers mois de 2017. Retourne le nombre de courses par chemin de fichier. La partie OPENROWSET de la requête spécifie les fichiers à lire.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

L’exemple suivant montre comment utiliser filepath() dans la clause WHERE pour filtrer les fichiers à lire.

Vous pouvez utiliser les caractères génériques dans la partie OPENROWSET de la requête et filtrer les fichiers dans la clause WHERE. Vos résultats seront les mêmes que dans l’exemple précédent.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',        
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Étape suivante