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

В этой статье вы узнаете, как с помощью бессерверного пула 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 предложении или задайте некоторые параметры сортировки 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. Затем инициализируйте объекты, выполнив сценарий установки для этой базы данных. Этот сценарий установки создает источники данных, учетные данные области базы данных и форматы внешних файлов, которые используются в этих примерах.

Набор данных

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

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

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

  • Last updated on