Köra frågor mot JSON-filer med hjälp av en serverlös SQL-pool i Azure Synapse Analytics

  • Artikel

I den här artikeln får du lära dig hur du skriver en fråga med hjälp av en serverlös SQL-pool i Azure Synapse Analytics. Frågans mål är att läsa JSON-filer med OPENROWSET.

  • JSON-standardfiler där flera JSON-dokument lagras som en JSON-matris.
  • Radavgränsade JSON-filer, där JSON-dokument avgränsas med nytt radtecken. Vanliga tillägg för dessa typer av filer är jsonl, ldjsonoch ndjson.

Läsa JSON-dokument

Det enklaste sättet att se innehållet i JSON-filen är att ange fil-URL:en till OPENROWSET funktionen, ange csv FORMAToch ange värden 0x0b för fieldterminator och fieldquote. Om du behöver läsa radavgränsade JSON-filer räcker det. Om du har en klassisk JSON-fil måste du ange värden 0x0b för rowterminator. OPENROWSET funktionen parsar JSON och returnerar varje dokument i följande format:

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

Om filen är offentligt tillgänglig, eller om din Microsoft Entra-identitet kan komma åt den här filen, bör du se innehållet i filen med hjälp av frågan som den som visas i följande exempel.

Läsa JSON-filer

Följande exempelfråga läser JSON- och radavgränsade JSON-filer och returnerar varje dokument som en separat rad.

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

JSON-dokumentet i föregående exempelfråga innehåller en matris med objekt. Frågan returnerar varje objekt som en separat rad i resultatuppsättningen. Kontrollera att du har åtkomst till den här filen. Om filen skyddas med SAS-nyckel eller anpassad identitet måste du konfigurera autentiseringsuppgifter på servernivå för sql-inloggning.

Användning av datakälla

I föregående exempel används en fullständig sökväg till filen. Alternativt kan du skapa en extern datakälla med den plats som pekar på lagringens rotmapp och använda den datakällan och den relativa sökvägen till filen i OPENROWSET funktionen:

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

Om en datakälla skyddas med SAS-nyckel eller anpassad identitet kan du konfigurera datakällan med databasomfångsbegränsade autentiseringsuppgifter.

I följande avsnitt kan du se hur du kör frågor mot olika typer av JSON-filer.

Parsa JSON-dokument

Frågorna i föregående exempel returnerar varje JSON-dokument som en enskild sträng i en separat rad i resultatuppsättningen. Du kan använda funktioner JSON_VALUE och OPENJSON parsa värdena i JSON-dokument och returnera dem som relationsvärden, som det visas i följande exempel:

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

Exempel på JSON-dokument

Frågeexemplen läser json-filer som innehåller dokument med följande struktur:

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

Kommentar

Om dessa dokument lagras som radavgränsad JSON måste du ange FIELDTERMINATOR och FIELDQUOTE 0x0b. Om du har ett JSON-standardformat måste du ange ROWTERMINATOR till 0x0b.

Fråga JSON-filer med hjälp av JSON_VALUE

Frågan nedan visar hur du använder JSON_VALUE för att hämta skalärvärden (date_rep, countries_and_territories, cases) från ett JSON-dokument:

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

När du har extraherat JSON-egenskaper från ett JSON-dokument kan du definiera kolumnalias och eventuellt omvandla textvärdet till någon typ.

Köra frågor mot JSON-filer med OPENJSON

Följande fråga använder OPENJSON. Den hämtar COVID-statistik som rapporteras i Serbien:

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;

Resultaten fungerar på samma sätt som de resultat som returneras med hjälp av JSON_VALUE funktionen. I vissa fall OPENJSON kan det ha en fördel jämfört JSON_VALUEmed :

  • WITH I -satsen kan du uttryckligen ange kolumnalias och typerna för varje egenskap. Du behöver inte placera CAST funktionen i varje kolumn i SELECT listan.
  • OPENJSON kan gå snabbare om du returnerar ett stort antal egenskaper. Om du bara returnerar 1–2 egenskaper OPENJSON kan funktionen vara omkostnader.
  • Du måste använda OPENJSON funktionen om du behöver parsa matrisen från varje dokument och koppla den till den överordnade raden.

Nästa steg

Nästa artiklar i den här serien visar hur du: