Запрашивание папок и нескольких файлов

Статья
02/04/2025

Из этой статьи вы узнаете, как написать запрос с помощью бессерверного пула SQL в Azure Synapse Analytics.

Бессерверный пул SQL поддерживает чтение нескольких файлов или папок с помощью подстановочных знаков, которые похожи на подстановочные знаки, используемые в Windows. Однако здесь проявляется большая гибкость, так как разрешено использовать несколько подстановочных знаков.

Необходимые компоненты

Первым шагом является создание базы данных , в которой можно выполнить запросы. Затем инициализировать объекты путем выполнения скрипта установки в этой базе данных. Этот скрипт установки создает источники данных, учетные данные базы данных и форматы внешних файлов, которые используются в этих примерах.

Используйте папку csv/taxi для выполнения примеров запросов. Он содержит данные NYC Taxi - Yellow Taxi Trip Records с июля 2016 по июнь 2018 года. Файлы в csv/такси называются в течение года и месяца, используя следующий шаблон:

yellow_tripdata_<year>-<month>.csv*

Чтение всех файлов в папке

В следующем примере считываются все файлы данных желтого такси Нью-Йорка из папки csv/taxi , а затем возвращается общее количество пассажиров и поездки в год. В нем также показано использование агрегатных функций.

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

Примечание.

Все файлы, к которым обращается один, OPENROWSET должны иметь одинаковую структуру (количество столбцов и их типов данных).

Чтение подмножества файлов в папке

В следующем примере считываются файлы данных NYC Yellow Taxi из папки csv/taxi с помощью подстановочного знака и возвращает общую сумму тарифа для каждого типа оплаты.

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;

Примечание.

Чтение подмножества файлов в папке с использованием нескольких путей к файлам

В следующем примере считываются файлы данных желтого такси Нью-Йорка 2017 из папки csv/taxi с помощью двух путей к файлу. Первый использует полный путь к файлу, содержащий данные из месяца января, а второй использует подстановочный знак для чтения месяцев, ноября и декабря. Для каждого пути возвращается общая сумма тарифа для каждого типа оплаты.

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;

Примечание.

Чтение папок

Указанный путь OPENROWSET также может быть путем к папке. Эти типы запросов приводятся в следующих разделах.

Чтение всех файлов в конкретной папке

Вы можете считывать все файлы в папке с помощью подстановочного знака уровня файла, как показано в разделе "Чтение всех файлов в папке". Но есть способ выполнения запроса к папке и использования всех содержащихся в ней файлов.

Если путь, указанный в OPENROWSET папке, указывает на папку, все файлы в этой папке используются в качестве источника запроса. Следующий запрос считывает все файлы в папке 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);

Примечание.

Чтение всех файлов из нескольких папок

С помощью подстановочного знака можно читать файлы из нескольких папок. Следующий запрос считывает все файлы из всех папок, расположенных в csv-папке с именами, начиная с t и заканчивая i.

Примечание.

Обратите внимание на существование / пути в конце запроса. Он обозначает папку. Если опущено / , запрос предназначен для файлов с именем 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);

Примечание.

Так как условиям соответствует только одна папка, результат запроса будет таким же, как в разделе Чтение всех файлов в папке.

Рекурсивный просмотр папок

Бессерверный пул SQL может рекурсивно проходить по папкам, если указать /** в конце пути. Следующий запрос считывает все файлы из всех папок и вложенных папок, расположенных в папке 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);

Примечание.

Использование нескольких подстановочных знаков

Можно использовать несколько подстановочных знаков на разных уровнях пути. Например, вы можете дополнить предыдущий запрос только для чтения файлов только с данными 2017 года, из всех папок, где имена начинаются с t и заканчиваются i.

Примечание.

Обратите внимание на существование / пути в конце запроса. Он обозначает папку. Если опущено / , запрос предназначен для файлов с именем t*i . Существует максимальное ограничение в 10 подстановочных знаков на запрос.

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

Примечание.

Так как условиям соответствует только одна папка, результат запроса будет таким же, как в разделах Чтение подмножества файлов в папке и Чтение всех файлов в конкретной папке. Более сложные сценарии использования подстановочных знаков см. в разделе "Запрос файлов Parquet".

Следующий шаг

Использование метаданных файлов в запросах к бессерверному пулу SQL

Поделиться через

Запрашивание папок и нескольких файлов

Необходимые компоненты

Чтение всех файлов в папке

Чтение подмножества файлов в папке

Чтение подмножества файлов в папке с использованием нескольких путей к файлам

Чтение папок

Чтение всех файлов в конкретной папке

Чтение всех файлов из нескольких папок

Рекурсивный просмотр папок

Использование нескольких подстановочных знаков

Следующий шаг

Обратная связь

Дополнительные ресурсы