JSON-fájlok lekérdezése

A következőkre vonatkozik:✅ SQL Analytics-végpont és -raktár a Microsoft Fabricben

Ebből a cikkből megtudhatja, hogyan kérdezhet le JSON-fájlokat a Fabric SQL használatával, beleértve a Fabric Data Warehouse-t és az SQL Analytics-végpontot.

A JSON (JavaScript Object Notation) egy egyszerűsített formátum félig strukturált adatokhoz, amelyeket széles körben használnak a big data-ban érzékelőstreamekhez, IoT-konfigurációkhoz, naplókhoz és térinformatikai adatokhoz (például GeoJSON).

JSON-fájlok közvetlen lekérdezése az OPENROWSET használatával

A Fabric Data Warehouse-ban és a Lakehouse SQL Analytics-végpontjában a függvény használatával OPENROWSET közvetlenül a tóban kérdezhet le JSON-fájlokat.

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

Amikor JSON-fájlokat kérdez le AZ OPENROWSET használatával, először adja meg a fájl elérési útját, amely lehet közvetlen URL-cím vagy helyettesítő minta, amely egy vagy több fájlt céloz meg. Alapértelmezés szerint a Fabric a JSON-dokumentum minden legfelső szintű tulajdonságát külön oszlopként jeleníti meg az eredményhalmazban. A JSON Lines-fájlok esetében minden sor külön sorként van kezelve, így ideális a streamelési forgatókönyvekhez.

Ha további vezérlésre van szüksége:

  • Az opcionális WITH záradék használatával explicit módon definiálhatja a sémát, és oszlopokat képezhet le adott JSON-tulajdonságokra, beleértve a beágyazott útvonalakat is.
  • A relatív elérési utak gyökérhelyére való hivatkozásra használható DATA_SOURCE .
  • Konfigurálja a hibakezelési paramétereket, mint például MAXERRORS, hogy elegánsan kezelje az elemzési problémákat.

Gyakori JSON-fájlhasználati esetek

A Microsoft Fabricben kezelhető gyakori JSON-fájltípusok és használati esetek:

  • Vonallal tagolt JSON-fájlok ("JSON-vonalak"), ahol minden sor önálló, érvényes JSON-dokumentum (például esemény, olvasás vagy naplóbejegyzés).
    • A teljes fájl nem feltétlenül egyetlen érvényes JSON-dokumentum, hanem JSON-objektumok sorozata, amelyeket újvonalas karakterek választanak el egymástól.
    • Az ilyen formátumú fájloknak általában .jsonl, .ldjson vagy .ndjson kiterjesztése van. Streameléshez és csak hozzáfűző forgatókönyvekhez ideális - az írók új eseményeket adhatnak hozzá új sorokként anélkül, hogy a fájlt újraírnák vagy megtörnék a struktúrát.
  • Egydokumentumos JSON-fájlok ("klasszikus JSON"), amelyek kiterjesztésével .json a teljes fájl egy érvényes JSON-dokumentum, egyetlen objektum vagy objektumtömb (esetleg beágyazott).
    • Általában egy darabban exportált konfigurációkhoz, pillanatképekhez és adatkészletekhez használják.
    • A GeoJSON-fájlok például általában egyetlen JSON-objektumot tárolnak, amely leírja a funkciókat és geometriáikat.

JSONL-fájlok lekérdezése OPENROWSET használatával

A Fabric Data Warehouse és a Lakehouse SQL Analytics-végpontja lehetővé teszi, hogy az SQL-fejlesztők a függvény használatával OPENROWSET közvetlenül a data lake-ből kérdezhessék le a JSON Lines-fájlokat (.jsonl, .ldjson, .ndjson).

Ezek a fájlok soronként egy érvényes JSON-objektumot tartalmaznak, így ideálisak a streameléshez és a hozzáfűzéshez. JSON Lines-fájl olvasásához adja meg az URL-címét az BULK argumentumban:

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

Alapértelmezés szerint OPENROWSET sémakövetkeztetést használ, automatikusan felderíti az egyes JSON-objektumok összes legfelső szintű tulajdonságát, és oszlopként adja vissza őket.

Azonban kifejezetten meghatározhatja a sémát, hogy szabályozza a visszaadott tulajdonságokat, és felülírja a levezetett adattípusokat.

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

Az explicit sémadefiníció akkor hasznos, ha:

  • Felül szeretné bírálni az alapértelmezett kikövetkeztetett típusokat (például a dátum adattípust szeretné kényszeríteni varchar helyett).
  • Stabil oszlopnevekre és szelektív vetítésre van szükség.
  • Az oszlopokat adott JSON-tulajdonságokhoz szeretné hozzárendelni, beleértve a beágyazott elérési utakat is.

Összetett (beágyazott) JSON-struktúrák olvasása OPENROWSET használatával

A Fabric Data Warehouse és a Lakehouse SQL Analytics-végpontja lehetővé teszi, hogy az SQL-fejlesztők közvetlenül a tóból OPENROWSETbeágyazott objektumokkal vagy alarrákkal olvassák a JSON-t.

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

Az alábbi példában lekérdezhet egy mintaadatokat tartalmazó fájlt, és a WITH záradék használatával explicit módon kivetítheti a levélszintű tulajdonságait:

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

Megjegyzés:

Ez a példa egy adatforrás nélküli relatív elérési utat használ, amely akkor működik, ha a Lakehouse-ban lévő fájlokat az SQL Analytics-végponton keresztül kérdezi le. A Fabric Data Warehouse-ban a következőket kell tennie:

  • A fájl abszolút elérési útjának használata, vagy
  • Adjon meg egy gyökér URL-címet egy külső adatforrásban, és hivatkozzon rá az OPENROWSET utasításban a DATA_SOURCE beállítás használatával.

Beágyazott JSON-tömbök sorokká alakítása az OPENROWSET segítségével

A Fabric Data Warehouse és a Lakehouse SQL Analytics-végpontja lehetővé teszi a beágyazott tömbökkel rendelkező JSON-fájlok olvasását a használatával OPENROWSET. Ezután kibonthatja ezeket a tömböket a CROSS APPLY OPENJSON használatával. Ez a módszer akkor hasznos, ha egy legfelső szintű dokumentum elemenként egy sorként kívánt altömböt tartalmaz.

Az alábbi, egyszerűsített példabemenetben egy GeoJSON-szerű dokumentum tartalmaz egy funkciótömböt:

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

A következő lekérdezés:

  1. Beolvassa a JSON-dokumentumot a tóból OPENROWSETa felső szintű típustulajdonság és a nyers funkciók tömbjének vetítésével.
  2. A funkciók tömbjének kibontására alkalmazza CROSS APPLY OPENJSON, így minden elem önálló sorba kerül az eredményben. Ebben a bővítésben a lekérdezés JSON-elérési útkifejezések használatával nyeri ki a beágyazott értékeket. Az olyan értékek, mint shapeName, shapeISO, és geometry, valamint a részletek, mint geometry.type és coordinates, mostantól síkoszlopként jelennek meg a könnyebb elemzés érdekében.
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;

Kapcsolódó tartalom

  • Last updated on