Partager via

Interroger des fichiers JSON

S’applique à :✅ point de terminaison pour les analyses SQL et entrepôt de données dans Microsoft Fabric

Dans cet article, vous allez apprendre à interroger des fichiers JSON à l’aide de Fabric SQL, notamment Fabric Data Warehouse et le point de terminaison d’analytique SQL.

JSON (JavaScript Object Notation) est un format léger pour les données semi-structurées, largement utilisées dans les flux de capteurs, les configurations IoT, les journaux et les données géospatiales (par exemple, GeoJSON).

Utiliser OPENROWSET pour interroger directement des fichiers JSON

Dans Fabric Data Warehouse et le point de terminaison d’analytique SQL d’un Lakehouse, vous pouvez interroger des fichiers JSON directement dans le lac à l’aide de la OPENROWSET fonction.

OPENROWSET( BULK '{{filepath}}', [ , <options> ... ])
[ WITH ( <column schema and mappings> ) ];

Lorsque vous interrogez des fichiers JSON avec OPENROWSET, vous commencez par spécifier le chemin du fichier, qui peut être une URL directe ou un modèle générique qui cible un ou plusieurs fichiers. Par défaut, Fabric projette chaque propriété de niveau supérieur dans le document JSON sous la forme d’une colonne distincte dans le jeu de résultats. Pour les fichiers de lignes JSON, chaque ligne est traitée comme une ligne individuelle, ce qui le rend idéal pour les scénarios de streaming.

Si vous avez besoin d’un contrôle supplémentaire :

  • Utilisez la clause facultative WITH pour définir le schéma explicitement et mapper des colonnes à des propriétés JSON spécifiques, y compris des chemins imbriqués.
  • Permet DATA_SOURCE de référencer un emplacement racine pour les chemins relatifs.
  • Configurez les paramètres de gestion des erreurs comme MAXERRORS pour gérer les problèmes d’analyse correctement.

Cas d’usage courants du fichier JSON

Types de fichiers JSON courants et cas d’usage que vous pouvez gérer dans Microsoft Fabric :

  • Fichiers JSON délimités par des lignes (« lignes JSON ») où chaque ligne est un document JSON autonome et valide (par exemple, un événement, une lecture ou une entrée de journal).
    • Le fichier entier n’est pas nécessairement un document JSON valide unique, mais il s’agit d’une séquence d’objets JSON séparés par des caractères de nouvelle ligne.
    • Les fichiers avec ce format ont généralement des extensions .jsonl, .ldjsonou .ndjson. Idéal pour le streaming et les scénarios en mode ajout uniquement, les rédacteurs peuvent ajouter un nouvel événement en tant que nouvelle ligne sans réécrire le fichier ni casser la structure.
  • Fichiers JSON à document unique (« JSON classique ») avec l’extension .json où l’intégralité du fichier est un document JSON valide, soit un seul objet, soit un tableau d’objets (potentiellement imbriqué).
    • Il est couramment utilisé pour la configuration, les instantanés et les jeux de données exportés en un seul morceau.
    • Par exemple, les fichiers GeoJSON stockent généralement un seul objet JSON décrivant les fonctionnalités et leurs géométries.

Interroger des fichiers JSONL avec OPENROWSET

Fabric Data Warehouse et le point de terminaison d’analytique SQL pour Lakehouse permettent aux développeurs SQL d’interroger des fichiers JSON Lines (.jsonl, .ldjson, .ndjson) directement à partir du lac de données à l’aide de la OPENROWSET fonction.

Ces fichiers contiennent un objet JSON valide par ligne, ce qui les rend idéaux pour les scénarios de streaming et d’ajout uniquement. Pour lire un fichier de lignes JSON, fournissez son URL dans l’argument BULK :

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
);

Par défaut, OPENROWSET utilise l’inférence de schéma, découvre automatiquement toutes les propriétés de niveau supérieur dans chaque objet JSON et les retourne en tant que colonnes.

Toutefois, vous pouvez définir explicitement le schéma pour contrôler les propriétés retournées et remplacer les types de données déduits :

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
) WITH (
    country_region VARCHAR(100),
    confirmed INT,
    date_reported DATE '$.updated'
);

La définition de schéma explicite est utile lorsque :

  • Vous souhaitez remplacer les types déduits par défaut (par exemple, pour forcer le type de données de date au lieu de varchar).
  • Vous avez besoin de noms de colonnes stables et de projection sélective.
  • Vous souhaitez mapper des colonnes à des propriétés JSON spécifiques, y compris des chemins imbriqués.

Lire des structures JSON complexes (imbriquées) avec OPENROWSET

Fabric Data Warehouse et le point de terminaison d'analyse SQL pour Lakehouse permettent aux développeurs SQL de lire directement des JSON contenant des objets imbriqués ou des sous-tableaux à partir du lac à l'aide de OPENROWSET.

{
  "type": "Feature",
  "properties": {
    "shapeName": "Serbia",
    "shapeISO": "SRB",
    "shapeID": "94879208B25563984444888",
    "shapeGroup": "SRB",
    "shapeType": "ADM0"
  }
}

Dans l’exemple suivant, interrogez un fichier qui contient des exemples de données et utilisez la WITH clause pour projeter explicitement ses propriétés de niveau feuille :

SELECT
    *
FROM
  OPENROWSET(
    BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
    -- Top-level field
    [type]     VARCHAR(50),
    -- Leaf properties from the nested "properties" object
    shapeName  VARCHAR(200) '$.properties.shapeName',
    shapeISO   VARCHAR(50)  '$.properties.shapeISO',
    shapeID    VARCHAR(200) '$.properties.shapeID',
    shapeGroup VARCHAR(50)  '$.properties.shapeGroup',
    shapeType  VARCHAR(50)  '$.properties.shapeType'
  );

Note

Cet exemple utilise un chemin relatif sans source de données, qui fonctionne lors de l’interrogation de fichiers dans votre Lakehouse via son point de terminaison d’analytique SQL. Dans Fabric Data Warehouse, vous devez :

  • Utiliser un chemin d’accès absolu au fichier ou
  • Spécifiez une URL racine dans une source de données externe et référencez-la dans l’instruction OPENROWSET en utilisant l’option DATA_SOURCE.

Développer des tableaux imbriqués (JSON à lignes) avec OPENROWSET

Fabric Data Warehouse et le point de terminaison d’analytique SQL pour Lakehouse vous permettent de lire des fichiers JSON avec des tableaux imbriqués à l’aide de OPENROWSET. Ensuite, vous pouvez développer et désimbriquer ces tableaux à l’aide de CROSS APPLY OPENJSON. Cette méthode est utile lorsqu'un document de niveau supérieur contient un sous-tableau que l'on souhaite organiser en une ligne par élément.

Dans l’exemple d’entrée simplifié suivant, un document de type GeoJSON comporte un tableau de fonctionnalités :

{
  "type": "FeatureCollection",
  "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "shapeName": "Serbia",
        "shapeISO": "SRB",
        "shapeID": "94879208B25563984444888",
        "shapeGroup": "SRB",
        "shapeType": "ADM0"
      },
      "geometry": {
        "type": "Line",
        "coordinates": [[[19.6679328, 46.1848744], [19.6649294, 46.1870428], [19.6638492, 46.1890231]]]
      }
    }
  ]
}

Requête suivante :

  1. Lit le document JSON à partir du lac à l’aide de OPENROWSET, en projetant la propriété de type de haut niveau avec l'ensemble des fonctionnalités brutes.
  2. Applique CROSS APPLY OPENJSON pour déplier le tableau de fonctions afin que chaque élément devienne sa propre ligne dans le jeu de résultats. Dans cette extension, la requête extrait des valeurs imbriquées à l’aide d’expressions de chemin JSON. Les valeurs telles que shapeName, shapeISO, et geometry ainsi que les détails comme geometry.type et coordinates sont désormais des colonnes simplifiées pour faciliter l’analyse.
SELECT
  r.crs_name,
  f.[type] AS feature_type,
  f.shapeName,
  f.shapeISO,
  f.shapeID,
  f.shapeGroup,
  f.shapeType,
  f.geometry_type,
  f.coordinates
FROM
  OPENROWSET(
      BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
      crs_name    VARCHAR(100)  '$.crs.properties.name', -- top-level nested property
      features    VARCHAR(MAX)  '$.features'             -- raw JSON array
  ) AS r
CROSS APPLY OPENJSON(r.features)
WITH (
  [type]           VARCHAR(50),
  shapeName        VARCHAR(200)  '$.properties.shapeName',
  shapeISO         VARCHAR(50)   '$.properties.shapeISO',
  shapeID          VARCHAR(200)  '$.properties.shapeID',
  shapeGroup       VARCHAR(50)   '$.properties.shapeGroup',
  shapeType        VARCHAR(50)   '$.properties.shapeType',
  geometry_type    VARCHAR(50)   '$.geometry.type',
  coordinates      VARCHAR(MAX)  '$.geometry.coordinates'
) AS f;

Contenu connexe

  • Last updated on