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

• Démarrez un essai gratuit Fabric.
• Assistant Migration pour Fabric Data Warehouse.

Dans cet article, vous allez apprendre à é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 à l’aide d’OPENROWSET.

• Fichiers JSON standard où plusieurs documents JSON sont stockés sous forme de 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, ldjsonet ndjson.

Lire des documents JSON

Le moyen le plus simple de voir le contenu de votre fichier JSON consiste à fournir l’URL du fichier à la OPENROWSET fonction, à spécifier csv FORMATet à définir des valeurs 0x0b pour fieldterminator et fieldquote. Si vous avez besoin de lire des fichiers JSON délimités par des lignes, cela suffit. Si vous avez un fichier JSON classique, vous devez définir des valeurs 0x0b pour rowterminator. OPENROWSET la fonction analyse JSON et retourne chaque document au format suivant :

Si le fichier est disponible publiquement ou si votre identité de Microsoft Entra peut accéder à ce fichier, vous devez voir le contenu du fichier à l’aide de la requête comme celle illustrée dans les exemples suivants.

Lire des fichiers JSON

L’exemple de requête suivant lit les fichiers JSON et JSON délimités par ligne et retourne chaque document sous forme de 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 inclut un tableau d’objets. La requête retourne chaque objet sous forme de 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é SAP ou une identité personnalisée, vous devez configurer les informations d’identification au niveau du serveur pour la connexion sql.

Utilisation de la source de données

L’exemple précédent utilise le chemin d’accès complet au 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 OPENROWSET fonction :

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 avec une clé SAP ou une identité personnalisée, vous pouvez configurer la source de données avec des informations d’identification délimitées à 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 dans les exemples précédents retournent chaque document JSON sous forme de chaîne unique dans une ligne distincte du jeu de résultats. Vous pouvez utiliser des fonctions JSON_VALUE et OPENJSON analyser les valeurs dans les documents JSON et les retourner en tant que valeurs relationnelles, comme indiqué dans l’exemple suivant :

Exemple de document JSON

Les exemples de requête lisent des 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"
}

Interroger des fichiers JSON à l’aide de JSON_VALUE

La requête ci-dessous vous 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 des propriétés JSON à partir 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 d’OPENJSON

La requête suivante utilise OPENJSON. Il va récupérer 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 aux résultats retournés à l’aide de la JSON_VALUE fonction. Dans certains cas, OPENJSON peut avoir un avantage sur JSON_VALUE:

• Dans la WITH clause, vous pouvez définir explicitement les alias de colonne et les types pour chaque propriété. Vous n’avez pas besoin de placer la CAST fonction dans chaque colonne de la SELECT liste.
• OPENJSON peut être plus rapide si vous renvoyez un grand nombre de propriétés. Si vous retournez seulement 1 à 2 propriétés, la OPENJSON fonction peut être une surcharge.
• Vous devez utiliser la OPENJSON fonction 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 montrent comment :

• Interrogation de dossiers et de plusieurs fichiers
• Créer et utiliser des vues