Fråga kapslade typer i Parquet- och JSON-filer med hjälp av en serverlös SQL-pool i Azure Synapse Analytics

Artikel
06/01/2023

I den här artikeln får du lära dig hur du skriver en fråga med hjälp av en serverlös SQL-pool i Azure Synapse Analytics. Frågan kommer att läsa Kapslade Parquet-typer. Kapslade typer är komplexa strukturer som representerar objekt eller matriser. Kapslade typer kan lagras i:

Parquet, där du kan ha flera komplexa kolumner som innehåller matriser och objekt.
Hierarkiska JSON-filer, där du kan läsa ett komplext JSON-dokument som en enda kolumn.
Azure Cosmos DB-samlingar (för närvarande under gated public preview), där varje dokument kan innehålla komplexa kapslade egenskaper.

Serverlös SQL-pool formaterar alla kapslade typer som JSON-objekt och matriser. Så du kan extrahera eller ändra komplexa objekt med hjälp av JSON-funktioner eller parsa JSON-data med hjälp av funktionen OPENJSON.

Här är ett exempel på en fråga som extraherar skalär- och objektvärden från JSON-filen COVID-19 Open Research Dataset , som innehåller kapslade objekt:

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;

Funktionen JSON_VALUE returnerar ett skalärt värde från fältet vid den angivna sökvägen. Funktionen JSON_QUERY returnerar ett objekt formaterat som JSON från fältet vid den angivna sökvägen.

Viktigt

I det här exemplet används en fil från open research-datauppsättningen COVID-19. Se licensen och datastrukturen här.

Förutsättningar

Det första steget är att skapa en databas där datakällan skapas. Sedan initierar du objekten genom att köra ett installationsskript på databasen. Installationsskriptet skapar datakällor, databasbegränsade autentiseringsuppgifter och externa filformat som används i exemplen.

Project kapslade eller upprepade data

En Parquet-fil kan ha flera kolumner med komplexa typer. Värdena från dessa kolumner formateras som JSON-text och returneras som VARCHAR-kolumner. Följande fråga läser filen structExample.parquet och visar hur du läser värdena för de kapslade kolumnerna:

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

Den här frågan returnerar följande resultat. Innehållet i varje kapslat objekt returneras som JSON-text.

DateStruct	TimeStruct	TidsstämpelStruct	DecimalStruct	FloatStruct
{"Datum":"2009-04-25"}	{"Tid":"20:51:54.3598000"}	{"Tidsstämpel":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412.25350}	{"Float":0.5}
{"Datum":"1916-04-29"}	{"Tid":"00:16:04.6778000"}	{"Tidsstämpel":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545.62800}	{"Float":-2.125}

Följande fråga läser filen justSimpleArray.parquet. Den projicerar alla kolumner från Parquet-filen, inklusive kapslade och upprepade data.

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

Den här frågan returnerar följande resultat:

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

Läsa egenskaper från kapslade objektkolumner

Med JSON_VALUE funktionen kan du returnera värden från kolumner som är formaterade som JSON-text:

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;

Resultatet visas i följande tabell:

title	first_author_name	body_text	complex_column
Kompletterande information En ekoepidemiolo...	Julien	- Bild S1: Phylogeny av ...	`{ "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"`

Till skillnad från JSON-filer, som i de flesta fall returnerar en enda kolumn som innehåller ett komplext JSON-objekt, kan Parquet-filer ha flera komplexa kolumner. Du kan läsa egenskaperna för kapslade kolumner med hjälp JSON_VALUE av funktionen i varje kolumn. OPENROWSET gör att du kan ange sökvägarna för de kapslade egenskaperna direkt i en WITH -sats. Du kan ange sökvägarna som namnet på en kolumn, eller så kan du lägga till ett JSON-sökvägsuttryck efter kolumntypen.

Följande fråga läser filen structExample.parquet och visar hur du visar element i en kapslad kolumn. Det finns två sätt att referera till ett kapslat värde:

Genom att ange det kapslade värdets sökvägsuttryck efter typspecifikationen.
Genom att formatera kolumnnamnet som en kapslad sökväg med hjälp av gör "." för att referera till fälten.

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

Komma åt element från upprepade kolumner

Följande fråga läser filen justSimpleArray.parquet och använder JSON_VALUE för att hämta ett skalärt element inifrån en upprepad kolumn, till exempel en matris eller karta:

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

Här är resultatet:

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

Få åtkomst till underobjekt från komplexa kolumner

Följande fråga läser filen mapExample.parquet och använder JSON_QUERY för att hämta ett icke-skalärt element inifrån en upprepad kolumn, till exempel en matris eller karta:

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

Du kan också uttryckligen referera till de kolumner som du vill returnera i en WITH -sats:

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

Strukturen MapOfPersons returneras som en VARCHAR-kolumn och formateras som en JSON-sträng.

Projektvärden från upprepade kolumner

Om du har en matris med skalära värden (till exempel [1,2,3]) i vissa kolumner kan du enkelt expandera dem och koppla dem till huvudraden med hjälp av det här skriptet:

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

Nästa steg

I nästa artikel visas hur du kör frågor mot JSON-filer.

Dela via