Query's uitvoeren op JSON-bestanden met behulp van een serverloze SQL-pool in Azure Synapse Analytics

Artikel
10/18/2023

In dit artikel leert u hoe u een query schrijft met behulp van een serverloze SQL-pool in Azure Synapse Analytics. Het doel van de query is om JSON-bestanden te lezen met behulp van OPENROWSET.

Standaard JSON-bestanden waarin meerdere JSON-documenten worden opgeslagen als een JSON-matrix.
Met regels gescheiden JSON-bestanden, waarbij JSON-documenten worden gescheiden door een nieuw regelteken. Algemene extensies voor deze typen bestanden zijn jsonl, ldjsonen ndjson.

JSON-documenten lezen

De eenvoudigste manier om de inhoud van uw JSON-bestand te bekijken, is door de bestands-URL naar de OPENROWSET functie op te geven, CSV FORMATop te geven en waarden 0x0b in te stellen voor fieldterminator en fieldquote. Als u JSON-bestanden met regelscheidingstekens moet lezen, is dit voldoende. Als u een klassiek JSON-bestand hebt, moet u waarden 0x0b instellen voor rowterminator. OPENROWSET de functie parseert JSON en retourneert elk document in de volgende indeling:

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

Als het bestand openbaar beschikbaar is of als uw Microsoft Entra-identiteit toegang heeft tot dit bestand, ziet u de inhoud van het bestand met behulp van de query, zoals in de volgende voorbeelden.

JSON-bestanden lezen

De volgende voorbeeldquery leest JSON- en met regels gescheiden JSON-bestanden en retourneert elk document als een afzonderlijke rij.

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

Het JSON-document in de voorgaande voorbeeldquery bevat een matrix met objecten. De query retourneert elk object als een afzonderlijke rij in de resultatenset. Zorg ervoor dat u toegang hebt tot dit bestand. Als uw bestand is beveiligd met een SAS-sleutel of aangepaste identiteit, moet u referenties op serverniveau instellen voor sql-aanmelding.

Gebruik van gegevensbronnen

In het vorige voorbeeld wordt het volledige pad naar het bestand gebruikt. Als alternatief kunt u een externe gegevensbron maken met de locatie die verwijst naar de hoofdmap van de opslag en die gegevensbron en het relatieve pad naar het bestand in de OPENROWSET functie gebruiken:

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

Als een gegevensbron is beveiligd met een SAS-sleutel of een aangepaste identiteit, kunt u de gegevensbron configureren met referenties binnen het databasebereik.

In de volgende secties ziet u hoe u query's kunt uitvoeren op verschillende typen JSON-bestanden.

JSON-documenten parseren

De query's in de vorige voorbeelden retourneren elk JSON-document als één tekenreeks in een afzonderlijke rij van de resultatenset. U kunt functies JSON_VALUE gebruiken en OPENJSON de waarden parseren in JSON-documenten en deze retourneren als relationele waarden, zoals wordt weergegeven in het volgende voorbeeld:

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

Voorbeeld van JSON-document

In de queryvoorbeelden worden json-bestanden met documenten met de volgende structuur gelezen:

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

Notitie

Als deze documenten zijn opgeslagen als met regels gescheiden JSON, moet u instellen FIELDTERMINATOR en FIELDQUOTE 0x0b. Als u een standaard JSON-indeling hebt, moet u instellen ROWTERMINATOR op 0x0b.

Query's uitvoeren op JSON-bestanden met behulp van JSON_VALUE

In de onderstaande query ziet u hoe u JSON_VALUE kunt gebruiken om scalaire waarden (date_rep, countries_and_territories, cases) op te halen uit JSON-documenten:

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

Zodra u JSON-eigenschappen uit een JSON-document hebt geëxtraheerd, kunt u kolomaliassen definiëren en desgewenst de tekstwaarde naar een bepaald type casten.

Query's uitvoeren op JSON-bestanden met OPENJSON

De volgende query maakt gebruik van OPENJSON. Het haalt COVID-statistieken op die in Servië zijn gerapporteerd:

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;

De resultaten zijn functioneel hetzelfde als de resultaten die worden geretourneerd met behulp van de JSON_VALUE functie. In sommige gevallen kan OPENJSON dit voordeel hebben ten opzichte van JSON_VALUE:

In de WITH component kunt u expliciet de kolomaliassen en de typen voor elke eigenschap instellen. U hoeft de CAST functie niet in elke kolom in SELECT de lijst te plaatsen.
OPENJSON kan sneller zijn als u een groot aantal eigenschappen retourneert. Als u slechts 1-2 eigenschappen retourneert, is de OPENJSON functie mogelijk overhead.
U moet de OPENJSON functie gebruiken als u de matrix uit elk document wilt parseren en deze wilt samenvoegen met de bovenliggende rij.

Volgende stappen

In de volgende artikelen in deze reeks ziet u hoe u het volgende kunt doen:

Share via