Consultar tipos aninhados em ficheiros Parquet e JSON com o conjunto de SQL sem servidor no Azure Synapse Analytics

Neste artigo, irá aprender a escrever uma consulta com o conjunto de SQL sem servidor no Azure Synapse Analytics. A consulta irá ler os tipos aninhados parquet. Os tipos aninhados são estruturas complexas que representam objetos ou matrizes. Os tipos aninhados podem ser armazenados em:

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

O conjunto de SQL sem servidor formatará todos os tipos aninhados como matrizes e objetos JSON. Assim, pode extrair ou modificar objetos complexos com funções JSON ou analisar dados JSON com a função OPENJSON.

Eis um exemplo de uma consulta que extrai valores escalares e de objetos do ficheiro JSON do Conjunto de Dados de Investigação 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 devolve um valor escalar do campo no caminho especificado. A JSON_QUERY função devolve um objeto formatado como JSON do campo no caminho especificado.

Pré-requisitos

O primeiro passo é criar uma base de dados onde a origem de dados será criada. Em seguida, irá inicializar os objetos ao executar um script de configuração na base de dados. O script de configuração irá criar as origens de dados, as credenciais no âmbito da base de dados e os formatos de ficheiro externos utilizados nos exemplos.

Dados aninhados ou repetidos do projeto

Um ficheiro Parquet pode ter múltiplas colunas com tipos complexos. Os valores destas colunas são formatados como texto JSON e devolvidos como colunas VARCHAR. A consulta seguinte lê o ficheiro 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 devolve o seguinte resultado. O conteúdo de cada objeto aninhado é devolvido como texto JSON.

A consulta seguinte lê o ficheiro justSimpleArray.parquet. Projeta todas as colunas do ficheiro Parquet, incluindo dados aninhados e repetidos.

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

Esta consulta devolverá o seguinte resultado:

Ler propriedades de colunas de objeto aninhadas

A JSON_VALUE função permite-lhe devolver 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 é apresentado na seguinte tabela:

Ao contrário dos ficheiros JSON, que na maioria dos casos devolvem uma única coluna que contém um objeto JSON complexo, os ficheiros Parquet podem ter múltiplas colunas complexas. Pode ler as propriedades das colunas aninhadas com a JSON_VALUE função em cada coluna. OPENROWSET permite-lhe especificar diretamente os caminhos das propriedades aninhadas numa WITH cláusula. Pode definir os caminhos como o nome de uma coluna ou adicionar uma expressão de caminho JSON após o tipo de coluna.

A consulta seguinte lê o ficheiro structExample.parquet e mostra como apresentar elementos de uma coluna aninhada. Existem duas formas de referenciar um valor aninhado:

• Ao especificar a expressão de caminho de valor aninhado após a especificação do tipo.
• Ao formatar o nome da coluna como um caminho aninhado, utilize "" para referenciar os 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];

Aceder a elementos de colunas repetidas

A consulta seguinte lê o ficheiro justSimpleArray.parquet e utiliza JSON_VALUE para obter um elemento escalar a partir 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:

Aceder a sub-objetos a partir de colunas complexas

A consulta seguinte lê o ficheiro mapExample.parquet e utiliza JSON_QUERY para obter um elemento não escalar a partir 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];

Também pode referenciar explicitamente as colunas que pretende devolver numa 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 é devolvida como uma coluna VARCHAR e formatada como uma cadeia JSON.

Valores de projeto de colunas repetidas

Se tiver uma matriz de valores escalares (por exemplo [1,2,3]) em algumas colunas, pode expandi-los facilmente e associá-los à linha principal com 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

Passos seguintes

O próximo artigo irá mostrar-lhe como Consultar ficheiros JSON.