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

Článek
06/01/2023

V tomto článku se dozvíte, jak napsat dotaz pomocí bezserverového fondu SQL v Azure Synapse Analytics. Dotaz bude číst vnořené typy Parquet. Vnořené typy jsou komplexní struktury, které představují objekty nebo pole. Vnořené typy se dají ukládat do:

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ě ve 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. Můžete tedy extrahovat nebo upravovat složité objekty pomocí funkcí JSON nebo parsovat data JSON pomocí funkce OPENJSON.

Tady je příklad dotazu, který extrahuje skalární hodnoty a hodnoty objektů ze souboru JSON COVID-19 Open Research Dataset , 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 v zadané cestě. Funkce JSON_QUERY vrátí z pole v zadané cestě objekt formátovaný jako JSON.

Důležité

Tento příklad používá soubor z datové sady COVID-19 Open Research Dataset. Informace o licenci a struktuře dat najdete tady.

Požadavky

Prvním krokem je vytvoření databáze, ve které se vytvoří zdroj dat. Pak inicializujete objekty 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.

Vnořená nebo opakovaná data projektu

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ácené jako sloupce VARCHAR. Následující dotaz přeč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.

Date – struktura	TimeStruct	TimestampStruct	DecimalStruct	FloatStruct
{"Date":"2009-04-25"}	{"Čas":"20:51:54.3598000"}	{"Časové razítko":"5501-04-08 12:13:57.4821000"}	{"Decimal":11143412.25350}	{"Float":0.5}
{"Date":"1916-04-29"}	{"Čas":"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. Promítá 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 je uveden v následující tabulce:

title	first_author_name	body_text	complex_column
Doplňující informace Eco-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 obsahující složitý objekt JSON, můžou soubory Parquet obsahovat více složitých sloupců. Vlastnosti vnořených sloupců můžete číst pomocí JSON_VALUE funkce pro každý sloupec. OPENROWSET umožňuje přímo zadat cesty k vnořeným vlastnostem v klauzuli WITH . Cesty můžete nastavit jako název sloupce nebo můžete přidat výraz cesty JSON za typ sloupce.

Následující dotaz přeč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.
Naformátováním názvu sloupce jako vnořené cesty pomocí příkazu do "." odkazujícího 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 přečte soubor justSimpleArray.parquet a pomocí JSON_VALUE načte skalární element z opakovaného sloupce, jako je pole nebo mapování:

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	Prvníelement	Druhýelement	Třetíelement
[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 přečte soubor mapExample.parquet a použije JSON_QUERY k načtení nes skalárního elementu z opakovaného sloupce, jako je pole nebo mapování:

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

V klauzuli můžete také explicitně odkazovat na sloupce, které chcete vrátit 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];

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