Bestandsmetagegevens gebruiken in serverloze SQL-poolquery's

Artikel
06/01/2023

Serverloze SQL-pool kan betrekking hebben op meerdere bestanden en mappen, zoals beschreven in het artikel Querymappen en meerdere bestanden . In dit artikel leert u hoe u metagegevens kunt gebruiken over bestands- en mapnamen in de query's.

Soms moet u weten welk bestand of welke mapbron overeenkomt met een specifieke rij in de resultatenset.

U kunt functie filepath en filename gebruiken om bestandsnamen en/of het pad in de resultatenset te retourneren. U kunt ze ook gebruiken om gegevens te filteren op basis van de bestandsnaam en/of het mappad. Deze functies worden beschreven in de syntaxissectie bestandsnaamfunctie en bestandspadfunctie. In de volgende secties vindt u korte beschrijvingen samen met voorbeelden.

Vereisten

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

Functions

Bestandsnaam

Deze functie retourneert de bestandsnaam waaruit de rij afkomstig is.

Het volgende voorbeeld leest de NYC Yellow Taxi-gegevensbestanden voor de laatste drie maanden van 2017 en retourneert het aantal ritten per bestand. Het OPENROWSET-gedeelte 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 bestandsnaam() kan worden gebruikt in de WHERE-component om de bestanden te filteren die moeten worden gelezen. Het opent de hele map in het OPENROWSET-gedeelte van de query en filtert bestanden in de WHERE-component.

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

Filepath

De bestandspadfunctie retourneert een volledig of gedeeltelijk pad:

Wanneer aangeroepen zonder parameter, wordt het volledige bestandspad geretourneerd waaruit de rij afkomstig is. Wanneer DATA_SOURCE wordt gebruikt in OPENROWSET, wordt het pad ten opzichte van DATA_SOURCE geretourneerd.
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 laatste drie maanden van 2017 gelezen. Het aantal ritten per bestandspad wordt geretourneerd. Het OPENROWSET-gedeelte 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() kan worden gebruikt in de WHERE-component om de bestanden te filteren die moeten worden gelezen.

U kunt de jokertekens in het OPENROWSET-gedeelte van de query gebruiken en de bestanden in de WHERE-component filteren. De 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 stappen

In het volgende artikel leert u hoe u query's kunt uitvoeren op Parquet-bestanden.

Share via