Bestandsmetagegevens gebruiken in serverloze SQL-poolquery's

Artikel
07/23/2024

Serverloze SQL-pool kan meerdere bestanden en mappen aanpakken, zoals beschreven in de querymappen en het artikel over meerdere bestanden . In dit artikel leert u hoe u metagegevensgegevens gebruikt over bestands- en mapnamen in de query's.

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

U kunt de functie filepath gebruiken en filename bestandsnamen en/of het pad in de resultatenset retourneren. U kunt ze ook gebruiken om gegevens te filteren op basis van de bestandsnaam en/of het pad naar de map. Deze functies worden beschreven in de syntaxissectie bestandsnaamfunctie en bestandspadfunctie. In de volgende secties vindt u korte beschrijvingen in 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, databasereferenties en externe bestandsindelingen gemaakt die in deze voorbeelden worden gebruikt.

Functies

Bestandsnaam

Deze 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 gedeelte OPENROWSET 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 volledige map in het GEDEELTE OPENROWSET 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 bestandspadfunctie retourneert een volledig of gedeeltelijk pad:

Wanneer deze wordt 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 afgelopen drie maanden van 2017 gelezen. Het retourneert het aantal ritten per bestandspad. Het gedeelte OPENROWSET 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 gedeelte OPENROWSET van de query gebruiken en de bestanden filteren in de WHERE-component. 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 stappen

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

Share via