Beágyazott típusok lekérdezése Parquet- és JSON-fájlokban kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben

  • Cikk

Ebből a cikkből megtudhatja, hogyan írhat lekérdezést kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben. A lekérdezés felolvassa a Parquet beágyazott típusait. A beágyazott típusok olyan összetett struktúrák, amelyek objektumokat vagy tömböket jelölnek. A beágyazott típusok a következő helyen tárolhatók:

  • Parquet, ahol több összetett oszlop is lehet, amelyek tömböket és objektumokat tartalmaznak.
  • Hierarchikus JSON-fájlok, ahol egy összetett JSON-dokumentumot olvashat egyetlen oszlopként.
  • Azure Cosmos DB-gyűjtemények (jelenleg nyilvános előzetes verzióban), ahol minden dokumentum összetett beágyazott tulajdonságokat tartalmazhat.

A kiszolgáló nélküli SQL-készlet minden beágyazott típust JSON-objektumként és tömbként formáz. Így JSON-függvényekkel kinyerheti vagy módosíthatja az összetett objektumokat , vagy elemezheti a JSON-adatokat az OPENJSON függvénnyel.

Íme egy példa egy lekérdezésre, amely skaláris és objektumértékeket nyer ki a COVID-19 Open Research Dataset JSON-fájlból, amely beágyazott objektumokat tartalmaz:

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;

A JSON_VALUE függvény skaláris értéket ad vissza a megadott elérési út mezőiből. A JSON_QUERY függvény egy JSON formátumú objektumot ad vissza a megadott elérési út mezőjéből.

Fontos

Ez a példa a COVID-19 Open Research Dataset fájljait használja. Itt tekintse meg az engedély és az adatok struktúráját.

Előfeltételek

Első lépésként hozzon létre egy adatbázist, amelyben létrejön az adatforrás. Ezután inicializálja az objektumokat egy beállítási szkript futtatásával az adatbázisban. A beállítási szkript létrehozza a mintákban használt adatforrásokat, adatbázis-hatókörű hitelesítő adatokat és külső fájlformátumokat.

Projektbe ágyazott vagy ismétlődő adatok

A Parquet-fájlok több, összetett típusú oszlopot tartalmazhatnak. Az oszlopokból származó értékek JSON-szövegként vannak formázva, és VARCHAR oszlopként jelennek meg. A következő lekérdezés beolvassa a structExample.parquet fájlt, és bemutatja, hogyan olvashatja be a beágyazott oszlopok értékeit:

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

Ez a lekérdezés a következő eredményt adja vissza. Minden beágyazott objektum tartalma JSON-szövegként lesz visszaadva.

DateStruct TimeStruct Időbélyegstruktúra DecimalStruct FloatStruct
{"Date":"2009-04-25"} {"Time":"20:51:54.3598000"} {"Időbélyeg":"5501-04-08 12:13:57.4821000"} {"Decimális":11143412.25350} {"Float":0.5}
{"Date":"1916-04-29"} {"Time":"00:16:04.6778000"} {"Időbélyeg":"1990-06-30 20:50:52.6828000"} {"Decimális":1963545.62800} {"Float":-2.125}

A következő lekérdezés beolvassa az justSimpleArray.parquet fájlt. A Parquet-fájl összes oszlopát kivetüli, beleértve a beágyazott és ismétlődő adatokat is.

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

Ez a lekérdezés a következő eredményt adja vissza:

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

Tulajdonságok beolvasása beágyazott objektumoszlopokból

A JSON_VALUE függvény lehetővé teszi, hogy JSON-szövegként formázott oszlopok értékeit adja vissza:

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;

Az eredmény az alábbi táblázatban látható:

cím first_author_name body_text complex_column
Kiegészítő információk Egy öko-epidemiolo... Julien - S1. ábra: Phylogeny a... { "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"

A JSON-fájloktól eltérően, amelyek a legtöbb esetben egyetlen, összetett JSON-objektumot tartalmazó oszlopot adnak vissza, a Parquet-fájlok több összetett oszlopot is tartalmazhatnak. A beágyazott oszlopok tulajdonságait az egyes oszlopokon JSON_VALUE lévő függvénnyel olvashatja el. OPENROWSET lehetővé teszi a beágyazott tulajdonságok elérési útvonalainak közvetlen megadását egy WITH záradékban. Beállíthatja az elérési utakat egy oszlop neveként, vagy hozzáadhat egy JSON-elérésiút-kifejezést az oszloptípus után.

Az alábbi lekérdezés beolvassa a structExample.parquet fájlt, és bemutatja, hogyan lehet egy beágyazott oszlop elemeit felszínre tenni. A beágyazott értékekre kétféleképpen hivatkozhat:

  • Adja meg a beágyazott érték elérési útjának kifejezést a típus specifikációja után.
  • Ha az oszlop nevét beágyazott elérési útként formázja a "." paranccsal a mezőkre való hivatkozáshoz.
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];

Ismétlődő oszlopok elemeinek elérése

A következő lekérdezés beolvassa az justSimpleArray.parquet fájlt, és JSON_VALUE használatával lekér egy skaláris elemet egy ismétlődő oszlopból, például tömbből vagy térképből:

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

Az eredmény a következő:

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

Alobjektumok elérése összetett oszlopokból

A következő lekérdezés beolvassa a mapExample.parquet fájlt, és JSON_QUERY használatával kér le egy nem skaláris elemet egy ismétlődő oszlopból, például tömbből vagy térképből:

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

Explicit módon hivatkozhat a záradékban WITH visszaadni kívánt oszlopokra is:

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

A struktúra MapOfPersons VARCHAR oszlopként lesz visszaadva, és JSON-sztringként van formázva.

Ismétlődő oszlopokból származó projektértékek

Ha egyes oszlopokban skaláris értékek tömbje van (például [1,2,3]), egyszerűen kibonthatja és összekapcsolhatja őket a fő sortal az alábbi szkripttel:

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

Következő lépések

A következő cikk bemutatja, hogyan kérdezhet le JSON-fájlokat.