Eseguire query sui file JSON usando il pool SQL serverless in Azure Synapse Analytics

Articolo
04/29/2024

Questo articolo spiega come scrivere una query con il pool SQL serverless in Azure Synapse Analytics. L'obiettivo della query è leggere i file JSON usando OPENROWSET.

File JSON standard in cui più documenti JSON sono archiviati come matrice JSON.
File JSON delimitati da righe, in cui i documenti JSON sono separati da caratteri di nuova riga. Le estensioni comuni per questi tipi di file sono jsonl, ldjson e ndjson.

Leggere documenti JSON

Il modo più semplice per visualizzare il contenuto del file JSON consiste nel fornire l'URL del file alla funzione OPENROWSET, specificare csv per FORMAT e impostare i valori 0x0b per fieldterminator e fieldquote. Se è necessario leggere file JSON delimitati da righe, questo è sufficiente. Se è presente un file JSON classico, è necessario impostare i valori 0x0b per rowterminator. La funzione OPENROWSET analizzerà il codice JSON e restituirà ogni documento nel formato seguente:

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

Se il file è disponibile pubblicamente o se la propria identità di Microsoft Entra può accedere a questo file, sarà possibile visualizzarne il contenuto usando una query come quella mostrata negli esempi seguenti.

Leggere file JSON

La query di esempio seguente legge i file JSON e JSON delimitati da riga e restituisce ogni documento come riga separata.

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

Il documento JSON nella query di esempio precedente include una matrice di oggetti. La query restituisce ogni oggetto come riga separata nel set di risultati. Verificare che sia possibile accedere a questo file. Se il file è protetto con una chiave di firma di accesso condiviso o con un'identità personalizzata, potrebbe essere necessario configurare la credenziale a livello di server per l'account di accesso SQL.

Utilizzo dell'origine dati

Nell'esempio precedente viene usato il percorso completo del file. In alternativa, è possibile creare un'origine dati esterna con il percorso che punta alla cartella radice della risorsa di archiviazione e usare tale origine dati e il percorso relativo del file nella funzione OPENROWSET:

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

Se un'origine dati è protetta con una chiave di firma di accesso condiviso o un'identità personalizzata, è possibile configurare l'origine dati con credenziali con ambito database.

Nelle sezioni seguenti si vedrà come eseguire query su diversi tipi di file JSON.

Analizzare documenti JSON

Le query degli esempi precedenti restituiscono tutti i documenti JSON come singola stringa in una riga separata del set di risultati. È possibile usare le funzioni JSON_VALUE e OPENJSON per analizzare i valori nei documenti JSON e restituirli come valori relazionali, come illustrato nell'esempio seguente:

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

Documento JSON di esempio

Negli esempi di query vengono letti file JSON contenenti documenti con la struttura seguente:

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

Nota

Se questi documenti vengono archiviati in formato JSON delimitato da righe, è necessario impostare FIELDTERMINATOR e FIELDQUOTE su 0x0b. Se si usa il formato JSON standard, è necessario impostare ROWTERMINATOR su 0x0b.

Eseguire query sui file JSON usando JSON_VALUE

La query seguente illustra come usare JSON_VALUE per recuperare i valori scalari (date_rep, countries_and_territories, cases) da documenti 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

Dopo aver estratto le proprietà JSON da un documento JSON, è possibile definire alias di colonna ed eventualmente eseguire il cast del valore testuale in un determinato tipo.

Eseguire query sui file JSON usando OPENJSON

Nella query seguente viene usato il comando OPENJSON. La query recupera le statistiche sul COVID relative alla Serbia:

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;

I risultati sono funzionalmente uguali a quelli restituiti con la funzione JSON_VALUE. In alcuni casi, OPENJSON potrebbe essere più vantaggioso rispetto a JSON_VALUE:

Nella clausola WITH è possibile impostare in modo esplicito gli alias di colonna e i tipi per ogni proprietà. Non è necessario inserire la funzione CAST in ogni colonna nell'elenco SELECT.
OPENJSON potrebbe essere più veloce se viene restituito un numero elevato di proprietà. Se vengono restituite solo 1-2 proprietà, la funzione OPENJSON potrebbe essere eccessiva.
È necessario usare la funzione OPENJSON se si vuole analizzare la matrice da ogni documento e aggiungerla alla riga padre.

Passaggi successivi

Negli articoli successivi di questa serie verrà illustrato come: