Delen via

Query's uitvoeren op JSON-bestanden

Van toepassing op:✅ SQL Analytics-eindpunt en -magazijn in Microsoft Fabric

In dit artikel leert u hoe u query's uitvoert op JSON-bestanden met Fabric SQL, waaronder Fabric Data Warehouse en het SQL-analyse-eindpunt.

JSON (JavaScript Object Notation) is een lichtgewicht indeling voor semi-gestructureerde gegevens, veel gebruikt in big data voor sensorstromen, IoT-configuraties, logboeken en georuimtelijke gegevens (bijvoorbeeld GeoJSON).

OPENROWSET gebruiken om rechtstreeks query's uit te voeren op JSON-bestanden

In Fabric Data Warehouse en het SQL-analyse-eindpunt voor een Lakehouse kunt u rechtstreeks in de lake query's uitvoeren op JSON-bestanden met behulp van de OPENROWSET functie.

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

Wanneer u query's uitvoert op JSON-bestanden met OPENROWSET, geeft u eerst het bestandspad op. Dit kan een directe URL of een jokertekenpatroon zijn dat gericht is op een of meer bestanden. Fabric projecteert standaard elke eigenschap op het hoogste niveau in het JSON-document als een afzonderlijke kolom in de resultatenset. Voor JSON-lijnenbestanden wordt elke regel behandeld als een afzonderlijke rij, waardoor deze ideaal is voor streamingscenario's.

Als u meer controle nodig hebt:

  • Gebruik de optionele WITH component om het schema expliciet te definiëren en kolommen toe te wijzen aan specifieke JSON-eigenschappen, inclusief geneste paden.
  • Gebruik DATA_SOURCE om te verwijzen naar een hoofdlocatie voor relatieve paden.
  • Configureer parameters voor foutafhandeling, zoals MAXERRORS, om parseringsproblemen op een elegante manier te beheren.

Veelvoorkomende gebruiksvoorbeelden voor JSON-bestanden

Algemene JSON-bestandstypen en gebruiksvoorbeelden die u in Microsoft Fabric kunt verwerken:

  • JSON-bestanden met scheidingstekens ('JSON-regels') waarbij elke regel een zelfstandig, geldig JSON-document is (bijvoorbeeld een gebeurtenis, een leesbewerking of een logboekvermelding).
    • Het hele bestand is niet noodzakelijkerwijs één geldig JSON-document. Het is een reeks JSON-objecten, gescheiden door nieuweregeltekens.
    • De bestanden met deze indeling hebben meestal extensies .jsonl, .ldjsonof .ndjson. Ideaal voor streaming en alleen-bijvoegen scenario's waarin schrijvers een nieuwe gebeurtenis als een nieuwe regel kunnen toevoegen, zonder het bestand te herschrijven of de structuur te breken.
  • JSON-bestanden met één document (klassiek JSON) met de .json extensie waarbij het hele bestand één geldig JSON-document is of één object of een matrix met objecten (mogelijk genest).
    • Het wordt vaak gebruikt voor configuratie, momentopnamen en gegevenssets die in één stuk worden geëxporteerd.
    • GeoJSON-bestanden slaan bijvoorbeeld vaak één JSON-object op waarin functies en hun geometrieën worden beschreven.

Query's uitvoeren op JSONL-bestanden met OPENROWSET

Fabric Data Warehouse en het SQL-analyse-eindpunt voor Lakehouse stellen SQL-ontwikkelaars in staat om query's uit te voeren op JSON Lines-bestanden (.jsonl, .ldjson, .ndjson) rechtstreeks vanuit de data lake met behulp van de OPENROWSET functie.

Deze bestanden bevatten één geldig JSON-object per regel, waardoor ze ideaal zijn voor het streamen en alleen toevoegen van scenario's. Als u een JSON Lines-bestand wilt lezen, geeft u de URL op in het BULK argument:

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

OPENROWSET Standaard wordt schemadeductie gebruikt, automatisch alle eigenschappen op het hoogste niveau in elk JSON-object detecteren en als kolommen geretourneerd.

U kunt echter expliciet het schema definiëren om te bepalen welke eigenschappen worden geretourneerd en om uitgestelde gegevenstypen te overschrijven:

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

Expliciete schemadefinitie is handig wanneer:

  • U wilt standaard uitgestelde typen overschrijven (bijvoorbeeld om het datumgegevenstype af te dwingen in plaats van varchar).
  • U hebt stabiele kolomnamen en selectieve projectie nodig.
  • U wilt kolommen toewijzen aan specifieke JSON-eigenschappen, inclusief geneste paden.

Complexe (geneste) JSON-structuren lezen met OPENROWSET

Met Fabric Data Warehouse en het SQL-analyse-eindpunt voor Lakehouse kunnen SQL-ontwikkelaars JSON met geneste objecten of subarray's rechtstreeks vanuit het meer lezen met behulp van OPENROWSET.

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

Voer in het volgende voorbeeld een query uit op een bestand dat voorbeeldgegevens bevat en gebruik de WITH component om expliciet de eigenschappen op bladniveau te projecteren:

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

Opmerking

In dit voorbeeld wordt een relatief pad zonder gegevensbron gebruikt. Dit werkt bij het uitvoeren van query's op bestanden in uw Lakehouse via het SQL-analyse-eindpunt. In Fabric Data Warehouse moet u het volgende doen:

  • Gebruik een absoluut pad naar het bestand of
  • Geef een hoofd-URL op in een externe gegevensbron en verwijs ernaar in de OPENROWSET instructie met behulp van de DATA_SOURCE optie.

Geneste matrices (JSON uitbreiden naar rijen) met OPENROWSET

Met Fabric Data Warehouse en het SQL-analyse-eindpunt voor Lakehouse kunt u JSON-bestanden lezen met geneste matrices met behulp van OPENROWSET. Vervolgens kunt u deze arrays 'unnesten' en uitbreiden met behulp van CROSS APPLY OPENJSON. Deze methode is handig wanneer een document op het hoogste niveau een submatrix bevat die u wilt gebruiken als één rij per element.

In de volgende, vereenvoudigde voorbeeldinvoer heeft een GeoJSON-achtig document een matrix met functies:

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

De volgende query:

  1. Leest het JSON-document uit het meer met behulp van OPENROWSET, waarbij het attribuut van het bovenste niveau type wordt geprojecteerd samen met de array van onbewerkte kenmerken.
  2. Past CROSS APPLY OPENJSON toe om de kenmerkenarray uit te vouwen, zodat elk element een eigen rij in de resultatenset wordt. Bij deze uitbreiding extraheert de query geneste waarden met behulp van JSON-padexpressies. Waarden zoals shapeName, shapeISOen geometry details zoals geometry.type en coordinates, zijn nu platte kolommen voor eenvoudigere 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;

Verwante inhoud

  • Last updated on