Eseguire query su tipi nidificati nei file Parquet e JSON con il pool SQL serverless in Azure Synapse Analytics

Questo articolo illustra come scrivere una query usando il pool SQL serverless in Azure Synapse Analytics. La query leggerà i tipi nidificati Parquet. I tipi nidificati sono strutture complesse che rappresentano oggetti o matrici. I tipi nidificati possono essere archiviati in:

• Parquet, in cui è possibile avere più colonne complesse che contengono matrici e oggetti.
• File JSON gerarchici, in cui è possibile leggere un documento JSON complesso come una singola colonna.
• Raccolte di Azure Cosmos DB (attualmente in anteprima pubblica controllata), in cui ogni documento può contenere proprietà annidate complesse.

Il pool SQL serverless formatta tutti i tipi nidificati come oggetti e matrici JSON. È quindi possibile estrarre o modificare oggetti complessi con funzioni JSON o analizzare i dati JSON con la funzione OPENJSON.

Ecco un esempio di query che estrae i valori scalari e di oggetto dal file JSON del set di dati per la ricerca aperta sul COVID-19, che contiene oggetti annidati:

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;

La funzione JSON_VALUE restituisce un valore scalare dal campo in corrispondenza del percorso specificato. La funzione JSON_QUERY restituisce un oggetto in formato JSON dal campo nel percorso specificato.

Prerequisiti

Il primo passaggio consiste nel creare un database in cui verrà creata l'origine dati. Si inizializzeranno quindi gli oggetti eseguendo uno script di configurazione nel database. Questo script di configurazione creerà le origini dati, le credenziali con ambito database e i formati di file esterni usati negli esempi.

Proiettare dati annidati o ripetuti

Un file Parquet può avere più colonne con tipi complessi. I valori di queste colonne vengono formattati come testo JSON e restituiti come colonne VARCHAR. La query seguente legge il file structExample.parquet e mostra come leggere i valori delle colonne annidate:

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

Questa query restituisce il risultato seguente. Il contenuto di ogni oggetto annidato viene restituito come testo JSON.

La query seguente legge il file justSimpleArray.parquet Proietta tutte le colonne del file parquet, inclusi i dati annidati e ripetuti.

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

Questa query restituirà il risultato seguente:

Leggere le proprietà dalle colonne di oggetti annidate

La funzione JSON_VALUE consente di restituire valori da colonne formattate come testo 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;

Il risultato è illustrato nella tabella seguente:

A differenza dei file JSON, che nella maggior parte dei casi restituiscono una singola colonna che contiene un oggetto JSON complesso, i file Parquet possono avere più colonne complesse. È possibile leggere le proprietà delle colonne annidate usando la funzione JSON_VALUE su ogni colonna. OPENROWSET consente di specificare direttamente i percorsi delle proprietà annidate in una clausola WITH. È possibile impostare i percorsi come nome di una colonna oppure aggiungere un'espressione di percorso JSON dopo il tipo di colonna.

La query seguente legge il file structExample.parquet e mostra come visualizzare gli elementi di una colonna annidata. Esistono due modi per fare riferimento a un valore annidato:

• Specificando l'espressione di percorso del valore annidato dopo la specifica del tipo.
• Formattando il nome della colonna come percorso annidato e usando "." per fare riferimento ai campi.

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

Accesso agli elementi di colonne ripetute

La query seguente legge il file justSimpleArray.parquet e usa JSON_VALUE per recuperare un elemento scalare dall'interno di una colonna ripetuta, ad esempio una matrice o un mapping:

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

Il risultato è il seguente:

Accedere a oggetti secondari da colonne complesse

La query seguente legge il file mapExample.parquet e usa JSON_QUERY per recuperare un elemento non scalare dall'interno di una colonna ripetuta, ad esempio una matrice o un mapping:

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

È anche possibile fare riferimento in modo esplicito alle colonne da restituire in una clausola 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];

La struttura MapOfPersons viene restituita come colonna VARCHAR e formattata come stringa JSON.

Proiettare i valori da colonne ripetute

Se è disponibile una matrice di valori scalari (ad esempio [1,2,3]) in alcune colonne, è possibile espanderli facilmente e unirli con la riga principale usando questo 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

Passaggi successivi

Il prossimo articolo illustra come eseguire query su file JSON.