Запрос по файлам Parquet с помощью бессерверного пула SQL в Azure Synapse Analytics

Статья
03/10/2023

В этой статье вы узнаете, как с помощью бессерверного пула SQL написать запрос, который будет считывать файлы Parquet.

Пример для быстрого начала

Функция OPENROWSET позволяет считывать содержимое файла Parquet по его URL-адресу.

Чтение файла Parquet

Чтобы увидеть содержимое файла PARQUET, проще всего вызвать функцию OPENROWSET, передав ей URL-адрес нужного файла и параметр parquet FORMAT. Если файл доступен в общедоступном виде или если удостоверение Microsoft Entra может получить доступ к этому файлу, вы сможете просмотреть содержимое файла с помощью запроса, как показано в следующем примере:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Убедитесь, что у вас есть доступ к этому файлу. Если файл защищен с помощью ключа SAS или пользовательского удостоверения Azure, необходимо настроить учетные данные на уровне сервера для входа sql.

Важно!

Убедитесь, что используются параметры сортировки базы данных UTF-8 (например, Latin1_General_100_BIN2_UTF8), так как строковые значения в файлах Parquet всегда имеют кодировку UTF-8. Несоответствие кодировки текста в файле Parquet и параметров сортировки может привести к неожиданным ошибкам при преобразовании. Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Дополнительные сведения о параметрах сортировки см. в разделе "Типы сортировки", поддерживаемые для Synapse SQL.

При использовании Latin1_General_100_BIN2_UTF8 параметров сортировки вы получите дополнительное повышение производительности по сравнению с другими параметрами сортировки. Latin1_General_100_BIN2_UTF8Параметры сортировки совместимы с правилами сортировки строк Parquet. Пул SQL может исключить некоторые части файлов Parquet, которые не будут содержать данные, требуемые в запросах (удаление сегментов файлов или столбцов). При использовании других параметров сортировки все данные из файлов Parquet будут загружены в Synapse SQL, и фильтрация будет происходить в рамках процесса SQL. Параметры Latin1_General_100_BIN2_UTF8 сортировки имеют дополнительную оптимизацию производительности, которая работает только для parquet и Cosmos DB. Недостаток в том, что вы не можете использовать преимущества детализированных правил сравнения, например нечувствительность к регистру.

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

В предыдущем примере используется полный путь к файлу. Вместо этого вы можете создать внешний источник данных с информацией о расположении корневой папки хранилища и указать этот источник данных вместе с относительным путем к файлу в функции OPENROWSET:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Если источник данных защищен с помощью ключа SAS или пользовательского удостоверения, можно настроить источник данных с учетными данными для базы данных.

Явное указание схемы

Функция OPENROWSET позволяет явным образом указывать, какие столбцы вы хотите считать из файла, с помощью предложения WITH:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Важно!

Обязательно укажите явным образом любой параметр сортировки для кодировки UTF-8 (например, Latin1_General_100_BIN2_UTF8) для всех строковых столбцов в предложении WITH или на уровне базы данных. Несоответствие кодировки текста в файле и параметров сортировки для строковых столбцов может привести к непредвиденным ошибкам преобразования текста. Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Можно легко задать параметры сортировки для типов colum, например: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Дополнительные сведения о параметрах сортировки см. в разделе "Типы сортировки", поддерживаемые для Synapse SQL.

В следующих разделах вы узнаете, как запрашивать различные типы файлов PARQUET.

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

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

Набор данных

В этом примере используется набор данных NYC Yellow Taxi. Запросить файлы Parquet можно таким же образом, как вы считываете CSV-файлы. Единственное различие состоит в том, что параметр FILEFORMAT должен иметь значение PARQUET. В примерах в этой статье показаны особенности считывания файлов Parquet.

Запрашивание набора файлов Parquet

При запросе файлов Parquet можно указать только необходимые столбцы.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Автоматический вывод схемы

При чтении файлов Parquet не нужно использовать предложение OPENROWSET WITH. Имена столбцов и типы данных автоматически считываются из файлов Parquet.

Имейте в виду, что при одновременном чтении количества файлов схема, имена столбцов и типы данных будут выводиться из первой файловой службы, полученной из хранилища. Это может означать, что некоторые из ожидаемых столбцов опущены, все потому, что файл, используемый службой для определения схемы, не содержал этих столбцов. Чтобы явно указать схему, используйте предложение OPENROWSET WITH.

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

Примечание.

При чтении файлов Parquet указывать столбцы в предложении OPENROWSET WITH не требуется. В этом случае бессерверный пул SQL будет использовать метаданные в файле Parquet и привязывать столбцы по имени.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Запрос по секционированным данным

Набор данных, представленный в этом примере, разделен на отдельные вложенные папки. Конкретные секции можно выбрать с помощью функции filepath. В этом примере показаны суммы по тарифам за год, месяц и payment_type за первые три месяца 2017 года.

Примечание.

Запрос пула бессерверного SQL совместим со схемой секционирования Hive на платформе Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Сопоставление типов

Сопоставления типов Parquet с собственными типами SQL см. в этом документе.

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

Перейдите к следующей статье, чтобы узнать, как запросить вложенные типы Parquet.