Använda filmetadata i serverlösa SQL-poolfrågor

Serverlös SQL-pool kan hantera flera filer och mappar enligt beskrivningen i artikeln Frågemappar och flera filer . I den här artikeln får du lära dig hur du använder metadatainformation om fil- och mappnamn i frågorna.

Ibland kan du behöva veta vilken fil eller mappkälla som korrelerar med en viss rad i resultatuppsättningen.

Du kan använda funktionen filepath och filename för att returnera filnamn och/eller sökvägen i resultatuppsättningen. Du kan också använda dem för att filtrera data baserat på filnamnet och/eller mappsökvägen. Dessa funktioner beskrivs i syntaxavsnittet filnamnsfunktion och filsökvägsfunktion. I följande avsnitt hittar du korta beskrivningar i exemplen.

Förutsättningar

Det första steget är att skapa en databas med en datakälla som refererar till lagringskontot. Initiera sedan objekten genom att köra installationsskriptet på databasen. Det här installationsskriptet skapar datakällor, databasbegränsade autentiseringsuppgifter och externa filformat som används i dessa exempel.

Functions

Filnamn

Den här funktionen returnerar filnamnet som raden kommer från.

Följande exempel läser datafilerna för NYC Yellow Taxi under de senaste tre månaderna av 2017 och returnerar antalet turer per fil. OPENROWSET-delen av frågan anger vilka filer som ska läsas.

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

I följande exempel visas hur filename() kan användas i WHERE-satsen för att filtrera de filer som ska läsas. Den kommer åt hela mappen i OPENROWSET-delen av frågan och filtrerar filer i WHERE-satsen.

Resultatet blir detsamma som i föregående exempel.

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

Funktionen filepath returnerar en fullständig eller partiell sökväg:

• När den anropas utan en parameter returneras den fullständiga filsökvägen som raden kommer från. När DATA_SOURCE används i OPENROWSET returneras sökvägen i förhållande till DATA_SOURCE.
• När den anropas med en parameter returneras en del av sökvägen som matchar jokertecknet på den position som anges i parametern . Parametervärdet 1 returnerar till exempel en del av sökvägen som matchar det första jokertecknet.

Följande exempel läser datafilerna för NYC Yellow Taxi under de tre sista månaderna 2017. Den returnerar antalet körningar per filsökväg. OPENROWSET-delen av frågan anger vilka filer som ska läsas.

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;

I följande exempel visas hur filepath() kan användas i WHERE-satsen för att filtrera de filer som ska läsas.

Du kan använda jokertecken i OPENROWSET-delen av frågan och filtrera filerna i WHERE-satsen. Resultatet blir detsamma som i föregående exempel.

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;

Nästa steg

I nästa artikel får du lära dig hur du kör frågor mot Parquet-filer.