Consultar pastas e vários arquivos

  • Artigo

Neste artigo, você aprenderá a escrever uma consulta usando o pool de SQL sem servidor no Azure Synapse Analytics.

O pool de SQL sem servidor dá suporte à leitura de vários arquivos/pastas usando curingas, que são semelhantes aos curingas usados no sistema operacional Windows. No entanto, há maior flexibilidade, já que vários curingas são permitidos.

Pré-requisitos

A primeira etapa é criar um banco de dados no qual você executará as consultas. 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, as credenciais no escopo do banco de dados e os formatos de arquivo externos que são usados nessas amostras.

Você usará a pasta csv/taxi para seguir as consultas de exemplo. Ela contém os dados NYC Taxi – Registros de corridas de táxi amarelo de julho de 2016 a junho de 2018. Os arquivos em csv/taxi são nomeados refletindo o ano e o mês usando o seguinte padrão: yellow_tripdata_<ano>-<mês>.csv

Ler todos os arquivos na pasta

O exemplo a seguir lê todos os arquivos de dados de NYC Yellow Taxi da pasta csv/taxi e retorna o número total de passageiros e corridas por ano. Ele também mostra o uso 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);

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Ler subconjunto de arquivos na pasta

O exemplo abaixo lê os arquivos de dados de 2017 NYC Yellow Taxi da pasta csv/taxi usando um curinga e retorna 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;

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Ler o subconjunto de arquivos na pasta usando vários caminhos de arquivo

O exemplo abaixo lê os arquivos de dados de Táxis Amarelos de Nova York em 2017 da pasta csv/taxi usando dois caminhos de arquivo, o primeiro um com o caminho completo para o arquivo contendo dados do mês de janeiro, e o segundo com uma leitura curinga dos meses de novembro e dezembro, que retorna 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;

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Ler pastas

O caminho que você fornece para OPENROWSET também pode ser um caminho para uma pasta. As seções a seguir incluem esses tipos de consulta.

Ler todos os arquivos de uma pasta específica

Você pode ler todos os arquivos em uma pasta usando o curinga de nível de arquivo, conforme mostrado em Ler todos os arquivos na pasta. Mas há uma forma de consultar uma pasta e consumir todos os arquivos dentro dela.

Se o caminho fornecido no OPENROWSET apontar para uma pasta, todos os arquivos nessa pasta serão usados como uma fonte para a consulta. A consulta a seguir lerá todos os arquivos na pasta csv/taxi.

Observação

Observe a existência do / no final do caminho na consulta abaixo. Isso denota uma pasta. Se a / for omitida, a consulta direcionará um arquivo chamado taxi em vez disso.

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

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Ler todos os arquivos de várias pastas

É possível ler arquivos de várias pastas usando um curinga. A consulta a seguir lerá todos os arquivos de todas as pastas localizadas na pasta csv que tenham nomes que comecem com t e terminem com i.

Observação

Observe a existência do / no final do caminho na consulta abaixo. Isso denota uma pasta. Se a / for omitida, a consulta vai direcionar os arquivos chamados 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);

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Como você tem apenas uma pasta que corresponde aos critérios, o resultado da consulta é o mesmo que Ler todos os arquivos na pasta.

Percorrer pastas recursivamente

O pool de SQL sem servidor pode percorrer as pastas recursivamente se você especificar /** no final do caminho. A consulta a seguir lerá todos os arquivos 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);

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Vários caracteres curinga

Você pode usar vários curingas em diferentes níveis de caminho. Por exemplo, você pode enriquecer a consulta anterior para ler arquivos somente com os dados de 2017, de todas as pastas que os nomes que comecem com t e terminem com i.

Observação

Observe a existência do / no final do caminho na consulta abaixo. Isso denota uma pasta. Se a / for omitida, a consulta vai direcionar os arquivos chamados t*i. Há um limite máximo de dez curingas 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);

Observação

Todos os arquivos acessados com o OPENROWSET único precisam ter a mesma estrutura (ou seja, o número de colunas e o tipo de dados delas).

Como você tem apenas uma pasta que corresponde aos critérios, o resultado da consulta é o mesmo que Ler um subconjunto de arquivos na pasta e Ler todos os arquivos da pasta específica. Cenários de uso de caracteres curingas mais complexos são abordados em Consultar arquivos de Parquet.

Próximas etapas

Mais informações podem ser encontradas no artigo, Consultar arquivos específicos.