JSON-fájlok lekérdezése kiszolgáló nélküli SQL-készlet használatával az Azure Synapse Analyticsben

Cikk
10/18/2023

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 célja JSON-fájlok olvasása AZ OPENROW Standard kiadás T használatával.

Standard JSON-fájlok, amelyekben több JSON-dokumentum is JSON-tömbként van tárolva.
Vonallal tagolt JSON-fájlok, amelyekben a JSON-dokumentumok új sor karakterrel vannak elválasztva. Az ilyen típusú fájlok gyakori bővítményei a következőkjsonl: és ldjsonndjson.

JSON-dokumentumok olvasása

A JSON-fájl tartalmát úgy tekintheti meg a legegyszerűbben, ha megadja a fájl URL-címét a függvénynek, megadja a OPENROWSET csv FORMATértéket, és beállítja az és 0x0bfieldquotea fieldterminator . Ha sorhatárolt JSON-fájlokat kell olvasnia, akkor ez elegendő. Ha klasszikus JSON-fájllal rendelkezik, akkor be kell állítania az értékeket 0x0b a következőhöz rowterminator: . OPENROWSET függvény elemzi a JSON-t, és a következő formátumban adja vissza az összes dokumentumot:

Bizonylat
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"cases":3,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","day":25,"month":7,"year":2020,"cases":7,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"cases":4,"deaths":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","day":27,"month":7,"year":2020,"cases":8,"deaths":0,"geo_id":"AF"}

Ha a fájl nyilvánosan elérhető, vagy ha a Microsoft Entra-identitása hozzáfér ehhez a fájlhoz, akkor a következő példákhoz hasonló lekérdezéssel kell látnia a fájl tartalmát.

JSON-fájlok olvasása

Az alábbi minta lekérdezés beolvassa a JSON- és sorhatárolt JSON-fájlokat, és minden dokumentumot külön sorként ad vissza.

select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.jsonl',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.json',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Az előző minta lekérdezés JSON-dokumentuma objektumtömböt tartalmaz. A lekérdezés minden objektumot külön sorként ad vissza az eredményhalmazban. Győződjön meg arról, hogy hozzáfér ehhez a fájlhoz. Ha a fájl SAS-kulccsal vagy egyéni identitással van védve, kiszolgálószintű hitelesítő adatokat kell beállítania az SQL-bejelentkezéshez.

Adatforrások használata

Az előző példa a fájl teljes elérési útját használja. Másik lehetőségként létrehozhat egy külső adatforrást a tár gyökérmappájára mutató hellyel, és használhatja ezt az adatforrást és a függvényben lévő OPENROWSET fájl relatív elérési útját:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.json',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b',
        rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON
    ) with (doc nvarchar(max)) as rows

Ha egy adatforrás SAS-kulccsal vagy egyéni identitással van védve, az adatforrást adatbázis-hatókörű hitelesítő adatokkal konfigurálhatja.

A következő szakaszokban megtudhatja, hogyan kérdezhet le különböző típusú JSON-fájlokat.

JSON-dokumentumok elemzése

Az előző példákban szereplő lekérdezések minden JSON-dokumentumot egyetlen sztringként ad vissza az eredményhalmaz egy külön sorában. Függvények JSON_VALUEOPENJSON használatával elemezheti a JSON-dokumentumok értékeit, és relációs értékként adja vissza őket, ahogyan az a következő példában is látható:

date_rep	elemzése	geo_id
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

JSON-mintadokumentum

A lekérdezési példák a következő struktúrájú dokumentumokat tartalmazó JSON-fájlokat olvasnak be:

{
    "date_rep":"2020-07-24",
    "day":24,"month":7,"year":2020,
    "cases":13,"deaths":0,
    "countries_and_territories":"Afghanistan",
    "geo_id":"AF",
    "country_territory_code":"AFG",
    "continent_exp":"Asia",
    "load_date":"2020-07-25 00:05:14",
    "iso_country":"AF"
}

Megjegyzés:

Ha ezek a dokumentumok sorhatárolt JSON-ként vannak tárolva, be kell állítania FIELDTERMINATOR és FIELDQUOTE 0x0b. Ha szabványos JSON-formátummal rendelkezik, 0x0b kell beállítania ROWTERMINATOR .

JSON-fájlok lekérdezése JSON_VALUE

Az alábbi lekérdezés bemutatja, hogyan használhatja a JSON_VALUE a skaláris értékek (date_rep, countries_and_territories, cases) JSON-dokumentumokból való lekérésére:

select
    JSON_VALUE(doc, '$.date_rep') AS date_reported,
    JSON_VALUE(doc, '$.countries_and_territories') AS country,
    CAST(JSON_VALUE(doc, '$.deaths') AS INT) as fatal,
    JSON_VALUE(doc, '$.cases') as cases,
    doc
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
order by JSON_VALUE(doc, '$.geo_id') desc

Miután JSON-tulajdonságokat kinyert egy JSON-dokumentumból, megadhatja az oszlop aliasát, és igény szerint valamilyen típusra vetheti a szöveges értéket.

JSON-fájlok lekérdezése OPENJSON használatával

Az alábbi lekérdezés OPENJSON-t használ. Lekéri a Szerbiában jelentett COVID-statisztikákat:

select
    *
from openrowset(
        bulk 'latest/ecdc_cases.jsonl',
        data_source = 'covid',
        format = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
    ) with (doc nvarchar(max)) as rows
    cross apply openjson (doc)
        with (  date_rep datetime2,
                cases int,
                fatal int '$.deaths',
                country varchar(100) '$.countries_and_territories')
where country = 'Serbia'
order by country, date_rep desc;

Az eredmények funkcionálisan megegyeznek a függvény használatával JSON_VALUE visszaadott eredményekkel. Bizonyos esetekben előnyt jelenthet a OPENJSON következőhöz:JSON_VALUE

A záradékban WITH explicit módon állíthatja be az oszlop aliasokat és a típusokat minden tulajdonsághoz. A függvényt nem kell minden oszlopban elhelyeznie CAST a SELECT listában.
OPENJSON akkor lehet gyorsabb, ha nagy számú tulajdonságot ad vissza. Ha csak 1-2 tulajdonságot ad vissza, a OPENJSON függvény többletterhelést jelenthet.
A függvényt OPENJSON akkor kell használnia, ha minden dokumentumból elemeznie kell a tömböt, és csatlakoztatnia kell a szülősorhoz.

Következő lépések

A sorozat következő cikkei bemutatják, hogyan:

Share via