Consultar pastas e vários ficheiros

Artigo
06/01/2023

Neste artigo, irá aprender a escrever uma consulta com o conjunto de SQL sem servidor no Azure Synapse Analytics.

O conjunto de SQL sem servidor suporta a leitura de vários ficheiros/pastas através de carateres universais, que são semelhantes aos carateres universais utilizados no SO Windows. No entanto, existe uma maior flexibilidade, uma vez que são permitidos vários carateres universais.

Pré-requisitos

O primeiro passo é criar uma base de dados onde irá executar as consultas. 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.

Irá utilizar a pasta csv/taxi para seguir as consultas de exemplo. Contém dados da NYC Taxi - Yellow Taxi Trip Records de julho de 2016 a junho de 2018. Os ficheiros em csv/taxi têm o nome ano e mês com o seguinte padrão: yellow_tripdata_< mês>>.csv<

Ler todos os ficheiros na pasta

O exemplo abaixo lê todos os ficheiros de dados do Táxi Amarelo de NYC da pasta csv/taxi e devolve o número total de passageiros e passeios por ano. Também mostra a utilização de funções de agregação.

SELECT 
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        pickup_datetime DATETIME2 2, 
        passenger_count INT 4
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Ler subconjunto de ficheiros na pasta

O exemplo abaixo lê os ficheiros de dados do Táxi Amarelo de NYC de 2017 da pasta csv/taxi com um caráter universal e devolve o valor total da tarifa por tipo de pagamento.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Ler o subconjunto de ficheiros na pasta com vários caminhos de ficheiro

O exemplo abaixo lê os ficheiros de dados do Táxi Amarelo de NYC de 2017 da pasta csv/taxi utilizando dois caminhos de ficheiro primeiro com caminho completo para o ficheiro que contém dados do mês de janeiro e segundo com um caráter universal de leitura meses de novembro e dezembro que devolve o valor total da tarifa por tipo de pagamento.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK (
            'csv/taxi/yellow_tripdata_2017-01.csv',
            'csv/taxi/yellow_tripdata_2017-1*.csv'
        ),
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Ler pastas

O caminho que fornece para OPENROWSET também pode ser um caminho para uma pasta. As secções seguintes incluem estes tipos de consulta.

Ler todos os ficheiros a partir de uma pasta específica

Pode ler todos os ficheiros numa pasta com o caráter universal ao nível do ficheiro, conforme mostrado em Ler todos os ficheiros na pasta. Contudo, existe uma forma de consultar uma pasta e consumir todos os ficheiros nessa pasta.

Se o caminho fornecido em OPENROWSET apontar para uma pasta, todos os ficheiros nessa pasta serão utilizados como uma origem para a consulta. A seguinte consulta irá ler todos os ficheiros na pasta csv/taxi .

Nota

Tenha em atenção a existência do /no final do caminho na consulta abaixo. Indica uma pasta. Se o /for omitido, a consulta terá como destino um ficheiro com o nome taxi .

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Ler todos os ficheiros de várias pastas

É possível ler ficheiros de várias pastas com um caráter universal. A consulta seguinte irá ler todos os ficheiros de todas as pastas localizadas na pasta csv que têm nomes que começam com t e terminam com i.

Nota

Tenha em atenção a existência do /no final do caminho na consulta abaixo. Indica uma pasta. Se o /for omitido, a consulta terá como destino ficheiros com o nome t*i .

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Uma vez que tem apenas uma pasta que corresponde aos critérios, o resultado da consulta é o mesmo que Ler todos os ficheiros na pasta.

Percorrer pastas de forma recursiva

O conjunto de SQL sem servidor pode percorrer pastas de forma recursiva se especificar /** no final do caminho. A consulta seguinte irá ler todos os ficheiros de todas as pastas e subpastas localizadas na pasta csv/taxi .

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/**', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Vários carateres universais

Pode utilizar vários carateres universais em diferentes níveis de caminho. Por exemplo, pode enriquecer a consulta anterior para ler ficheiros apenas com dados de 2017, a partir de todas as pastas que os nomes começam com t e terminam com i.

Nota

Tenha em atenção a existência do /no final do caminho na consulta abaixo. Indica uma pasta. Se o /for omitido, a consulta terá como destino ficheiros com o nome t*i . Existe um limite máximo de 10 carateres universais por consulta.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Todos os ficheiros acedidos com o único OPENROWSET têm de ter a mesma estrutura (ou seja, o número de colunas e os respetivos tipos de dados).

Uma vez que tem apenas uma pasta que corresponde aos critérios, o resultado da consulta é o mesmo que Ler subconjunto de ficheiros na pasta e Ler todos os ficheiros a partir de uma pasta específica. Os cenários de utilização de carateres universais mais complexos são abordados em ficheiros Parquet de Consulta.

Passos seguintes

Pode encontrar mais informações no artigo Consultar ficheiros específicos .

Consultar pastas e vários ficheiros

Pré-requisitos

Ler todos os ficheiros na pasta

Ler subconjunto de ficheiros na pasta

Ler o subconjunto de ficheiros na pasta com vários caminhos de ficheiro

Ler pastas

Ler todos os ficheiros a partir de uma pasta específica

Ler todos os ficheiros de várias pastas

Percorrer pastas de forma recursiva

Vários carateres universais

Passos seguintes

Recursos adicionais