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

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.

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.

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:

Č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:

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:

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.