Consultar tipos aninhados em arquivos Parquet e JSON usando o pool SQL sem servidor no Azure Synapse Analytics

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

• Parquet, onde você pode ter várias colunas complexas que contêm matrizes e objetos.
• Arquivos JSONhierárquicos, nos quais você pode ler um documento JSON complexo como uma única coluna.
• Coleção do CosmosDB, na qual cada documento pode conter propriedades aninhadas complexas (no momento, em versão prévia pública restrita).

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

Veja um exemplo de uma consulta que extrai valores escalares e de objeto do arquivo JSON COVID-19 Open Research DataSet, 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 a partir do campo no caminho especificado.

Pré-requisitos

A primeira etapa é criar um banco de dados no qual as fontes de dados serão criadas. Em seguida, você inicializará os objetos executando um script de instalação no banco de dados. Esse script de instalação criará as fontes de dados, as credenciais no escopo do banco de dados e os formatos de arquivo externos que são usados nessas amostras.

Dados de projeto aninhados ou repetidos

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

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

A consulta a seguir lê o arquivo justSimpleArray.parquet. Ela 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];

A consulta deve retornar o resultado a seguir:

Ler propriedades de colunas de objeto aninhado

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 no seguinte tabela:

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 de colunas aninhadas usando a JSON_VALUE função em cada coluna. OPENROWSET permite que você especifique 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 levantar 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.
• Ao Formatar 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 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:

Acessar subobjetos de colunas complexas

A consulta a seguir lê o arquivo mapExample.parquet e usa JSON_QUERY para recuperar um elemento não escalar 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 referenciar explicitamente as 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.

Valores de projeto de colunas repetidas

Se você tiver uma matriz de valores escalares (por exemplo [1,2,3] ) em algumas colunas, poderá expandi-los facilmente e associá-los com a 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óximas etapas

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