Geneste typen in Parquet- en JSON-bestanden opvragen met behulp van een serverloze SQL-pool in Azure Synapse Analytics

Artikel
06/01/2023

In dit artikel leert u hoe u een query schrijft met behulp van een serverloze SQL-pool in Azure Synapse Analytics. De query leest geneste Parquet-typen. Geneste typen zijn complexe structuren die objecten of matrices vertegenwoordigen. Geneste typen kunnen worden opgeslagen in:

Parquet, waar u meerdere complexe kolommen kunt hebben die matrices en objecten bevatten.
Hiërarchische JSON-bestanden, waar u een complex JSON-document als één kolom kunt lezen.
Azure Cosmos DB-verzamelingen (momenteel onder beperkte openbare preview), waarbij elk document complexe geneste eigenschappen kan bevatten.

Serverloze SQL-pool maakt alle geneste typen op als JSON-objecten en matrices. U kunt complexe objecten dus extraheren of wijzigen met behulp van JSON-functies of JSON-gegevens parseren met behulp van de functie OPENJSON.

Hier volgt een voorbeeld van een query waarmee scalaire en objectwaarden worden geëxtraheerd uit het JSON-bestand COVID-19 Open Research Dataset , dat geneste objecten bevat:

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;

De JSON_VALUE functie retourneert een scalaire waarde uit het veld op het opgegeven pad. De JSON_QUERY functie retourneert een object dat is opgemaakt als JSON uit het veld op het opgegeven pad.

Belangrijk

In dit voorbeeld wordt een bestand uit de COVID-19 Open Research Dataset gebruikt. Bekijk hier de licentie en de structuur van de gegevens.

Vereisten

De eerste stap is het maken van een database waarin de gegevensbron wordt gemaakt. Vervolgens initialiseert u de objecten door een installatiescript uit te voeren op de database. Met het installatiescript worden de gegevensbronnen, referenties voor databasebereik en externe bestandsindelingen gemaakt die in de voorbeelden worden gebruikt.

Geneste of herhaalde gegevens projecteren

Een Parquet-bestand kan meerdere kolommen met complexe typen hebben. De waarden uit deze kolommen zijn opgemaakt als JSON-tekst en geretourneerd als VARCHAR-kolommen. De volgende query leest het bestand structExample.parquet en laat zien hoe u de waarden van de geneste kolommen kunt lezen:

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

Deze query retourneert het volgende resultaat. De inhoud van elk geneste object wordt geretourneerd als JSON-tekst.

DateStruct	TimeStruct	TimestampStruct	DecimalStruct	FloatStruct
{"Datum":"2009-04-25"}	{"Tijd":"20:51:54.3598000"}	{"Timestamp":"5501-04-08 12:13:57.4821000"}	{"Decimaal":11143412,25350}	{"Float":0.5}
{"Datum":"1916-04-29"}	{"Tijd":"00:16:04.6778000"}	{"Timestamp":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Float":-2.125}

Met de volgende query wordt het bestand justSimpleArray.parquet gelezen. Alle kolommen uit het Parquet-bestand worden geprojecteerd, inclusief geneste en herhaalde gegevens.

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

Deze query retourneert het volgende resultaat:

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

Eigenschappen van geneste objectkolommen lezen

Met de JSON_VALUE functie kunt u waarden retourneren uit kolommen die zijn opgemaakt als JSON-tekst:

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;

Het resultaat wordt weergegeven in de volgende tabel:

title	first_author_name	body_text	complex_column
Aanvullende informatie Een eco-epidemiolo...	Julien	- Afbeelding S1: Fylogenie van...	`{ "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"`

In tegenstelling tot JSON-bestanden, die in de meeste gevallen één kolom retourneren die een complex JSON-object bevat, kunnen Parquet-bestanden meerdere complexe kolommen hebben. U kunt de eigenschappen van geneste kolommen lezen met behulp van de JSON_VALUE functie voor elke kolom. OPENROWSET hiermee kunt u rechtstreeks de paden van de geneste eigenschappen in een WITH component opgeven. U kunt de paden instellen als de naam van een kolom of u kunt een JSON-padexpressie toevoegen na het kolomtype.

De volgende query leest het bestand structExample.parquet en laat zien hoe u elementen van een geneste kolom kunt weergeven. Er zijn twee manieren om te verwijzen naar een geneste waarde:

Door de geneste waardepadexpressie na de typespecificatie op te geven.
Door de kolomnaam op te maken als een geneste pad door do te gebruiken om naar de velden te verwijzen.

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

Elementen benaderen vanuit herhaalde kolommen

De volgende query leest het bestand justSimpleArray.parquet en gebruikt JSON_VALUE om een scalair element op te halen uit een herhaalde kolom, zoals een matrix of kaart:

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

Dit is het resultaat:

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

Toegang tot subobjecten vanuit complexe kolommen

De volgende query leest het bestand mapExample.parquet en gebruikt JSON_QUERY om een niet-scalair element op te halen uit een herhaalde kolom, zoals een matrix of kaart:

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

U kunt ook expliciet verwijzen naar de kolommen die u wilt retourneren in een WITH component:

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

De structuur MapOfPersons wordt geretourneerd als een VARCHAR-kolom en opgemaakt als een JSON-tekenreeks.

Projectwaarden uit herhaalde kolommen

Als u een matrix met scalaire waarden (bijvoorbeeld [1,2,3]) in sommige kolommen hebt, kunt u deze eenvoudig uitvouwen en samenvoegen met de hoofdrij met behulp van dit 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

Volgende stappen

In het volgende artikel wordt uitgelegd hoe u query's kunt uitvoeren op JSON-bestanden.

Share via