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

O conjunto de SQL sem servidor pode abordar vários ficheiros e pastas, conforme descrito no artigo Pastas de consulta e vários ficheiros . Neste artigo, irá aprender a utilizar informações de metadados sobre nomes de ficheiros e pastas nas consultas.

Por vezes, poderá ter de saber que ficheiro ou origem de pasta está correlacionada com uma linha específica no conjunto de resultados.

Pode utilizar a função filepath e filename devolver nomes de ficheiros e/ou o caminho no conjunto de resultados. Em alternativa, pode utilizá-los para filtrar dados com base no nome do ficheiro e/ou no caminho da pasta. Estas funções estão descritas na função filename da secção de sintaxe e na função filepath. Nas secções seguintes, encontrará breves descrições ao longo de exemplos.

Pré-requisitos

O primeiro passo é criar uma base de dados com uma origem de dados que faça referência à conta de armazenamento. Em seguida, inicialize os objetos ao executar o script de configuração nessa base de dados. Este script de configuração irá criar as origens de dados, as credenciais no âmbito da base de dados e os formatos de ficheiro externos utilizados nestes exemplos.

Funções

Nome do ficheiro

Esta função devolve o nome de ficheiro do qual a linha tem origem.

O exemplo seguinte lê os ficheiros de dados do Táxi Amarelo de NYC nos últimos três meses de 2017 e devolve o número de viagens por ficheiro. A parte OPENROWSET da consulta especifica os ficheiros que 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 seguinte mostra como o nome de ficheiro() pode ser utilizado na cláusula WHERE para filtrar os ficheiros a ler. Acede a toda a pasta na parte OPENROWSET da consulta e filtra ficheiros na cláusula WHERE.

Os seus resultados serão os mesmos que o 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 de Ficheiro

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

• Quando chamado sem um parâmetro, devolve o caminho de ficheiro completo do qual a linha tem origem. Quando DATA_SOURCE é utilizada em OPENROWSET, devolve o caminho relativamente a DATA_SOURCE.
• Quando chamado com um parâmetro, devolve parte do caminho que corresponde ao caráter universal na posição especificada no parâmetro. Por exemplo, o valor do parâmetro 1 devolveria parte do caminho que corresponde ao primeiro caráter universal.

O exemplo seguinte lê os ficheiros de dados do Táxi Amarelo de NYC nos últimos três meses de 2017. Devolve o número de passeios por caminho de ficheiro. A parte OPENROWSET da consulta especifica os ficheiros que 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 seguinte mostra como o filepath() pode ser utilizado na cláusula WHERE para filtrar os ficheiros a ler.

Pode utilizar os carateres universais na parte OPENROWSET da consulta e filtrar os ficheiros na cláusula WHERE. Os seus resultados serão os mesmos que o 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;

Passos seguintes

No próximo artigo, irá aprender a consultar ficheiros Parquet.