Interroger des fichiers JSON à l’aide d’un pool SQL serverless dans Azure Synapse Analytics

Article
04/27/2023

Cet article explique comment écrire une requête à l’aide d’un pool SQL serverless dans Azure Synapse Analytics. L’objectif de la requête est de lire des fichiers JSON avec OPENROWSET.

Fichiers JSON standard où plusieurs documents JSON sont stockés sous la forme d’un tableau JSON.
Fichiers JSON délimités par des lignes où les documents JSON sont séparés par un caractère de nouvelle ligne. Les extensions courantes pour ces types de fichiers sont jsonl, ldjson et ndjson.

Lire les documents JSON

Le moyen le plus simple d’afficher le contenu de votre fichier JSON consiste à fournir l’URL du fichier à la fonction OPENROWSET, à spécifier le FORMAT CSV et à définir des valeurs 0x0b pour fieldterminator et fieldquote. Si vous devez lire des fichiers JSON délimités par des lignes, cela est suffisant. Si vous avez un fichier JSON classique, vous devez définir des valeurs 0x0b pour rowterminator. La fonction OPENROWSET analyse JSON et retourne tous les documents au format suivant :

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

Si le fichier est disponible publiquement ou si votre identité Microsoft Entra peut y accéder, vous devez voir le contenu du fichier en utilisant une requête comme celle montrée dans les exemples suivants.

Lire des fichiers JSON

L’exemple de requête suivant lit des fichiers JSON et des fichiers JSON et les fichiers JSON délimités par des lignes et retourne chaque document sous la forme d’une ligne distincte.

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

Le document JSON de l’exemple de requête précédent comprend un tableau d’objets. La requête retourne chaque objet sous la forme d’une ligne distincte dans le jeu de résultats. Assurez-vous que vous pouvez accéder à ce fichier. Si votre fichier est protégé par une clé SAS ou une identité personnalisée, vous devez configurer les informations d’identification au niveau du serveur pour la connexion SQL.

Utilisation d’une source de données

L’exemple précédent utilise le chemin complet du fichier. Vous pouvez également créer une source de données externe avec l’emplacement qui pointe vers le dossier racine du stockage et utiliser cette source de données et le chemin relatif du fichier dans la fonction 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

Si une source de données est protégée par une clé SAS ou une identité personnalisée, vous pouvez configurer la source de données avec des informations d’identification dans l’étendue de la base de données.

Dans les sections suivantes, vous pouvez voir comment interroger différents types de fichiers JSON.

Analyser des documents JSON

Les requêtes des exemples précédents renvoient chaque document JSON sous la forme d’une chaîne unique dans une ligne distincte du jeu de résultats. Vous pouvez utiliser les fonctions JSON_VALUE et OPENJSON pour analyser les valeurs des documents JSON et les retourner en tant que valeurs relationnelles, comme le montre l’exemple suivant :

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

Exemple de document JSON

Les exemples de requêtes lisent les fichiers json contenant des documents avec la structure suivante :

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

Notes

Si ces documents sont stockés sous forme de fichiers JSON délimités par des lignes, vous devez définir FIELDTERMINATOR et FIELDQUOTE sur 0x0b. Si vous avez un format JSON standard, vous devez définir ROWTERMINATOR sur 0x0b.

Interroger des fichiers JSON à l’aide de JSON_VALUE

La requête ci-dessous montre comment utiliser JSON_VALUE pour récupérer des valeurs scalaires (date_rep, countries_and_territories, cases) à partir d'un document 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

Une fois que vous avez extrait les propriétés JSON d'un document JSON, vous pouvez définir des alias de colonne et éventuellement convertir la valeur textuelle en un certain type.

Interroger des fichiers JSON à l’aide de OPENJSON

La requête suivante utilise OPENJSON. Elle récupère les statistiques de la COVID signalées en Serbie :

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;

Les résultats sont fonctionnellement identiques à ceux renvoyés par la fonction JSON_VALUE. Dans certains cas, OPENJSON peut avoir un avantage sur JSON_VALUE :

Dans la clause WITH, vous pouvez explicitement définir les alias de colonne et les types de chaque propriété. Vous n'avez pas besoin de placer la fonction CAST dans chaque colonne de la liste SELECT.
OPENJSON peut être plus rapide si vous renvoyez un grand nombre de propriétés. Si vous ne renvoyez que 1 ou 2 propriétés, la fonction OPENJSON risque d'être en surcharge.
Utilisez la fonction OPENJSON si vous devez analyser le tableau à partir de chaque document et le joindre à la ligne parente.

Étapes suivantes

Les articles suivants de cette série vont vous montrer comment exécuter les tâches suivantes :