Partilhar via

Consultar pastas e vários ficheiros

  • Artigo

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

O pool SQL sem servidor oferece suporte à leitura de vários arquivos ou pastas usando curingas, que são semelhantes aos curingas usados no Windows. No entanto, existe uma maior flexibilidade, uma vez que são permitidos vários curingas.

Pré-requisitos

Seu primeiro passo é criar um banco de dados onde você possa executar as consultas. Em seguida, inicialize os objetos executando um script de instalação nesse banco de dados. Esse script de instalação cria as fontes de dados, credenciais com escopo de banco de dados e formatos de arquivo externos usados nesses exemplos.

Use a pasta csv/taxi para seguir as consultas de exemplo. Ele contém dados do NYC Taxi - Yellow Taxi Trip Records de julho de 2016 a junho de 2018. Os arquivos em csv/taxi são nomeados após ano e mês usando o seguinte padrão:

yellow_tripdata_<year>-<month>.csv*

Leia todos os arquivos na pasta

O exemplo a seguir lê todos os arquivos de dados do NYC Yellow Taxi da pasta csv/taxi e, em seguida, retorna o número total de passageiros e viagens por ano. Ele também mostra o uso de funções agregadas.

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 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 do NYC Yellow Taxi de 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;

Nota

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 usando vários caminhos de arquivo

O exemplo a seguir lê os arquivos de dados do NYC Yellow Taxi de 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 trajeto, o valor total da tarifa por tipo de pagamento é devolvido.

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

Ler pastas

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

Leia todos os arquivos de uma pasta específica

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

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

Nota

Observe a existência do / no final do caminho na consulta. Ele denota uma pasta. Se o / for omitido, a consulta terá como alvo um arquivo chamado táxi .

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

Leia 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 têm nomes começando com t e terminando com i.

Nota

Observe a existência do / no final do caminho na consulta. Ele denota uma pasta. Se o / for omitido, a consulta destina-se a 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);

Nota

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.

Atravessar pastas recursivamente

O pool SQL sem servidor pode atravessar 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);

Nota

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 apenas com dados de 2017, de todas as pastas em que os nomes começam com t e terminam com i.

Nota

Observe a existência do / no final do caminho na consulta. Ele denota uma pasta. Se o / for omitido, a consulta destina-se a arquivos chamados t*i . Há um limite máximo de 10 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);

Nota

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 subconjunto de arquivos na pasta e Ler todos os arquivos de uma pasta específica. Para cenários de uso de curingas mais complexos, consulte Arquivos do Query Parquet.

Próximo passo

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