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

Tip

Microsoft Fabric Data Warehouse — это реляционное хранилище корпоративного масштаба на основе озера данных, с архитектурой, готовой к будущему, встроенной ИИ и новыми функциями. Если вы не знакомы с хранилищем данных, начните с Fabric Data Warehouse. Существующие рабочие нагрузки выделенного пула SQL могут обновляться до Fabric для доступа к новым возможностям в области науки о данных, аналитики в реальном времени и отчетности.

• Start a Fabric бесплатная пробная версия.
• Ассистент по миграции для Fabric Data Warehouse.

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

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

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

Прочитать файл паркет

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

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

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

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

Набор данных

В этом примере используется набор данных 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 г. без указания схемы.

Примечание.

Вам не нужно указывать столбцы в предложении OPENROWSET WITH при чтении файлов Parquet. В этом случае сервис запросов бессерверного пула 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.

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

Как выполнять запросы вложенных типов Parquet