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

Применимо к:✅ Конечная точка аналитики SQL и хранилище в Microsoft Fabric

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

Используя OPENROWSET функцию в хранилище данных Fabric или конечную точку аналитики SQL, можно запросить содержимое файла Parquet, указав URL-адрес файла. Синтаксис OPENROWSET функции:

  OPENROWSET(BULK {{parquet-file-url}})
  [ WITH ( {{result set column schema}} ) ]

Как OPENROWSET обрабатывает схему

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

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

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

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

Чтение файла Parquet с помощью OPENROWSET

Самый простой способ просмотра содержимого файла Parquet — предоставить URL-адрес файла непосредственно функции OPENROWSET .

В следующем примере показано, как считывать файл Parquet, указав полный URL-адрес в инструкции OPENROWSET :

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.parquet'
);

В этом запросе:

• Аргумент BULK задает полный URI файла Parquet в хранилище.
• OPENROWSET считывает содержимое файла и возвращает его в виде табличного результированного набора.
• Имена столбцов и типы данных автоматически выводятся из метаданных файла Parquet.
• Условие TOP 10 ограничивает набор результатов, чтобы упростить проверку данных.

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

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

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

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

В следующем примере создается внешний источник данных, указывающий на папку, содержащую файлы данных:

CREATE EXTERNAL DATA SOURCE sample_data_source
WITH ( LOCATION = 'https://<storage account>.blob.core.windows.net/public/<subfolder>' );

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

SELECT TOP 10 *
FROM OPENROWSET(
        BULK '<subfolder>/<file name>.parquet',
        DATA_SOURCE = 'sample_data_source'
    ) as rows;

В этом примере параметр DATA_SOURCE инструктирует OPENROWSET определить путь к файлу относительно внешнего источника данных.

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

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

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

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

В следующем примере показано, как явно определить схему при чтении данных из файла Parquet:

SELECT TOP 10 *
FROM OPENROWSET(
        BULK '<subfolder>/<file name>.parquet',
        DATA_SOURCE = 'sample_data_source'
    ) WITH ( date_rep date, cases int, geo_id varchar(6) );

В этом примере WITH предложение определяет результирующую схему, указав:

• Имена столбцов (date_rep, cases, geo_id)
• Типы данных, применяемые к каждому столбцу в результатах запроса

Возвращаются только столбцы, перечисленные в WITH предложении, даже если файл Parquet содержит дополнительные столбцы. Такой подход уменьшает объем считываемых данных и управляет формой результирующего набора.

Явное определение схемы особенно важно, если:

• Запрос нескольких файлов, не имеющих одинаковых наборов столбцов
• Для выполнения запросов требуется детерминированная и стабильная схема.
• Вы хотите переопределить выводимые типы данных или применить определенные типы данных
• Исходные файлы могут со временем изменяться, добавляя новые или необязательные столбцы.

При явном определении схемы OPENROWSET не зависит от вывода схемы. Вместо этого он применяет определения столбцов, предоставляемые в WITH предложении, обеспечивая согласованные и прогнозируемые результаты запроса.

Запросить набор файлов Parquet с использованием OPENROWSET

Функция OPENROWSET поддерживает запросы к нескольким файлам в одной инструкции, позволяя указать путь к файлу, содержащий подстановочные знаки (*) символов.

Подстановочные знаки позволяют определить шаблон URI, представляющий набор файлов или папок, а не один файл. При использовании подстановочного знака в пути к файлу все файлы, соответствующие указанному шаблону URI, считываются и объединяются в один результирующий набор.

Этот подход часто используется при запросе секционированных данных, таких как файлы, упорядоченные по дате, региону или другим иерархическим структурам папок.

Например, можно использовать подстановочные знаки для:

• Запрос всех файлов в определенной папке
• Поиск файлов в нескольких вложенных папках
• Запросите файлы, которые имеют общий шаблон именования.

SELECT *
FROM OPENROWSET(
        BULK '/Files/puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi'
     );

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

Шаблоны файлов на основе подстановочных знаков предоставляют гибкий и масштабируемый способ запроса больших коллекций файлов непосредственно из хранилища без необходимости знать точные имена файлов.

Запрос секционированных данных с помощью OPENROWSET

Секционирование наборов данных, хранящихся в озере данных в вложенных папках, повышает производительность и организацию.

Распространенным шаблоном является секционирование в стиле Hive или Hadoop, где имена папок кодируют значения секций, такие как год, месяц или регион.

Пример набора данных, используемого в этом примере, секционируется по отдельным вложенным папкам по годам сбора (puYear) и месяцу сбора (puMonth). Функция OPENROWSET может напрямую считывать данные, организованные с помощью этой схемы секционирования.

В следующем примере показано, как запрашивать секционированные данные Parquet и извлекать значения секций из пути к файлу. Он возвращает данные о поездке в течение первых трех месяцев 2017 года:

SELECT
        CAST( nyc.filepath(2) AS INT), *
FROM  
    OPENROWSET(
        BULK '/Files/puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi'
    ) AS nyc
WHERE
    nyc.filepath(1) = '2017'
    AND nyc.filepath(2) IN (1, 2, 3);

Чтение сложных типов с помощью OPENROWSET

Хранилище данных Fabric и конечная точка аналитики SQL могут читать столбцы Parquet, содержащие сложные типы, такие как структура или запись, а также массив или повторяющиеся поля. При проецировании этих столбцов через OPENROWSET запрос возвращает значения из этих сложных столбцов в виде текста JSON и отображает их как столбцы varchar.

• Структуры (записи) возвращаются в виде объектов JSON, например {"a":1,"b":"x"}.
• Массивы (повторяющиеся поля) возвращаются в виде массивов JSON, например [1,2,3].

В следующем примере показано, как считывать структуру и массив из одного файла Parquet и возвращать их в виде текста JSON. Столбцы объекта JSON и массива проецируются как varchar(max).

SELECT
 StructCol,  -- JSON object (for example, {"id":123,"name":"Ada","tags":["ml","sql"]})
 ArrayCol    -- JSON array  (for example, [11,12,13])
FROM    
 OPENROWSET(
  BULK '/Files/parquet/nested/complexExample.parquet',
  DATA_SOURCE = 'MyDataSource')
 WITH ( -- Project complex Parquet columns as VARCHAR to receive JSON text.
       StructCol VARCHAR(MAX),
       ArrayCol  VARCHAR(MAX)    );

Содержимое каждого вложенного значения возвращается в виде текста JSON.

• Используйте varchar(8000) или более крупный тип, например varchar(max), для безопасного размещения больших полезных данных JSON, возвращаемых из сложных столбцов Parquet.
• Вы можете проецировать дополнительные сложные поля, такие как вложенные структуры или массивы структур, добавив в предложение дополнительные столбцы WITH. Каждое сложное поле возвращается в виде текста JSON.

Доступ к свойствам или элементам вложенных типов

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

Эти свойства или элементы можно получить тремя способами:

• Использование функций JSON — применение JSON_VALUE (скалярное) или JSON_QUERY (объект/массив) над текстом JSON, возвращаемым из сложных столбцов.
• Имена столбцов с путём через точку (структуры) — укажите имя столбца, разделенное символом . (например, [Struct.prop] TYPE), в WITH классе, чтобы проецировать вложенное поле структуры непосредственно в скалярный столбец.
• Путь JSON после типа — укажите выражение пути JSON сразу после типа в WITH предложении (например, Col INT '$.Struct.prop'), чтобы извлечь вложенное скалярное значение.

В следующем примере показано, как получить доступ к вложенным полям в Parquet:

SELECT
 -- Raw complex values (JSON text)
 UserStruct,                 -- for example, {"id":123,"name":"Ada","scores":[11,12,13]}
 ScoresArray,                -- for example, [11,12,13]

 -- Access struct fields via JSON_VALUE
 JSON_VALUE(UserStruct, '$.id')   AS UserId_json,
 JSON_VALUE(UserStruct, '$.name') AS UserName_json,

 -- Access array elements via JSON_VALUE (0-based)
 JSON_VALUE(ScoresArray, '$[0]')  AS Score1,
 JSON_VALUE(ScoresArray, '$[1]')  AS Score2,
 JSON_VALUE(ScoresArray, '$[2]')  AS Score3

FROM
 OPENROWSET(
    BULK '/Files/parquet/nested/complexExample.parquet',
    DATA_SOURCE = 'MyDataSource',
    FORMAT = 'PARQUET'
 )
 WITH (
    -- Complex columns as JSON text
    UserStruct  VARCHAR(8000) '$.UserStruct',
    ScoresArray VARCHAR(8000) '$.ScoresArray',

    -- Option A: JSON path after type
    UserId      INT           '$.UserStruct.id',
    UserName    VARCHAR(200)  '$.UserStruct.name',

    -- Option B: Dot-path column names
    [UserStruct.id]    INT,
    [UserStruct.name]  VARCHAR(200)
) AS r;

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

Расширение массивов

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

В этом случае можно развернуть массив и объединить его элементы с родительской строкой с помощью OPENJSON. Если столбец Parquet содержит массив (например, [1,2,3]), может потребоваться сравить (развернуть) его элементы, чтобы каждый элемент массива отображалось в отдельной строке вместе с исходными данными родительской строки.

Вы можете сделать это, применяя CROSS APPLY OPENJSON(...) поверх текста JSON, возвращаемого из столбца массива.

SELECT
  r.Id,      -- Example parent column (if present in your file)
  r.Array,   -- Raw JSON array text
  a.Element  -- Flattened scalar value from the array
FROM
  OPENROWSET(
        BULK '/Files/parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'MyDataSource'
        ) 
    WITH ( Id INT, Array VARCHAR(MAX) ) 
    AS r
CROSS APPLY OPENJSON(r.Array) 
  WITH (Element INT '$' ) AS a;

OPENROWSET проецирует столбец массива (Array) как массив JSON и возвращает одну строку для каждого элемента массива, что позволяет объединять значения массива с родительскими столбцами (например, Id). Результат этого запроса может выглядеть следующим образом:

Для массивов объектов замените WITH предложение внутри OPENJSON для сопоставления свойств объекта, например:

CROSS APPLY OPENJSON(r.Array)
WITH (
    ItemId   INT,
    ItemName VARCHAR(200) ) AS a

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

Файлы Parquet содержат описания типов для каждого столбца. В следующей таблице описывается, как типы Parquet соотносятся с собственными типами SQL, которые возвращает функция OPENROWSET.

* (параметры сортировки UTF8)

Перед преобразованием в значение даты и времени вы должны явно настроить значение bigint с учётом смещения часового пояса.

Связанный контент

• Запрос файлов внешних дата-лейков
• Запрос JSON-файлов
• Запрос файлов с разделителями