Usare i metadati dei file nelle query del pool SQL serverless

Il pool SQL serverless può gestire più file e cartelle, come descritto nell'articolo Eseguire query su cartelle e più file. In questo articolo verrà illustrato come usare le informazioni sui metadati relative a nomi di file e cartelle nelle query.

In alcuni casi, potrebbe essere necessario individuare l'origine del file o della cartella correlata a una riga specifica nel set di risultati.

È possibile usare la funzione filepath e filename per restituire i nomi file e/o il percorso nel set di risultati. In alternativa, è possibile usarli per filtrare i dati in base al nome file e/o al percorso della cartella. Queste funzioni sono descritte nella sezione relativa alla sintassi della funzione filename e della funzione filepath. Nelle sezioni seguenti sono disponibili brevi descrizioni ed esempi.

Prerequisiti

Il primo passaggio consiste nel creare un database con un'origine dati che fa riferimento all'account di archiviazione. Inizializzare quindi gli oggetti eseguendo uno script di installazione su tale database. Questo script di installazione creerà le origini dati, le credenziali con ambito database e i formati di file esterni usati in questi esempi.

Funzioni

Filename

Questa funzione restituisce il nome del file da cui ha origine la riga.

L'esempio seguente legge i file di dati dei taxi di New York per gli ultimi tre mesi del 2017 e restituisce il numero di corse per ogni file. La parte OPENROWSET della query specifica i file che verranno letti.

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'esempio seguente mostra come usare filename() nella clausola WHERE per filtrare i file da leggere. Accede all'intera cartella nella parte OPENROWSET della query e filtra i file nella clausola WHERE.

I risultati saranno identici a quelli dell'esempio precedente.

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

La funzione filepath restituisce un percorso completo o parziale:

• Se viene chiamata senza un parametro, restituisce il percorso completo del file da cui ha origine la riga. Quando DATA_SOURCE viene usato in OPENROWSET, restituisce il percorso relativo di DATA_SOURCE.
• Se viene chiamata con un parametro, viene restituita una parte del percorso che corrisponde al carattere jolly nella posizione specificata nel parametro. Ad esempio, il valore del parametro 1 restituisce la parte del percorso che corrisponde al primo carattere jolly.

L'esempio seguente legge i file di dati dei taxi di New York per gli ultimi tre mesi del 2017. Restituisce il numero di corse per ogni percorso file. La parte OPENROWSET della query specifica i file che verranno letti.

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'esempio seguente mostra come usare filepath() nella clausola WHERE per filtrare i file da leggere.

È possibile usare i caratteri jolly nella parte OPENROWSET della query e filtrare i file nella clausola WHERE. I risultati saranno identici a quelli dell'esempio precedente.

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;

Passaggi successivi

Nell'articolo successivo si imparerà a eseguire query su file Parquet.