Usare dati JSON in pool SQL

  • 9 minuti

I pool SQL dedicati di Synapse supportano l'archiviazione di dati in formato JSON usando colonne di tabella NVARCHAR standard. Il formato JSON consente di rappresentare strutture di dati complesse o gerarchiche come tabelle. Permette di trasformare le matrici di oggetti JSON in formato tabella. Le prestazioni dei dati JSON possono essere ottimizzate usando indici columnstore e tabelle ottimizzate per la memoria.

Inserimento di dati JSON: è possibile inserire i dati JSON usando le consuete istruzioni T-SQL INSERT.

Lettura di dati JSON: è possibile leggere i dati JSON usando le funzioni T-SQL seguenti ed eseguire operazioni di aggregazione e filtro sui valori JSON.

  • ISJSON: verifica se il testo è in formato JSON valido
  • JSON_VALUE: estrae un valore scalare da una stringa JSON
  • JSON_QUERY: estrae una matrice o un oggetto JSON da una stringa JSON

Modifica di dati JSON: è possibile modificare ed eseguire query sui dati JSON usando le funzioni T-SQL seguenti, che consentono di aggiornare la stringa JSON usando T-SQL e di convertire dati gerarchici in una struttura tabellare piatta.

  • JSON_MODIFY: modifica un valore in una stringa JSON
  • OPENJSON: converte una raccolta JSON in un set di righe e colonne

È anche possibile eseguire query su file JSON usando SQL serverless. L'obiettivo della query è leggere il tipo di file JSON seguente 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 di 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 il formato CSV con la funzione FORMATe impostare i valori 0x0b per le variabili fieldterminator e fieldquote. Se è necessario leggere file JSON delimitati da righe, questo è sufficiente. Nel caso di un file JSON classico, è necessario impostare i valori 0x0b per rowterminator. La funzione OPENROWSET analizzerà il contenuto JSON e restituirà tutti i documenti nel formato seguente:

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

Le due query di esempio seguenti leggono i file JSON e i file JSON delimitati da righe (. JSONL). La prima query legge un file JSON delimitato da righe e l'esempio è riportato di seguito.

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

La prima query legge un file JSON standard e l'esempio è riportato di seguito.

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

Questo secondo esempio legge un file JSON classico:

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 da una chiave di firma di accesso condiviso o un'identità personalizzata, sarà necessario configurare le credenziali a livello di server per l'account di accesso SQL

Definire un'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 quell'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 mostrato nell'esempio seguente:

ANALIZZARE DOCUMENTI JSON

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

Si tratta di un file JSON che contiene 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 sono archiviati in formato JSON delimitato da righe, è necessario impostare FIELDTERMINATOR e FIELDQUOTE su 0x0b. Se 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 valori scalari, ad esempio titolo e autore, dai documenti JSON:

select 
    JSON_VALUE(doc, '$.date_rep') AS date_reported, 
    JSON_VALUE(doc, '$.countries_and_territories') AS country, 
    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

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;