Обработка запросами элементов вложенного типа в файлах Parquet и JSON с помощью бессерверного пула SQL в Azure Synapse Analytics

  • Статья

В этой статье вы узнаете, как написать запрос с помощью бессерверного пула SQL в Azure Synapse Analytics. Этот запрос предназначен для считывания элементов вложенного типа из файлов Parquet. Вложенные типы являются сложными структурами, представляющими объекты или массивы. Вложенные типы могут храниться:

  • В файлах Parquet, где может быть несколько сложных столбцов с массивами и объектами.
  • В иерархических файлах JSON, где можно считывать сложные документы JSON в виде одного столбца.
  • В коллекции Azure Cosmos DB (в настоящее время имеет статус условно общедоступной предварительной версии), в которой каждый документ может содержать сложные вложенные свойства.

Бессерверный пул SQL форматирует все вложенные типы как объекты JSON и массивы. Поэтому можно извлекать или изменять сложные объекты с помощью функций JSON или обрабатывать данные JSON с помощью функции OPENJSON.

Ниже приводится пример запроса, который извлекает скалярные величины и величины объектов из JSON-файла COVID-19 Open Research Dataset (Набор данных открытых исследований COVID-19), в котором содержатся вложенные объекты:

SELECT
    title = JSON_VALUE(doc, '$.metadata.title'),
    first_author = JSON_QUERY(doc, '$.metadata.authors[0]'),
    first_author_name = JSON_VALUE(doc, '$.metadata.authors[0].first'),
    complex_object = doc
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
        FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b'
    )
    WITH ( doc varchar(MAX) ) AS docs;

Функция JSON_VALUE возвращает скалярное значение из поля с указанным путем. Функция JSON_QUERY возвращает объект, имеющий формат JSON, из поля с указанным путем.

Важно!

В этом примере используется файл c информационного ресурса "COVID-19 Open Research Dataset" (Набор данных открытых исследований COVID-19). С лицензией и структурой данных можно ознакомиться здесь.

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

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

Вложенные или повторяющиеся данные в проекте

В файле Parquet может быть несколько столбцов со сложными типами. Значения из этих столбцов форматируются в виде текста JSON и возвращаются в виде столбцов типа VARCHAR. Следующий запрос считывает файл structExample.parquet и демонстрирует, как нужно считывать значения вложенных столбцов:

SELECT
    DateStruct, TimeStruct, TimestampStruct, DecimalStruct, FloatStruct
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        DateStruct VARCHAR(8000),
        TimeStruct VARCHAR(8000),
        TimestampStruct VARCHAR(8000),
        DecimalStruct VARCHAR(8000),
        FloatStruct VARCHAR(8000)
    ) AS [r];

Результат этого запроса будет следующим. Содержимое каждого вложенного объекта возвращается в виде текста JSON.

DateStruct TimeStruct TimestampStruct DecimalStruct FloatStruct
{"Дата":"2009-04-25"} {"Время":"20:51:54.3598000"} {"Метка времени":"5501-04-08 12:13:57.4821000"} {"Десятичное значение":11143412.25350} {"Плавающее значение":0.5}
{"Дата":"1916-04-29"} {"Время":"00:16:04.6778000"} {"Метка времени":"1990-06-30 20:50:52.6828000"} {"Десятичное значение":1963545.62800} {"Плавающее значение":-2.125}

Приведенный ниже запрос считывает файл justSimpleArray.parquet. Извлекаются все столбцы из файла Parquet, включая вложенные или повторяющиеся данные.

SELECT
    SimpleArray
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

При выполнении этого запроса возвращается следующий результат:

SimpleArray
[11,12,13]
[21,22,23]

Чтение свойств из вложенных столбцов с объектами

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

SELECT
    title = JSON_VALUE(complex_column, '$.metadata.title'),
    first_author_name = JSON_VALUE(complex_column, '$.metadata.authors[0].first'),
    body_text = JSON_VALUE(complex_column, '$.body_text.text'),
    complex_column
FROM
    OPENROWSET( BULK 'https://azureopendatastorage.blob.core.windows.net/covid19temp/comm_use_subset/pdf_json/000b7d1517ceebb34e1e3e817695b6de03e2fa78.json',
                FORMAT='CSV', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b', ROWTERMINATOR = '0x0b' ) WITH ( complex_column varchar(MAX) ) AS docs;

Результат показан в следующей таблице:

title first_author_name body_text complex_column
Дополнительная информация: эколого-эпидеми… Жюльен — Рис. S1. Филогенезис... { "paper_id": "000b7d1517ceebb34e1e3e817695b6de03e2fa78", "metadata": { "title": "Supplementary Information An eco-epidemiological study of Morbilli-related paramyxovirus infection in Madagascar bats reveals host-switching as the dominant macro-evolutionary mechanism", "authors": [ { "first": "Julien"

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

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

  • Путем указания выражения пути для вложенного значения после спецификации типа.
  • Путем форматирования имени столбца как вложенного пути с помощью команды "." для ссылки на поля.
SELECT
    *
FROM
    OPENROWSET(
        BULK 'parquet/nested/structExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    )
    WITH (
        [DateValue] DATE '$.DateStruct.Date',
        [TimeStruct.Time] TIME,
        [TimestampStruct.Timestamp] DATETIME2,
        DecimalValue DECIMAL(18, 5) '$.DecimalStruct.Decimal',
        [FloatStruct.Float] FLOAT
    ) AS [r];

Доступ к элементам из повторяющихся столбцов

Следующий запрос считывает файл justSimpleArray.parquet и использует функцию JSON_VALUE для получения скалярного элемента из повторяющегося столбца, такого как массив или карта:

SELECT
    *,
    JSON_VALUE(SimpleArray, '$[0]') AS FirstElement,
    JSON_VALUE(SimpleArray, '$[1]') AS SecondElement,
    JSON_VALUE(SimpleArray, '$[2]') AS ThirdElement
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Ниже приведен результат:

SimpleArray FirstElement SecondElement ThirdElement
[11,12,13] 11 12 13
[21,22,23] 21 22 23

Доступ к вложенным объектам из сложных столбцов

Следующий запрос считывает файл mapExample.parquet и использует JSON_QUERY для получения нескалярного элемента из повторяющегося столбца, такого как массив или карта:

SELECT
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS [r];

Также можно явно ссылаться на столбцы, возвращаемые в предложении WITH:

SELECT DocId,
    MapOfPersons,
    JSON_QUERY(MapOfPersons, '$."John Doe"') AS [John]
FROM
    OPENROWSET(
        BULK 'parquet/nested/mapExample.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) 
    WITH (DocId bigint, MapOfPersons VARCHAR(max)) AS [r];

Структура MapOfPersons возвращается в виде столбца VARCHAR и форматируется как строка JSON.

Извлечение значений из повторяющихся столбцов

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

SELECT
    SimpleArray, Element
FROM
    OPENROWSET(
        BULK 'parquet/nested/justSimpleArray.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) AS arrays
    CROSS APPLY OPENJSON (SimpleArray) WITH (Element int '$') as array_values

Дальнейшие действия

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