Freigeben über

Abfragen von JSON-Dateien

Gilt für:✅ SQL-Analyseendpunkt und Warehouse in Microsoft Fabric

In diesem Artikel erfahren Sie, wie Sie JSON-Dateien mit Fabric SQL abfragen, einschließlich Fabric Data Warehouse und dem SQL-Analyseendpunkt.

JSON (JavaScript Object Notation) ist ein einfaches Format für halbstrukturierte Daten, das häufig in Big Data für Sensordatenströme, IoT-Konfigurationen, Protokolle und Geospatialdaten (z. B. GeoJSON) verwendet wird.

Verwenden von OPENROWSET zum direkten Abfragen von JSON-Dateien

In Fabric Data Warehouse und dem SQL-Analyseendpunkt für ein Lakehouse können Sie JSON-Dateien mithilfe der OPENROWSET Funktion direkt im See abfragen.

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

Wenn Sie JSON-Dateien mit OPENROWSET abfragen, geben Sie zunächst den Dateipfad an, bei dem es sich um eine direkte URL oder ein Wildcardmuster handeln kann, das auf eine oder mehrere Dateien ausgerichtet ist. Standardmäßig projiziert Fabric jede Eigenschaft der obersten Ebene im JSON-Dokument als separate Spalte im Resultset. Bei JSON-Zeilendateien wird jede Zeile als einzelne Zeile behandelt und eignet sich ideal für Streamingszenarien.

Wenn Sie mehr Kontrolle benötigen:

  • Verwenden Sie die optionale WITH Klausel, um das Schema explizit zu definieren und Spalten bestimmten JSON-Eigenschaften zuzuordnen, einschließlich geschachtelter Pfade.
  • Verwenden Sie DATA_SOURCE, um auf ein Stammverzeichnis für relative Pfade zu verweisen.
  • Konfigurieren Sie Fehlerbehandlungsparameter wie MAXERRORS, um Parsingprobleme ordnungsgemäß zu verwalten.

Häufige JSON-Dateianwendungsfälle

Allgemeine JSON-Dateitypen und Anwendungsfälle, die Sie in Microsoft Fabric behandeln können:

  • JSON-Dateien mit Zeilentrennzeichen ("JSON-Zeilen"), wobei jede Zeile ein eigenständiges, gültiges JSON-Dokument ist (z. B. ein Ereignis, ein Lesevorgang oder ein Protokolleintrag).
    • Die gesamte Datei ist nicht unbedingt ein einziges gültiges JSON-Dokument, sondern eine Sequenz von JSON-Objekten, die durch Zeilenumbruchzeichen getrennt sind.
    • Die Dateien mit diesem Format verfügen in der Regel über Erweiterungen .jsonl, .ldjsonoder .ndjson. Ideal für Streaming- und Szenarien mit nur Anhängen, können Schreiber ein neues Ereignis als neue Zeile anfügen, ohne die Datei neu zu schreiben oder die Struktur zu unterbrechen.
  • JSON-Dateien mit einem einzelnen Dokument ("klassisches JSON") mit der .json Erweiterung, wo die gesamte Datei ein gültiges JSON-Dokument ist– also entweder ein einzelnes Objekt oder ein Array von Objekten (potenziell geschachtelt).
    • Es wird häufig für Konfigurationen, Momentaufnahmen und Datensätze verwendet, die im Ganzen exportiert werden.
    • GeoJSON-Dateien speichern z. B. häufig ein einzelnes JSON-Objekt, das Features und deren Geometrien beschreibt.

Abfragen von JSONL-Dateien mit OPENROWSET

Fabric Data Warehouse und der SQL-Analyseendpunkt für Lakehouse ermöglichen SQL-Entwicklern das Abfragen von JSON-Zeilendateien (JSONL, LDJSON, ndjson) direkt vom Data Lake mithilfe der OPENROWSET Funktion.

Diese Dateien enthalten ein gültiges JSON-Objekt pro Zeile, sodass sie ideal für Streaming- und Anfügeszenarien geeignet sind. Um eine JSON Lines-Datei zu lesen, geben Sie die BULK URL im Argument an:

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'
);

Standardmäßig verwendet OPENROWSET Schema-Inferenzen, um automatisch alle Top-Level-Eigenschaften in jedem JSON-Objekt zu ermitteln und sie als Spalten zurückzugeben.

Sie können das Schema jedoch explizit definieren, um zu steuern, welche Eigenschaften zurückgegeben werden, und abgeleitete Datentypen außer Kraft setzen:

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'
);

Explizite Schemadefinition ist nützlich, wenn:

  • Sie möchten standardmäßige abgeleitete Typen außer Kraft setzen (z. B. um den Datumsdatentyp anstelle von Varchar zu erzwingen).
  • Sie benötigen stabile Spaltennamen und selektive Projektion.
  • Sie möchten Spalten bestimmten JSON-Eigenschaften zuordnen, einschließlich geschachtelter Pfade.

Lesen komplexer (geschachtelter) JSON-Strukturen mit OPENROWSET

Fabric Data Warehouse und der SQL-Analyseendpunkt für Lakehouse ermöglichen SQL-Entwicklern, mithilfe von OPENROWSET JSON mit geschachtelten Objekten oder Unterarrays direkt aus dem Data Lake zu lesen.

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

Im folgenden Beispiel fragen Sie eine Datei ab, die Beispieldaten enthält, und verwenden Sie die Klausel, um die WITH Eigenschaften der Blattebene explizit zu projizieren:

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'
  );

Hinweis

In diesem Beispiel wird ein relativer Pfad ohne Datenquelle verwendet, der beim Abfragen von Dateien in Ihrem Lakehouse über seinen SQL-Analyseendpunkt funktioniert. In Fabric Data Warehouse müssen Sie eine der folgenden Aktionen ausführen:

  • Verwenden Sie einen absoluten Pfad zur Datei, oder
  • Geben Sie eine Stamm-URL in einer externen Datenquelle an, und verweisen Sie mithilfe der OPENROWSET Option in der DATA_SOURCE Anweisung darauf.

Erweitern geschachtelter Arrays (JSON zu Zeilen) mit OPENROWSET

Mit Fabric Data Warehouse und dem SQL-Analyseendpunkt des Lakehouse können Sie JSON-Dateien mit geschachtelten Arrays mithilfe von OPENROWSET lesen. Anschließend können Sie diese Arrays aufklappen (auseinanderziehen), indem Sie CROSS APPLY OPENJSON verwenden. Diese Methode ist nützlich, wenn ein Dokument auf oberster Ebene ein Unterarray enthält, das pro Element als eine Zeile verwendet werden soll.

Im folgenden vereinfachten Beispieleingabe verfügt ein GeoJSON-ähnliches Dokument über ein Featurearray:

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

Die folgende Abfrage:

  1. Liest das JSON-Dokument mithilfe von OPENROWSET aus dem Lake und projiziert die Typeigenschaft der obersten Ebene zusammen mit dem Array der rohen Features.
  2. Wendet CROSS APPLY OPENJSON an, um das Feature-Array zu erweitern, sodass jedes Element zu einer eigenen Zeile im Ergebnisdatensatz wird. In dieser Erweiterung extrahiert die Abfrage geschachtelte Werte mithilfe von JSON-Pfadausdrücken. Werte wie shapeName, shapeISOund geometry Details wie geometry.type und coordinates, sind jetzt flache Spalten zur einfacheren 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;

Verwandte Inhalte

  • Last updated on