Dotazování vnořených typů v souborech Parquet a JSON pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics

Článek
12/18/2024

V tomto článku se dozvíte, jak napsat dotaz pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics. Dotaz bude číst vnořené typy Parquet. Vnořené typy jsou složité struktury, které představují objekty nebo pole. Vnořené typy mohou být uloženy v:

Parquet, kde můžete mít více složitých sloupců, které obsahují pole a objekty.
Hierarchické soubory JSON, kde můžete číst složitý dokument JSON jako jeden sloupec.
Kolekce Azure Cosmos DB (aktuálně v chráněné verzi Public Preview), kde každý dokument může obsahovat složité vnořené vlastnosti.

Bezserverový fond SQL formátuje všechny vnořené typy jako objekty a pole JSON. Komplexní objekty můžete extrahovat nebo upravovat pomocí funkcí JSON nebo parsovat data JSON pomocí funkce OPENJSON.

Tady je příklad dotazu, který extrahuje skalární a objektové hodnoty ze souboru JSON open research dataset COVID-19, který obsahuje vnořené objekty:

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;

Funkce JSON_VALUE vrátí skalární hodnotu z pole na zadané cestě. Funkce JSON_QUERY vrátí objekt formátovaný jako JSON z pole v zadané cestě.

Důležité

Tento příklad používá soubor z open research dataset COVID-19. Tady najdete licenci a strukturu dat.

Požadavky

Prvním krokem je vytvoření databáze, ve které se vytvoří zdroj dat. Objekty pak inicializujete spuštěním instalačního skriptu v databázi. Instalační skript vytvoří zdroje dat, přihlašovací údaje v oboru databáze a formáty externích souborů, které se používají v ukázkách.

Projektová vnořená nebo opakovaná data

Soubor Parquet může mít více sloupců se složitými typy. Hodnoty z těchto sloupců jsou formátované jako text JSON a vrátí se jako sloupce VARCHAR. Následující dotaz načte soubor structExample.parquet a ukazuje, jak číst hodnoty vnořených sloupců:

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

Tento dotaz vrátí následující výsledek. Obsah každého vnořeného objektu se vrátí jako text JSON.

DateStruct	TimeStruct	Timestamp – struktura	DecimalStruct	Float – struktura
{"Datum":"2009-04-25"}	{"Time":"20:51:54.3598000"}	{"Časové razítko":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412,25350}	{"Float":0.5}
{"Date":"1916-04-29"}	{"Time":"00:16:04.6778000"}	{"Časové razítko":"1990-06-30 20:50:52.6828000"}	{"Decimal":1963545,62800}	{"Float":-2.125}

Následující dotaz přečte soubor justSimpleArray.parquet. Projektuje všechny sloupce ze souboru Parquet, včetně vnořených a opakovaných dat.

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

Tento dotaz vrátí následující výsledek:

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

Čtení vlastností ze sloupců vnořených objektů

Funkce JSON_VALUE umožňuje vracet hodnoty ze sloupců formátovaných jako text 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;

Výsledek se zobrazí v následující tabulce:

title	first_author_name	body_text	complex_column
Doplňující informace Eko-epidemiolo...	Julien	- Obrázek S1: Phylogenie...	`{ "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"`

Na rozdíl od souborů JSON, které ve většině případů vrací jeden sloupec, který obsahuje složitý objekt JSON, můžou mít soubory Parquet několik složitých sloupců. Vlastnosti vnořených sloupců můžete číst pomocí JSON_VALUE funkce na každém sloupci. OPENROWSET umožňuje přímo zadat cesty vnořených vlastností v WITH klauzuli. Cesty můžete nastavit jako název sloupce nebo můžete za typ sloupce přidat výraz cesty JSON.

Následující dotaz načte soubor structExample.parquet a ukazuje, jak zobrazit prvky vnořeného sloupce. Existují dva způsoby, jak odkazovat na vnořenou hodnotu:

Zadáním výrazu cesty k vnořené hodnotě za specifikaci typu.
Formátováním názvu sloupce jako vnořené cesty pomocí příkazu do ". odkazujete na pole.

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

Přístup k prvkům z opakovaných sloupců

Následující dotaz načte soubor justSimpleArray.parquet a pomocí JSON_VALUE načte skalární prvek z opakujícího se sloupce, jako je pole nebo 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];

Tady je výsledek:

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

Přístup k dílčím objektům ze složitých sloupců

Následující dotaz načte soubor mapExample.parquet a používá JSON_QUERY k načtení nes skalárního elementu z opakujícího se sloupce, jako je pole nebo mapa:

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

Můžete také explicitně odkazovat na sloupce, které chcete vrátit v WITH klauzuli:

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

Struktura MapOfPersons se vrátí jako sloupec VARCHAR a naformátuje se jako řetězec JSON.

Hodnoty projektu z opakovaných sloupců

Pokud máte v některých sloupcích pole skalárních hodnot (například [1,2,3]), můžete je snadno rozbalit a spojit s hlavním řádkem pomocí tohoto skriptu:

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

Další kroky

V dalším článku se dozvíte, jak dotazovat soubory JSON.

Sdílet prostřednictvím