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

Ebben a cikkben 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 beolvassa a Parquet beágyazott típusokat. 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 összetett JSON-dokumentumokat 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ény használatával.

Í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 megtekintheti az adatok licencét és szerkezetét.

Előfeltételek

Az első lépés egy adatbázis létrehozása, amelyben az adatforrás létrejön. 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.

Beágyazott vagy ismétlődő adatok vetítése

A parquet-fájlok több, összetett típusú oszlopot is tartalmazhatnak. Az oszlopok értékei 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 olvashatók be a beágyazott oszlopok értékei:

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. A beágyazott objektumok tartalma JSON-szövegként lesz visszaadva.

DateStruct	TimeStruct	IdőbélyegStruktúra	Decimális szerkezet	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}

Az alábbi lekérdezés beolvassa az justSimpleArray.parquet fájlt. A Parquet-fájl összes oszlopát kiprojektezi, 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	első_szerző_neve	body_text	complex_column
Kiegészítő információk Egy öko-epidemiolo...	Julien	- S1. ábra: 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ájlokkal ellentétben, 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 oszlopok függvényével JSON_VALUE olvashatja el. OPENROWSET lehetővé teszi, hogy közvetlenül megadhatja a beágyazott tulajdonságok elérési útjá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 útkifejezé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 hozni. A beágyazott értékekre kétféleképpen hivatkozhat:

A beágyazott érték elérési útjának kifejezésének megadásával a típus specifikációja után.
Az oszlop nevének beágyazott elérési útként való formázásához használja a "." parancsot 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	MásodikElem	ThirdElement
[11,12,13]	11	12	13
[21,22,23]	21	22	23

Alobjektumok elérése összetett oszlopokból

Az alábbi lekérdezés beolvassa a mapExample.parquet fájlt, és JSON_QUERY használatával lekér 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];

A WITH utasításban kifejezetten hivatkozhat azokra az oszlopokra is, amelyeket vissza szeretne kapni.

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 őket, és összekapcsolhatja őket a fő sortal ezzel a 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.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-04-03