Bestandsmetagegevens gebruiken in serverloze SQL-poolquery's

Artikel
02/04/2025

In dit artikel leert u hoe u query's kunt uitvoeren op specifieke bestanden of mappen met behulp van metagegevens. Serverloze SQL-pool kan meerdere bestanden en mappen aanpakken. Zie Querymappen en meerdere bestanden voor meer informatie.

Soms moet u mogelijk weten welk bestand of welke mapbron overeenkomt met een specifieke rij in een resultatenset. U kunt de functies filepath gebruiken en filename bestandsnamen en/of het pad in de resultatenset retourneren, of u kunt ze gebruiken om gegevens te filteren op basis van de bestandsnaam of het pad naar de map. Deze functies worden beschreven in de bestandsnaamfunctie en de bestandspadfunctie.

De volgende secties bevatten korte beschrijvingen en codevoorbeelden.

Vereisten

De eerste stap is het maken van een database met een gegevensbron die verwijst naar een opslagaccount. Initialiseer vervolgens de objecten door een installatiescript op die database uit te voeren. Met dit installatiescript worden de gegevensbronnen, referenties voor databasebereik en externe bestandsindelingen gemaakt die in deze voorbeelden worden gebruikt.

Functies

Bestandsnaam

De filename functie retourneert de bestandsnaam waaruit de rij afkomstig is.

In het volgende voorbeeld worden de NYC Yellow Taxi-gegevensbestanden voor september 2017 gelezen en wordt het aantal ritten per bestand geretourneerd. Het OPENROWSET deel van de query geeft aan welke bestanden worden gelezen.

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();

In het volgende voorbeeld ziet u hoe filename() u in de WHERE component kunt gebruiken om de bestanden te filteren die moeten worden gelezen. Het opent de volledige map in het OPENROWSET deel van de query en filtert bestanden in de WHERE component.

Uw resultaten zijn hetzelfde als in het vorige voorbeeld.

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];

Bestandspad

De filepath functie retourneert een volledig of gedeeltelijk pad:

Wanneer aangeroepen zonder parameter, wordt het volledige bestandspad geretourneerd van waaruit de rij afkomstig is. Wanneer DATA_SOURCE wordt gebruikt in OPENROWSET, retourneert het het pad ten opzichte van DATA_SOURCE.
Wanneer deze wordt aangeroepen met een parameter, wordt een deel van het pad geretourneerd dat overeenkomt met het jokerteken op de positie die is opgegeven in de parameter. Parameterwaarde 1 retourneert bijvoorbeeld een deel van het pad dat overeenkomt met het eerste jokerteken.

In het volgende voorbeeld worden NYC Yellow Taxi-gegevensbestanden voor de afgelopen drie maanden van 2017 gelezen. Het retourneert het aantal ritten per bestandspad. Het OPENROWSET deel van de query geeft aan welke bestanden worden gelezen.

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;

In het volgende voorbeeld ziet u hoe filepath() u in de WHERE component kunt gebruiken om de bestanden te filteren die moeten worden gelezen.

U kunt de jokertekens in het OPENROWSET deel van de query gebruiken en de bestanden in de WHERE component filteren. Uw resultaten zijn hetzelfde als in het vorige voorbeeld.

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;

Volgende stap

Query's uitvoeren op Parquet-bestanden met behulp van een serverloze SQL-pool

Delen via