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

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

Бессерверный пул SQL поддерживает чтение нескольких файлов или папок с помощью подстановочных знаков, которые похожи на подстановочные знаки, используемые в Windows. However, greater flexibility is present since multiple wildcards are allowed.

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

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

Используйте папку 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);

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

The following example reads the 2017 NYC Yellow Taxi data files from the csv/taxi folder using a wildcard and returns the total fare amount per payment type.

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.

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

Use multiple wildcards

You can use multiple wildcards on different path levels. Например, вы можете усовершенствовать предыдущий запрос, чтобы читать файлы только с данными 2017 года из всех папок, где имена начинаются с t и заканчиваются на i.

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

Так как условиям соответствует только одна папка, результат запроса будет таким же, как в разделах Чтение подмножества файлов в папке и Чтение всех файлов в конкретной папке. For more complex wildcard usage scenarios, see Query Parquet files.

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