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

Artigo
07/23/2024

O pool SQL sem servidor pode endereçar vários arquivos e pastas, conforme descrito no artigo Consultar pastas e vários arquivos . Neste artigo, você aprenderá a usar informações de metadados sobre nomes de arquivos e pastas nas consultas.

Às vezes, você pode precisar saber qual fonte de arquivo ou pasta se correlaciona a uma linha específica no conjunto de resultados.

Você pode usar a função filepath e filename retornar nomes de arquivo e/ou o caminho no conjunto de resultados. Ou você pode usá-los para filtrar dados com base no nome do arquivo e/ou caminho da pasta. Essas funções são descritas na seção de sintaxe, função de nome de arquivo e função de caminho de arquivo. Nas seções a seguir, você encontrará breves descrições ao longo de exemplos.

Pré-requisitos

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

Funções

Nome do ficheiro

Esta função retorna o nome do arquivo do qual 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 parte OPENROWSET da consulta especifica quais arquivos serã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 seguir mostra como filename() pode ser usado na cláusula WHERE para filtrar os arquivos a serem lidos. Ele acessa a pasta inteira na parte OPENROWSET da consulta e filtra os arquivos na cláusula WHERE.

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 função filepath retorna um caminho completo ou parcial:

Quando chamado sem um parâmetro, ele retorna o caminho completo do arquivo do qual a linha se origina. Quando DATA_SOURCE é usado em OPENROWSET, ele retorna 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 retornaria 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 parte OPENROWSET da consulta especifica quais arquivos serã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 seguir mostra como filepath() pode ser usado na cláusula WHERE para filtrar os arquivos a serem lidos.

Você pode usar os curingas na parte OPENROWSET da consulta e filtrar os arquivos na cláusula WHERE. 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óximos passos

No próximo artigo, você aprenderá como consultar arquivos do Parquet.

Partilhar via