Tipos aninhados de consulta em arquivos Parquet e JSON usando o pool SQL sem servidor no Azure Synapse Analytics

Artigo
12/18/2024

Neste artigo, você aprenderá como escrever uma consulta usando o pool SQL sem servidor no Azure Synapse Analytics. A consulta lerá Tipos aninhados de parquet. Os tipos aninhados são estruturas complexas que representam objetos ou matrizes. Os tipos aninhados podem ser armazenados em:

Parquet, onde você pode ter várias colunas complexas que contêm matrizes e objetos.
Arquivos JSON hierárquicos, onde você pode ler um documento JSON complexo como uma única coluna.
Coleções do Azure Cosmos DB (atualmente em visualização pública fechada), onde cada documento pode conter propriedades aninhadas complexas.

O pool SQL sem servidor formata todos os tipos aninhados como objetos JSON e matrizes. Assim, você pode extrair ou modificar objetos complexos usando funções JSON ou analisar dados JSON usando a função OPENJSON.

Aqui está um exemplo de uma consulta que extrai valores escalares e de objeto do arquivo JSON do conjunto de dados de pesquisa aberta COVID-19, que contém objetos aninhados:

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;

A JSON_VALUE função retorna um valor escalar do campo no caminho especificado. A JSON_QUERY função retorna um objeto formatado como JSON do campo no caminho especificado.

Importante

Este exemplo usa um arquivo do COVID-19 Open Research Dataset. Veja a licença e a estrutura dos dados aqui.

Pré-requisitos

A primeira etapa é criar um banco de dados onde a fonte de dados será criada. Em seguida, você inicializará os objetos executando um script de instalação no banco de dados. O script de instalação criará as fontes de dados, credenciais com escopo de banco de dados e formatos de arquivo externos usados nos exemplos.

Dados aninhados ou repetidos do projeto

Um arquivo Parquet pode ter várias colunas com tipos complexos. Os valores dessas colunas são formatados como texto JSON e retornados como colunas VARCHAR. A consulta a seguir lê o arquivo structExample.parquet e mostra como ler os valores das colunas aninhadas:

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];

Esta consulta retorna o seguinte resultado. O conteúdo de cada objeto aninhado é retornado como texto JSON.

DateStruct	TimeStruct	Carimbo de data/horaStruct	DecimalStruct	FloatStruct
{"Data":"2009-04-25"}	{"Tempo":"20:51:54.3598000"}	{"Carimbo de data/hora":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412.25350}	{"Flutuar":0.5}
{"Data":"1916-04-29"}	{"Tempo":"00:16:04.6778000"}	{"Carimbo de data/hora":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Flutuar":-2.125}

A consulta a seguir lê o arquivo justSimpleArray.parquet. Ele projeta todas as colunas do arquivo Parquet, incluindo dados aninhados e repetidos.

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

Esta consulta retornará o seguinte resultado:

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

Ler propriedades de colunas de objetos aninhados

A JSON_VALUE função permite que você retorne valores de colunas formatadas como texto 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;

O resultado é mostrado na tabela a seguir:

title	first_author_name	body_text	complex_column
Informações Complementares Um eco-epidemio...	Julien	- Figura S1: Filogenia de...	`{ "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"`

Ao contrário dos arquivos JSON, que na maioria dos casos retornam uma única coluna que contém um objeto JSON complexo, os arquivos Parquet podem ter várias colunas complexas. Você pode ler as propriedades das colunas aninhadas usando a JSON_VALUE função em cada coluna. OPENROWSET Permite especificar diretamente os caminhos das propriedades aninhadas em uma WITH cláusula. Você pode definir os caminhos como o nome de uma coluna ou pode adicionar uma expressão de caminho JSON após o tipo de coluna.

A consulta a seguir lê o arquivo structExample.parquet e mostra como exibir elementos de uma coluna aninhada. Há duas maneiras de fazer referência a um valor aninhado:

Especificando a expressão de caminho de valor aninhado após a especificação de tipo.
Formatando o nome da coluna como um caminho aninhado usando "." para fazer referência aos campos.

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];

Acessar elementos de colunas repetidas

A consulta a seguir lê o arquivo justSimpleArray.parquet e usa JSON_VALUE para recuperar um elemento escalar de dentro de uma coluna repetida, como uma matriz ou mapa:

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];

Eis o resultado:

SimpleArray	PrimeiroElemento	SecondElement	TerceiroElemento
[11,12,13]	11	12	13
[21,22,23]	21	22	23

Acessar subobjetos a partir de colunas complexas

A consulta a seguir lê o arquivo mapExample.parquet e usa JSON_QUERY para recuperar um elemento não escalar de dentro de uma coluna repetida, como uma matriz ou mapa:

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

Você também pode fazer referência explícita às colunas que deseja retornar em uma WITH cláusula:

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];

A estrutura MapOfPersons é retornada como uma coluna VARCHAR e formatada como uma cadeia de caracteres JSON.

Projetar valores a partir de colunas repetidas

Se você tiver uma matriz de valores escalares (por exemplo [1,2,3]) em algumas colunas, poderá expandi-los facilmente e juntá-los à linha principal usando este script:

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

Próximos passos

O próximo artigo mostrará como consultar arquivos JSON.

Partilhar via