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

Статья
06/01/2023

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

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

Предварительные требования

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

Для выполнения примеров запросов вы будете использовать папку csv/taxi. В ней содержатся данные записей о поездках в желтом такси Нью-Йорка с июля 2016 г. по июнь 2018 г. В именах файлов в папке csv/taxi указываются год и месяц и используется следующий шаблон: 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, должны иметь одинаковую структуру (т. е. число столбцов и их типы данных).

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

В примере ниже показано чтение файлов данных по желтому такси Нью-Йорка за 2017 г. из папки 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 NYC Yellow Taxi из папки 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.

Примечание

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

Дальнейшие действия

Дополнительные сведения можно найти в статье Запрашивание конкретных файлов.

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

Предварительные требования

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

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

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

Чтение папок

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

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

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

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

Дальнейшие действия

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