Condividi tramite

Eseguire query su file JSON

Si applica a:✅ endpoint di analisi SQL e magazzino dati in Microsoft Fabric

Questo articolo illustra come eseguire query su file JSON usando Fabric SQL, tra cui Fabric Data Warehouse e l'endpoint di analisi SQL.

JSON (JavaScript Object Notation) è un formato leggero per i dati semistrutturati, ampiamente usato in Big Data per flussi di sensori, configurazioni IoT, log e dati geospaziali ( ad esempio GeoJSON).

Usare OPENROWSET per eseguire query sui file JSON direttamente

In Fabric Data Warehouse e nell'endpoint di analisi SQL per un Lakehouse è possibile eseguire query sui file JSON direttamente nel lake usando la OPENROWSET funzione .

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

Quando si eseguono query su file JSON con OPENROWSET, si inizia specificando il percorso del file, che può essere un URL diretto o un pattern con caratteri jolly destinato a uno o più file. Per impostazione predefinita, Fabric proietta ogni proprietà di primo livello nel documento JSON come colonna separata nel set di risultati. Per i file di righe JSON, ogni riga viene considerata come una singola riga, rendendola ideale per gli scenari di streaming.

Se è necessario un maggiore controllo:

  • Usare la clausola facoltativa WITH per definire lo schema in modo esplicito ed eseguire il mapping delle colonne a proprietà JSON specifiche, inclusi i percorsi annidati.
  • Usare DATA_SOURCE per fare riferimento a una posizione radice per i percorsi relativi.
  • Configurare i parametri di gestione degli errori, come MAXERRORS, per gestire correttamente i problemi di analisi.

Casi d'uso comuni di file JSON

Tipi di file JSON comuni e casi d'uso che è possibile gestire in Microsoft Fabric:

  • File JSON delimitati da righe ("righe JSON") in cui ogni riga è un documento JSON autonomo e valido(ad esempio, un evento, una lettura o una voce di log).
    • L'intero file non è necessariamente un singolo documento JSON valido, ma è una sequenza di oggetti JSON separati da caratteri di nuova riga.
    • I file con questo formato in genere hanno estensioni .jsonl, .ldjsono .ndjson. Ideale per gli scenari di streaming e di sola accodamento, gli scrittori possono aggiungere un nuovo evento come nuova riga senza riscrivere il file o interrompere la struttura.
  • File JSON a documento singolo ("JSON classico") con l'estensione .json in cui l'intero file è un documento JSON valido, ovvero un singolo oggetto o una matrice di oggetti (potenzialmente annidati).
    • Viene comunemente usato per la configurazione, gli snapshot e i set di dati esportati in un unico pezzo.
    • Ad esempio, i file GeoJSON in genere archiviano un singolo oggetto JSON che descrive le caratteristiche e le relative geometrie.

Eseguire query su file JSONL con OPENROWSET

Fabric Data Warehouse e l'endpoint di analisi SQL per Lakehouse consentono agli sviluppatori SQL di eseguire query su file JSON Lines (.jsonl, .ldjson, .ndjson) direttamente dal data lake usando la OPENROWSET funzione .

Questi file contengono un oggetto JSON valido per riga, che li rende ideali per scenari di streaming e che consentono solo accodamenti. Per leggere un file di righe JSON, specificare il relativo URL nell'argomento 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'
);

Per impostazione predefinita, OPENROWSET usa l'inferenza dello schema, individuando automaticamente tutte le proprietà di primo livello in ogni oggetto JSON e restituendole come colonne.

È tuttavia possibile definire in modo esplicito lo schema per controllare quali proprietà vengono restituite ed eseguire l'override dei tipi di dati dedotti:

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 definizione esplicita dello schema è utile quando:

  • Si vuole eseguire l'override dei tipi derivati predefiniti, ad esempio per forzare il tipo di dati date anziché varchar.
  • Sono necessari nomi di colonna stabili e proiezione selettiva.
  • Si vuole eseguire il mapping delle colonne a proprietà JSON specifiche, inclusi i percorsi annidati.

Leggere strutture JSON complesse (annidate) con OPENROWSET

Fabric Data Warehouse e l'endpoint di analisi SQL per Lakehouse consentono agli sviluppatori SQL di leggere JSON con oggetti o sottoarray annidati direttamente dal lake usando OPENROWSET.

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

Nell'esempio seguente eseguire una query su un file contenente dati di esempio e usare la WITH clausola per proiettare in modo esplicito le proprietà a livello foglia:

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

Annotazioni

Questo esempio usa un percorso relativo senza un'origine dati, che funziona durante l'esecuzione di query sui file in Lakehouse tramite l'endpoint di analisi SQL. In Fabric Data Warehouse è necessario:

  • Usare un percorso assoluto del file o
  • Specificare un URL radice in un'origine dati esterna e farvi riferimento nell'istruzione OPENROWSET usando l'opzione DATA_SOURCE .

Espandere matrici annidate (DA JSON a righe) con OPENROWSET

Fabric Data Warehouse e l'endpoint di analisi SQL per Lakehouse consentono di leggere i file JSON con matrici annidate usando OPENROWSET. È quindi possibile espandere (annullare) tali matrici usando CROSS APPLY OPENJSON. Questo metodo è utile quando un documento di primo livello contiene un sottoarray che desideri come una riga per elemento.

Nell'input di esempio semplificato seguente, un documento simile a GeoJSON ha una matrice di funzionalità:

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

La query seguente:

  1. Legge il documento JSON dal lake usando OPENROWSET, proiettando la proprietà di tipo di primo livello insieme alla matrice di funzionalità non elaborate.
  2. Si applica CROSS APPLY OPENJSON per espandere la matrice di funzionalità in modo che ogni elemento diventi la propria riga nel set di risultati. All'interno di questa espansione, la query estrae i valori annidati usando espressioni di percorso JSON. I valori come shapeName, shapeISO e geometry, dettagli come geometry.type e coordinates, sono ora colonne piatte per un'analisi più semplice.
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;

Contenuti correlati

  • Last updated on