Utilizar metadados de ficheiros em consultas do conjunto de SQL sem servidor

Artigo
02/04/2025

Neste artigo, você aprenderá a consultar arquivos ou pastas específicos usando metadados. O pool SQL sem servidor pode endereçar vários arquivos e pastas. Para obter mais informações, consulte Consultar pastas e vários arquivos.

Às vezes, talvez seja necessário saber qual fonte de arquivo ou pasta se correlaciona a uma linha específica em um conjunto de resultados. Você pode usar as funções filepath e filename retornar nomes de arquivo e/ou o caminho no conjunto de resultados, ou pode usá-los para filtrar dados com base no nome do arquivo ou caminho da pasta. Essas funções são descritas em função de nome de arquivo e função de caminho de arquivo.

As seções a seguir fornecem descrições curtas e exemplos de código.

Pré-requisitos

Sua primeira etapa é criar um banco de dados com uma fonte de dados que faça referência a uma conta de armazenamento. Em seguida, inicialize os objetos executando um script de instalação nesse banco de dados. Esse script de instalação cria as fontes de dados, credenciais com escopo de banco de dados e formatos de arquivo externos usados nesses exemplos.

Funções

Nome do ficheiro

A filename função retorna o nome do arquivo de onde a linha se origina.

O exemplo a seguir lê os arquivos de dados do NYC Yellow Taxi de setembro de 2017 e retorna o número de corridas por arquivo. A OPENROWSET parte da consulta especifica quais arquivos são lidos.

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

O exemplo a WHERE seguir mostra como filename() pode ser usado na cláusula para filtrar os arquivos a serem lidos. Ele acessa toda a OPENROWSET pasta na parte da consulta e filtra os WHERE arquivos na cláusula.

Os resultados serão os mesmos do exemplo anterior.

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

Caminho do arquivo

A filepath função retorna um caminho completo ou parcial:

Quando chamado sem um parâmetro, ele retorna o caminho completo do arquivo de onde a linha se origina. Quando DATA_SOURCE usado no OPENROWSET, ele retorna o caminho relativo a DATA_SOURCE.
Quando chamado com um parâmetro, ele retorna parte do caminho que corresponde ao curinga na posição especificada no parâmetro. Por exemplo, o valor do parâmetro 1 retorna parte do caminho que corresponde ao primeiro curinga.

O exemplo a seguir lê os arquivos de dados do NYC Yellow Taxi para os últimos três meses de 2017. Ele retorna o número de passeios por caminho de arquivo. A OPENROWSET parte da consulta especifica quais arquivos são lidos.

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;

O exemplo a WHERE seguir mostra como filepath() pode ser usado na cláusula para filtrar os arquivos a serem lidos.

Você pode usar os curingas OPENROWSET na parte da consulta e filtrar os arquivos na WHERE cláusula. Os resultados serão os mesmos do exemplo anterior.

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;

Próximo passo

Consultar arquivos do Parquet usando o pool SQL sem servidor

Partilhar via