Compartilhar via

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 ou pastas usando curingas, que são semelhantes aos curingas usados no Windows. No entanto, há maior flexibilidade, já que vários curingas são permitidos.

Pré-requisitos

A primeira etapa é criar um banco de dados onde você pode as consultas. Em seguida, inicialize os objetos executando o script de instalação nesse banco de dados. Esse script de instalação cria as fontes de dados, as credenciais no escopo do banco de dados e os formatos de arquivo externos que são usados nessas amostras.

Use a pasta csv/taxi para seguir as consultas de exemplo. Ele contém os dados do Táxi de NYC – Registros de Viagem 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_<year>-<month>.csv*

Ler todos os arquivos na pasta

O exemplo a seguir lê todos os arquivos de dados de Táxi Amarelo de NYC da pasta csv/taxi e retorna o número total de passageiros e passeios 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

Ler subconjunto de arquivos na pasta

O exemplo a seguir lê os arquivos de dados de Táxi Amarelo de NYC 2017 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

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

O exemplo a seguir lê os arquivos de dados do Táxi Amarelo de NYC 2017 da pasta csv/taxi usando dois caminhos de arquivo. O primeiro usa o caminho completo para o arquivo que contém dados do mês de janeiro e o segundo usa um curinga para ler os meses de outubro, novembro e dezembro. Para cada caminho, o valor total da tarifa por tipo de pagamento é retornado.

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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

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 aponta para uma pasta, todos os arquivos nessa pasta sã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 de / no final do caminho na consulta. Isso denota uma pasta. Se / é omitida, a consulta direciona 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

Ler todos os arquivos de várias pastas

É possível ler arquivos de várias pastas usando um curinga. A consulta a seguir lê 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 de / no final do caminho na consulta. Isso denota uma pasta. Se / é omitida, a consulta direciona 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

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 lê 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

Usar vários curingas

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

Observação

Observe a existência de / no final do caminho na consulta. Isso denota uma pasta. Se / é omitida, a consulta direciona os arquivos chamados t*i. Há um limite de 10 caracteres 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 único OPENROWSET devem ter a mesma estrutura (número de colunas e seus tipos de dados).

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. Para cenários de uso curinga mais complexos, consulte Arquivos de Consulta Parquet.

Próxima etapa

Use metadados de arquivo em consultas de pool de SQL sem servidor