Dotazování souborů JSON pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics

Článek
10/18/2023

V tomto článku se dozvíte, jak napsat dotaz pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics. Cílem dotazu je čtení souborů JSON pomocí OPENROWSET.

Standardní soubory JSON, ve kterých je uloženo více dokumentů JSON jako pole JSON.
Soubory JSON s oddělovači řádků, ve kterých jsou dokumenty JSON oddělené znakem nového řádku. Běžné přípony pro tyto typy souborů jsou jsonl, ldjsona ndjson.

Čtení dokumentů JSON

Nejjednodušší způsob, jak zobrazit obsah souboru JSON, je zadat adresu URL souboru do OPENROWSET funkce, zadat csv FORMATa nastavit hodnoty 0x0b pro fieldterminator a fieldquote. Pokud potřebujete číst soubory JSON s oddělovači řádků, stačí to. Pokud máte klasický soubor JSON, budete muset nastavit hodnoty 0x0b pro rowterminator. OPENROWSET funkce parsuje JSON a vrátí každý dokument v následujícím formátu:

Doc
{"date_rep":"2020-07-24","day":24,"month":7,"year":2020,"případy":3,"úmrtí":0,"geo_id":"AF"}
{"date_rep":"2020-07-25","den":25;"měsíc":7,"rok":2020,"případy":7,"úmrtí":0,"geo_id":"AF"}
{"date_rep":"2020-07-26","day":26,"month":7,"year":2020,"případy":4,"úmrtí":0,"geo_id":"AF"}
{"date_rep":"2020-07-27","den":27;"měsíc":7,"rok":2020,"případy":8,"úmrtí":0,"geo_id":"AF"}

Pokud je soubor veřejně dostupný nebo pokud má vaše identita Microsoft Entra přístup k tomuto souboru, měli byste vidět obsah souboru pomocí dotazu, jako je ten zobrazený v následujících příkladech.

Čtení souborů JSON

Následující ukázkový dotaz načte soubory JSON a soubory JSON s oddělovači řádků a vrátí každý dokument jako samostatný řádek.

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

Dokument JSON v předchozím ukázkovém dotazu obsahuje pole objektů. Dotaz vrátí každý objekt jako samostatný řádek v sadě výsledků. Ujistěte se, že máte přístup k tomuto souboru. Pokud je váš soubor chráněný klíčem SAS nebo vlastní identitou, budete muset pro přihlášení SQL nastavit přihlašovací údaje na úrovni serveru.

Využití zdroje dat

Předchozí příklad používá úplnou cestu k souboru. Jako alternativu můžete vytvořit externí zdroj dat s umístěním, které odkazuje na kořenovou složku úložiště, a použít tento zdroj dat a relativní cestu k souboru ve OPENROWSET funkci:

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

Pokud je zdroj dat chráněný klíčem SAS nebo vlastní identitou, můžete zdroj dat nakonfigurovat s přihlašovacími údaji v oboru databáze.

V následujících částech se dozvíte, jak dotazovat různé typy souborů JSON.

Parsování dokumentů JSON

Dotazy v předchozích příkladech vrátí každý dokument JSON jako jeden řetězec v samostatném řádku sady výsledků. Funkce JSON_VALUE a OPENJSON parsování hodnot v dokumentech JSON a jejich vrácení jako relačních hodnot, jak je znázorněno v následujícím příkladu:

date_rep	cases	geo_id
2020-07-24	3	AF
2020-07-25	7	AF
2020-07-26	4	AF
2020-07-27	8	AF

Ukázkový dokument JSON

Příklady dotazů čtou soubory JSON obsahující dokumenty s následující strukturou:

{
    "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"
}

Poznámka:

Pokud jsou tyto dokumenty uložené ve formátu JSON s oddělovači řádků, musíte nastavit FIELDTERMINATOR a FIELDQUOTE 0x0b. Pokud máte standardní formát JSON, musíte nastavit ROWTERMINATOR 0x0b.

Dotazování souborů JSON pomocí JSON_VALUE

Následující dotaz ukazuje, jak pomocí JSON_VALUE načíst skalární hodnoty (date_rep, countries_and_territories, cases) z dokumentů JSON:

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

Jakmile extrahujete vlastnosti JSON z dokumentu JSON, můžete definovat aliasy sloupců a volitelně přetypovat textovou hodnotu na určitý typ.

Dotazování souborů JSON pomocí OPENJSON

Následující dotaz používá OPENJSON. Načte statistiky COVID hlášené v Srbsku:

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;

Výsledky jsou funkčně stejné jako výsledky vrácené pomocí JSON_VALUE funkce. V některých případech OPENJSON můžou mít výhody:JSON_VALUE

V klauzuli WITH můžete explicitně nastavit aliasy sloupců a typy pro každou vlastnost. Funkci nemusíte zadávat CAST do všech sloupců v SELECT seznamu.
OPENJSON může být rychlejší, pokud vracíte velký počet vlastností. Pokud vracíte pouze 1–2 vlastnosti, OPENJSON může být tato funkce režijní náklady.
Funkci musíte použít OPENJSON , pokud potřebujete analyzovat pole z každého dokumentu a spojit ho s nadřazeným řádkem.

Další kroky

Další články v této sérii ukazují, jak: